Merging UnEven Names in Power Query

montyfern

Board Regular
Joined
Oct 12, 2017
Messages
65
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!
 
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.
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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 :


Code:
[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
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0
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

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]
 
Upvote 0
@post#19

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

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top