Page 1 of 3 123 LastLast
Results 1 to 10 of 30

Thread: Merging UnEven Names in Power Query

  1. #1
    New Member montyfern's Avatar
    Join Date
    Oct 2017
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Merging UnEven Names in Power Query

    Greetings!

    Would anyone out there be so kind as to tell me how to merge uneven names?
    For example:
    File1 has 3000 rows or names.
    File2 has 4000 rows of names. Customer wants the last name and all its' records to merge to one file but there's an uneven amount of names like this:

    Attached are two tables. Not including the heading, File-Summary contains 3042 records, and File-Unique contains 2658 records. Basically, Iím trying to combine both sheets into a 3 sheet by Last Name and First Name. However, the names may not be in the same forms, although itís the same person. Can excel match in this type of situation?

    Examples.

    Last First
    PAT_Summary sheet 5-6-19 Adu-Mohsen
    Pat_Unique PIs 5-6-19 ADU MOHSEN
    PAT_Summary sheet 5-6-19 Ala Le G
    Pat_Unique PIs 5-6-19 ALA'LE G.


    I've tried merging, appending, merging columns, custom columns, importing from a folder so I can expand/combine/load/edit but am failing. Maybe this is vlookup territory? Please don't say macros...not real great with those.

    Many thanks!

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,010
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Merging UnEven Names in Power Query

    post link(s) to representative example shared files and expected result.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  3. #3
    New Member montyfern's Avatar
    Join Date
    Oct 2017
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merging UnEven Names in Power Query

    Dear sandy666, will do. Asked client for requisite file; please stay tuned. Do you want me to email you or use DropBox? Thanks!

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,010
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Merging UnEven Names in Power Query

    Dropbox, Onedrive, Googledrive , whatever you want
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  5. #5
    New Member montyfern's Avatar
    Join Date
    Oct 2017
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merging UnEven Names in Power Query


  6. #6
    New Member montyfern's Avatar
    Join Date
    Oct 2017
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merging UnEven Names in Power Query


  7. #7
    New Member montyfern's Avatar
    Join Date
    Oct 2017
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merging UnEven Names in Power Query

    Quote Originally Posted by sandy666 View Post
    Dropbox, Onedrive, Googledrive , whatever you want
    Pls. let me know if this link didn't work. Thanks~

  8. #8
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,010
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Merging UnEven Names in Power Query

    links are to the same file and both are ok
    So...
    you want proper last and first name only or with the data?

    btw. I don't use formula but PowerQuery (Get&Transform) so you need to check your excel contain it.
    Last edited by sandy666; May 21st, 2019 at 06:04 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  9. #9
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,010
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Merging UnEven Names in Power Query

    Ops, I was blind about title

    is that what you want?

    Last First SIC Code SIC $ Weighted RCR Pubs without RCR Mean RCR Median RCR No. of PIIDs
    Aaa Bba 003
    551471
    119.4256061
    6
    1.706080088
    1.176092505
    1
    Aab Bbb 001
    274451
    152.7387133
    8
    1.909233916
    1.402388453
    1
    Aac Bbc
    0
    66.35654759
    10
    2.3698767
    1.347082734
    1
    Aad Bbd 003
    1287739
    241.0645792
    10
    2.274194143
    0.670348793
    1
    Abe Jun-Ichi
    169.2167406
    6
    1.581464865
    1.219055414
    1
    Abel Kristina
    0
    0
    1
    Cca Dda 003
    82500
    0
    0
    1
    Ccb Margret 001
    354085
    2.657554865
    0
    1.328777432
    1.328777432
    1
    Ccc Erin 003
    77886
    100.3861262
    4
    1.930502427
    1.621542573
    1
    Ccc Joella 001
    44044
    5.333346404
    4
    0.761906629
    0.339095891
    1
    Ccc Lisa 003
    303688
    15.6703706
    0
    0.746208124
    0.440338343
    1
    Ccc Robert
    0
    1165.528692
    12
    4.332820417
    0.972700894
    2
    Eea Brandon 005
    33184
    13.19227229
    0
    1.319227229
    0.663325042
    1
    Dda Irwin
    0


    next time try to prepare real names not aaa aac etc... I got a squint
    Last edited by sandy666; May 21st, 2019 at 07:07 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  10. #10
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,010
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Merging UnEven Names in Power Query

    I realized that I made a small mistake in the previous table so here is proper table (I hope)

    Last First SIC Code SIC $ Index Weighted RCR Pubs without RCR Mean RCR Median RCR No. of PIIDs
    Aaa Bba 003
    551471
    0
    119.4256061
    6
    1.706080088
    1.176092505
    1
    Aab Bbb 001
    274451
    1
    152.7387133
    8
    1.909233916
    1.402388453
    1
    Aac Bbc
    0
    2
    66.35654759
    10
    2.3698767
    1.347082734
    1
    Aad Bbd 003
    1287739
    3
    241.0645792
    10
    2.274194143
    0.670348793
    1
    Abe Jun-Ichi
    4
    169.2167406
    6
    1.581464865
    1.219055414
    1
    Abel Kristina
    5
    0
    0
    1
    Cca Dda 003
    82500
    6
    0
    0
    1
    Ccb Marge 001
    354085
    7
    2.657554865
    0
    1.328777432
    1.328777432
    1
    Ccc Erin 003
    77886
    8
    100.3861262
    4
    1.930502427
    1.621542573
    1
    Ccc Joella 001
    44044
    9
    5.333346404
    4
    0.761906629
    0.339095891
    1
    Ccc Lisa 003
    303688
    10
    15.6703706
    0
    0.746208124
    0.440338343
    1
    Ccc Robert
    0
    11
    1165.528692
    12
    4.332820417
    0.972700894
    2
    Dda Irwin
    0
    12
    Eea Brandon 005
    33184
    13
    13.19227229
    0
    1.319227229
    0.663325042
    1


    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Last", type text}, {"First", type text}, {"SIC Code", type text}}),
        Capitalize = Table.TransformColumns(#"Changed Type",{{"Last", Text.Proper, type text}, {"First", Text.Proper, type text}}),
        Trim = Table.TransformColumns(Capitalize,{{"Last", Text.Trim, type text}, {"First", Text.Trim, type text}}),
        Index = Table.AddIndexColumn(Trim, "Index", 0, 1)
    in
        Index
    
    // Table2
    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        Type = Table.TransformColumnTypes(Source,{{"Last", type text}, {"First", type text}, {"Weighted RCR", type number}, {"Pubs without RCR ", Int64.Type}, {"Mean RCR", type number}, {"Median RCR", type number}, {"No. of PIIDs ", Int64.Type}}),
        Capitalize = Table.TransformColumns(Type,{{"Last", Text.Proper, type text}, {"First", Text.Proper, type text}}),
        Trim = Table.TransformColumns(Capitalize,{{"Last", Text.Trim, type text}, {"First", Text.Trim, type text}}),
        Index = Table.AddIndexColumn(Trim, "Index", 0, 1)
    in
        Index
    
    // Merge1
    let
        Source = Table.NestedJoin(Table1,{"Index"},Table2,{"Index"},"Table2",JoinKind.FullOuter),
        Expand = Table.ExpandTableColumn(Source, "Table2", {"Last", "First", "Weighted RCR", "Pubs without RCR ", "Mean RCR", "Median RCR", "No. of PIIDs "}, {"Last.1", "First.1", "Weighted RCR", "Pubs without RCR ", "Mean RCR", "Median RCR", "No. of PIIDs "}),
        IF1 = Table.AddColumn(Expand, "Custom", each if [Last] = [Last.1] then [Last] else if [Last] = null then [Last.1] else [Last]),
        IF2 = Table.AddColumn(IF1, "Custom.1", each if [First] = [First.1] then [First] else if [First] = null then [First.1] else [First]),
        RC = Table.RemoveColumns(IF2,{"Last", "First", "Last.1", "First.1"}),
        Extract = Table.TransformColumns(RC, {{"Custom.1", each Text.BeforeDelimiter(_, "_"), type text}}),
        Reorder = Table.ReorderColumns(Extract,{"Custom", "Custom.1", "SIC Code", "SIC $", "Index", "Weighted RCR", "Pubs without RCR ", "Mean RCR", "Median RCR", "No. of PIIDs "}),
        Ren = Table.RenameColumns(Reorder,{{"Custom", "Last"}, {"Custom.1", "First"}})
    in
        Ren
    Last edited by sandy666; May 21st, 2019 at 07:58 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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