You are currently viewing Duplicate vs Reference Query in Power BI
Reference Vs Duplicate Power BI

Duplicate vs Reference Query in Power BI

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.

Duplicate vs Reference Query in Power BI
Duplicate vs Reference

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.

reference a table in power query in Power BI

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:

copy query without steps from original query in Power BI

> 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:

reference query without steps from original query in Power BI | Power Platform Geeks
  1. Only the SOURCE step will be available in the new Reference table and can’t be edited.
can't edit source step in the power query editor in Power BI
  1. 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.
  2. 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.

 Reference vs Duplicate Power BI

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

 Duplicate vs Reference Power BI

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.
Query dependencies in Power Query
Duplicate vs Reference

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.

duplicate a table in power query in Power BI | Power Platform Geeks

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:

copy query without steps from original query in Power BI

> 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:

when happened when you duplicate query in Power Query
  1. The Duplicate query requires more processing because it creates a new object in the memory.
  2. 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.
  3. Unlike the Reference query, you can change the query source in the Duplicate query without affecting the original query.
edit query in Power Query in Power BI
Duplicate vs Reference

You might also like to read

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!

copy table in power BI | Power Platform Geeks

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.

copy paste table Power BI

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“.

Query dependencies in Power Query
Duplicate vs Reference

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.

copy paste table query Power BI | Power Platform Geeks

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

Mohamed El-Qassas

I'm Microsoft MVP, SharePoint StackExchange Moderator, Microsoft QnA Moderator, C# Corner MVP, Microsoft TechNet Wiki Judge, and Senior Technical Consultant with +12 years of experience in SharePoint, Project Server and Power Platform.