LASTNAME FIRSTNAME Power Query Text replace

M

masterelaichi

New member
Joined
Feb 27, 2019
Messages
25
Reaction score
0
Points
1
Excel Version(s)
2016
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 UnitColumn1Column 2
Unit 1SIMPSONHomer
Unit 1FLANDERSNed
Unit 1BartSimpson
Unit 2SIMPSONMarge
Unit 2LisaSimpson
Unit 2MaggieSimpson
Unit 3FLANDERSTodd
Unit 3FLANDERSRod


Expected Output

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


Any help would be much appreciated!

Thanks
 
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.
 
Last edited:
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


 
This should do it:

Code: 
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"
 

Attachments

  • PQ The Simpsons AliGW.xlsx
    17.5 KB · Views: 17
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.
 

Attachments

  • xlguru - LASTNAME FIRSTNAME Power Query Text replace (PQ).xlsx
    21.8 KB · Views: 11
Last edited:
Addendum: I have to say that the solution of Ali is very clever
 
Thanks! There really isn't any need for more than one query to do this. :)
 
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"
 

Attachments

  • Swordz_Ans1.xlsx
    16.4 KB · Views: 13
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.
 
I'm just starting to learn PQ. Thanks for the advise, I'll surely remember this going forward.
 
You must log in or register to reply here.
Back
Top