Merging UnEven Names in Power Query

[montyfern]

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.

<tbody>
</tbody>

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!

 

[montyfern]

Hey Sandy666, brilliant! Can you help me a tad, I don't know this part of Power Query but yes I have it & use it. How do I get this code in? What I did: created two queries called Table1 and Table2. Merged table2 into table1. Yes, no? When I paste your code into Table1 (wrong one?) in Advanced Editor, it errors out with: "Expression.SyntaxError: Token Eof expected." I have a feeling am really close, just doing something stupid. Thanks EVER so much.​

 

[sandy666]

click Show error in advnced editor and it will show you where it is

maybe try from the begining:

• create Table1 (Ctrl+T)
• create Table2 (Ctrl+T)
• Data - New Query - From Other Sources - Blank Query

it will open Advanced Editor so replace code there with this :

[SIZE=1]let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Capitalize = Table.TransformColumns(Source1,{{"Last", Text.Proper, type text}, {"First", Text.Proper, type text}}),
        Index1 = Table.AddIndexColumn(Capitalize, "Index", 0, 1),
    C2R = Text.ToList("_1234567890"),
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        Clean = Table.TransformColumns(Table.AddColumn(Source2, "Clean", each Text.Trim(Text.Remove([First],C2R))),{{"Clean", Text.Proper, type text}}),
        ROC = Table.SelectColumns(Clean,{"Last", "Clean", "Weighted RCR", "Pubs without RCR ", "Mean RCR", "Median RCR", "No. of PIIDs "}),
        Ren2 = Table.RenameColumns(ROC,{{"Clean", "First"}}),
        Index2 = Table.AddIndexColumn(Ren2, "Index", 0, 1),
    Source3 = Table.NestedJoin(Table1,{"Index"},Table2,{"Index"},"Table2",JoinKind.FullOuter),
        Expand = Table.ExpandTableColumn(Source3, "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 "}),
        IF3.1 = Table.AddColumn(Expand, "Custom", each if [Last] = [Last.1] then [Last] else if [Last] = null then [Last.1] else [Last]),
        IF3.2 = Table.AddColumn(IF3.1, "Custom.1", each if [First] = [First.1] then [First] else if [First] = null then [First.1] else [First]),
        ROC3 = Table.SelectColumns(IF3.2,{"Custom", "Custom.1", "SIC Code", "SIC $", "Weighted RCR", "Pubs without RCR ", "Mean RCR", "Median RCR", "No. of PIIDs "}),
        Result = Table.RenameColumns(ROC3,{{"Custom", "Last"}, {"Custom.1", "First"}})
in
    Result[/SIZE]

should work

remember that if you change structure or any header or replace from lower case to upper case or vice versa it will not work

usually this is NOT copy/paste code so you need to know what are you doing

 

[montyfern]

Hi sandy666,

I've tried & am blind now parsing through code. Am not getting it, so sorry. Can you please look at file & lmk where I went wrong. I was very careful to re-type your code with correct text. The advanced query editor found errors so I fixed. But every time i close & load it says "Expresssion.Error: a cyclic reference was encountered during evaluation." Can you help one more time please?
Copy of Dummy Data (1).xlsx

 

[sandy666]

but your file is not shared for anyone

 

[montyfern]

but your file is not shared for anyone

Let me start all over again. Think that's the best idea. I find this thread hard to follow sometimes & missed a few things. Failing this, do you have an email account? Thanks so much.

 

[sandy666]

do you have an email account?

I don't think you need my email.
All can be done just here, on the forum

 

[montyfern]

Hi Sandy666,

Started over by renaming Table1, Table2, insert blank query, replacing their source code w/ yours, & get this error:

=Table.RenameColumns(ROC3,{{"Custom", "Last"}, {{"Custom.1", "First"}}) In yellow: !Expression.Error: The import Table1 matches no exports. Did you miss a module reference?

 

[sandy666]

I think there is a typo mistake, PQ is case sensitive so headers of the tables must be exactly like in the code.
Anyway you can check here. There are both ways - with separated codes (post#10) and with single code (post#12)
Because I can't see the workbook I can't say more.

 

[pinarello]

Hello,


directly available in the merge of Power BI, but also usable in Power Query of Excel, your problem can be solved very well by using "Table.FuzzyNestedJoin".


Here is a link to my OneDrive workbook

And here the M-code

[TABLE="width: 80"]
 <colgroup><col width="80" style="width:60pt"> </colgroup><tbody>[TR]
  [TD="width: 80"]let[/TD]
 [/TR]
 [TR]
  [TD]     Source = Table.FuzzyNestedJoin(Tabelle1, {"Last",  "First"},[/TD]
 [/TR]
 [TR]
  [TD]        Tabelle245, {"Last", "First"},  "Tabelle24", JoinKind.FullOuter,[/TD]
 [/TR]
 [TR]
  [TD]         [IgnoreCase=true, IgnoreSpace=true, Threshold=0.7]),[/TD]
 [/TR]
 [TR]
  [TD]     Expanded_Tabelle24 = Table.ExpandTableColumn(Source,  "Tabelle24", {"Last", "First", "Weighted  RCR", "Pubs without RCR ", "Mean RCR", "Median  RCR", "No. of PIIDs "}, {"Tabelle24.Last",  "Tabelle24.First", "Tabelle24.Weighted RCR",  "Tabelle24.Pubs without RCR ", "Tabelle24.Mean RCR",  "Tabelle24.Median RCR", "Tabelle24.No. of PIIDs "})[/TD]
 [/TR]
 [TR]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]in[/TD]
 [/TR]
 [TR]
  [TD]     Expanded_Tabelle24[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

@post#19

remember that your M code should work on all PQ versions, even in 2010/2013 with PQ add-ins