Forum Discussion
Fuzzy Match calculation explained
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.
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?
Thanks, I'll look into it and see what that does. What is interesting is I was mostly getting good results. So if there was an issue using two columns I would have expected it to be an issue on the entire data set. But it's only an issue in a small number of rows not matching. I've found other rows that did get a match that had the exact same difference as my example, but maybe the names were longer/shorter. That's why I was trying to understand the calculation being done by the software.
