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
    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.

Icey's avatar
Icey
Community 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"