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

[bravo1563]

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!

CriteriaTbl		TextTbl		Desired Output
Criteria	Value		Text		Text	Criteria	Value
Red	1		The ball is red.		The ball is red.	Red	1
Green	2		Putting on the greens with my red ball.		Putting on the greens with my red ball.	Green	2
Blue	-1		Feeling blue? Wear your green shirt.		Putting on the greens with my red ball.	Red	1
			The ball is orange		Feeling blue? Wear your green shirt.	Blue	-1
					Feeling blue? Wear your green shirt.	Green	2
					The ball is orange	null	null

 

[Bill Szysz]

Check it out.

Cheers

 

[bravo1563]

Thanks Bill this is exactly what I was looking for!