Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
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"
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.