Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Andy_Lee
Frequent Visitor

Fazzy lookup in Excel and Fuzzy merge in Power BI

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. 

4 REPLIES 4
v-xinruzhu-msft
Community Support
Community Support

Hi @Andy_Lee 

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.

 

 

 

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. 

Hi @Andy_Lee 

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.

 

Andy_Lee
Frequent Visitor

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors