A query in Power BI refers to a series of steps performed on the raw data to convert it into a format that is best suited for reporting and analysis. This process is known as data transformation. Data transformation is performed in the query editor.
To open the Query Editor:
1.Click on the Home tab in the Ribbon Menu.
2.In the External Data section, click on Edit Queries.
3.Now click on Edit Queries to enter the Query Editor.
A new window opens showing the interface for Microsoft Power BI Query Editor.
It is often tedious to perform the same steps on different queries again and again. This is especially true if queries contain similar but independent data. There are two solutions to the this:
- Duplicate a query
- Reference a query.
In this blog post, we will walk through each of these methods and compare them towards the end.
Duplicating a Query
Power BI allows you to Duplicate a Query with its complete steps. This saves time and increases work efficiency.
To duplicate a query:
1.Right click on Sales in the Queries Pane.
2.Click on Duplicate.
3.Rename Sales (2) to “Duplicated Sales”.
Under the query settings pane, observe that all the query steps are duplicated.
Referencing a Query
By Referencing a Query, you can create a link between a new query and an existing query. This allows the data to flow in sequential models. Any change in the original query will be reflected in the referenced query.
To reference a query:
1.Right click on Sales in the Query Pane.
2.Click on Reference.
3. Rename it as Referenced Sales.
Under the query settings pane, observe query steps only refer to the source.
4.Click on the Sales in the Queries Pane.
5.Click on Price column header.
6.Click on Transform Tab in the Ribbon Menu.
7.Click on Data Type and Choose Whole Number.
8. Now click on Referenced Sales in the Queries Pane.
Note that the data type has been automatically updated.
Difference between Duplicating and Referencing a Query
Duplicating and referencing are a form of querying in the Query Editor. Both of these create a new query as a result. Duplicating a Query creates a copy of original query with all the steps copied in the same order. Whereas, Referencing a Query creates a query with a single step referencing its parent source query. These differences can be visualized using the Query Dependency feature.
Characteristics of a Duplicate Query
- Duplicated query behaves as an autonomous query which is independent of its parent query. Therefore, any changes in the parent query configuration does not impact the duplicate.
- Each step needs to be performed independently. This requires more processing power.
- Duplicating a query creates an independent object in memory.
Characteristics of a Referenced Query
- Reference query is directly linked to its parent query. Any changes in parent query directly impacts the referenced query.
- Reference queries cannot be used extensively as they can result in looping or circular references.
- Changing the source directly effects the referenced query because it is dependent on the source used in parent query.
- Referencing a query does not create an independent object. References act as pointers in memory.
Viewing Query Dependency
Viewing Query Dependency helps you to understand what is going behind youc clicks in Power BI. In this blogpost, we will use Query depency feature to understand the difference between Duplicate and Refrence.
To find the query dependency:
Open the Query Editor
1.In the ribbon, click on Home> Edit Queries. From the dropdown, select Edit Queries.
Microsoft Power BI Query Editor opens.
Create a Duplicate Query
2.Right click on Sales in Queries pane and click on Duplicate. Rename this to Duplicate Query.
Create a Reference Query
3.Right click on Sales in Queries pane and click on Reference. Rename this to Replicate Query.
Open the Query Dependency viewer
4.Click on View tab in the Ribbon
5.Click on Query Dependency in the Advanced Dependencies section.
A pop-up menu appears.
Duplicate Query is at the same hierarchal level as the original query. Whereas Reference step lower.
Conclusion:
A query is a series of steps applied on the raw data to make it suitable for data analysis. These steps can be duplicated or referenced based on the requirement. This blog post summarizes the concept of duplicating and referencing queries and compares the both methods of query transformation.
Are you a data analyst and want to learn more about Power BI? Why not sign up for Power BI training in Australia. We provide our services in the following regions
