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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
Alicia_Anderson
Resolver I
Resolver I

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.

Release_CID.PNG

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

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

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
Super User
Super User

Hi @Alicia_Anderson 

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

 

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

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?

 

image.png

 

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

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)

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

image.png

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

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"

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))
   )

 

 

 

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

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

 

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

Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.