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

Question

So, I have a column like this in Table A:Codes611710, 541611, 541720541310314999541810, 541430, 541613541611I have a list called "Interested Codes"List541310541613541611541890&nbsp;I am trying to add a column to&nbsp;Table A that identifies if each cell contains a value from the&nbsp;Interested Codes list. So, in the end it would be like this:CodesInterested In611710, 541611, 541720true541310true314999false541810, 541430false541611true&nbsp;I was using the List.ContainsAny({[Codes]},#"Interested Codes")but it only works when there is a single value in the Codes cell.&nbsp;&nbsp;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.CodesListListListListList&nbsp;Or is there a better way to handle this.&nbsp;Thank you for your help.

vicky_ · Accepted Answer

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:

&nbsp;Then merge the queries to find which codes are in the list

And finally combine it all back together with the GroupBy&nbsp;

BTW, this will require some small modifications to the default formulas.&nbsp;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"
&nbsp;

Forum Discussion

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

1 Reply

Related content

Transform columns / type

Table structure

Table Transformation

Transform Column / Type

ELT JSON / Semi structured

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