Conditional Column in M using another table as a reference

DebbieE yes it can be done but the solution depends if the value you want to search is at a fixed location in the text or it can be anywhere in the text. I guess the length of 3 characters is fixed?

Hi DebbieE ,

Try to use DAX to create a calculated column, you can use IF() or SWITCH() function.

Column = 
IF(
    table[column] in {"TYE", "COP", "HAE" ,"NOP", "WEP"},
    TRUE(),
    FALSE() 
)

Or you can do like this.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi DebbieE 

parry2k's solution is great if you also want to do some replacements.

If you'd only like a true/false output, then you could add a function on your code directly and then call it while adding a new column. It will return true as soon as it finds a value from the lookup or false if it goes through all of them and finds nothing:

    fnSearch =
        let search = (t as text, l as list, x as logical) =>
            if List.Count(l) > 0 and x = false then
                @search(t, List.Skip(l, 1), Text.Contains(t, l{0}, Comparer.OrdinalIgnoreCase))
            else x
        in search,
    col = Table.AddColumn(PreviousStep, "search", each fnSearch([data], #"ref table"[Column], false), type logical)

Where [data] is the column you want to search on and #"ref table"[Column] is your reference lookup table's column containing the values you wish to search dynamically.

It is not case sensitive: if you want case sestitivity you should remove the Comparer.OrdinalIgnoreCase.

Cheers