Fazzy lookup in Excel and Fuzzy merge in Power BI

Anonymous · ‎09-28-2023

Hello,

I am trying to join two tables using Fuzzy Merge in Power BI. However the result only yield about 50% accuracy. In other words, I can only capture about 50% of the records that are join correctly.

I built these two tables in Excle then use Fuzzy lookup to compare the two tables. Surprising, it has achieved over 90% accuracy. WHat I did was use Fuzzy lookup and identfied the columns from each table to be matched. Then I use "Search" function to compare the output and see if I can find the string from one table in the matched row from another table. For those that achieved more than 85% of similarity, the match was right on.

My questions is does the Fuzzy logic in Excle work the same way as Power BI? I prefer to use Power BI, but I like the results from Excel.

v-xinruzhu-msft · ‎10-01-2023

Hi @Anonymous

1.You can set the similary thresold to improve the similarity, you can refer to the following link.

https://learn.microsoft.com/en-us/power-query/merge-queries-fuzzy-match#fuzzy-matching-options

2.You can create a transform table to improve the similarity, you can refer to the following link.

Fuzzy Match By Transformation Table | Power Query Masterclass EP35 | Get & Transform - YouTube

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous · ‎10-01-2023

Hello,

Thank you for your feedback.

I have tried the 1st solution. But even I creased the similarity score to 90%, it still not yield good results. The Excel was able to do a good Fuzzy match with just 85% similarity. I also notice that my version of Power BI does not have the option to allow me show the similarity score. I wonder if there is a set up I need to do in the Power BI.

For 2nd suggestion, I have a data table of more than 20,000 rows. It will be quite difficult to create a transformation table.

Hope we can find another solution.

Thank you.

v-xinruzhu-msft · ‎10-02-2023

Hi @Anonymous

The default Similarity threshold of power bi is 0.8, it means that it at least need to be matched with 0.8, so to improve the similarity, you can check strings for case, or spaces, convert them, and merge them to improve match similarity. you can refer to the following link.

How fuzzy matching works in Power Query - Power Query | Microsoft Learn

Bing Videos

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.