How to search multiple strings in a string?

Question

I want to check in a powerquery 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

In this case, we are going to combine a few M library functions together.

You will have to use Text.Contains many times against a list which is a good case for List.Transform. List.AnyTrue will inform if any string gets matched

List.AnyTrue(List.Transform({"dog","string","bark"}, (substring) => Text.Contains("This is a test string", substring)))

If you think that there is Text.ContainsAny function, you can write it.

let

    Text.ContainsAny = (string as text, list as list) as logical =>

        List.AnyTrue(List.Transform(list, (substring) => Text.Contains(string, substring))),

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

in

    Invoked