Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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