The Difference Between Referencing and Duplicating a Query in Power BI

Getting to the Power Query Editor: 

On the home ribbon in Power BI Desktop, we have the query section, as shown below. By clicking on Transform data another window pops up, which is the Power Query Editor.  The title bar will have: “Filename – Power Query Editor” and when the Power BI Desktop window is open the title bar will read: “Filename – Power BI Desktop. Also, the tool you are in will have a different shaded backgrounds on the title bar. One shade for when you are in the desktop tool and the another for when in the Power Query editor. See the example below: 

Creating a Duplicate or Reference Query: 

To create a duplicate query table, right click on table of interest and then select the duplicate command as shown below. The same method would be used to create a reference query table. 

Duplicate or Reference Query Relationship to the Original Query: 

Whenever you duplicate a query or reference a query it creates a new table with a (2) at the end of the table name. And if you repeat the action for the same table with either a duplicate or reference action, a new table will be created and labeled (3) at the end. It is best to rename the table to indicate the action taken. Otherwise, you will forget what kind of table you have. For the products table we renamed the tables “Products Duplicate” and “Products Reference”. The key is to look at the Query Settings on the far right of the Power Query Editor. The duplicated table will be and exact copy of the original Products table. The applied steps will be the same as the Products table. The Reference table will only say “Source” for the applied steps. Source indicates it is a direct query of the Products table.  Below are snapshots of the Query Settings for each of the Product's tables: 

Duplicate or Reference Query Branching: 

If we add an index counting from 0 to the Products table as shown below, the applied steps will show “Added index”. The Products Duplicate table will remain unchanged. The Product Reference table will add the new index, but the applied step will still show only “Source”. The Product Reference table will aways be linked to changes in the original Products table. The reference table can make changes with added steps, but they will not affect the original Products table. The Products Duplicate table will become independent of the original Products table at the time of its creation. Changes to the duplicate table will not affect the original Products table.  

The main use of duplicate and reference tables is for branching with applied steps not affecting the original source table. The duplicate table branches independently from the source table when created. The reference table branching is still dependent on the original source table.