Duplicate vs Reference in Power Query
Duplicate copies a query and its steps into a new fork. Reference creates a new query that starts from another query’s output, so one cleaning can feed many tables. Same menu, opposite intent.
Friends! Students duplicate everything “to be safe” and then fix the same spelling twice. Why? They never learned Reference. How? We clean Sales Raw once for FreshBasket, Reference it for a city summary, and only Duplicate when we truly need a fork. Query Dependencies shows the chain.
मित्रांनो! Students “सुरक्षित राहायचे” म्हणून सगळे Duplicate करतात आणि नंतर तोच spelling दोनदा सुधारतात. का? Reference शिकलेच नाही. कसे? Sales Raw एकदा स्वच्छ करायचे, Reference ने city summary, Duplicate फक्त खरा fork हवा असेल तर. Query Dependencies साखळी दाखवते.
मित्रों! Students “सेफ रहना” कहकर सब Duplicate करते हैं और फिर वही spelling दो बार ठीक करते हैं. क्यों? Reference सीखा ही नहीं. कैसे? Sales Raw एक बार साफ़ करें, Reference से city summary, Duplicate सिर्फ जब सच में fork चाहिए. Query Dependencies चेन दिखाती है.
Quick answer
Vertical path:
- Finish cleaning on Sales Raw (types, blanks, rename).
- Need a shared child? Right-click → Reference.
- Need an independent experiment? Right-click → Duplicate.
- Turn Enable load off on staging queries you do not want in the model.
- View → Query Dependencies to see forks vs chains.
- Fix a city spelling on the base once; referenced queries inherit it on refresh.
Real example: one clean sales table, two uses
Sales Raw after cleaning (three rows, total 190):
| City | Year | Channel | Amount |
|---|---|---|---|
| Mumbai | 2025 | Online | 100 |
| Mumbai | 2024 | Store | 50 |
| Pune | 2025 | Online | 40 |
Reference path — query Sales 2025 references Sales Raw, then filters Year = 2025:
- Rows in from parent: all three.
- Filter keeps Mumbai 100 and Pune 40.
- Total 140.
- You did not re-trim or re-type columns. Parent already did.
Duplicate path — you copy Sales Raw to experiment with a harsh filter. Changing the duplicate’s early steps does not update Sales Raw. Two recipes.
Why Reference is the helper for shared cleaning: one place to fix “Pune ” with a trailing space; every child sees the fix.
What do I need before this guide?
- Applied Steps and data types.
- Course: Reference vs Duplicate.
Before and after (look at the tables first)
Before
आधी (Before)
पहले (Before)
After
नंतर (After)
बाद में (After)
Duplicate = fork
Duplicate copies the query and its steps. Later changes on one copy do not update the other.
Duplicate query आणि तिचे steps कॉपी करतो. नंतर एका कॉपीवरील बदल दुसरी अपडेट करत नाही.
Duplicate query और उसके steps कॉपी करता है. बाद में एक कॉपी पर बदलाव दूसरी को अपडेट नहीं करता.
- Right-click query → Duplicate.
- Full copy of steps appears.
- Safe sandbox when you might break something.
- Cost: two places to maintain if both stay in production.
Reference = chain
Reference starts from the output of another query. Fix the source once; children see the clean table.
Reference दुसऱ्या query च्या output पासून सुरू होतो. Source एकदा ठीक करा; children स्वच्छ टेबल बघतात.
Reference दूसरी query के output से शुरू होता है. Source एक बार ठीक करें; children साफ़ टेबल देखते हैं.
Duplicate = fork. Reference = chain. Use reference when two queries should share the same cleaning.
Duplicate = fork. Reference = chain. जेव्हा दोन queries एकच cleaning शेअर करतात तेव्हा reference वापरा.
Duplicate = fork. Reference = chain. जब दो queries एक ही cleaning शेयर करें तब reference इस्तेमाल करें.
- Right-click → Reference.
- New query’s first step points at the other query.
- Add only the extra filters or Group By you need.
- Disable load on the parent if it is only a staging table.
Enable load and dependencies
- Staging / helper queries: Enable load off — they clean, they do not clutter the model.
- Final tables the report needs: Enable load on.
- Query Dependencies diagram: arrows show reference chains. Flat copies show duplicates.
Mistakes and calm fixes
| Symptom | Likely cause | Fix |
|---|---|---|
| Fixed spelling twice | Used Duplicate for shared clean | Switch to Reference |
| Model full of helpers | Enable load left on | Disable load on staging |
| Child not updating | Actually a Duplicate | Recreate as Reference |
| Circular reference | Query points at itself oddly | Simplify dependency chain |
Ravindra Bagale's Tip
Interview line: “Duplicate forks the steps; Reference chains from the output. I reference shared cleaning and disable load on staging.” Got it?
Ravindra Bagale's Tip – मराठी
Interview line: “Duplicate steps चा fork करतो; Reference output पासून chain करतो. मी shared cleaning साठी reference करतो आणि staging वर enable load बंद करतो.” समजलं का?
Ravindra Bagale's Tip – हिंदी
Interview line: “Duplicate steps का fork करता है; Reference output से chain करता है. मैं shared cleaning के लिए reference करता हूँ और staging पर enable load बंद करता हूँ.” समझ में आया?
Practice task
- Clean Sales Raw.
- Reference → filter 2025.
- Duplicate → experiment with Channel = Online.
- Draw which one should stay in production and why.
Got it? Duplicate forks; Reference chains. Clean once, reuse with Reference. Next: star schema. Let us go ahead.
समजलं का? Duplicate fork करतो; Reference chain करतो. एकदा स्वच्छ करा, Reference ने पुन्हा वापरा. पुढे: star schema. आता पुढे जाऊया.
समझ में आया? Duplicate fork करता है; Reference chain करता है. एक बार साफ़ करो, Reference से फिर इस्तेमाल करो. आगे: star schema. अब आगे बढ़ते हैं.
Frequently asked questions
Duplicate vs Reference?
Duplicate copies steps into a new query. Reference creates a new query that starts from the other query’s result.
When do I duplicate?
When the second path must change early steps differently, or you are experimenting safely.
When do I reference?
When several queries should share the same cleaning, and you want one place to fix it.
Why is my model full of staging tables?
Enable load is still on for helpers. Turn it off for queries that only feed others.
Does reference copy the data twice in the source file?
No. It chains transformations. What loads into the model depends on Enable load.
Course lesson?
Reference vs Duplicate in Power Query essentials.