How to add a column that indicates whether values in a column contain any of a list of keywords?

[allieg2830]

Hi!

My goal is to write a function that basically answers, If the title contains any of the following words, then Y

Specific example: the title is "Supergoop! Unseen Sunscreen SPF 40, Reef-Friendly Face Sunscreen for All Skin Types"

If the title contains any of the following words, I'd want the column value to be "Y" (yes). If not, then "N".

- reef safe, reef-friendly, reef friendly, reef-safe, coral reef

So, the value here would be "Y"

Any suggestions?? 

Thank you so much!

[smpa01 · Accepted solution]

@allieg2830  you can write a dynamic measure like this if you have two tables, one for the lookup value and one for the string, without needing to hardcode. Pbix is attached

The same expression doubles as a calculated column

Measure =
VAR _lookup =
    MAXX (
        FILTER (
            CROSSJOIN ( VALUES ( t1[Column1] ), VALUES ( t2[LookupValue] ) ),
            CONTAINSSTRING ( [Column1], [LookupValue] )
        ),
        [LookupValue]
    )
RETURN
    IF ( _lookup <> BLANK (), "Y", "N" )

[AlexisOlson]

@allieg2830 Note that this checks the entire column t1[Column1] at once. If you only want to check for keywords one row at a time, then it can be simplified a bit.

Column1ContainsKeyword =
VAR _matchingKeywords =
    FILTER ( VALUES ( t2[keyword] ), CONTAINSSTRING ( t1[Column1], t2[keyword] ) )
RETURN
    IF ( ISEMPTY ( _matchingKeywords ), "N", "Y" )

[Samarth_18]

Hi @allieg2830 ,

You can create a column using below code:-

Column 2 =
SWITCH (
    TRUE (),
    CONTAINSSTRING ( Contains_specific_string[column], "reef-friendly" ), "Y",
    CONTAINSSTRING ( Contains_specific_string[column], "reef safe" ), "Y",
    CONTAINSSTRING ( Contains_specific_string[column], "reef friendly" ), "Y",
    CONTAINSSTRING ( Contains_specific_string[column], "reef-safe" ), "Y",
    CONTAINSSTRING ( Contains_specific_string[column], "coral reef" ), "Y",
    "N"
)

Thanks,

Samarth