Check if column contains any value from another table's column

[judithcreek]

I have a table with a single column that contains text:

In a query, I want to check if a column has at least one of the System Statuses above:

Ultimately, I will add a column to the query that shows if the WBS status column has at least one of the system statuses from the first table.  How can I do this?  Thanks

[Syndicate_Admin]

Thank you!

[AlexisOlson · Accepted solution]

In this particular case, I'd recommend splitting the text into a list and using List.ContainsAny.

Try putting this into the Custom Column box:

List.ContainsAny(
    Text.Split([WBS Status], " "),
    SingleColumn[System Status]
)

Full sample query you can paste into the Advanced Editor to check out yourself:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvYJdlEIcg12VAhy9HdWitWJVgpy9VFwdA4IUfD084QLOPmEOMMFgJoU/PydIKJwJSGuzv5gjUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"WBS Status" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WBS Status", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Check", each List.ContainsAny(Text.Split([WBS Status], " "), SingleColumn[System Status]), type logical)
in
    #"Added Custom"

[judithcreek]

This is beautiful.  Thanks so much!

[CNENFRNL]

let
    SYSTEM = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vTzVIrViVYKcXX2BzOcfYJdlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"System Status" = _t]),
    WBS = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTw9PNUcFJw9gl2UYrViVZyVnBRcAWz3BSCFdyhErEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"WBS Status" = _t]),

    Status = SYSTEM[System Status],

    Chk_List.Accumulate = Table.AddColumn(WBS, "Chk_List.Accumulate", each List.Accumulate(Status, false, (s,c) => s or Text.Contains([WBS Status], c))),
    Chk_List.Intersect = Table.AddColumn(Chk_List.Accumulate, "Chk_List.Intersect", each let wbs=Text.Split([WBS Status], " ") in List.Intersect({wbs, Status})<>{}),
    Chk_Regex =
        let
            RE = (regex as text, str as text) =>
            let
                html = "<script>var regex = " & regex & "; var str = """ & str & """; var res = regex.test(str); document.write(res)</script>"
            in
                Web.Page(html)[Data]{0}[Children]{0}[Children]{1}[Text]{0},
            ptn = "/" & Text.Combine(List.Transform(Status, each "\b"&_&"\b"), "|") & "/i"
        in
            Table.AddColumn(Chk_List.Intersect, "Chek_Regex", each RE(ptn, [WBS Status]))
in
    Chk_Regex