Forum Discussion
Approx Match 2 Sources Relationship
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.
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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.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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
