Wsrtjtyk

Is there a way to check a text field to see if it contains any of the strings from a list?

Example Strings to Check:

The raisin is green

The pear is red

The apple is yellow

List Example to Validate Against

red

blue

green

The result would be

either:

green

red

null

or:

TRUE

TRUE

FALSE

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.