Forum Discussion
Fuzzy Merge - similarity score
Yes, that documentation should have noted that this is a feature available only for Power Query Online (app.powerbi.com). This feature is still not transported to Power BI Desktop / Power Query Excel. You need to be having at least Power BI Pro subscription to use Dataflows which will enable you to use PQ Online.
See the snip from Power Query Online.
There is a trick that will work in Excel. You can use Advanced Editor and edit the code directy to include the SimilarityColumnName option. NOTE THERE IS A CATCH. IF you edit the SOURCE paramters through the GUI the SimilarityColumnName is lost (overwritten). Seems like a simple fix for MS to add to Excel.
Example: Source = Table.FuzzyNestedJoin(Contacts, {"Employer"}, Companies, {"Company name"}, "All Companies", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=0.90, TransformationTable=CompanyTransformationTable,SimilarityColumnName="matchscore"]),
Somewhat related.... if you use a Tranformation Table to map values, the match returns a 0.95 score. This seems very counterintuitive to me. If I am using a transformation table I am forciing it to a match, and the similarity score should be 1. It took me along time to figure this out and turn down my threshold to .95 to get my transformation table to work. Ideally it would return 0.9999 so we could ieintify hits from the transformation table.
If I add the SimilarityColumnName="matchscore" to the code, how do I get that column to display in the table once I run the query? I do have a step after the "Source" step to Remove Other Columns - do I have to add anything to the code there? Thanks!