topic Approx Match 2 Sources Relationship in Developer

Approx Match 2 Sources Relationship

ethanlsaul — Wed, 27 Jul 2022 20:22:07 GMT

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.

Re: Approx Match 2 Sources Relationship

lbendlin — Thu, 28 Jul 2022 21:56:24 GMT

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.

Re: Approx Match 2 Sources Relationship

Anonymous — Mon, 01 Aug 2022 08:28:04 GMT

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.