Ravindra BagaleCourses & study guides मराठी Track your progress

6. Power Query Essentials

6.6 Reference vs Duplicate

By Last updated:

Quick answer: the short, self-contained version of this lesson is the guide Power Query Duplicate vs Reference.

Start with a problem

Raja needs a Stores table (one row per store) built from the cleaned Orders query. If he copies the query, every future fix must be made twice.

Explain simply

Reference builds a new query on the output of another query; fixes flow through. Duplicate copies all steps into an independent query.

Show the data

Orders (clean, from 6.5 and Module 7):

Order ID Order Date (Date) Store Amount (Fixed decimal)
BLK-401 06-Apr-2026 Kothrud 1,250
BLK-402 06-Apr-2026 Baner 980
BLK-403 07-Apr-2026 Kothrud 450
BLK-404 07-Apr-2026 Baner 320
Total (4 rows) 3,000

About this data: This table uses columns: Order ID, Order Date (Date), Store, Amount (Fixed decimal). This table uses columns: Query, Source, Rows. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: pq_raw_pune_export.csv (source: src/practice/).

Demonstrate

  1. Right-click Orders in the Queries pane › Reference. Rename the new query Stores.

  2. In Stores: right-click Store › Remove Other Columns.

    Your result should look like this:

    Store
    Kothrud
    Baner
    Kothrud
    Baner

  3. Right-click Store › Remove Duplicates.

    Your result should look like this:

    Store
    Kothrud
    Baner

  4. Go back to Orders and add a step (for example filter out Baner); return to Stores and see it change. Then delete that test step.

Show the result

Your result should look like this (fictional sample):

Query Source Rows
Orders CSV file 4
Stores = Orders (Reference) 2

Explain what happened

Stores starts with Source = Orders, so it always uses the latest clean Orders output.

Explore further

  • Use Duplicate only for a fully independent experiment.

Detailed reference for this lesson:

Right-click a query in the Queries pane:

  • Duplicate – copies all the steps into a new independent query.
  • Reference – creates a new query whose source is the output of the original query. Changes to the original flow into the reference.

Use Reference to create, for example, a DarkStore dimension from a big flat orders file: reference the file query, keep the store columns, remove duplicates.

Ravindra Bagale's Tip

Friends, many students use Duplicate when they meant Reference, and then fix the same cleaning bug in two queries. Use Reference when a query should build on another query's cleaned result. Use Duplicate only when you want a fully independent copy. Keep this in mind!

Fix common mistakes

Wrong result Reason Correction
Bug fixed in Orders still visible in Stores Used Duplicate Rebuild Stores with Reference
Stores has 4 rows Forgot Remove Duplicates Remove duplicates on Store
Circular reference error Orders also referenced Stores Keep a one-way flow

Practise independently

Practice task

Build a Dates list from Orders the same way. How many rows?

Hints
  • 06-Apr and 07-Apr.
Solution

2 rows: 06-Apr-2026 and 07-Apr-2026.

Think like a professional

Reference chains (raw, then clean, then dimension) are how real projects stay maintainable.

Ravi Sir's Cards for this lesson

Reference vs Duplicate. You often need two tables from one source. The choice decides what happens when the original changes.Revision cardWhat is the difference between Reference and Duplicate in Power Query? Duplicate copies all steps into a new, independent query. Reference creates a new query that starts from the result of the originInterview cardReference vs Duplicate query. Both make a new query from an existing one in Power Query.Comparison card

मराठी cards