Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculated Column for Text Containing from another Column

Hello,

 

I am looking for a way to create a calculated column called "Matching" where it compares Column B against column A.

The output I am looking for is column D.

 

I have tried doing Text.Contains function but not expecting the results I need. 

 

Looking for the best way to do this in either Power Query or within the Data Model! 

 

Thank you!

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    Please try this custom column in Power Query.

    = if List.Contains(Text.Split([App Approvers], ", "), [Approver]) then "True" else "False"

    This formula uses the Text.Split function to split the values in Column App Approvers into a list, and then uses the List.Contains function to check if the value in Column Approver is in the list. If it is, the formula returns "True". If it isn't, the formula returns "False".

     

     

     

                                                                                                                                                             

    Best Regards,

    Stephen Tao

     

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

5 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    3 years ago

    Hi Anonymous,

     

    Give this a go. You can copy this script into a new blank query.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sovzkxVCM7NLMnQUfBKLM7NzEPiJmdD2Eo6Skic2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"App Approvers" = _t, Approver = _t]),
    AddMatching = Table.AddColumn(Source, "Matching", each Text.Contains([App Approvers], [Approver]), type logical)
in
    AddMatching

     

    with this result.

     

    Ps. If this helps solve your query please mark this post as Solution, thanks!

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      This solution results in "True" for all the rows.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      = if Text.Contains([Approver],[App Approver]) then "True" else "False"

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    Please try this custom column in Power Query.

    = if List.Contains(Text.Split([App Approvers], ", "), [Approver]) then "True" else "False"

    This formula uses the Text.Split function to split the values in Column App Approvers into a list, and then uses the List.Contains function to check if the value in Column Approver is in the list. If it is, the formula returns "True". If it isn't, the formula returns "False".

     

     

     

                                                                                                                                                             

    Best Regards,

    Stephen Tao

     

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