Forum Discussion
Fuzzy match gives different results on the same data
Hi Sharkybu ,
Thank you for reaching out to the Microsoft Fabric Community. Also thank you BeaBF for your input.
Fuzzy matching can be sensitive to even minor changes like extra spaces, hidden characters, or case differences these can cause previously successful matches to fail after a refresh.
To resolve this, please apply the following steps in Power Query for both tables before performing the fuzzy merge:
= Table.TransformColumns(YourTable, {{"Name", each Text.Lower(Text.Trim(Text.Clean(_)))}})This ensures the data is clean, trimmed, and case-normalized, improving match consistency. Also, consider slightly lowering the similarity threshold if needed.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thankyou.
Hello Sharkybu ,
Thanks for the update and for testing the suggestions. Power BI's fuzzy matching is useful but can sometimes give different results even when your data hasn’t changed. This happens because the matching logic depends on how the names are broken into parts (called tokens), and small changes behind the scenes like the order of rows or internal updates during a refresh can affect the results.
In your example, matching "John, Smith" with "Smith, J." is tricky because it involves recognizing initials and name order, which can be unreliable across refreshes. When you lowered the similarity to 0.5, the match reappeared, but that also caused many incorrect matches to show up.
To fix this, I recommend creating a new column in both tables that formats the names in a consistent way like combining the last name with the first initial (for example, "Smith J"). This makes the matching clearer and lets you use a better similarity setting (like 0.7) to get more accurate results.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thankyou.
Hi Sharkybu ,
Thank you for reaching out to the Microsoft Fabric Community. Also thank you BeaBF for your input.
Fuzzy matching can be sensitive to even minor changes like extra spaces, hidden characters, or case differences these can cause previously successful matches to fail after a refresh.
To resolve this, please apply the following steps in Power Query for both tables before performing the fuzzy merge:
= Table.TransformColumns(YourTable, {{"Name", each Text.Lower(Text.Trim(Text.Clean(_)))}})This ensures the data is clean, trimmed, and case-normalized, improving match consistency. Also, consider slightly lowering the similarity threshold if needed.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thankyou.
Thank you Anonymous and @BeaBF.
I tried using the trim formula you gave me and lowering the similarity.
I test the results with the same example (the one that disappered from the results).
The example showed up again when I want down to 0.5 in the similarity, but I want from having 2000 matches to 20,000 matches (which are not matches at all).
I still can' t understand why is it no longer a match if the data didn't change.
I tried going up to 0.6 and it wont give me "John, Smith" and "Smith, J" as a match but it does give "John, Smith" and "Smith, Dan" as a match.
Thank you again.
Hello Sharkybu ,
Thanks for the update and for testing the suggestions. Power BI's fuzzy matching is useful but can sometimes give different results even when your data hasn’t changed. This happens because the matching logic depends on how the names are broken into parts (called tokens), and small changes behind the scenes like the order of rows or internal updates during a refresh can affect the results.
In your example, matching "John, Smith" with "Smith, J." is tricky because it involves recognizing initials and name order, which can be unreliable across refreshes. When you lowered the similarity to 0.5, the match reappeared, but that also caused many incorrect matches to show up.
To fix this, I recommend creating a new column in both tables that formats the names in a consistent way like combining the last name with the first initial (for example, "Smith J"). This makes the matching clearer and lets you use a better similarity setting (like 0.7) to get more accurate results.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thankyou.
Hello Anonymous
I tried what you suggested now and while it didn't return the example I was using it did return about half of the other cases that disappeared.
It also added some non matching cases, but at least not 20,000 of them.
thank you so much for all your help.