Forum Discussion

MMoll's avatar
MMoll
Frequent Visitor
3 years ago

Need help iterating over all columns

I have columns constaining a mix of records and lists. I need to convert them all to lists, collapse them into a table, and then expand them. (Thus extracting the contents of each list without disrup...
Need Help
ThxAlot's avatar
ThxAlot
Super User
3 years ago

To my understanding, you try to combine all columns containing different data types.

let
    Source = #table({"Col1","Col2","Col3"},{{null,"ac",{1..3}},{{"A".."D"},123,""}}),
    #"Combined Columns" = Table.CombineColumns(
        Source,
        Table.ColumnNames(Source), each Table.FromColumns(List.Transform(_, each if Value.Is(_, type list) then _ else {_})),
        "Combined"
    ),
    #"Expanded Combined" = Table.ExpandTableColumn(#"Combined Columns", "Combined", {"Column1", "Column2", "Column3"})
in
    #"Expanded Combined"
  • MMoll's avatar
    MMoll
    Frequent Visitor
    3 years ago

    Yes; that's exactly what I'm trying to do. But I need the list of columns (and/or column names) to be dynamic so I can set it up as a function.

    • ThxAlot's avatar
      ThxAlot
      Super User
      3 years ago

       

      let
    fx_TableTransfermation = (t as table) =>
        let
            #"Combined Columns" = Table.CombineColumns(
                t,
                Table.ColumnNames(t), each Table.FromColumns(List.Transform(_, each if Value.Is(_, type list) then _ else {_})),
                "Combined"),
            #"Expanded Combined" = Table.ExpandTableColumn(#"Combined Columns", "Combined", List.Transform({1..Table.ColumnCount(t)}, each "Column"&Text.From(_)))
        in
            #"Expanded Combined",

    Source = Table.FromColumns({{null,"ac",{1..3}},{{"A".."D"},123,""},{1,{"u".."z"}}}),
    Invocation = fx_TableTransfermation(Source)
in
    Invocation