Power BI衍生查询同步更新：Copy/Duplicate/Reference选哪个？

Based on your requirements, Reference is the right choice here—it’s the standard Power Query practice for creating dependent queries that stay in sync with a base query. Let me break down why, and how to implement it:

Why Reference is Better Than Duplicate/Copy #

• Duplicate creates an exact copy of your base query’s steps at the time of duplication. If you later add or modify steps in the original query, the duplicated queries won’t pick up those changes—they’re stuck with the old step list. That’s not what you want since you need all queries to sync when the base query updates.
• Copy (as you’ve seen) can lead to redundant data or disconnected steps, which defeats the purpose of keeping your workflow linked.
• Reference creates a query that uses the final output of your base query as its starting point. This means:
  • Any changes to the base query (new steps, updated merges, etc.) automatically flow through to all Reference queries.
  • When you refresh, Power BI first runs the base query (merging the latest files), then runs each Reference query’s specific modification steps on top of the updated base data.

How to Set It Up #

1. Keep your base query intact: Let’s say your existing query (with the merge steps) is named MergedBaseData. This is your single source of truth for the merged data.
2. Create a Reference for each derived output:
   • Right-click MergedBaseData in the Queries pane.
   • Select Reference—this creates a new query (e.g., Output1) where the first step is Source = MergedBaseData.
3. Add your specific steps to each Reference:
   • Edit the new Reference query, then add your custom modifications (filtering, column transformations, aggregations, etc.) after the initial Source step.
   • Repeat this for your two other outputs (Output2, Output3).

Key Benefits #

• Sync on refresh: Every time you refresh, MergedBaseData runs first (updating the merged files), then all three Reference queries apply their unique steps to the latest data.
• Easy maintenance: If you need to adjust the base merge logic (like adding a third file, fixing a join condition), you only edit MergedBaseData—all Reference queries automatically use the updated result.
• Clean, DRY workflow: You avoid duplicating the merge steps across multiple queries, which reduces errors and makes your project easier to manage.

Addressing Your Concern About Reference #

You mentioned that Reference queries only show the base query’s result and not its steps—that’s intentional! The Reference query doesn’t need to re-run the base steps; it just uses the base query’s output as its starting point. If you need to modify the merge logic, you do that in the original MergedBaseData query, not the Reference ones. This separation keeps your workflow organized: the base query handles the core data preparation, and each Reference handles its own output-specific tweaks.

内容的提问来源于stack exchange，提问作者Elvino Michel