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]

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]

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

 

[sandy666]

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

 

[pinarello]

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]

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]

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]

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]

that is why PBI Desktop is very annoyed

 

[montyfern]

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]

You are welcome

Have a nice day