Approx Match 2 Sources Relationship

[ethanlsaul]

Hello,

I have 2 data sources that i'd like to connect on UPC. 

One source has 10 characters while the other has an 11th (check digit).

Example 1:

Source A:

7430000070

Soruce B:

74300000701

Example 2:

38137003664

Source b:

381370036647

The trick part is some Source A codes are 10 digits while others are 11. I tried cutting off the 11th to make them all 10 and it errored out because the last digit can make a different between products. 

Ideal solution: Like in Excel, we use approximate matches. Is that possible? I need to create a relatinoship between source A and B. 

[Anonymous · Accepted solution]

Hi @ethanlsaul ,

Here I suggest you to add a calculated column in Source B to help relate two tables.

Approx Match = 
VAR _LEN = LEN('Source B'[Column1])
RETURN
LEFT('Source B'[Column1],_LEN - 1)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Use Table.AddColumn() with a custom generator function . There you can merge tables based on the first ten characters that match etc.

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.