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
| Item | Excel → Power BI | Power BI → Excel |
|---|---|---|
| Excel.CurrentWorkbook() | Not available: replace with Excel.Workbook on the file, or a parameter | Available |
| Local file paths | Work in Desktop; the service needs a gateway or cloud location | Work if the path exists on the PC |
| Parameters | Copy them too; consider service-editable parameters | Copy them too |
| Load settings | Untick Enable load on staging queries | Load staging queries as connection only |
| Calculations after loading | Worksheet formulas become DAX measures | DAX measures become PivotTable measures in the Data Model, or formulas |
| Privacy levels and credentials | Set again per source | Set 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
TargetsResult
| Region | Target |
|---|---|
| North | 5000 |
| South | 4200 |
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
- Refresh every query; fix sources and credentials first.
- Compare row counts and one control total with the original host.
- Set load settings: only output tables load.
- Recreate measures in the new calculation layer.
- Note any host-specific function you replaced, in a comment on the step.
Related pages
Lessons
Sources
Microsoft's official documentation for the features and functions used on this page.
- Query overview in Power BI Desktop (Concept, Microsoft Learn)
- Excel connector (Concept, Microsoft Learn)
- Behind the scenes of the Data Privacy Firewall (Concept, Microsoft Learn)
- Excel.CurrentWorkbook (Function reference, Microsoft Learn)
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.