Search code examples
excelpowerbipowerquerypowerbi-desktopm

Dynamically append tables from different workbooks in Power Query

I'm trying to append several Excel tables from different workbooks using PowerQuery.

But instead of loading every table manually and then appending them, I would like to keep record of workbook names and tables along with their addresses in another table (say 'data_sources' table), so that PowerQuery could know what tables I want to append & where to find them.

How could I accomplish this? Suppose I have the following table, where other workbooks could be added later.

enter image description here

EDIT:

The following code will include to the query results any new column from the original tables. The #"Added Custom" argument will create a column containing the tables, and then it is used in the next step so to combine them.

let
    Source = Excel.CurrentWorkbook(){[Name="data_sources"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Workbook location", type text}, {"Workbook name", type text}, {"Table name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Excel.Workbook(File.Contents([Workbook location]&[Workbook name])){[Item=[Table name],Kind="Table"]}[Data]),
    #"Combine" = Table.Combine(#"Added Custom"[Custom]),
    #"Filtered Rows" = Table.SelectRows(Combine, each [Client] <> null and [Client] <> "")
in
    #"Filtered Rows"
Solution

  • Given the following input table, then the code below works to retrieve all tables and combine them. enter image description here

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Workbook Location", type text}, {"Workbook Name", type text}, {"Table Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Excel.Workbook(File.Contents([Workbook Location]&[Workbook Name]) ){[Item=[Table Name],Kind="Table"]}[Data])
in
    #"Added Custom"