In Power BI Desktop, there are multiple ways to copy a query in Power Query Editor such as COPY, DUPLICATE, and REFERENCES. However, these actions have different use and purpose in Power Query and Power BI.

In this post, we are going to explain in details What are the main differences Duplicate vs Reference Power Query and When you should use each option to copy a query in Power BI?

Duplicate vs Reference Vs Copy in Power BI?

1) Reference in Power BI

As we earlier mentioned, the Reference option is used to take a copy from the original query.

But what exactly happened when you copy a query using Reference option:

1. It copies the original query without any custom steps.

> Original Query

As you can see, the original query has some of the applied steps as shown below:

> Reference Query

And when you reference the original query, you will note that all the applied steps will be removed from the new Reference query as shown below:

2. Only the SOURCE step will be available in the new Reference table and can’t be edited.

3. The Reference query doesn’t require more processing because it just acts as a pointer to the original query in memory and does not create a new object in the memory.
4. The Reference query mainly depends on the main query, so
   • Any changes in the original query will affect the Reference table.
   • The new applied steps in the original query will be applied to the Reference table.

Example:

When adding a new column or renamed or delete a column in the original query, these changes will be automatically reflected into the Reference table. but it will not be added as a new step in the Reference query.

However, any custom steps in the Reference table will not be applied or affect the original query.

When you should use Reference Query in Power BI?

You should use Reference Query in Power Query Editor in Power BI at the following cases:

1. If you need to take a copy from the original query without custom steps however you can add different steps.
2. If you need to take a copy from the original query that still referenced and not isolated from the original query.

Note: using References quires severely may lead to circular references.

2) Duplicate in Power BI

Again, the Duplicate option is also used to take a copy from the original query.

But what exactly happened when you copy a query using Duplicate option:

1. It copies the entire original query with all applied steps.

> Original Query

As you can see, the original query has applied steps as shown below:

> Duplicate Query

When you duplicate the original query, you will note that all the steps will be copied to the Duplicate query as shown below:

2. The Duplicate query requires more processing because it creates a new object in the memory.
3. The new Duplicate query will be isolated from the original query that means
   • Any changes in the original query will NOT affect the Duplicate query and vice versa.
4. Unlike the Reference query, you can change the query source in the Duplicate query without affecting the original query.

You might also like to read

• Power BI Edit Enter Data Table
• Power BI: Edit Existing SQL Query

When you should use Duplicate Query in Power BI?

You should use Duplicate Query in Power Query Editor in Power BI at the following cases:

1. If you need to take an exact copy from the main query with all applied steps.
2. If you need to take an isolated copy from the original query.
3. If you need to add additional steps with different configurations without affecting the original query.

3) Copy Table in Power BI

The Copy and Paste option is also used to copy a query in Power Query editor. it seems as a Duplicate query but it actually neither Reference action nor Duplicate action!

Practically, if you performed a copy-paste for a table with a normal query that not depends on other queries, it will act as a Duplicate query.

However, if you performed a copy-paste for a table that depends on other queries like (reference table), it will copy all dependencies queries as well.

The below example we clarify what will happened when use copy-paste option instead of Duplicate or Reference options.

Example (Copy-Paste a query with no query dependencies):

In this example, we will take a copy from the ‘Power Platform Geeks” query that doesn’t depend on other quires, so when we perform a copy-paste, it will copy this query with all applied steps and the copied query will be isolated from the original query.

Example (Copy-Paste a query with query dependencies):

In this example, as you can see in the Query Dependencies, the “Reference” query is already depends on “Power Platform Geeks“.

So when you copy and paste the “Reference” table that depends on other queries, it will generate two copies for the “Reference” query as well as the dependacy query that is ‘Power Platform Geeks” with all applied steps.

---

Conclusion

In the end, the COPY, DUPLICATE, and REFERENCES are options to copy a query in Power Query Editor. However, these actions have different purpose and usage.

So in this post we have tried to clarify the main differences between Duplicate vs Reference vs Copy Power Query in Power BI, and when you should use each option!

Download

Download the BIPX file that used in the article from GitHub at Duplicate vs Reference vs Copy in Power BI.

See Also

• Reference vs Duplicate in Power BI
• Power BI: Custom Column Vs Calculated Column
• Power BI: DAX Vs M Language