Power Query Reference: What It Does and When to Use It

In Excel Power Query, a reference is a new query built from the result of an existing query. In the Power Query Editor, right-click the query you want to reuse and choose Reference; unlike Duplicate, the new query remains connected to the original, so it can inherit later changes to that query.

What a reference means in Power Query

Power Query is Excel’s tool for importing, shaping, and combining data. A query records a sequence of transformation steps, such as removing columns, filtering rows, changing data types, or merging tables. You can make one query the starting point for another instead of repeating those steps.

For example, suppose a workbook imports a monthly transactions file. One query might clean the source by promoting headers, assigning data types, removing blank rows, and standardizing a region field. A referenced query can use that cleaned result to create a regional sales report, while another reference can filter the same cleaned result to show refunds. The shared preparation is maintained in one place.

A reference is a dependency, not a saved copy of the source data. The second query uses the first query’s output as its input. If the first query’s steps or results change, the dependent query may change when it is evaluated or refreshed. It is not the same as linking to a visible worksheet cell range, nor does it make an independent snapshot.

How to create a reference in Excel

In Excel for Microsoft 365 on Windows, open the Power Query Editor from the Data tab or open an existing query from Data > Queries & Connections. In the editor, use these steps:

Find the query you want to build on in the Queries pane.
Right-click its name and select Reference.
Rename the new query so its purpose is clear, then add the transformations needed for its own output.
Use Home > Close & Load or Home > Close & Load To to choose how to return the result to Excel.

The new query appears as a separate item in the Queries pane. It has its own later steps, so you can filter or select columns in the new query without adding those steps to the original. The original remains the common starting point for its other dependents.

For instance, if the base query is named CleanTransactions, a reference might be named SalesByRegion. You could then filter out refund rows or group the sales rows by region in SalesByRegion. A second reference named RefundReview could retain only refund rows. Keep broadly useful cleaning steps in the base query and report-specific steps in the references.

Reference versus Duplicate

These commands can look similar because both create another query, but their relationship to the original is different. Use Reference when you intend to build on the original query’s output and want shared preparation to remain centralized. Use Duplicate when you want a separate copy of the existing steps that can evolve independently.

Reference: Starts from the existing query’s result. Changes to that upstream query can flow into the referenced query.
Duplicate: Copies the query and its applied steps. The duplicate is a separate query; later edits to the original do not automatically become edits to the duplicate.

Consider a source table that needs ten common cleanup steps before two reports diverge. With references, those ten steps live in one base query and each report adds only its own steps. With duplicates, both copies contain their own cleanup steps. If a new cleanup rule is needed, it may have to be applied in both copies. Conversely, if the two copies need to become completely independent, a duplicate can be a clearer starting point.

Neither option is universally faster or safer. The right choice depends on whether the queries are meant to share ongoing logic. A reference reduces repeated definitions, but it also introduces a dependency: a change to the base query can affect every report that relies on it.

How references and query dependencies work

Power Query evaluates a query from its source and its sequence of steps. A referenced query uses another query as an upstream input, then applies its own steps. The Queries pane and query dependency view, where available in the editor, help show how source queries and dependent queries connect.

This relationship is useful for designing a workflow in layers. A source query can connect to a file or database; a staging query can clean and standardize data; and one or more output queries can shape it for different tasks. A change to a shared column name or data type in the staging layer can affect every output that expects the old version, so the dependency structure should be treated as part of the workbook’s design.

References do not promise that an intermediate result is stored once and reused as a permanent cache. Power Query may evaluate queries as needed, and source behavior, connector, privacy settings, and refresh process can affect what is read or recomputed. Avoid assuming that a reference always reduces refresh time or guarantees a single read of the underlying source.

When a reference is useful

A reference is especially helpful when several outputs need the same preparation but not the same final shape. It can reduce duplicated transformation logic and make it easier to maintain a workbook as source files or reporting needs change.

Separate reports from one clean table: Create individual references for sales by month, sales by region, and returns, all based on the same standardized transactions.
Reuse data type and naming fixes: Apply consistent date, currency, and column-name cleanup once, then build multiple outputs.
Keep a staging query distinct from deliverables: Maintain a clean intermediate result while loading only the finished tables that workbook users need.
Explore alternate filters: Create separate query branches for different categories or periods without changing the shared preparation steps.

A reference is less useful when the second query should not respond to changes in the first. For example, if you need a fixed historical snapshot that must not change when the original source is refreshed, a live query reference is not a snapshot mechanism. Store an appropriately controlled copy of the data or use a separate archival process that suits the requirement.

Loading, refreshing, and performance considerations

A query can be used as an intermediate step without necessarily putting its result on a worksheet. Excel’s load choices vary with the query and workbook, but Close & Load To commonly lets you choose a table, a PivotTable report, a connection, or the Data Model where supported. A base query used only by other queries often does not need a visible worksheet table. Keeping intermediate results out of sheets can prevent clutter, but it does not by itself guarantee faster refreshes.

When you refresh an output query, Power Query must obtain the data needed to produce that output, including its upstream dependencies. Whether it reuses an evaluated result or contacts the source again depends on the situation; do not treat a reference as a caching setting. If refresh is slow, examine the source connection, the number and cost of transformations, the volume of data, and whether the connector can fold steps back to the source.

Query folding means that compatible transformations can be translated into operations performed by the data source rather than entirely in Power Query. A reference does not automatically create or destroy folding. Later transformations may continue to fold, depending on the connector and steps. For large database tables, filtering rows and removing unneeded columns early can often help, but confirm the behavior for the actual source rather than assuming it.

Also consider privacy levels and credentials when a workflow combines data sources. A reference does not bypass Excel’s or Power Query’s privacy controls. If a refresh prompts for credentials or raises a privacy warning, inspect the data-source settings and the queries involved rather than trying to work around the warning by making extra references.

Common problems and how to avoid them

The most frequent issue is an unexpected downstream change. If a base query drops a column, renames it, changes its type, or filters out rows, every reference that depends on that output may behave differently or fail. Before making a broad change, check the dependent queries and their later steps.

Missing-column errors: A referenced query expects a column that was renamed or removed upstream. Keep shared column names stable, or update the dependent queries deliberately.
Unexpectedly different row counts: A filter added to the base query affects all branches. Put filters that apply to only one report in that report’s reference instead.
Confusing query names: Generic names make dependencies difficult to understand. Use names that signal both the data and purpose, such as CleanTransactions or ReturnsByMonth.
Duplicate-looking outputs: A referenced query may load to a worksheet even when it is intended only as an intermediate. Review its load destination and use a connection-only option where appropriate.
Slower refresh than expected: References are not a guaranteed performance optimization. Reduce unnecessary data and steps, and check source and folding behavior.

One common misconception is that a reference simply copies the rows currently visible in Excel. It instead creates a query-level relationship. Its result is determined by the upstream query and the new query’s steps when Power Query processes them; the result is not frozen merely because the reference was created.

Choosing between a reference and other approaches

Use a reference for a maintained branch in a transformation workflow. Choose Duplicate for an independent copy of the steps. If the goal is to append or merge data, use the corresponding combine operation rather than assuming that Reference combines tables: Reference only makes one query the input to another.

If you only need to reuse a result on a worksheet, loading a query to a table may be enough. If multiple reports need the same transformation logic, a base query plus references is often easier to maintain. If the workbook must preserve an unchanging historical record, use an explicit snapshot or archive design rather than relying on a query dependency.

Excel for the web does not provide the same full Power Query authoring experience as Excel desktop, so an existing workbook may be refreshable in some circumstances without offering every editing command. Excel for Mac also has a more limited set of Power Query capabilities and connectors than Excel for Windows, and availability can vary by build. If Reference is absent or a connector behaves differently, check the current feature support for that Excel platform and version.

Frequently Asked Questions

Does a Power Query reference update automatically?

It reflects the upstream query’s current logic when Power Query evaluates the dependent query, but it does not mean the output is continuously live. To see changed source data, refresh the relevant query or workbook connection. Automatic refresh behavior depends on workbook and connection settings.

Does Reference copy the data?

No. It creates a query that uses another query’s output as its starting point. It is not a guaranteed stored copy or historical snapshot, and it should not be used when you need data to remain fixed after the source changes.

Can I reference a query that is not loaded to a worksheet?

Yes. A query can serve as an input to other queries without displaying its result as a worksheet table. Use the available load options to keep intermediate queries out of the worksheet when that fits your workbook.

Can I edit a referenced query without changing the original?

Yes. You can add steps to the referenced query, and those steps belong to that query. However, the referenced query still depends on the original’s output, so changes to the original can affect the input available to the new steps.

Is Reference better than Duplicate?

Neither is inherently better. Reference is usually the right choice for related outputs that should share upstream transformations; Duplicate is appropriate when you want an independent copy that will not inherit later edits to the original.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.