Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Alicia_Anderson
Resolver I
Resolver I

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.     

 

RowRelease Table

Row

Release CID TableRowNew Release Tablewith Release CID
1ABCD 11.1.51ABCD 111ABCD 11.1.5ABCD 11
2ABCD 11.22ABCD 11.4.12ABCD 11.2ABCD 11
3ABCD 11.4.03ABCD 11.53ABCD 11.4.0ABCD 11
4ABCD 11.4.14XYZ 17.14ABCD 11.4.1ABCD 11.4.1
5ABCD 11.4.1 HF15XYZ 17.1 CU45ABCD 11.4.1 HF1ABCD 11.4.1
6ABCD 11.56XYZ 17.1_CU106ABCD 11.5.0ABCD 11.5
7ABCD 11.5.0 HF3  7ABCD 11.5.0 HF3ABCD 11.5
8ABCD 11.6  8ABCD 11.6None
9EFG 1.0  9EFG 1.0None
10XYZ 17.1   10XYZ 17.1 XYZ 17.1 
11XYZ 17.1 CU1  11XYZ 17.1 CU1XYZ 17.1 
12XYZ 17.1 CU2  12XYZ 17.1 CU2XYZ 17.1 CU4
13XYZ 17.1 CU4 WC1  13XYZ 17.1 CU4 WC1XYZ 17.1 CU4
14XYZ 17.1_CU10 WC1  14XYZ 17.1_CU10 WC1XYZ 17.1_CU10
15YYZ 18  15XYZ 18None

 

1 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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

15 REPLIES 15

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors