Choosing Reference vs. Duplicate Queries

This episode focuses on a practical PL-300 objective: Choosing Reference vs. Duplicate Queries. A referenced query remains dependent on the output of its source query, while a duplicated query creates a separate copy of the transformation logic at that point. The discussion keep…

Suppose you’re preparing sales data in Power BI and you need two related outputs. One output will feed the sales analysis. The other will contain records that need a quality review.

Both should begin with the same cleaned sales data. You can create the second query as a reference or as a duplicate, and both choices may appear to work when you first inspect the rows. What evidence tells you that you chose the one that fits the requirement, rather than a configuration that merely produces today’s expected table?

That decision is part of the PL-300 scope. You’re expected to identify when to use reference or duplicate queries and understand the resulting impact. The important word is when. This isn’t a test of whether you can find two commands in an interface. It’s a test of whether you understand the dependency each command creates and can connect that behavior to the business requirement.

This work belongs in Power Query, Power BI’s data acquisition and transformation environment. Power Query is where you connect to data and shape it before that prepared data is used in the semantic model. A reference or duplicate changes the organization of that preparation logic. It doesn’t create a measure, alter a visual interaction, or grant a person access in the Power BI service. Those are different layers with different responsibilities.

A referenced query builds on the output of another query. Think of an existing query called Clean Sales. It connects to the sales source, keeps the required columns, assigns the intended data types, and removes rows that the business has declared invalid.

If you create a reference from Clean Sales, the new query starts from the output produced by Clean Sales. The new query is therefore downstream from it. You can add more transformations to the referenced query, but its starting point remains the result of the upstream query.

A duplicate behaves differently. When you duplicate Clean Sales, Power Query copies its current transformation steps into another query. The copied query becomes an independent branch.

At the moment of duplication, the two queries can look identical because they contain the same sequence of steps. That visual similarity doesn’t mean they retain a live dependency. Later changes to the original sequence aren’t automatically inherited by the duplicate.

That distinction produces a practical decision rule. Use a reference when multiple outputs should share upstream preparation logic. Use a duplicate when you intentionally need an independent transformation path. Neither option is universally better. The appropriate choice depends on whether the requirement calls for shared maintenance or deliberate separation.

Consider a hypothetical sales and operations solution. The source contains order date, order number, customer identifier, and product identifier. Quantity, unit price, sales region, and order status complete each record. The analyst creates a Clean Sales query that selects those fields, assigns suitable types, standardizes the region values, and removes rows missing an order number. Two business teams need different results from that prepared data.

The reporting team needs completed sales rows for the semantic model. The data quality team needs rows whose customer identifier or product identifier is missing. Both teams agree that source selection, type assignment, region standardization, and the order-number rule should be governed in one place. Only the final filtering differs. That’s a shared-upstream-logic requirement, so references are the natural choice.

The analyst can create one reference from Clean Sales and call it Completed Sales. That branch filters the prepared output to the completed order status. A second reference, perhaps called Sales Exceptions, filters the same prepared output for missing customer or product identifiers. Completed Sales and Sales Exceptions have different downstream purposes, but they both depend on the output of Clean Sales.

Now imagine the source begins sending the region value North-East with a hyphen, while the required standard is Northeast without one. The analyst updates the standardization logic in Clean Sales. Because both downstream queries build on that output, they both receive the corrected region value when Power Query evaluates their results. The shared rule remains centralized rather than being maintained separately in every branch.

A duplicate would give a different maintenance structure. If the analyst duplicated Clean Sales twice and then added the two filters, each duplicate would contain its own copied version of the earlier preparation steps. Correcting the region rule in one copy wouldn’t, by itself, correct the other. The analyst would need to identify every independent branch that requires the same change and update each one appropriately.

That doesn’t make duplication defective. It means duplication expresses a different intention. Suppose the operations team wants to test a substantially different interpretation of the sales source without having later changes to the governed Clean Sales process flow into its experiment. The analyst may want the new path to begin with the current steps but then evolve independently. A duplicate supplies that independent starting point.

The requirement has to say, in substance, which relationship is wanted. If the request is, “Create several outputs that must continue to use the same cleansing rules,” the requirement points toward references. If it’s, “Use the current query as a starting template, but allow this version to change independently,” it points toward a duplicate. Choosing by habit ignores the most important consequence of the choice.

It’s also important not to confuse shared logic with identical output. A referenced query can add its own filters, column selections, and other transformations after it receives the upstream result. Completed Sales and Sales Exceptions can therefore return different rows even though both reference Clean Sales. The dependency concerns their shared starting logic. It doesn’t require every downstream result to remain identical.

Likewise, two duplicate queries can continue to produce identical rows for a long time. That doesn’t turn them into references. Their relationship is determined by how the transformation paths were created and maintained, not by whether today’s outputs happen to match. If you only compare a few rows, you might miss the architectural difference entirely.

This is why successful creation isn’t sufficient validation. Power BI accepting the command proves that a query was created. It doesn’t prove that the dependency matches the business requirement. For the sales solution, the analyst needs evidence from the query layer. The names should make each query’s purpose understandable, the transformation definitions should show which branch builds on which output, and the resulting rows should satisfy the intended conditions.

Start verification with a known input condition. Include a completed order with a valid customer and product, an incomplete order, and a completed order with a missing customer identifier. After the queries are evaluated, Clean Sales should reflect the shared cleaning rules.

Completed Sales should contain the rows allowed by its completed-status condition. Sales Exceptions should contain the prepared rows that meet the missing-identifier condition. Those outputs test the downstream transformations.

