Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: LASTNAME FIRSTNAME Power Query Text replace

  1. #1
    Seeker masterelaichi's Avatar
    Join Date
    Feb 2019
    Posts
    15
    Articles
    0
    Excel Version
    2016

    LASTNAME FIRSTNAME Power Query Text replace



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    33
    Articles
    0
    Excel Version
    Office 365
    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 by pinarello; 2020-03-29 at 11:16 AM.

  3. #3
    Seeker masterelaichi's Avatar
    Join Date
    Feb 2019
    Posts
    15
    Articles
    0
    Excel Version
    2016
    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



  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,436
    Articles
    0
    Excel Version
    Office 365 Subscription
    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"
    Attached Files Attached Files
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    33
    Articles
    0
    Excel Version
    Office 365
    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.
    Last edited by pinarello; 2020-03-29 at 12:46 PM.

  6. #6
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    33
    Articles
    0
    Excel Version
    Office 365
    Addendum: I have to say that the solution of Ali is very clever

  7. #7
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,436
    Articles
    0
    Excel Version
    Office 365 Subscription
    Thanks! There really isn't any need for more than one query to do this.
    Ali
    Enthusiastic self-taught user of MS Excel!

  8. #8
    Seeker masterelaichi's Avatar
    Join Date
    Feb 2019
    Posts
    15
    Articles
    0
    Excel Version
    2016
    Thanks AliGW and Pinarello. It really helped!

  9. #9
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,436
    Articles
    0
    Excel Version
    Office 365 Subscription
    Thanks for letting us know.
    Ali
    Enthusiastic self-taught user of MS Excel!

  10. #10
    Acolyte masterswordz's Avatar
    Join Date
    Apr 2020
    Location
    Philippines
    Posts
    22
    Articles
    0
    Excel Version
    2016

    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"
    Attached Files Attached Files

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •