Excel.Workbook
An Excel workbook's sheets as a navigation table: pick a sheet by its name and take its Data.
Group: Files and documents. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Excel.Workbook(workbook as binary, optional useHeaders as any, optional delayTypes as nullable logical) as table| Parameter | Type | Description |
|---|---|---|
workbook | binary | The workbook's bytes, from File.Contents, Web.Contents, a blob or any other binary. |
useHeaders | any optional | true to make each sheet's first row its headers. |
delayTypes | nullable logical optional | Accepted as Power Query writes it; it changes nothing here. |
Returns
table. A row per sheet: Name, Data (the sheet's cells as a table), Item, Kind ("Sheet") and Hidden.
Remarks
- The query Get data writes takes a sheet by its Item and Kind:
Source{[Item = "Sales", Kind = "Sheet"]}[Data]. - A sheet's columns are Column1, Column2 ... unless useHeaders is true. Numbers come as numbers, logicals as logicals, dates and times as datetimes and texts as texts; a cell that holds an Excel error (#N/A) is an error in its cell.
- A hidden sheet is listed too, with Hidden true.
- Workbooks in the .xlsx format (Office Open XML) are read. Any binary works, not only a file's: one made in M with
Binary.FromTextreads the same.
Examples
The sheets of a workbook
Excel.Workbook(File.Contents("C:\Data\book.xlsx"))[[Name], [Item], [Kind], [Hidden]]| Name | Item | Kind | Hidden |
|---|---|---|---|
| Sales & Costs | Sales & Costs | Sheet | false |
| Hidden | Hidden | Sheet | true |
One sheet, its first row as headers
The sheet's Note column has an Excel error in one cell, so this keeps the other columns.
let
Source = Excel.Workbook(File.Contents("C:\Data\book.xlsx"), true),
Sheet = Source{[Item = "Sales & Costs", Kind = "Sheet"]}[Data]
in
Table.SelectColumns(Sheet, {"Product", "Date", "Amount", "Paid"})| Product | Date | Amount | Paid |
|---|---|---|---|
| Chair | 2024-01-31 00:00:00 | 100.5 | true |
| Desk & lamp | 2024-02-01 12:00:00 | 7 | false |
| formula text | null | 107.5 | null |
The texts are as the cells hold them, a trailing space and a line break included; the dates come as datetimes.
Related functions
File.Contents, Table.PromoteHeaders, Csv.Document, Table.SelectColumns.