Find all code combinations using text string in Power Query

Question

What I need is to find all the code combinations from Codes (table 1) using text string (long text string in A2) and to get all the combinations like on the Results table (table 2). For instance, you have CE1 part of string, and it can happen that has many combinations as you can see in Results...

I tried in Power Query but i cant figure out how to look for on row level when it has comma values like CE1,CE2,CE3,CE4 or else. In my case it can only match one and first value but not the whole string.

I would like to have solution in Power Query (when it is possible)!

This is formula solution:

=UNIQUE(FILTER(Table3[Ausgang];LET(X;TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A2;",";"_");"_";"</s><s>")&"</s></t>";"//s"));MMULT(IFERROR(FIND(","&X&",";","&Table3[Ausgang]&",");0);SEQUENCE(COUNTA(X);;;0)));"None Found"))

https://docs.google.com/spreadsheets/d/1bg7aodiJb-A1hOBUlBxc-25ERn2Pn5bY/edit?usp=sharing&ouid=101738555398870704584&rtpof=true&sd=true

Answer 1

In powerquery, load your lookup cell into query named Table3 (data..from table/range [x]columns ) then file close and load. Assumes column is named String

Load your T1 table (data .. from table/range [x] columns ) and add column .. custom column ... with formula

=List.ContainsAny(Text.Split([Codes],","),Text.Split(Table.ReplaceValue(Table3,",","_",Replacer.ReplaceText,{"String"})[String]{0},"_"))

then use arrow atop column to filter for TRUE and right click to remove extra column

Sample full code, splitting that out a bit better is below. Assumes other query is named Table3 with column String. Replace T_1 with the name that powerquery gives to your table

let Source = Excel.CurrentWorkbook(){[Name="T_1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Codes", type text}}),
FindList = Text.Split(Table.ReplaceValue(Table3,",","_",Replacer.ReplaceText,{"String"})[String]{0},"_"),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.ContainsAny(Text.Split([Codes],","),FindList)),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in  #"Removed Columns"

the trick is to (a) replace , with _ in the source cell then Text.Split to make that a list (b) do the same with each row in the second table, and use List.ContainsAny to look for matches