Text.contains search value from a second table

[sbp_axt]

Hi - I am trying to add a custom column which search each row in a column for multiple substrings which are contained in a second table. Ie if the substring "red" is contained in "XredX', then populate a new column with "color"

Currently I have to add each value to search for manually, eg

= Table.AddColumn(#"Table", "Result", each if Text.Contains([TextToSearch], "red", Comparer.OrdinalIgnoreCase) then "color"

else if Text.Contains([TextToSearch], "blue", Comparer.OrdinalIgnoreCase) then "color"

else null )

but I'm hoping there's a way to add  reference to a dynamic List which isn't in the same table and have the Text.Contains function loop through these. 

eg

= Table.AddColumn(#"Table", "Result", each if Text.Contains([TextToSearch], [LookupList], Comparer.OrdinalIgnoreCase) then [LookupResult]

else null )

Any help is much appreciated. 

[v-chuncz-msft]

@sbp_axt,

You may also try SEARCH Function in DAX.

https://community.powerbi.com/t5/Desktop/quot-IF-Contains-Text-quot-function-on-a-table-querying-a-L...

[ImkeF · Accepted solution]

Please check this out: https://community.powerbi.com/t5/Desktop/joinAlgorithm-and-keyEqualityComparers/m-p/182148#M79842

[sbp_axt]

Thanks ImkeF, this is exactly what I was after.