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.

Jakinta's avatar
Jakinta
Solution Sage
5 years ago

Hi,

 

Paste this to blank query, it should work...

 

let
Source = Excel.CurrentWorkbook(),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Name", Splitter.SplitTextByDelimiter("Combine_", QuoteStyle.Csv), {"Name.1", "Name.2"}),
#"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each ([Name.2] <> null)),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Name.2", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Name.2", Order.Ascending}}), // In case you need them sorted by number
#"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Content"}),
in
#"Removed Other Columns"

 

...and expand tables from [Content] column afterwards.