Forum Discussion

BartoszKoryzno's avatar
BartoszKoryzno
New Member
5 years ago
Solved

Combine multiple tables using dynamic list

I have multiple tables which I want to combine into one. All tables that should be combined are named in similar way (name always starts with "Combine_"). The number of tables might increase, therefo...
Need Help
Icey's avatar
Icey
Icon for Community Support rankCommunity Support
5 years ago

Hi BartoszKoryzno ,

 

Assuming all the tables are loaded into Power Query Editor, try this:

let
    Source = #shared,
    #"Converted to Table" = Record.ToTable(Source),
    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each Text.StartsWith([Name], "Combine_")),
    #"Expanded Value" = Table.ExpandTableColumn(#"Filtered Rows", "Value", {"Column1", "Column2"}, {"Column1", "Column2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Value",{"Name"})
in
    #"Removed Columns"

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Daniel1987's avatar
    Daniel1987
    Regular Visitor
    2 years ago

    Thanks for sharing your solution. Didn't even know there is something like #shared in Power Query.
    For me using Table.Combine worked even better than using Table.ExpandTableColumn as it is also dynamic for changes in column names in the combined tables.

     

    let
    Source = #shared,
    #"Converted to Table" = Record.ToTable(Source),
    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each Text.StartsWith([Name], "Combine_")),
    #"Combined Table" = Table.Combine(#"Filtered Rows"[Value]),
in
    #"Combined Table"