Forum Discussion
Fuzzy Match Score Wrong
Actually documentation is clear that "The best scenario for applying the fuzzy match algorithm is when all text strings in a column contain only the strings that need to be compared and no extra components. For example, comparing Apples against 4ppl3s yields higher similarity scores than comparing Apples to My favorite fruit, by far, is Apples. I simply love them!. Because the word Apples in the second string is only a small part of the whole text string, that comparison yields a lower similarity score."
https://docs.microsoft.com/en-us/power-query/fuzzy-matching
Hence, to get the right match, you need to have a single word. Hence, you need to match SayersJohn not Sayers, John.
Just have one intermediate step. In first table i.e. where you want match insert one more column which should have following formula (please replace Name with your column name)
= Text.Replace([Name],", ","")Now, you should perform fuzzy matching on this column to get right result. (You need not make this change in lookup table).
After fuzzy matching, you can delete this column from your result table.
Problem is that you applied the formula on both the columns. I specifically wrote this that you need to apply this only in source column. You are supposed to apply this formula only in AssetSteward Leader Name column. Don't apply this formula on Emp Name column.
Then you would get right result.
I have demonstrated this in this file where I have shown merge as you were originally doing, then merge where I applied the formula on both source and target columns and merge where I applied the formula on source column only - https://1drv.ms/u/s!Akd5y6ruJhvhuW0f_3h5TxwnKmX7?e=JzLdWS
Actually documentation is clear that "The best scenario for applying the fuzzy match algorithm is when all text strings in a column contain only the strings that need to be compared and no extra components. For example, comparing Apples against 4ppl3s yields higher similarity scores than comparing Apples to My favorite fruit, by far, is Apples. I simply love them!. Because the word Apples in the second string is only a small part of the whole text string, that comparison yields a lower similarity score."
https://docs.microsoft.com/en-us/power-query/fuzzy-matching
Hence, to get the right match, you need to have a single word. Hence, you need to match SayersJohn not Sayers, John.
Just have one intermediate step. In first table i.e. where you want match insert one more column which should have following formula (please replace Name with your column name)
= Text.Replace([Name],", ","")Now, you should perform fuzzy matching on this column to get right result. (You need not make this change in lookup table).
After fuzzy matching, you can delete this column from your result table.
Thanks for the guidance. I actually added the space in one of my attempts to get more accurate matching. I did as you instructed, removed it and it still gives me the same results. It really looks like it is only matching the string after the comma and giving no consideration to the string before the comma. Forgive me but I don't see ANY scenario where "Hayes, John" should be scored higher than "Sayers, John" when comparing both to "Sayers, John O". With or without a space. "Sayers, John" is an EXACT match minus two characters (a space and an "O") on the end. Why in the world would "Hayes, John" ever be considered a closer match?
Any other ideas to try?
Here is what I get when removing the space after the comma.
Problem is that you applied the formula on both the columns. I specifically wrote this that you need to apply this only in source column. You are supposed to apply this formula only in AssetSteward Leader Name column. Don't apply this formula on Emp Name column.
Then you would get right result.
I have demonstrated this in this file where I have shown merge as you were originally doing, then merge where I applied the formula on both source and target columns and merge where I applied the formula on source column only - https://1drv.ms/u/s!Akd5y6ruJhvhuW0f_3h5TxwnKmX7?e=JzLdWS
OK. I went back and removed the comma from the Asset Steward Name coulmn only and performed the fuzzy merge and it worked! I don't understand why but I am just going to take it and move on. Thanks for the help!