Forum Discussion
Fuzzy Match calculation explained
So i thought I knew how Power Query is using the Jaccard similarity to generate a fuzzy match. However I am not getting expeced matches in some instances unless I set the threshold lower to .5
Can anyone explain the exact calculation steps power BI uses to come up with the similarity value? I'm doing a fuzzy merge on colums 1 and 2. See two sample tables below (2 columns separated by ;). I would expect these to match using the default threshold as they only have 2 differences, but they don't match unless i set to .5.
PPID01234 ; john-smith 2A 3H
PPID01234; john-smith 2B 4H
Hi jhamil560 ,
About similarity threshold in fuzzy match, here is the explaination in Power Query editor in Power BI Desktop:
Similarity Threshold:
Indicates how similar two values need to be in order to match. The minimum value of 0.00 causes all values to match each other. The maximum value of 1.00 only allows exact matches. The default value is 0.80.
To check the calculation of this threshold, you can create a table in Power Query online(dataflow) to use cluster values feature, please refer this document which introduces it in details:
How fuzzy matching works in Power Query?
Note:
Currently only the Cluster values feature in Power Query Online will provide a new column with the similarity score.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
The documentation quoted here states: The minimum value of 0.00 causes all values to match each other.
This doesn't actually happen though. Can someone clarify what is meant by this statement or correct the documentation?
jhamil560 Have you read through this article? How fuzzy matching works in Power Query? - Power Query | Microsoft Docs
If you want to "roll your own", you can use this: https://community.powerbi.com/t5/Quick-Measures-Gallery/Fuzzy/m-p/1352914#M608
I did read that yes, but it doesn't explain how they are coming up with the similarity values. That is what I am curious about. I would like to understand the logic being used. When i researched it was shown to be the Jaccard similarity algorithm, which is the length of intersection divided by length of union. But is power query looking at unique words as '1' element in a string? Is it just looking at overall unique list of letters/symbols? I would like to see a step by step example of what Power BI is doing, just to understand it better.
jhamil560 Try creating a concatenated value column in each table and then doing a Fuzzy match based on those single columns. Perhaps using multiple columns is causing slightly different behavior and it is matching only when one column is equal to another and hence the .5. Half of the columns match?
I don't know, maybe it looks for length (different), characters in same position (none), who can tell what the computer sees-- but I bet that if you simply add a column that removes spaces to both queries--maybe only to just one of them, and they'll probably fuzzy match. Also, make sure you have the Match by combining text parts checked.
--Nate
Hi jhamil560 ,
About similarity threshold in fuzzy match, here is the explaination in Power Query editor in Power BI Desktop:
Similarity Threshold:
Indicates how similar two values need to be in order to match. The minimum value of 0.00 causes all values to match each other. The maximum value of 1.00 only allows exact matches. The default value is 0.80.
To check the calculation of this threshold, you can create a table in Power Query online(dataflow) to use cluster values feature, please refer this document which introduces it in details:
How fuzzy matching works in Power Query?
Note:
Currently only the Cluster values feature in Power Query Online will provide a new column with the similarity score.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
