Excel.Workbook Function in Power Query M (5 Easy Examples)

If you want to pull data out of a separate Excel file in Power Query, the Excel.Workbook function is what reads that file’s contents for you.

You pass it the bytes of a workbook, and it hands back a table listing everything inside (sheets, tables, named ranges) that you can then drill into.

In this article, I’ll show you how to use Excel.Workbook to open an external workbook, find the sheet or table you want, and get clean data out of it.

Table of Contents

Syntax of Excel.Workbook Function

Excel.Workbook(workbook as binary, optional useHeaders as any, optional delayTypes as nullable logical) as table

where

workbook (required, binary). The contents of the Excel file as binary data. You almost always get this from File.Contents("C:\path\to\file.xlsx") inside Power Query.
useHeaders (optional, any). Controls whether the first row of each sheet is treated as column headers. Pass true to promote the first row, false (the default) to leave generic Column1, Column2 names. You can also pass a record here for finer control (see below).
delayTypes (optional, nullable logical). When true, Power Query skips automatic type detection and returns every column as any. The default is false.

The record form of useHeaders lets you set three fields at once:

UseHeaders (logical). Same as passing true/false directly. Promotes the first row to headers.
DelayTypes (logical). Same as the delayTypes argument. Skips type detection when true.
InferSheetDimensions (logical). When true, Power Query ignores the dimension metadata stored in the file and scans the sheet to work out the real used range. Useful when that metadata is wrong or missing.

Returns: a navigation table with one row per object in the workbook, with the columns Name, Data (the object’s contents as a nested table), Item, Kind (Sheet, Table, or DefinedName), and Hidden. You reach the actual data by drilling into the Data column.

In plain terms, Excel.Workbook does not hand you your data directly. It hands you a list of what is inside the file, and you pick the sheet or table you want from there.

Example 1: Open a workbook and see its navigation table

Say you have a file at C:\Reports\GreenhouseSensors.xlsx and you want to see what is inside it.

let
    Source = Excel.Workbook(File.Contents("C:\Reports\GreenhouseSensors.xlsx"))
in
    Source

This returns the top-level navigation table: one row for each object in the file.

The navigation table looks like this (illustrative):

Name	Data	Item	Kind	Hidden
Greenhouse	[Table]	Greenhouse	Sheet	false
ReadingsLog	[Table]	ReadingsLog	Table	false

Each row points at a sheet, table, or defined name. The Data column holds that object’s contents as a nested table, which is what you drill into next.

Example 2: Drill into one sheet with delayTypes set to true

Now you want just the Greenhouse worksheet, and you want it fast, so you skip type detection by passing true for delayTypes.

let
    Source = Excel.Workbook(File.Contents("C:\Reports\GreenhouseSensors.xlsx"),null,true),
    Greenhouse_Sheet = Source{[Item="Greenhouse",Kind="Sheet"]}[Data]
in
    Greenhouse_Sheet

This returns the raw cell grid of the Greenhouse sheet.

The columns come back named Column1, Column2, and so on. The header row is still sitting in the first data row, and every column is untyped because you set delayTypes to true.

Example 3: Promote the headers the recommended way

The previous example left the real headers stuck in row one. Here you promote them so the first row becomes proper column names.

let
    Source = Excel.Workbook(File.Contents("C:\Reports\GreenhouseSensors.xlsx"),null,true),
    Greenhouse_Sheet = Source{[Item="Greenhouse",Kind="Sheet"]}[Data],
    Promoted = Table.PromoteHeaders(Greenhouse_Sheet,[PromoteAllScalars=true])
in
    Promoted

This returns the same sheet data, but now the first row is promoted to real column names.

Microsoft recommends Table.PromoteHeaders with [PromoteAllScalars=true] over the useHeaders=true shortcut, because useHeaders is culture-dependent and can behave differently across regional settings.

Example 4: Pull a defined Table by its Kind

A workbook often holds both worksheets and formal Excel Tables. Here you want only the ReadingsLog Table, so you filter the navigation table down to Kind = "Table" first.

let
    Source = Excel.Workbook(File.Contents("C:\Reports\GreenhouseSensors.xlsx"),null,true),
    OnlyTables = Table.SelectRows(Source,each [Kind]="Table"),
    ReadingsLog = OnlyTables{[Item="ReadingsLog"]}[Data]
in
    ReadingsLog

This returns the contents of just the ReadingsLog Excel Table.

Filtering on Kind first is how you keep sheet objects and table objects apart, which matters when a sheet and a table share a similar name.

Example 5: Fix a wrong used range with InferSheetDimensions

Sometimes a sheet reports the wrong used range, so columns or rows go missing. The options record lets you turn on InferSheetDimensions to recompute the range.

let
    Source = Excel.Workbook(File.Contents("C:\Reports\GreenhouseSensors.xlsx"),[UseHeaders=true,InferSheetDimensions=true],null),
    Greenhouse_Sheet = Source{[Item="Greenhouse",Kind="Sheet"]}[Data]
in
    Greenhouse_Sheet

This returns the Greenhouse sheet with headers promoted and the used range recomputed by scanning the sheet, which pulls back any data that the stale dimension metadata had cut off.

InferSheetDimensions works on Open XML files (.xlsx) only, not the legacy .xls format.

Things to keep in mind with Excel.Workbook

Excel.Workbook is not Excel.CurrentWorkbook. This trips a lot of people up. Excel.Workbook reads an external file’s bytes (via File.Contents). Excel.CurrentWorkbook() reads the tables in the workbook the query already lives in. Different inputs, different jobs.
Hidden sheets and ranges still show up. The navigation table lists hidden objects too, flagged with true in the Hidden column. Filter them out with Table.SelectRows(Source,each [Hidden]=false) if you only want the visible ones.
Legacy .xls files behave differently. Some options, including InferSheetDimensions, do not apply to the old binary .xls format. Convert to .xlsx when you can.

Performance and query folding

File sources do not fold. Once you read a workbook with Excel.Workbook, every transformation after it runs locally on your machine, so there is no pushing work back to a server.

Passing delayTypes=true skips automatic type detection, which speeds up the read on wide sheets. You can apply the types you actually need yourself afterward.

Common questions about Excel.Workbook

What is the difference between Excel.Workbook and Excel.CurrentWorkbook?

Excel.Workbook reads an external .xlsx file’s bytes that you load with File.Contents. Excel.CurrentWorkbook() reads the tables in the same workbook the query is running in. Reach for Excel.Workbook when you are importing another file, and Excel.CurrentWorkbook() when the data already lives next to your query.

How do I get just one sheet or one table?

Drill into the navigation table. For a sheet, use Source{[Item="SheetName",Kind="Sheet"]}[Data]. For a defined table, use Kind="Table". The Kind filter is what keeps a sheet and a table with similar names apart.

Why is my first row showing up as data instead of headers?

By default Excel.Workbook leaves the header row in the data and names the columns Column1, Column2, and so on. Promote it with Table.PromoteHeaders([PromoteAllScalars=true]), as in Example 3.

List of All Power Query Functions

Related Power Query Functions / Articles: