Import from Excel: tables, sheets and the navigation table
Pick the right object from a workbook and get clean columns from it.
~9 min read · beginner · Module 2: Importing and inspecting data
By Tahir Nas · Examples run in our simulator · Updated
Before this: Remove report banners before promoting headers
Excel.Workbook doesn't return your data directly. It returns a navigation table: one row per sheet, table and named range, with the content in a Data column. The Navigator dialog you see when importing is a friendly view of this table.
Input workbook Book
Table Orders
| Column1 | Column2 |
|---|---|
| OrderID | Qty |
| A-1 | 2 |
| A-2 | 5 |
Sheet Export
| Column1 | Column2 |
|---|---|
| Orders export | null |
| null | null |
| OrderID | Qty |
| A-1 | 2 |
| A-2 | 5 |
let
Source = Excel.Workbook(Book),
Objects = Table.SelectColumns(Source, {"Name", "Kind"})
in
ObjectsResult
| Name | Kind |
|---|---|
| Orders | Table |
| Export | Sheet |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Prefer a named table
A formatted Excel table (Insert → Table) has a name, a header row and a clear boundary. Its Data arrives with the right column names already. Select it by name and kind, not by position:
Input workbook Book
Table Orders
| Column1 | Column2 |
|---|---|
| OrderID | Qty |
| A-1 | 2 |
| A-2 | 5 |
Sheet Export
| Column1 | Column2 |
|---|---|
| Orders export | null |
| null | null |
| OrderID | Qty |
| A-1 | 2 |
| A-2 | 5 |
let
Source = Excel.Workbook(Book),
Orders = Source{[Item = "Orders", Kind = "Table"]}[Data]
in
OrdersResult
| OrderID | Qty |
|---|---|
| A-1 | 2 |
| A-2 | 5 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Source{[Item = "Orders", Kind = "Table"]} finds the row whose Item and Kind match; [Data] takes its table.Sheets need cleaning
A sheet is just the used range of cells. Titles, blank rows and notes all come along, and the columns are called Column1, Column2… Treat it like a report-style CSV: skip the banner, then promote headers.
Input workbook Book
Table Orders
| Column1 | Column2 |
|---|---|
| OrderID | Qty |
| A-1 | 2 |
| A-2 | 5 |
Sheet Export
| Column1 | Column2 |
|---|---|
| Orders export | null |
| null | null |
| OrderID | Qty |
| A-1 | 2 |
| A-2 | 5 |
let
Source = Excel.Workbook(Book),
Sheet = Source{[Item = "Export", Kind = "Sheet"]}[Data],
Skipped = Table.Skip(Sheet, each [Column1] <> "OrderID"),
Promoted = Table.PromoteHeaders(Skipped)
in
PromotedResult
| OrderID | Qty |
|---|---|
| A-1 | 2 |
| A-2 | 5 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Why tables are more refresh-safe
- A table grows with new rows automatically; a sheet's used range can include stray formatting far below the data.
- Moving or inserting a banner row on a sheet changes positions; a table's header stays its header.
- Selecting by name survives someone reordering the sheets;
Source{0}doesn't.
Where to find this in Excel
- Excel for Windows
- Data > Get Data > Excel Workbook
- Excel for Mac
- Data > Get Data (Power Query) > Excel Workbook
- What you see
- The Navigator lists the workbook's sheets, tables and suggested tables; pick one and select Transform Data.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Excel connector (Microsoft Learn); Where to get data (Microsoft Learn); Import and shape data in Excel for Mac (Power Query) (Microsoft Support).
Common mistakes
- Navigating with
Source{0}[Data], which silently picks a different sheet when someone reorders the workbook. - Importing a sheet when a named table exists, then fighting banner rows and blank columns.
- Forgetting that hidden sheets still appear in the navigation table (Hidden = true).
Excel, Power BI and the simulator
- Excel: Data → Get Data → From File → From Workbook. Power BI Desktop: Get data → Excel workbook. Both show the same Navigator.
- To read tables in the same workbook, Excel uses Excel.CurrentWorkbook() instead; that function is Excel-only and the simulator doesn't run it.
- The simulator's workbooks are parsed fixtures with values only: no formulas, macros, links or embedded queries.
Next step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Importing data quiz