Power Query append a list of tables from Access

[cr731]

I have a table in an Excel worksheet, and I want to append together all tables from a specific Access database that are listed in the table.

Can this be automated in Power Query without needing to write a line of code for each table?

Currently, I'm basically doing this,

Source1 = SourceTable {0}[Column]1,
Source1Access = Access.DataBase(File.Contents("networkpath\File.mdb")){[Schema="",Item=Source1]}[Data]

Source2 = SourceTable {0}[Column]1,
Source2Access = Access.DataBase(File.Contents("networkpath\File.mdb")){[Schema="",Item=Source2]}[Data]

CombinedSources = Table.Combine(Source1,Source2),

And basically have to repeat for every line in my table representing a source.

It would be great if I could pass one list to Table.Combine but I'm not sure how to get it to correctly pull those tables from Access.

Thanks

 

[ImkeF]

Table.Combine takes in a list of tables, so it can be applied to a column whose fields contain tables: Table.Combine(YourMasterTable[FunctionResult])
So turn your "Source"-query to a function (and name it function) and call it from YourMasterTable - passing the column that lists the Access-tables (AccessTables) to the function.

(Table) =>
let
Source = Access.DataBase(File.Contents("networkpath\File.mdb")){[Schema="",Item=[B]Table[/B]]}[Data]
in
Source

So your query would look like this:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
FunctionCall = Table.AddColumn(Source, "FunctionResult", each function([AccessTables])),
Custom1 = Table.Combine(FunctionCall[FunctionResult])
in
Custom1