How can I connect to xlsx workbook each table from another workbook with Power Query?

Question

So, I have lots of similar workbooks, which are connected to one special, kind of DB workbook. Each time, when I add a worksheet with a new table in DB workbook, I need to add a new Power Query connection to this table in all workbooks (which are connected to DB).

As you see on the pic, I have to manually set the connection to each table. Is there a way to get all the tables from DB workbook dynamically, but not using VBA macros?

Answer 1

You asked if there's a way to get all tables without VBA.

Yes.

Excel.Workbook(File.Contents(DbFilePath), null, true) returns a table with the following columns:

• Name is the name of the object
• Data is a table containing the object's contents
• Item is a text column I've yet to ever use.
• Kind is the type of object within the workbook, e.g. "Sheet", "Table", "DefinedName"
• Hidden is a logical value showing whether or not the object is hidden.

This means you can have a query called "fGetDbTable" with code like

let
  DbFilePath = "FilePathGoesHere",
  dbFile = Excel.Workbook(File.Contents(DbFilePath), null, true),
  OnlyTables = Table.SelectRows(dbFile, each [Kind] = "Table")
in
  (TableName as text) as nullable table => try
    OnlyTables{[Name = TableName]}[Data]
  otherwise
    null

and you can get the value of any existing table in the DB workbook via fGetDbTable(TableName)

However, is that what you really want?

The answer to your question means you don't have to add a new PowerQuery connection per table in DB that you want to use, but you still want to do something with it anyway, right?

Consider this:

Expression.Evaluate(
  "fMyTest({1,2,3})",
  [
    fMyTest = (nums as list) => Text.Combine("Sum is ", Text.From(List.Sum(nums)), "!")
  ] & #shared
)

#shared is the current context, all parameters, queries, and functions available to the code. Basically, this causes "fMyTest({1,2,3})" to run under a context as if fMyTest was a query you'd created.

So what if you need to do the same thing in PowerQuery for every workbook, but don't want to edit every workbook per change?

If, in a safe location that you trust the editors of, you have a text file (or anything else, like a worksheet in a special tab in your DB workbook) that contains PowerQuery code that, when evaluated, will result in a record that defines every function and query you want to be in your other workbooks, then each of your workbooks can have a query called "DbQueries" with code like

let
  DbQueriesText = ... // how you get the text is up to you, here. Maybe getting a table from DB and then doing `Text.Combine(DbQueriesTextTable[Column1], "#(lf)")` to combine all rows separated by a line-feed character.
  DbQueries = Expression.Evaluate(DbQueriesText, #shared)
in
  DbQueries

And then other PowerQuery code in your workbooks can just be DbQueries[fIssuesForSpecificWorkbook](WorkbookSpecificValues).

Maintaining (or extracting via VB) Power Query code that each workbook uses will still have issues, but at least now you can centralize as much of your Power Query code as possible. Depending on your needs, you'll still likely need to write and run some VBA macros to get everything as effortless as possible, and it might not be worth investing the time it would take to get there.