Forum Discussion
How to merge two tables based on a partial match
I have two tables each with one column: Release Table [Release] & Release CID Table [Release CID].
I want to associate Release CID to Release based on whether there is a exact match first and if not, if there is a partial match.
I can merge the two queries just fine if there is an exact match. If there isn't an exact match, I want it to search for the closest partial match. For example: "ABCD 11.1.5" does not exist in Release CID but there is a match on the first 7 digits to "ABCD 11".
If there is no match, I want it to show "None". Note for row 5, "ABCD 11.4.1 HF1" should match to "ABCD 11.4.1" not "ABCD 11" as this is more complete match.
| Row | Release Table | Row | Release CID Table | Row | New Release Table | with Release CID |
| 1 | ABCD 11.1.5 | 1 | ABCD 11 | 1 | ABCD 11.1.5 | ABCD 11 |
| 2 | ABCD 11.2 | 2 | ABCD 11.4.1 | 2 | ABCD 11.2 | ABCD 11 |
| 3 | ABCD 11.4.0 | 3 | ABCD 11.5 | 3 | ABCD 11.4.0 | ABCD 11 |
| 4 | ABCD 11.4.1 | 4 | XYZ 17.1 | 4 | ABCD 11.4.1 | ABCD 11.4.1 |
| 5 | ABCD 11.4.1 HF1 | 5 | XYZ 17.1 CU4 | 5 | ABCD 11.4.1 HF1 | ABCD 11.4.1 |
| 6 | ABCD 11.5 | 6 | XYZ 17.1_CU10 | 6 | ABCD 11.5.0 | ABCD 11.5 |
| 7 | ABCD 11.5.0 HF3 | 7 | ABCD 11.5.0 HF3 | ABCD 11.5 | ||
| 8 | ABCD 11.6 | 8 | ABCD 11.6 | None | ||
| 9 | EFG 1.0 | 9 | EFG 1.0 | None | ||
| 10 | XYZ 17.1 | 10 | XYZ 17.1 | XYZ 17.1 | ||
| 11 | XYZ 17.1 CU1 | 11 | XYZ 17.1 CU1 | XYZ 17.1 | ||
| 12 | XYZ 17.1 CU2 | 12 | XYZ 17.1 CU2 | XYZ 17.1 CU4 | ||
| 13 | XYZ 17.1 CU4 WC1 | 13 | XYZ 17.1 CU4 WC1 | XYZ 17.1 CU4 | ||
| 14 | XYZ 17.1_CU10 WC1 | 14 | XYZ 17.1_CU10 WC1 | XYZ 17.1_CU10 | ||
| 15 | YYZ 18 | 15 | XYZ 18 | None |
Hi Alicia_Anderson ,
my code included the small letter L "l" and not 1.
The reason for your error message is that you used a number instead.
So either replacing it with any character or a string like Jimmy801 mentioned should solve the problem.
BTW: It might be more suitable for your use case to use the "Text.StartWith"- function instead of the "Text.Contains"-function.
15 Replies
The Query Editor's Merge query function has fuzzy matching capabilities.
I did try that with a couple variations. Everytime it returned only errors for each item.
Yes, it matches but I want it to only match to one value and I want it to be the one with the most characters matched.
Having problems to understand that logic: How about those other duplicates then?
I am having a hard time getting this to work. I get an error "Token Identifer expected". It seems to have a problem with the (1). See image.
Hello Alicia_Anderson
try to substitute this code
(l) => Text.Contains( [Release Table], l)with this one
(oneint) => Text.Contains( [Release Table], oneint)Hope this helps
Jimmy
