Fuzzy Matching errors

DAX DAX Calculations
1

Hello -
I’m trying to preform a fuzzy match via new merge queries and it doesn’t work.
Would you be kind to explain to to me how to merge 3 tables with a similar brand names.
For example:
Table UNFI → ANDALOU NATURALS SELECT FACIAL CARE
Table PreBilling Fees → ANDALOU NATURALS
Table PreBillingScans → ANDALOU NATURAL

Matching.pbix (2.7 MB)

image
image1001×535 40.9 KB

Thank you very much!
Natasha

3

More details about Merging query techniques you can find at Sam’s course:

Good luck.

4

Thank you very much Maja!
Your answer is very detailed, but not what I was looking into.
Maybe I should rephrase it:

  1. I need to match records from 3 tables. If I do merger twice, it will be very slow. Do you know any other way around?
  2. I have to do matching only by one column “Brand”, therefore I believe your suggestion for for Full Outer will error my results.
    Please help!
    Thank you!
    Natasha
5

Hi @npower. A possible work-around might be, perhaps, to create a stand-alone mapping table with Brand ID, Brand Name (UNFI), Brand Name (Fees), Brand Name (Scans), then merge each of your source tables with the mapping table to get the correct Brand ID, then link as normal from there. I’ve done this successfully many times in my database/consulting work.
Greg

6

Thank you, Greg!
My problem is that brands names are spelled differently every months, therefore , I have to assign manually ID every month too :frowning: that’s why I was looking into something like fuzzy mapping, when you can do your best on a fly.
Thank you!
Natasha

7

Hi @npower - Fuzzy Merge will only work properly if there are minor differences b/w the required value and the actual values.

As in your example, Fuzzy Merge will work for Table PreBilling Fees and Table PreBillingScans as difference is only of single alphabet however in Table UNFI difference is of complete “SELECT FACIAL CARE”.

This can still work but for this Threshold values needs to be kept very low which will result in Incorrect results as for example “ANDALOU NATURALS” will match with “NUNATURALS” which is Incorrect.

Option given by Greg is one you can go with if possible. Second option is to create a table like below with two columns i.e. Actual value and Expected value where you can merge on Expected values and get Actual value as Output.

Actual Expected in Tables
ANDALOU NATURALS ANDALOU NATURALS SELECT FACIAL CARE
ANDALOU NATURALS ANDALOU NATURALS
ANDALOU NATURALS ANDALOU NATURAL

Thanks
Ankit J

1 Like
8

Hi @npower , did the response provided by our community help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

9

It’s not a solution, but I appreciate your help!