Excel.CurrentWorkbook
Accessing DataReturns a navigation table of tables and named ranges from the current Excel workbook.
Syntax
Excel.CurrentWorkbook(optional options as nullable record) as tableParameters
| Name | Type | Required | Description |
|---|---|---|---|
options | record | No | An optional record with additional options. |
Return Value
table — A navigation table where each row represents a table or named range in the current workbook, with columns for Name, Content, and Kind.
Remarks
Excel.CurrentWorkbook is an Excel-only function that returns a navigation table of all named tables (ListObject objects) and named ranges defined in the workbook that contains the current Power Query query. It provides a way for queries within an Excel workbook to read data from other tables or cells in the same file, enabling self-contained, parameter-driven workbooks.
Excel only: This function is not available in Power BI Desktop, Power BI Service, dataflows, Fabric Notebooks, or any other Power Query host. It is exclusive to Excel's Power Query engine. Attempting to use it outside of Excel results in a DataSource.Error.
Navigation table columns:
- Name (text) — the name of the table or named range as defined in Excel.
- Content (table) — the data as a Power Query table. For named ranges, each row of the range becomes a table row.
- Kind (text) — either "Table" (for Excel structured tables / ListObject objects created via Insert > Table) or "DefinedName" (for named ranges created via Formulas > Name Manager).
Self-referencing and circular dependency: If a query is loaded back into a named table in the workbook, referencing that same table with Excel.CurrentWorkbook creates a circular dependency and will fail. Always reference a different table or named range — typically a table that holds input parameters or lookup data that no query loads into.
Parameter table pattern: A common and powerful pattern is to create a two-column Excel table (e.g., Parameters) with Name and Value columns, then read individual parameter values in M using record access. Users can update the parameter values directly in the spreadsheet without opening the Power Query editor. This technique works across multiple queries in the same workbook.
No authentication: Since the data comes from the local workbook, no credentials or data source settings are involved. Query folding is not supported.
Examples
Example 1: List all tables and named ranges in the current workbook
```powerquery
Example 2: Access a specific named table by name
```powerquery
let
Source = Excel.CurrentWorkbook(),
SalesTable = Source{[Name="SalesData"]}[Content]
in
SalesTableExample 3: Read a single parameter value from a named range
```powerquery
let
Source = Excel.CurrentWorkbook(),
ParamTable = Source{[Name="Parameters"]}[Content],
StartDate = ParamTable{[Name="StartDate"]}[Value]
in
StartDateExample 4: Use a workbook table as a dynamic filter list for another query
```powerquery
let
AllData = Excel.CurrentWorkbook(){[Name="AllOrders"]}[Content],
AllowList = Excel.CurrentWorkbook(){[Name="AllowedRegions"]}[Content],
AllowedValues = AllowList[Region],
Filtered = Table.SelectRows(AllData, each List.Contains(AllowedValues, [Region]))
in
Filtered