Dynamically append tables from different workbooks in Power Query-CodePudding

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.

Here's the query I'm using after David's suggestion & its results:

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]),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Sales"}, {"Custom.Sales"})
in
    #"Expanded Custom"

CodePudding user response：

Given the following input table, then the code below works to retrieve all tables and combine them.

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"