Reference other queries: staging and reuse
Structure a workbook as staging queries plus output queries that reference them.
~9 min read · intermediate · Module 18: Nested data and JSON
By Tahir Nas · Examples run in our simulator · Updated
Before this: Applied steps are a recipe, not a one-off edit, Merge queries: choosing a join kind
A query can use another query's result by name, exactly like a step. That lets you clean each source once in a staging query and build several outputs on top of it. When the cleaning rule changes, every output follows.
Two staging queries, one output
Query OrdersStaging
let
Raw = #table({"OrderID", "Cust", "Amount"}, {{"A-1", " c1", "20"}, {"A-2", "C2", "35"}, {"A-3", "c1 ", "12"}}),
Clean = Table.TransformColumns(Raw, {{"Cust", each Text.Upper(Text.Trim(_)), type text}}),
Typed = Table.TransformColumnTypes(Clean, {{"Amount", type number}})
in
TypedQuery CustomersStaging
#table(type table [Cust = text, Name = text], {{"C1", "Ana"}, {"C2", "Ben"}})let
Totals = Table.Group(OrdersStaging, {"Cust"}, {{"Spend", each List.Sum([Amount]), type number}}),
Named = Table.NestedJoin(CustomersStaging, {"Cust"}, Totals, {"Cust"}, "T", JoinKind.LeftOuter),
Result = Table.ExpandTableColumn(Named, "T", {"Spend"})
in
ResultResult
| Cust | Name | Spend |
|---|---|---|
| C1 | Ana | 32 |
| C2 | Ben | 35 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Reference vs Duplicate
| Action | Creates | When the original changes |
|---|---|---|
| Reference | A new query whose Source is the original query's name | The new query follows |
| Duplicate | A copy of all the original's steps | The copy doesn't follow; two places to maintain |
Reference is almost always what you want for staging. Duplicate is for experiments, or when you genuinely need a variant that should evolve separately.
Layering
- Source / staging: one query per source; import, type, clean. Not loaded.
- Transform: merges, appends and business rules on top of staging queries. Usually not loaded.
- Output: the tables people use, shaped for the report. Loaded.
Separating sources into their own queries also avoids many formula firewall errors in real hosts, because each source is read by one query and combined in another (see privacy levels).
Where to find this in Excel
- Excel for Windows
- Queries pane or Data > Queries & Connections > right-click a query > Reference (or Duplicate)
- What you see
- Reference starts a new query from the original's result; Duplicate copies all of its steps.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Manage queries (Power Query) (Microsoft Support); Use Power Query to transform data (the Power Query editor) (Microsoft Learn).
Common mistakes
- Duplicating a query to build a second output, then fixing a bug in only one copy.
- Loading every staging query to a worksheet or the model.
- Repeating the same cleaning steps in several output queries instead of one staging query.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: right-click a query → Reference or Duplicate. View → Query Dependencies shows the resulting graph.
- Staging queries: Excel → Load To… → Only Create Connection; Power BI → untick Enable load.
- In the simulator, other queries in your workspace can be referenced by name in the same way.
Next step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Nested data and JSON quiz