Calculated Column for Text Containing from another Column

[Anonymous]

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!

[Anonymous · Accepted solution]

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.           

[HotChilli]

Please post the Power Query code you have tried.

[Anonymous]

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

[m_dekorte]

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]

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