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

This is a case where you'll want to combine a few M library functions together.

You'll want to use Text.Contains many times against a list, which is a good case for List.Transform. List.AnyTrue will tell you if any string matched.

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

If you wished that there was a 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

Answer 2

Another 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.

Answer 3

If it's a specific (static) list of matches, you'll want to add a custom column with an if then else statement in PQ. Then use a filter on that column to keep or remove the columns. AFAIK PQ doesn't support regex so Alexey's solution won't work.

If you need the lookup to be dynamic, it gets more complicated... but doable you essentially need to

1. have an ID column for the original row.
2. duplicate the query so you have two queries, then in the newly created query
3. split the text field into separate columns, usually by space
4. unpivot the newly created columns.
5. get the list of intended names
6. use list.generate method to generate a list that shows 1 if there's a match and 0 if there isn't.
7. sum the values of the list
8. if sum > 0 then mark that row as a match, usually I use the value 1 in a new column. Then you can filter the table to keep only rows with value 1 in the new column. Then group this table on ID - this is the list of ID that contain the match. Now use the merge feature to merge in the first table ensuring you keep only rows that match the IDs. That should get you to where you want to be.