Forum Discussion

Wahid777's avatar
Wahid777
Frequent Visitor
6 years ago
Solved

Creating a conditional column with boolean (Yes/No) result for matching partial text matches

Hi   I've got a column listed as below and I'm trying to create a new column (with a Yes/No or True/False response) if some matches of certain keywords are found in the original column. Would "CONT...
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Wahid777 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table1:

     

    Table2:

     

    You may create a custom column with the following codes.

    if List.ContainsAny(Text.Split([Original column]," "),Table2[Keywords],Comparer.OrdinalIgnoreCase)
then "Yes" else "No"

     

    Result:

     

    Best Regards

    Allan

     

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

CNENFRNL's avatar
CNENFRNL
Community Champion
6 years ago

Hi, there, I use to List.Intersect as follows,

let
    Source = #table({"Original column"}, {{"Mr John is the CEO of x"},{"Ms Lynn is the Vice president of Y"},{"Tom is the ambassador of USA to the UK"},{"Jerry is the Company Secretary of X Inc"}}),
    Keywords = #table({"Keywords"}, {{"Ambassador"}, {"CEO"}, {"Chief Executive Officer"}, {"Chief Financial Officer"}, {"CFO"}}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each 
        [counter = List.Count(List.Intersect({Text.Split(_[Original column], " "), Keywords[Keywords]}, Comparer.OrdinalIgnoreCase)),
        result = if counter > 0 then "YES" else "NO"][result]
    )
in
    #"Added Custom"