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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

you could get the info you want using the code used here

 

although no answer has been promoted to solution by Anonymous

 

you have just to change the labels, and get the additional information of what item matches, non only that there is one match:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvR1jPL3UwhwjPR19QsJVnANDfIPcFUI1nPWc9RTcHFVitUBKvJx8VRw93XyUNBWcPbXU/B2Bwunlhblp2WWGJkouOcmeYCF8lLLFYpL8otSwbwAf2d/BdfMvKLM5IyS0rz04tzE1KLsklSo+lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Bank transfer intended purpose" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Bank transfer intended purpose", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Category", each Text.Combine(List.Transform(Text.SplitAny([#"Bank transfer intended purpose"], Text.Combine(List.Difference({" ".."@"},{"0".."9"}))), each Record.FieldOrDefault(storeDict, Text.Lower(_))),";"))
in
    #"Added Custom"

 

 

 

let
    dict = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxNrMrPU9JRcivNU4rVAQrkpGSCuPn5KQrBJflFqWBR15TU7ERM4YD85Hyw3qK8zJJSmNrSovy0zBIjE6CMe2WuUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Store = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(dict,{{"Store", type text}, {"Category", type text}})
in
 Record.FromList(dict[Store],List.Transform(dict[Store],Text.Lower))