How do I retrieve a record from another table if it is contained in a value in the current table?

[Anonymous]

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?

[Zubair_Muhammad]

@Anonymous 

Try this custom column

=Text.Combine(List.Intersect({Text.Split([Trim_null]," "),EarningTypes[Type]})," ")

[Anonymous]

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.

[Zubair_Muhammad]

Hi @Anonymous 

Could you Copy paste a small dataset with expected results?

[Anonymous]

@Zubair_Muhammad ,

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]