Power QueryExercises

Power Query in Excel and Power BI: same engine, different hosts

Tell which behaviour belongs to the M language and which belongs to the host application.

~9 min read · beginner · Module 1: How Power Query thinks

By Tahir Nas · Examples run in our simulator · Updated

Before this: Applied steps are a recipe, not a one-off edit

Power Query is an engine with its own language, M. Excel, Power BI Desktop and online tools such as dataflows host that engine. A query you write in one host usually works in another, because the transformation functions are the same. What differs is everything around the query: where the result goes, how refresh is triggered and which data sources are available.

What stays the same

  • The M language and its standard library: Table.SelectRows, Table.Group, Text.Trim and the rest behave the same.
  • The Applied Steps pane, the formula bar and the Advanced Editor.
  • Data types, cultures, null handling and error values.

That's why a query is portable: copy it from Excel's Advanced Editor into Power BI Desktop and, once the source step points at a reachable file, it produces the same table. The example below is pure M; nothing in it depends on the host.

let
    Source = #table({"Product", "Units", "Price"}, {{"Kite", "4", "19.5"}, {"Reel", "10", "6"}}),
    Typed = Table.TransformColumnTypes(Source, {{"Units", Int64.Type}, {"Price", type number}}, "en-US"),
    WithRevenue = Table.AddColumn(Typed, "Revenue", each [Units] * [Price], type number)
in
    WithRevenue

Result

ProductUnitsPriceRevenue
Kite419.578
Reel10660

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

What the host decides

TopicExcelPower BI Desktop
Where results goA worksheet table, a PivotTable, the Data Model, or connection onlyThe data model; untick Enable load to keep a query as a staging step
RefreshData → Refresh All, or per query; optional refresh on openRefresh in Desktop; scheduled refresh after publishing to the service
Host-only sourcesExcel.CurrentWorkbook() reads tables in the open workbookSources published through a gateway for scheduled refresh
Calculations after loadingWorksheet formulas, PivotTables, Power Pivot DAXDAX measures and calculated columns

Some functions only make sense in one host. Excel.CurrentWorkbook() reads the tables of the workbook the query lives in, so it exists in Excel but not in Power BI. The simulator has no open workbook either, so it refuses rather than pretending:

let
    Source = Excel.CurrentWorkbook(){[Name = "Orders"]}[Content]
in
    Source

Not run by the simulator

Excel.CurrentWorkbook is not supported in the Power Query Exercises simulator. See the compatibility page for the supported subset. This code is shown for real hosts only; see the simulator compatibility page.

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Choosing a host for practice

If your job uses Excel, practise loading to a table and to the Data Model. If it uses Power BI, practise with Enable load switched off for staging queries. The transformation skills in this course apply to both, and each lesson ends with notes on host differences like the ones below.

Where to find this in Excel

Excel for Windows
Data > Get Data > Launch Power Query Editor
Excel for Mac
Data > Get Data (Power Query) > Launch Power Query Editor
What you see
The same Power Query editor opens on top of the workbook, with your queries listed on the left.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Create, load, or edit a query in Excel (Power Query) (Microsoft Support); Import and shape data in Excel for Mac (Power Query) (Microsoft Support).

Power Query in Excel for Windows, Mac and the web

Common mistakes

  • Assuming a query failed because of the host when the real cause is a source path that only exists on one machine.
  • Loading every intermediate query to a worksheet or the model, which bloats the file; keep staging queries as connection-only (Excel) or with load disabled (Power BI).
  • Using Excel.CurrentWorkbook() in a query you intend to move to Power BI.

Excel, Power BI and the simulator

  • Excel: Close & Load To… lets you choose Table, PivotTable Report, PivotChart, Only Create Connection and Add this data to the Data Model.
  • Power BI Desktop: right-click a query in the Queries pane to toggle Enable load; disabled queries can still be referenced by other queries.
  • The simulator is a third host with deliberate limits: sources come from exercise fixtures, and host-only functions such as Excel.CurrentWorkbook raise UnsupportedFeature.
Functions in this lesson:Table.TransformColumnTypes

Next step

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

Take the Power Query basics quiz