Excel.CurrentWorkbook
Reads the tables inside the Excel workbook that holds the query, so you can use data already typed in the same file.
Excel.CurrentWorkbook()Returns: A table with two columns, Name (the table name) and Content (the table's rows).
What it does
Excel.CurrentWorkbook reads the Excel tables in the workbook where the query lives. It is the way to bring in a small table you maintain by hand, such as a list of approved vendors, a mapping table or a list of rates, without saving it as a separate file. It works only inside Excel, not in Power BI Desktop.
Syntax and arguments
- This function takes no required arguments. Call it as
Excel.CurrentWorkbook().
Example
A controller keeps a table called OpenInvoices in the same workbook as the query, and wants its amount column totalled.
let
Source = Excel.CurrentWorkbook(),
OpenTable = Source{[Name = "OpenInvoices"]}[Content],
Total = List.Sum(OpenTable[amount], Precision.Decimal)
in
Total
Source lists the tables in the workbook. The lookup on [Name] selects OpenInvoices, and its [Content] is the table of rows. The total then comes from the amount column.
Example: a second, different use
Using a mapping table in the workbook to translate account codes: convert the table to a record with Record.FromTable on its Name and Value columns, then look up each code.
Things to watch
- Use Excel tables (Insert > Table), not plain ranges. A plain range is not found by name.
- If the table grows, the query picks up the new rows on refresh. Make sure the table range is correct.
- The data is typed by a person, so check for stray spaces and mixed types before you trust a total.
- Refresh the query after you change the table, or the old values stay in the result.
In business terms
Rate tables, mapping tables, approved lists and manual adjustments are often kept in a workbook next to the report. Reading them from the same file keeps the inputs and the output in one place for the reviewer.