Referencing And Duplicating Queries In Power BI

Referencing And Duplicating Queries In Power BI

One comment

Referencing and duplicating are two different actions. In this tutorial, we’re going to discuss the difference between referencing and duplicating queries in Power BI.

Right click on anything in the Queries pane. Then, we’ll see that we have Duplicate and Reference options.

One of the differences between Reference and Duplicate is what happens inside the Advanced Editor when we select either of them.

First, let’s look at the Advanced Editor of the Sales_2014 table. Let’s see what M code gets written when we do anything inside our tables.

Since we haven’t done any transformations yet, we can see that it looked for the raw data source and the name of the table. Then, it automated #“Change Type”. This indicates that it changed the data type of all the columns.

Duplicating Queries In Power BI

Now, let’s see the difference between duplicating and referencing. To start, let’s duplicate the Sales_2014 table.

Duplicating Queries In Power BI

Then, drag it into the Other Queries folder, so we can get it outside our Staging Queries folder.

moving the table after Duplicating Queries In Power BI

Let’s rename this duplicated table to Sales – Duplication so we can easily recognize it. 

renaming duplicated queries in power BI

Let’s check the Sales – Duplication table through Advanced Editor. We’ll then see that it duplicated every single line of code from the original Sales_2014 table.

result of duplicated queries in power BI

Referencing Queries In Power BI

Now, let’s reference the Sales_2014 table to see its difference from the duplicating option.  

Referencing Queries In Power BI

Drag that again inside the Other Queries folder. Then, rename it to Sales – Reference

renaming the referenced queries in power BI

Let’s check the Sales – Reference table through the Advanced Editor. Obviously, it didn’t duplicate any code at all. It only referenced the original table.  

result of the referenced queries in power BI

The example clearly shows the big difference between duplicating and referencing. When duplicating a certain table, it’ll just duplicate and display every single transformation we did at that particular point. Hence, the new changes that we’re going to make inside the original table won’t be applied to the duplicated table.

On the other hand, the referenced table will always get the transformations from the original table even after the referencing process.

For instance, let’s make minimal transformations to the Sales_2014 table. Rename the Currency Code column into Ccy, Warehouse Code into Warehouse, and OrderName into Order Name. Then, move the Warehouse column between the Channel and Ccy column.

transforming queries in Power BI

Let’s then check the Sales – Duplication table. We’ll see that the changes we made from the Sales_2014 table weren’t applied.

But if we check Sales – Reference table, we’ll see that it also reflected the changes we made in the original Sales_2014 table.

Conclusion

To conclude, duplicated queries in Power BI only copy the transformations that we did before the duplication process. On the other hand, the referenced table is always receiving the transformations that we’re doing from the original table. That’s the main difference between the Duplicate and Reference options. Indeed, we can make a lot of transformations in a particular table.

Hopefully, you now have a better understanding of how duplicating and referencing queries in Power BI differ from each other.

Sam

Membership Banne

***** Related Links *****
Advanced Transformations in Power BI
Organizing Your Queries: Power BI Query Editor Tutorial
Power BI Query Parameters: Optimizing Tables

***** Related Course Modules *****
Ultimate Beginners Guide to Power BI

Power Query Series
Advanced Data Transformations & Modeling

***** Related Support Forum Posts *****
Changing Data Sources In Power BI/Query Editor
How To Delete Unwanted Data From Query Editor
Custom Query for the date table not working in Power BI
For more query in Power BI queries to review see here….

1 comments on “Referencing And Duplicating Queries In Power BI”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.