Transform a columns values to a list (like a structured column)

[ACraig08]

So, I have a column like this in Table A:

Codes

611710, 541611, 541720

541310

314999

541810, 541430, 541613

541611

I have a list called "Interested Codes"

List

541310

541613

541611

541890

I am trying to add a column to Table A that identifies if each cell contains a value from the Interested Codes list. So, in the end it would be like this:

Codes	Interested In
611710, 541611, 541720	true
541310	true
314999	false
541810, 541430	false
541611	true

I was using the List.ContainsAny({[Codes]},#"Interested Codes")

but it only works when there is a single value in the Codes cell. 

My question is, is there a way to make it so the values in the Codes column are individual lists like if it were a structured column like below so that I can use the formula above.

Codes

List

List

List

List

List

Or is there a better way to handle this.

Thank you for your help.

[vicky_ · Accepted solution]

I would try a different way:

Add an index (so that i know which codes belong together), and then split and pivot the table so that i end up with this:

 Then merge the queries to find which codes are in the list

And finally combine it all back together with the GroupBy 

BTW, this will require some small modifications to the default formulas. The M-Code is below for reference. Also, after splitting, you'll want your Codes to be of type Text so that you don't get thrown an error in the Grouping step.

let
    Source = // your source here
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Codes", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Index", "Codes", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Codes.1", "Codes.2", "Codes.3"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Index"}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Columns",{{"Value", Text.Trim, type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Trimmed Text",{"Attribute"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Value"}, #"Interested List", {"List"}, "Interested List", JoinKind.LeftOuter),
    #"Aggregated Interested List" = Table.AggregateTableColumn(#"Merged Queries", "Interested List", {{"List", List.NonNullCount, "Code In List"}}),
    #"Grouped Rows" = Table.Group(#"Aggregated Interested List", {"Index"}, {{"Values", each Text.Combine([Value], ", "), type text}, {"Code in List", each List.Max([Code In List]), type number}})
in
    #"Grouped Rows"