PowerQuery: Combining tables configured in a “meta-table”

1
votes

I have 30 different Excel tables in a file that all of 5 columns in common. I want to merge them into one table with these 5 columns as well as one column for the table name.

I know I could import each table into PowerQuery without loading them (and add a column with the table name) - and then use Table.Combine to combine them.

However, I was wondering if it is possible to have another table that has 30 rows, depicting the names of the tables to import - and run the import of this table using a more sophisticated M query - similar as using Binary.Combine to import multiple CSV files...

1
excelpowerquery

1 Answers

0
votes

Assuming the name of the tables is stored in a table called MetaTable in the column Table name, the PowerQuery script below will do the job. In order to insert it, use the PowerQuery tab, From Other Sources -> Blank Query -> View Tab -> Advanced Editor and insert the following text:

let
    AllTables = Excel.CurrentWorkbook(),
    TableConf = AllTables{[Name="MetaTable"]}[Content],
    TableConfWithContent = Table.NestedJoin(TableConf,{"Table name"},AllTables,{"Name"},"Table"),
    Expand1 = Table.ExpandTableColumn(TableConfWithContent, "Table", {"Content"}, {"Table.Content"}),
    Expand2 = Table.ExpandTableColumn(Expand1, "Table.Content", {"Column Name1", "Column Name2", "Column Name3", "Column Name4", "Column Name5"}, {"Column Name1", "Column Name2", "Column Name3", "Column Name4", "Column Name5"})
in
    Expand2