Then test the dependency itself. Introduce a source value affected by a shared cleansing rule, such as the alternate region spelling. Update that rule in Clean Sales and evaluate the downstream outputs again. If Completed Sales and Sales Exceptions were intended to reference Clean Sales, the standardized value should appear in both relevant outputs. That observed result is evidence that shared upstream logic is reaching both branches.

For a duplicate, the test should reflect independence. Create the duplicate from the current query, then change a preparation rule in the original. Inspect both outputs after evaluation. If the duplicate is intentionally independent, you shouldn’t assume it will inherit the later change. Its copied transformation sequence has to be reviewed and changed separately when that’s what its own requirement calls for.

Notice what these tests don’t rely on. A report visual showing a plausible total isn’t enough to establish that the query structure is correct. The total may look right with today’s limited data even though two independent copies contain rules that will drift later. A successful refresh isn’t enough either. Successful processing can produce technically valid tables whose dependency structure doesn’t satisfy the maintenance requirement.

The query output is the first place to validate this behavior because the choice was made in Power Query. Downstream checks can still be useful when prepared data enters the semantic model and supports reports. If a cleaning correction should affect the figures consumed by a report, you can confirm that outcome after confirming the query results. But changing a measure or visual to conceal an incorrect query output doesn’t repair the preparation logic.

Suppose the sales report looks correct under its normal filter, but a regional selection exposes old spellings in Sales Exceptions. The tempting response might be to modify the visual or add another report filter. That treats the symptom at the report layer. The actual question is whether the region standardization rule exists in the shared upstream query, in only one downstream branch, or in one independent duplicate.

The analyst should reproduce the condition with a known regional value and compare expected with actual query output. If Completed Sales contains Northeast while Sales Exceptions still contains North-East, inspect the transformation paths. Perhaps Sales Exceptions was created as a duplicate even though the requirement called for shared cleaning. Perhaps both are references, but an additional downstream step changed the value in one branch. The observed divergence tells the analyst where to investigate without blaming an unrelated visual.

If the cause is an unintended duplicate, correcting it means restoring the architecture required by the business. The analyst can create the exceptions branch from the governed Clean Sales output, apply the exception-specific filter, and then test both ordinary and alternate region values. The normal case confirms that the original requirement still works. The alternate spelling confirms that the shared correction reaches the branch that previously failed.

If the independent path was intentional, replacing it with a reference would be the wrong correction. The operations experiment might need to retain its own rules even while the governed process changes. In that case, the analyst should document through clear query purpose and observable output that the paths are meant to differ. Independence is only a problem when it conflicts with the requirement.

Maintenance is where an accidental choice becomes expensive. With references, a common upstream rule can be changed where that shared rule is owned. The dependent outputs then build on the revised result.

With duplicates, common-looking rules exist in separate branches. Each branch may need its own review, testing, and correction. If the team mistakes duplicates for linked copies, one branch can retain an outdated rule while another receives the fix.

The opposite mistake also has consequences. An analyst may create a reference because centralized maintenance sounds attractive, even though the second process is supposed to be insulated from changes to the first. A later modification to the upstream query then affects the referenced branch because that branch depends on the upstream output. The configuration is behaving correctly, but it’s implementing the wrong requirement.

That’s why labels such as safer, cleaner, or more efficient aren’t enough to choose between the options. Ask who owns the upstream rules, whether the branches should inherit later changes, and whether independent evolution is intentional. Those questions turn a vague feature preference into a defensible data-preparation decision.

You should also separate the known effect from assumptions you haven’t tested. The supported distinction here is about copied steps, shared output, maintenance, and dependency structure. Don’t look at the word reference and automatically conclude that every other characteristic of the solution has been optimized. Validate the behavior that matters to the requirement rather than attaching unrelated promises to the feature name.

A useful quality check begins with three statements. First, name the required outcome in business terms. Second, identify Power Query as the layer that owns this transformation dependency. Third, state what evidence would distinguish a live upstream dependency from an independent copied path. If you can’t state the expected evidence, you probably haven’t made the requirement precise enough to validate the result.

For the governed sales solution, the completed and exception outputs should share the same cleansing foundation. References express that relationship because both branches build on Clean Sales. For the separate operations experiment, a duplicate can express deliberate independence because it begins with copied steps and then follows its own path. The same source and initially identical rows can therefore lead to different correct choices.

When you troubleshoot, resist changing several layers at once. Capture the failing condition, inspect the query output and transformation path that control it, make the smallest correction consistent with the requirement, and retest. Check the ordinary case as well as the condition that exposed the problem. That prevents a narrow repair from making one output look right while quietly breaking another.

This habit carries beyond one pair of commands. Power BI solutions connect data preparation, the semantic model, calculations, reports, refresh, and service access. An observed report problem may begin in any of those places. Here, however, the defining behavior is a Power Query dependency decision. Recognizing that boundary lets you investigate the cause directly and then confirm any downstream effect without confusing the layers.

A reference and a duplicate can begin with the same rows, but they communicate different intentions. A reference keeps a downstream query connected to shared upstream preparation, while a duplicate creates an independent path from copied steps. Choose between them by asking whether later changes should be inherited or managed separately. Then prove the choice in Power Query.

Inspect the transformation relationship, evaluate known input conditions, and compare the resulting outputs after a controlled upstream change. That evidence tells you more than a successful command or a plausible report total. When the dependency matches the business requirement and both the normal and failing conditions behave as expected, you have a solution you can explain, maintain, and troubleshoot.

Choose between referencing and duplicating a query for two downstream needs. How would you verify the dependency, refresh, and maintenance consequences of that choice? Use the matching episode reference as the visual and technical handoff for practice with this concept. This lesson was produced by Bare Metal Cyber dot com.

Choosing Reference vs. Duplicate Queries

headphones Listen Anywhere

More Options »
Broadcast by