Power Query - Filter A List Of Strings Based On Non Exact Values In Another Table

bravo1563

New member
Joined
Nov 7, 2019
Messages
2
Reaction score
0
Points
0
Excel Version(s)
2018
Hello I've seen a few posts that have similar issue but I have not seen what I'm trying to accomplish.

I have two tables, "CriteriaTbl" and "TextTbl" I need find the text where the criteria is contained and return the corresponding value. I have several thousand criterias and many thousand more texts so manual finds would not be sufficient and I need to keep in power query. I've created a function which filters for the first color but I have not come up with a way to assign it a color and value and loop through the rest of the list. Attached is my sample file, I appreciate any help anyone can provide. Thanks!


CriteriaTblTextTblDesired Output
CriteriaValueTextTextCriteriaValue
Red1The ball is red.The ball is red.Red1
Green2Putting on the greens with my red ball.Putting on the greens with my red ball.Green2
Blue-1Feeling blue? Wear your green shirt.Putting on the greens with my red ball.Red1
The ball is orangeFeeling blue? Wear your green shirt.Blue-1
Feeling blue? Wear your green shirt.Green2
The ball is orangenullnull
 

Attachments

  • Dataset.xlsx
    19.6 KB · Views: 4
Thanks Bill this is exactly what I was looking for!
 
Back
Top