Power QueryExercises

Move a Power Query query from Excel to Power BI (and back)

Guide · By Tahir Nas · Examples run in our simulator · Published · Updated

Copying the queries

In the Power Query editor of either host, select queries in the Queries pane, copy (Ctrl+C) and paste them into the other host's Queries pane. Dependencies come along if you select them too. Alternatively, copy the whole text from the Advanced Editor into a new blank query.

What needs attention

ItemExcel → Power BIPower BI → Excel
Excel.CurrentWorkbook()Not available: replace with Excel.Workbook on the file, or a parameterAvailable
Local file pathsWork in Desktop; the service needs a gateway or cloud locationWork if the path exists on the PC
ParametersCopy them too; consider service-editable parametersCopy them too
Load settingsUntick Enable load on staging queriesLoad staging queries as connection only
Calculations after loadingWorksheet formulas become DAX measuresDAX measures become PivotTable measures in the Data Model, or formulas
Privacy levels and credentialsSet again per sourceSet again per source

Replace Excel-only sources

A query that starts from a table in the same workbook can't move as-is. Make the data come from a file both hosts can read, keeping the rest of the query intact. In the simulator, Excel.CurrentWorkbook raises UnsupportedFeature for the same reason Power BI can't run it:

Excel.CurrentWorkbook(){[Name = "Targets"]}[Content]

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.

A small, stable lookup table can also be written into the query itself, which both hosts (and the simulator) run:

let
    Targets = #table(type table [Region = text, Target = number], {{"North", 5000}, {"South", 4200}})
in
    Targets

Result

RegionTarget
North5000
South4200

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

type table [...] declares column types inline. Use this only for small values that change rarely and are owned by the report author.

Checklist after moving

  1. Refresh every query; fix sources and credentials first.
  2. Compare row counts and one control total with the original host.
  3. Set load settings: only output tables load.
  4. Recreate measures in the new calculation layer.
  5. Note any host-specific function you replaced, in a comment on the step.

Lessons

More resources

Sources

Microsoft's official documentation for the features and functions used on this page.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.