Power Query check if string contains strings from a list

Daniel has a decent solution, but it won't work if the example strings aren't space-separated. For example, The brick is reddish would detect red as a substring.

You can create a custom column with this formula instead:

(C) => List.AnyTrue(List.Transform(Words, each Text.Contains(C[Texts], _)))

This takes the list Words = {"red","blue","green"} and checks if each of the colors in the list is contained in the [Texts] column for that row. If any are, then it returns TRUE otherwise FALSE.

The whole query looks like this:

let
    TextList = {"The raisin is green","The pear is red","The apple is yellow"},
    Texts = Table.FromList(TextList, Splitter.SplitByNothing(), {"Texts"}, null, ExtraValues.Error),
    Words = {"red","blue","green"},
    #"Added Custom" = Table.AddColumn(Texts, "Check", (C) => List.AnyTrue(List.Transform(Words, each Text.Contains(C[Texts], _))))
in
    #"Added Custom"

This will make the trick, it's PowerQuery ("M") code:

let
    Texts = {"The raisin is green","The pear is red","The apple is yellow"},
    Words = {"red","blue","green"},
    TextsLists = List.Transform(Texts, each Text.Split(_," ")),
    Output = List.Transform(TextsLists, each List.Count(List.Intersect({_,Words}))>0)
in
    Output

There are two lists: the sentences (Texts) and the words to check (Words). The first thing to do is to convert the sentences in lists of words splitting the strings using " " as the delimiter.

TextsLists = List.Transform(Texts, each Text.Split(_," ")),

Then you "cross" the new lists with the list of Words. The result are lists of elements (strings) that appears in both lists (TextLists and Words). Now you count these new lists and check if the result is bigger than cero.

Output = List.Transform(TextsLists, each List.Count(List.Intersect({_,Words}))>0)

Output is a new list {True, True, False).

Alternatively, you can change the Output line by this one:

Output = List.Transform(TextsLists, each List.Intersect({_,Words}){0}?)

This will return a list of the first coincidence or null if there's no coincidence. In the example: {"green", "red", "null"}

Hope this helps you.

each if Text.Remove([Texts], {"The raisin is green","The pear is red","The apple is yellow"})<>[Texts] then ...