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

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.

3 Replies

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

  • 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"