How can I search for multiple strings

Question

I want to check in a power query new column if a string like "This is a test string" contains any of the strings list items {"dog", "string", "bark"}.

I already tried Text.PositionOfAny("This is a test string",{"dog","string","bark"}), but the function only accepts single-character values

Expression.Error: The value isn't a single-character string.

Any solution for this?

Answer 1

A simple solution is this:

List.ContainsAny(Text.SplitAny("This is a test string", " "), {"dog","string","bark"})

It transforms the text into a list because there we find a function that does what you need.

Comments

Hi,  instead of return true,   it is possible to return the string,  for e.g.  if string is "This is a dog"  then return as dog

Thank you !

---

Hey, @Christopher,

You want the selected value function. This does exactly what you want. It returns a value only if there is a single value, otherwise, it returns blank (or an optional alternate).