Forum Discussion

DebbieE's avatar
DebbieE
Community Champion
6 years ago
Solved

Conditional Column in M using another table as a reference

Basically, If this description column contains anything in a seperate reference table I have e.g.   Reference Lookup table TYE COP HAE NOP  WEP   Then True Else False   So basically ...
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    DebbieE see attached, there are two tables, "search and replace" table is the one used for dynamic replace, it will search the text in "search" column (regardless of length) and replace the value from "replace" column

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

     

Smauro's avatar
Smauro
Solution Sage
6 years ago

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