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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors