Find certain code combinations using text (sub)string in Power Query

Question

I had similar question (link below), but it just lets say "add-on" to my issue that I found on the way.

Find all code combinations using text string in Power Query

What I need is to extract exact matches (or I would say fuzzy matches in Power Query) that are in one string using substring as lookup.

(Please ignore T1 and T2 in the screenshot and data)

As you can see in Table 3 (T3) is a main string, and in T4 is substring with slightly different markings (like JH instead of JH0 or else..) Thats exactly what I need, to use substring as it is but to filter out main string and get results as they are in T5.

I tried my luck using Fuzzy matching in Power Query but the problem is afterwards when I have different substring with more instances, my query is failing due to "column doesn't exist and so on...it has to be dynamic.

I would like to have solution in Power Query!

https://docs.google.com/spreadsheets/d/1Ji1kyV7UsD2YBRJgWUY5zisyL3ySPGwW/edit?usp=sharing&ouid=101738555398870704584&rtpof=true&sd=true

Answer 1

let Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
FindList = Text.Split(Table.ReplaceValue(Table3,",","_",Replacer.ReplaceText,{"String"})[String]{0},"_"),
FindList2 = List.Transform(FindList, each Text.Remove(_,{"0".."9"})),
Newlist=Text.Split(Source[Substring]{0},"_"),
Newlist2=Text.Combine(List.Transform(Newlist, each try FindList{List.PositionOf(FindList2,_)} otherwise "missing"),"_")
in Newlist2

what it is doing (a) split table3 into a list at either a , or _ (b) duplicate the list from A and remove all numbers (c) split table4 into a list at each _ (d) match each value from c against b. If there is a match, use that position number to pull the value from a, otherwise put "missing" (e) put the results back together with a comma separation

Per comments, alternate version that works for multiple matches from Table3:

Newlist2=Text.Combine(List.Transform(Newlist, each try
if List.Count(List.PositionOf(FindList2,_,20))=0 then "missing" else
Text.Combine( List.Transform(List.PositionOf(FindList2,_,20), each FindList{_}),"_")  otherwise "missing"),"_")