Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
Solved! Go to 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
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.
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
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?
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:
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"
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
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
41 | |
24 | |
21 | |
20 | |
13 |
User | Count |
---|---|
159 | |
61 | |
60 | |
28 | |
20 |