Forum Discussion
Logic/algorithms behind fuzzy matching
Dear all,
I really like the fuzzy matching when doing some merges, with not 100% clean data. As I need to recreate the logic in another software solution, I wonder if somebody could explain which logic/algorithms is behind the fuzzy matching? For example I know that the levenshtein distance can be used to compare strings. I think this is probably used in the fuzzy matching but it is not the only applied logic/algorithm. Does somebody know more about it? Thank you
The fuzzy matching in a table merge:
BR,
DaGee
Hi, DaGee
Fuzzy matching is only supported on merge operations over text columns. Power Query uses the Jaccard similarity algorithm to measure the similarity between pairs of instances.
-
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.
-
Ignore case Indicates whether text values should be compared in a case sensitive or insensitive manner. The default behavior is case insensitive, which means case is ignored.
-
Maximum number of matches Controls the maximum number of matching rows that will be returned for each input row. For example, if you only want to find one matching row for each input row, specify a value of 1. The default behavior is to return all matches.
-
Transformation table Specify another query that holds a mapping table, so that some values can be auto-mapped as part of the matching logic. For example, defining a two-column table with a “From” and “To” text columns with values “Microsoft” and “MSFT” will make these two values be considered the same (similarity score of 1.00).
refer:
Create a fuzzy match (Power Query)
See the links below for more information:
Fuzzy Matching in Power BI / Power Query
Using Transformation Table In Fuzzy Matching In Power BI
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.-
2 Replies
Hi, DaGee
Fuzzy matching is only supported on merge operations over text columns. Power Query uses the Jaccard similarity algorithm to measure the similarity between pairs of instances.
-
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.
-
Ignore case Indicates whether text values should be compared in a case sensitive or insensitive manner. The default behavior is case insensitive, which means case is ignored.
-
Maximum number of matches Controls the maximum number of matching rows that will be returned for each input row. For example, if you only want to find one matching row for each input row, specify a value of 1. The default behavior is to return all matches.
-
Transformation table Specify another query that holds a mapping table, so that some values can be auto-mapped as part of the matching logic. For example, defining a two-column table with a “From” and “To” text columns with values “Microsoft” and “MSFT” will make these two values be considered the same (similarity score of 1.00).
refer:
Create a fuzzy match (Power Query)
See the links below for more information:
Fuzzy Matching in Power BI / Power Query
Using Transformation Table In Fuzzy Matching In Power BI
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.-
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.Regarding the highlighted text in red, can you confirm this is true as it doesn't seem to work in my tests. Is this a bug in implementation or a documentation error?
