Forum Discussion
Measuring similarity between strings
Anonymous wrote:
I still have no information on what measure is used to measure the string similarity.
As I mentioned above, the measure used is Jaccard similarity.
Calculating the similarity between all pairs of 2 million strings means 4 trillion comparisons. This will take a lot of computing time and Power Query likely isn't the best tool for this particular job. You'll probably need some optimization techniques to make this more tractable. See this approach for example,
https://hpccsystems.com/blog/edit-distances-and-optimized-prefix-trees-big-data
As long as Power Query is the tool you're using, I think clustering is likely your best option. See also:
https://radacad.com/fuzzy-clustering-in-power-bi-using-power-query-finding-similar-values
I have tried clustering but I still have no information on what measure is used to measure the string similarity. Also I initially don't want to reduce the data. I want to add a similarity score for each combination of all of the origial strings.
Right now I'm working on using Azure Data Bricks. Data bricks has a function for measuring the Levinsthein Distance.
Anonymous wrote:
I still have no information on what measure is used to measure the string similarity.
As I mentioned above, the measure used is Jaccard similarity.
Calculating the similarity between all pairs of 2 million strings means 4 trillion comparisons. This will take a lot of computing time and Power Query likely isn't the best tool for this particular job. You'll probably need some optimization techniques to make this more tractable. See this approach for example,
https://hpccsystems.com/blog/edit-distances-and-optimized-prefix-trees-big-data
I have marked this topic as resolved but I still have one question left:
When I make a left join of a table with itself using "fuzzy logic" and set the threshold to 0 I still don't get all possible combinations in the result? How can that be explained?
When I set the threshold to 0 I would expect all records to match and that the result effectivly should be a crossjoin. What am I missing?