Combine multiple tables using dynamic list

[BartoszKoryzno]

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, therefore I want to make it dynamic. Is there any way to achieve that?

I have tried with #sections, but that only worked inside Pwoer Query editor. When I tried loading it to Power BI data model, an error occured.

Basically, I have tables like below:

Combine_01

Combine_02

Combine_03

I want to replace Table.Combine({Combine_01, Combine_02, Combine_03}) with dynamic expression in order to combine more tables without modifying the expression itself.

[Icey · Accepted solution]

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]

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"

[Jakinta]

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.