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.
Then, drag it into the Other Queries folder, so we can get it outside our Staging Queries folder.
Let’s rename this duplicated table to Sales – Duplication so we can easily recognize it.
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.
Referencing Queries In Power BI
Now, let’s reference the Sales_2014 table to see its difference from the duplicating option.
Drag that again inside the Other Queries folder. Then, rename it to Sales – Reference.
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.
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.
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.
***** Related Links *****
Advanced Transformations in Power BI
Organizing Your Queries: Power BI Query Editor Tutorial
Power BI Query Parameters: Optimizing Tables
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
