LASTNAME FIRSTNAME Power Query Text replace

[masterelaichi]

Hi,

I have a dataset which has a bunch of names in columns - Lastname and First name. As the file that I have got has come from different people, the format they used is inconsistent. The format I need for my purpose is LASTNAME, Firstname (note the case )

However, the file I have received has got the Firstname where the lastname should be and the case used is camelcase. Since I have a list of about 1000 names, is there a way to use PQ Text functions to fix this?

Hopefully, the image below should provide a bit of clarity. I have used names that can be easily identified

What I have

Business Unit Column1 Column 2 Unit 1 SIMPSON Homer Unit 1 FLANDERS Ned Unit 1 Bart Simpson Unit 2 SIMPSON Marge Unit 2 Lisa Simpson Unit 2 Maggie Simpson Unit 3 FLANDERS Todd Unit 3 FLANDERS Rod

Expected Output

Business Unit Column1 Unit 1 SIMPSON, Homer Unit 1 FLANDERS, Ned Unit 1 SIMPSON, Bart Unit 2 SIMPSON, Marge Unit 2 SIMPSON, Lisa Unit 2 SIMPSON, Maggie Unit 3 FLANDERS, Todd Unit 3 FLANDERS, Rodd

Any help would be much appreciated!

Thanks

 

[pinarello]

Hello,

how should the Power Query recognize whether the content in Column1/2 is first or last name? Especially since there are enough names that can be both first and last name.

 

[masterelaichi]

I have sets of data within the same sheet in the excel book. Basically,names are added to the end of the existing list. If I can do it for the small sample, then I think I can manage for the entire list. A quick scan of the list I got confirms that they are mixed up. But there is a consistent pattern. I could group them into "sets" and do it like that

But I was wondering if there is a way to do it by adding a new column. The logic being, if [Column1] contains a non-upper case then swap item in column 1 and 2 else retain item in column1

 

[AliGW]

This should do it:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Business Unit", type text}, {"Column1", type text}, {"Column 2", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "Column1", Splitter.SplitTextByCharacterTransition({"A".."Z"}, {"a".."z"}), {"Column1.1", "Column1.2"}),
    #"Added Conditional Column" = Table.AddColumn(#"Split Column by Character Transition", "Custom", each if [Column1.2] = null then [Column1.1] else [Column 2]),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Column1.2] = null then [Column 2] else [#"Column1 - Copy"]),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column1", "Custom.2", each Text.Upper([Custom])&", "&[Custom.1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1.1", "Column1.2", "Column 2", "Column1 - Copy", "Custom", "Custom.1"})
in
    #"Removed Columns"

 

[pinarello]

Hello,

see here my solution.

Compared to AliGW, I have chosen a different approach.

I prioritize the name in column 1 as family name over the name in column 2, which appeared earlier in the list.

 

[pinarello]

Addendum: I have to say that the solution of Ali is very clever

 

[AliGW]

Thanks! There really isn't any need for more than one query to do this.

 

[masterelaichi]

Thanks AliGW and Pinarello. It really helped!

 

[AliGW]

Thanks for letting us know.

 

[masterswordz]

Based on the DATA you have.

// Table
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Business Unit", type text}, {"Column1", type text}, {"Column 2", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1]=Text.Upper([Column1]) then Text.Upper([Column1]) & ", " & Text.Proper([Column 2]) else Text.Upper([Column 2]) & ", " & Text.Proper([Column1]))
in
#"Added Custom"

 

[Bob Phillips]

Much more elegant solution masterswordz, but I feel that mostr of the text shifting is unnecessary, bar the upshifting of column 2. For instance

if [Column1]=Text.Upper([Column1]) then Text.Upper([Column1])

only needs to be

if [Column1]=Text.Upper([Column1]) then [Column1])​

the if test already ascertained it was upper-case.

 

[masterswordz]

I'm just starting to learn PQ. Thanks for the advise, I'll surely remember this going forward.