1

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

enter image description here

MmVv
  • 553
  • 6
  • 22

1 Answers1

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

enter image description here

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"),"_")
horseyride
  • 17,007
  • 2
  • 11
  • 22
  • Sorry for spamming here instead of openning new thread. You solved it perfectly back then. What I am asking is just small "tweak" somewhere in code...eventually. In the Table4 (results) how to get all the matches that are corrresponding to for instance JH? Example, it can happen that I have in my String -JH0-JH1- and in my substring is only one "JH", and the result will be JH0 but not JH1 too...I wanna get all instances that can be found from substring table... Again sorry, I know it was older post... – MmVv May 17 '22 at 13:45