Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to use the Query Editor to check if a value in Table1 contains any value in Table2 (EarningTypes) and then return the matching value when found. Here is the custom M query code I am using:
if List.ContainsAny(Text.Split([Trim_null]," "),EarningTypes[Type]) = true then EarningTypes[Type] else ""
It returns a result, but the result appears as a "list" item instead of an actual value.
Anyone know how I can convert the list record into an actual table value alongside of the original data or simply retrieve the matching record in a manner similar to a Merge Query?
@Anonymous
Try this custom column
=Text.Combine(List.Intersect({Text.Split([Trim_null]," "),EarningTypes[Type]})," ")
That worked perfectly. Unfortunately, I realized after I tried your solution that I was actually wanting to do something slightly different. Currently, my formula splits a string at each occurence of the delimiter. What I actually need is to simply see if a string contains any text that appears as a value in another table and return the matching text.
I have tried modifying this on my own using functions such as List.ContainsAny, but I cannot get it to work.
Hi @Anonymous
Could you Copy paste a small dataset with expected results?
I appreciate all your help and hope the following gives you sufficient information.
This is the OriginalImport table:
CSV String |
001 Category A 11.11 13.121 1,232.11 1,125.15 Type C 145.21 1,641.85 |
005 Category B 150 2.62 311.24 760.14 Type B 57.25 176.95 |
W Type A 27.00 981.00 |
This is an example of Table1 from which I want to return a result if a match is found using:
EarnCode | Category | Joined |
001 | Category A | 001 Category A |
005 | Category B | 005 Category B |
This is an example of the Table2 from which I want to return a result if a match is found:
DeductCode | Type | Joined |
W | Type A | W Type A |
Type B | Type B | |
Type C | Type C |
This is the DesiredResult table:
CSV String | EarnCode | Category | DeductCode | Type |
001 Category A 11.11 13.121 1,232.11 1,125.15 Type C 145.21 1,641.85 | 001 | Category A | Type C | |
005 Category B 150 2.62 311.24 760.14 Type B 57.25 176.95 | 005 | Category B | Type B | |
W Type A 27.00 981.00 | W | Type A |
The logic I have in mind to populate the columns in the DesiredResult table looks something like this:
EarnCode: IF [CSV String] contains a value in Table1[Joined], then return matching Table1[EarnCode]
Category: IF [CSV String] contains a value in Table1[Joined], then return matching Table1[Category]
DeductCode: IF [CSV String]contains a value in Table2[Joined], then return matching Table2[DeductCode]
Type: IF [CSV String] contains a value in Table2[Joined], then return matching Table2[Type]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |