Spreadsheets

Reference or Duplicate in Power Query: which to use for statements

Reference or Duplicate in Power Query? See how each behaves with one cleaned bank statement feeding a monthly and a merchant summary, and which to pick.

Editorial illustration for Reference or Duplicate in Power Query: which to use for statements

Pick Reference when a second query should always follow the cleaned query it starts from, and Duplicate when you want an independent copy that you can change without touching the original. For one cleaned bank statement that feeds both a monthly summary and a merchant summary, Reference is usually the tidier choice.

The two commands sit side by side when you right-click a query in the Queries pane. They look alike, but they create very different things behind the scenes.

What does each command actually create?

  • Reference makes a new query whose only first step is "Source = the other query". It reads the output of the last step of the original, so any later change to the original flows into it.
  • Duplicate copies every step of the original into a brand new query. After the copy the two are strangers: fix a mistake in one and the other stays wrong.

Think of Reference as a link and Duplicate as a photocopy. With statements the link is what you want for the shared cleaning work, and the photocopy is what you want for experiments.

How do I set up one statement feeding two summaries?

Say you load a folder of monthly statements and clean them once: remove the header junk, fix the date column, make the amount a number, drop credits and rename the narration column.

  1. Name that cleaned query Statements_Clean. In Load To, choose Only Create Connection so it does not also fill a worksheet.
  2. Right-click Statements_Clean and choose Reference. Rename the new query Monthly. Add a Month column from the date, then Group By month with a Sum of amount.
  3. Right-click Statements_Clean again and choose Reference. Rename it Merchants. Group By the merchant column with a Sum of amount and sort descending.
  4. Load Monthly and Merchants to the sheet or the data model.

Now if the bank changes a column name, or you find a stray total row, you fix it once inside Statements_Clean and both summaries pick it up on the next refresh.

Reference or Duplicate: how do they compare?

SituationReferenceDuplicate
A cleaning rule changesFix it once, all dependants followRepeat the fix in every copy
You want a different early filterAwkward, because the base is sharedEasy, change the copy freely
Base query is renamedReference updates itselfNot affected
Base query is deletedDependants show an errorCopy keeps working
Trying a risky changeCan break the shared base if you edit itSafe sandbox for the experiment

Does Reference make refresh slower?

It can. In Excel each query is evaluated on its own, so two queries that start from the same folder read may each repeat the expensive early steps instead of sharing one result. Whether this matters depends on how big the folder is, so time a refresh before and after. If one statement folder takes 40 seconds to read and both summaries trigger that read, the refresh may cost about 40 + 40 = 80 seconds plus the grouping, where a single combined query would cost 40 plus the grouping. These numbers are only an illustration, not a benchmark.

Setting the shared base to connection only avoids loading a third copy of the rows into a sheet, which saves time and space, but it does not by itself ensure a single read. If the refresh feels slow, test a single query that outputs both tables, or ask whether you need one summary at all.

What breaks when I change the base query?

  • Renaming a column in the base: any Reference step that still mentions the old name fails with a column not found message.
  • Removing a column the summary needs: the group by step errors out, even though the base itself looks fine.
  • Changing the base to return a different shape, for example after switching from debits only to all rows: totals in the dependants change silently, with no error at all.

Which should I use for my own statements?

  • Use Reference for the shared cleaning and any number of summaries built from it.
  • Use Duplicate when you want to try a new cleaning idea without risking the working query, then delete or keep the copy.
  • Use Duplicate when two outputs need different early steps, such as one with credits kept and one with credits dropped.
  • Keep the base on connection only, and give every query a clear name so the Queries pane tells the story.

When folders of statements and several linked queries start to feel heavy, an expense tracker such as SpendixAI can take uploaded statements in PDF, CSV or Excel, let you review the rows and give you monthly and merchant reports without any query upkeep.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports