Fazzy lookup in Excel and Fuzzy merge in Power BI

[Anonymous]

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. 

[mckenna_mey]

Hi there! Realize your post was from years ago, but in the hopes of helping anyone who runs into this again in the future.. here are my thoughts: 50% accuracy from fuzzy merge is unfortunately pretty typical when you're matching anything beyond trivial cases.

The issue is that string similarity doesn't equal semantic similarity. "Apple Inc" and "Apple Incorporated" are obviously the same company, but "Apple Inc" and "Applebee's" might score similarly on fuzzy metrics depending on your threshold.

(Full disclosure) I work on a tool that uses LLM-based matching instead of string distance. You get much better accuracy because it actually understands that company name variations, abbreviations, and legal suffixes refer to the same entity. Also gives you a confidence score and explanation for each match so you can audit the ones you're uncertain about.

If you want to try it out free: https://everyrow.io/join

[Anonymous]

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]

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. 

[Anonymous]

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.

[Anonymous]

Another question is that how can I improve the accuracy in PowerBI.