1

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.

enter image description here

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

MmVv
  • 553
  • 6
  • 22

1 Answers1

2

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

enter image description here

horseyride
  • 17,007
  • 2
  • 11
  • 22
  • Question! I didnt open another thread because it is very very similar with this. What if I have substring like JH-LB-MV-NZ and I need exacty match for these four from main String (table 3)? so like: JH0-LB0-MV1-NZ5..but the string has to recognise substring and give exactly only those matches.... Sorry I was commenting on this post but my comments are deleted as I see... – MmVv Apr 08 '22 at 11:53
  • Sorry, I am not understanding. You have a-b-c-d in T_1, and want to make sure that T3 has a and has b and has c and has d? Or that T3 has a-b-c-d all bunched up? The first one is a totally different question and the logic of the entire things changes, so open a question for that. – horseyride Apr 08 '22 at 12:18
  • Yes, you got the point. Just bunched up matches from separate substring. So that means you have one more additional table 4 with A-B-C-D and it looks in T3 and if finds it should give A1-B2-C3-D4. I managed to do it without M coding but it is not efficient because it is not dynamic if I change table4 with new instances... Do you think I need separate tread? – MmVv Apr 08 '22 at 12:29
  • yes, separate thread – horseyride Apr 08 '22 at 12:37
  • Sorry again, is there a line of code which will keep some text filter without loosing it when I am ticking TRUE/FALSE within query? In my case I need to have text "Basis" always around, but I cant figure it out how to keep it in my query when I need to remove all FALSE instances (also Basis goes with). – MmVv Apr 13 '22 at 11:08
  • filter the TRUE/FALSE table before transformation as the last step, and combine with the results of the rest of the query with Table.Combine() – horseyride Apr 13 '22 at 12:13
  • not sure if i get it but I will try it again. – MmVv Apr 13 '22 at 13:22
  • do the above query. then put in a step to filter your original table (not the most recent prior step) for BASIS. now you have two tables. Merge them – horseyride Apr 13 '22 at 13:25