Power QueryExercises

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
    Typed

Query 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
    Result

Result

CustNameSpend
C1Ana32
C2Ben35

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

The output query contains no cleaning at all: OrdersStaging already trimmed and upper-cased the customer codes.

Reference vs Duplicate

ActionCreatesWhen the original changes
ReferenceA new query whose Source is the original query's nameThe new query follows
DuplicateA copy of all the original's stepsThe 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

  1. Source / staging: one query per source; import, type, clean. Not loaded.
  2. Transform: merges, appends and business rules on top of staging queries. Usually not loaded.
  3. 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).

Manage Power Query queries in Excel: load, refresh and edit

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.
Functions in this lesson:Table.NestedJoinTable.Group

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