How to merge two tables based on a partial match

[Alicia_Anderson]

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

[Alicia_Anderson]

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.

[Alicia_Anderson]

[ImkeF · Accepted solution]

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.

[Alicia_Anderson]

Thank you so much.   I did finally get this to work.   Yay!

[Jimmy801]

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

[ImkeF]

Hi @Alicia_Anderson 

why shouldn't row 8 not match with ABCD 11 ?

[Alicia_Anderson]

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.   

[ImkeF]

Having problems to understand that logic: How about those other duplicates then?

[Alicia_Anderson]

ABCD 11.1.5 does not exist in CID Release table.

ABCD 11.1 does not exist in CID Release table.

ABCD 11 DOES exist in CID Release table. (MATCH)

ABCD 11.2 does not exist in CID Release table.

ABCD 11 DOES exist in CID Release table. (MATCH)

ABCD 11.4.0 does not exist in CID Release table.

ABCD 11.4 does not exist in CID Release table.

ABCD 11 DOES exist in CID Release table. (MATCH)

[ImkeF]

this is not clear to me. See the 2 issues below:

[Alicia_Anderson]

My apologies.   I altered the data  examples at the last minute and did not reference the correct CID. 

"ABCD 11.6" should have matched to "ABCD 11"

"XYZ 17.1 CU2" should have matched to "XYZ 17.1"

[ImkeF]

Hi @Alicia_Anderson 

Thanks, appreciated.

Please add a column with the following formula:

List.First( 
    List.Select( 
        List.Buffer( 
           Table.Buffer( 
               Table.Sort(#"Release CID Table",
                          {{"Length", Order.Descending}}) )
                 [Release CID Table] ), 
        (l) => Text.Contains( [Release Table], l))
   )

[ImkeF]

… or you can sort the lookup table before, see attached file

[Greg_Deckler]

The Query Editor's Merge query function has fuzzy matching capabilities.

[Alicia_Anderson]

I did try that with a couple variations.  Everytime it returned only errors for each item.