Power QueryExercises

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

Column1Column2
OrderIDQty
A-12
A-25

Sheet Export

Column1Column2
Orders exportnull
nullnull
OrderIDQty
A-12
A-25
let
    Source = Excel.Workbook(Book),
    Objects = Table.SelectColumns(Source, {"Name", "Kind"})
in
    Objects

Result

NameKind
OrdersTable
ExportSheet

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

Column1Column2
OrderIDQty
A-12
A-25

Sheet Export

Column1Column2
Orders exportnull
nullnull
OrderIDQty
A-12
A-25
let
    Source = Excel.Workbook(Book),
    Orders = Source{[Item = "Orders", Kind = "Table"]}[Data]
in
    Orders

Result

OrderIDQty
A-12
A-25

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

Column1Column2
OrderIDQty
A-12
A-25

Sheet Export

Column1Column2
Orders exportnull
nullnull
OrderIDQty
A-12
A-25
let
    Source = Excel.Workbook(Book),
    Sheet = Source{[Item = "Export", Kind = "Sheet"]}[Data],
    Skipped = Table.Skip(Sheet, each [Column1] <> "OrderID"),
    Promoted = Table.PromoteHeaders(Skipped)
in
    Promoted

Result

OrderIDQty
A-12
A-25

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).

Your first Power Query in Excel, from table to refresh

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.
Functions in this lesson:Excel.WorkbookTable.SkipTable.PromoteHeaders

Next step

Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.

Take the Importing data quiz