Select items from a list based on items from another list

[danextian]

Hi All,

One of my columns contains a list of values while another one has the positions. What I'm trying to achieve is select items from the values list based on the positions.

I've attempted to follow the structure of this formula

= List.Transform({0,3,4,6}, each {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J"}{_})

which returns {"A","D","E","G"} but I would get an error but I would get this error: Expression.Error: We cannot apply field access to the type Number.

Here's my M including my attempt:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMFTSUQpILCqp9EvMTVVRVnFJzANRwfk5KSH5QIahkbGJqRmQ4ZxfmldSVAlkheZllqSmKASXJJakFoPUghhA2i+1XCEyvyhbKVYHbLQR0Gi4OWbmFpYGKMY4J+YlpiQi6fdNBBqcn5SoFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Transaction Number" = _t, String = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction Number", type text}, {"String", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Delimiter Occurence", each ( (Text.Length([String]) - Text.Length( Text.Replace([String], "$#$", "") ) )/Text.Length("$#$") ) + 1, Int64.Type),
    MaxOccurence = List.Max(#"Added Custom"[Delimiter Occurence]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "String", Splitter.SplitTextByDelimiter("$#$"),
MaxOccurence),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"String.1", type text}, {"String.2", type text}, {"String.3", type text}, {"String.4", type text}, {"String.5", type text}, {"String.6", type text}, {"String.7", type text}, {"String.8", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Delimiter Occurence"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "ListWithValues", each List.Skip(Record.FieldValues(_))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ListWithPositions", each List.Select(List.Positions([ListWithValues]), each Number.IsEven(_))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "SelectedList", each List.Transform([ListWithPositions], each [ListWithValues]{_})),
    SelectedList = #"Added Custom3"{0}[SelectedList],
    SelectedList1 = SelectedList{0}
in
    SelectedList1

[v-yulgu-msft · Accepted solution]

Hi @danextian,

Please try this query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMFTSUQpILCqp9EvMTVVRVnFJzANRwfk5KSH5QIahkbGJqRmQ4ZxfmldSVAlkheZllqSmKASXJJakFoPUghhA2i+1XCEyvyhbKVYHbLQR0Gi4OWbmFpYGKMY4J+YlpiQi6fdNBBqcn5SoFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Transaction Number" = _t, String = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction Number", type text}, {"String", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Delimiter Occurence", each ( (Text.Length([String]) - Text.Length( Text.Replace([String], "$#$", "") ) )/Text.Length("$#$") ) + 1, Int64.Type),
    MaxOccurence = List.Max(#"Added Custom"[Delimiter Occurence]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "String", Splitter.SplitTextByDelimiter("$#$"),
MaxOccurence),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"String.1", type text}, {"String.2", type text}, {"String.3", type text}, {"String.4", type text}, {"String.5", type text}, {"String.6", type text}, {"String.7", type text}, {"String.8", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Delimiter Occurence"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "ListWithValues", each List.Skip(Record.FieldValues(_))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ListWithPositions", each List.Select(List.Positions([ListWithValues]), each Number.IsEven(_))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "SelectedList", each Function.Invoke((list1 as list, list2 as list )=>List.Transform(list1, each list2{_}),{[ListWithPositions],[ListWithValues]}))
in
    #"Added Custom3"

Best regards,

Yuliana Gu

[danextian]

Thanks! This works like a charm.