# Merging UnEven Names in Power Query



## montyfern (May 17, 2019)

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!


----------



## sandy666 (May 18, 2019)

post link(s) to representative example shared files and expected result.


----------



## montyfern (May 20, 2019)

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


----------



## sandy666 (May 20, 2019)

Dropbox, Onedrive, Googledrive , whatever you want


----------



## montyfern (May 21, 2019)

https://netorg834590-my.sharepoint....be3c74c56648c3a6cab75e33b89ea6&csf=1&e=b1hOls


----------



## montyfern (May 21, 2019)

https://netorg834590-my.sharepoint....NIpsq3XjO4nqYB5OmtbAEx585Sdjqc2ubwUw?e=qbWQkx


----------



## montyfern (May 21, 2019)

sandy666 said:


> Dropbox, Onedrive, Googledrive , whatever you want



Pls. let me know if this link didn't work.  Thanks~


----------



## sandy666 (May 21, 2019)

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.


----------



## sandy666 (May 21, 2019)

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 *AaaBba003551471​119.4256061​6​1.706080088​1.176092505​1​AabBbb001274451​152.7387133​8​1.909233916​1.402388453​1​AacBbc0​66.35654759​10​2.3698767​1.347082734​1​AadBbd0031287739​241.0645792​10​2.274194143​0.670348793​1​AbeJun-Ichi169.2167406​6​1.581464865​1.219055414​1​AbelKristina0​0​1​CcaDda00382500​0​0​1​CcbMargret001354085​2.657554865​0​1.328777432​1.328777432​1​CccErin00377886​100.3861262​4​1.930502427​1.621542573​1​CccJoella00144044​5.333346404​4​0.761906629​0.339095891​1​CccLisa003303688​15.6703706​0​0.746208124​0.440338343​1​CccRobert0​1165.528692​12​4.332820417​0.972700894​2​EeaBrandon00533184​13.19227229​0​1.319227229​0.663325042​1​DdaIrwin0​

next time try to prepare real names not aaa aac etc... I got a squint


----------



## sandy666 (May 21, 2019)

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 *AaaBba003551471​0​119.4256061​6​1.706080088​1.176092505​1​AabBbb001274451​1​152.7387133​8​1.909233916​1.402388453​1​AacBbc0​2​66.35654759​10​2.3698767​1.347082734​1​AadBbd0031287739​3​241.0645792​10​2.274194143​0.670348793​1​AbeJun-Ichi4​169.2167406​6​1.581464865​1.219055414​1​AbelKristina5​0​0​1​CcaDda00382500​6​0​0​1​CcbMarge001354085​7​2.657554865​0​1.328777432​1.328777432​1​CccErin00377886​8​100.3861262​4​1.930502427​1.621542573​1​CccJoella00144044​9​5.333346404​4​0.761906629​0.339095891​1​CccLisa003303688​10​15.6703706​0​0.746208124​0.440338343​1​CccRobert0​11​1165.528692​12​4.332820417​0.972700894​2​DdaIrwin0​12​EeaBrandon00533184​13​13.19227229​0​1.319227229​0.663325042​1​


```
[SIZE=1]// 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[/SIZE]
```


----------



## montyfern (May 17, 2019)

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 (May 22, 2019)

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 (May 22, 2019)

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 (May 28, 2019)

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 (May 28, 2019)

but your file is not shared for anyone


----------



## montyfern (May 29, 2019)

sandy666 said:


> 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 (May 29, 2019)

montyfern said:


> do you have an email account?


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


----------



## montyfern (Jun 6, 2019)

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 (Jun 6, 2019)

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 (Jun 6, 2019)

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 (Jun 6, 2019)

@post#19

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


----------



## montyfern (May 17, 2019)

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 (Jun 7, 2019)

ok, thanks to you both.  For you sandy666, the worksheet is protected so I can't see the query. What's the password please?  I think we're really close. However, I can't view the queries due to protection.  But, they're two tables. ?

For the German person, thanks so much!  I tried to run your code but get an error "Expression.Error: The name 'Table.Tabelle24" wasn't recognized. Make sure it's spelled correctly."

Thanks you both!


----------



## montyfern (Jun 7, 2019)

https://1drv.ms/x/s!AopTvf3RGAN5kkAOdQcbf54PUjcr is sandy's file
https://1drv.ms/x/s!AkzPnN34efhMsSqbTEzSMzl0Gut1 is German's link


----------



## sandy666 (Jun 7, 2019)

Expression.Error because your Excel PowerQuery version doesn't support Table.FuzzyNestedJoin() function


----------



## pinarello (Jun 7, 2019)

*Very strange!* That should have been called "Table245". But with me the query ran without problems. But now I have reworked it again and added the optional parameter "NumberOfMatches" for the join and assigned it the value 1.


In the folder I also added a link to the Microsoft documentation.


I hope you can now open the folder and execute the Power Query query without any problems.

Here once more the actual link


----------



## sandy666 (Jun 7, 2019)

Table.Fuzzy* was released in October 2018 and Microsoft still didn't update it for all versions of Excel.
It works for 2019 but not for any previous versions. I am not sure about 365


----------



## pinarello (Jun 7, 2019)

I use 365 and it works. By the way, a few years ago I programmed a fuzzy search myself that also takes synonym lists into account.


----------



## pinarello (Jun 8, 2019)

If the Power Query of your Excel version does not yet support the table join with the fuzzy logic, then there is a very simple way.


Install the free Power BI Desktop version and create the query there. When you have loaded the query, activate the table view, place your cursor in the table and choose the function "Copy table". Now switch to Excel and import the table using Cntrl+V.


Copy and paste is necessary because the free Power BI Desktop version does not offer the possibility to save the query in a format that Excel can read and Excel is not able to read .pbix files.


----------



## sandy666 (Jun 8, 2019)

that is why PBI Desktop is very annoyed


----------



## montyfern (Jun 10, 2019)

Yup, can't use Fuzzy Join.  But @pinarello, I've copied all your info down when my co. updates.  Great to know, & thanks for all your help & hard work! @ sandy666, much obliged.  I'm not sure if she wants two tables but I can handle that.  I too use PBI Desktop & a little Tableau.  Will undoubtedly have more BI questions as my knowledge of DAX & PQ grows. Cheers!  --montyfern


----------



## sandy666 (Jun 10, 2019)

You are welcome

Have a nice day


----------

