Referencing Power Query queries
This article targets you as a data modeler working with Power BI Desktop. It provides you with guidance when defining Power Query queries that reference other queries.
Let's be clear about what this means: When a query references a second query, it's as though the steps in the second query are combined with, and run before, the steps in the first query.
Consider several queries: Query1 sources data from a web service, and its load is disabled. Query2, Query3, and Query4 all reference Query1, and their outputs are loaded to the data model.
When the data model is refreshed, it's often assumed that Power Query retrieves the Query1 result, and that it's reused by referenced queries. This thinking is incorrect. In fact, Power Query executes Query2, Query3, and Query4 separately.
You can think that Query2 has the Query1 steps embedded into it. It's the case for Query3 and Query4, too. The following diagram presents a clearer picture of how the queries are executed.
Query1 is executed three times. The multiple executions can result in slow data refresh, and negatively impact on the data source.
The use of the Table.Buffer function in Query1 won't eliminate the additional data retrieval. This function buffers a table to memory. And, the buffered table can only be used within the same query execution. So, in the example, if Query1 is buffered when Query2 is executed, the buffered data couldn't be used when Query3 and Query4 are executed. They'll themselves buffer the data twice more. (This result could in fact compound the negative performance, because the table will be buffered by each referencing query.)
Power Query caching architecture is complex, and it's not the focus of this article. Power Query can cache data retrieved from a data source. However, when it executes a query, it may retrieve the data from the data source more than once.
Generally, we recommend you reference queries to avoid the duplication of logic across your queries. However, as described in this article, this design approach can contribute to slow data refreshes, and overburden data sources.
We recommend you create a dataflow instead. Using a dataflow can improve data refresh time, and reduce impact on your data sources.
You can design the dataflow to encapsulate the source data and transformations. As the dataflow is a persisted store of data in the Power BI service, its data retrieval is fast. So, even when referencing queries result in multiple requests for the dataflow, data refresh times can be improved.
In the example, if Query1 is redesigned as a dataflow entity, Query2, Query3, and Query4 can use it as a data source. With this design, the entity sourced by Query1 will be evaluated only once.
For more information related to this article, check out the following resources:
Referencing And Duplicating Queries In Power BI
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.
Download Free Resources Here
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, thereferenced 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.
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.
Download Free Resources Here
***** 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….
Tags: Advanced Editor, duplicate queries, Power BI Desktop Data Model, power bi queries, power bi query language, power query in power bi, power query now, Power Query reference table, queries in power bi, reference queries in Power BI
Published by Sam McKay, CFA
Founder, Enterprise DNA View all posts by Sam McKay, CFA
When you work with tables and queries in Power Query and Power BI, you get the option to copy them through these actions: Duplicate, or Reference. It has been always a question in my sessions and courses that what is the actual difference between these two actions. The explanation is simple but very important to understand. Because when you know the difference, you will use it properly. In this short blog post, I’ll explain what is the Reference, and the difference of that with Duplicate. To learn more about Power BI; read Power BI from Rookie to Rock Star book.
If you are looking to copy an entire query with all of its steps, then Duplicate is your friend. Let’s see this in action. As an example, Let’s assume that we got the data from a web page that shows us the best seller’s movies information. If you have done the movies example of my book previously, the website is BoxOfficeMojo. Here is the link to the page:
In Power Query, we got data From Web, and selected this source;
Let’s say for this table, we do some transformations. For example; removing extra “^” character from the last column (Year column);
and then some other transformations, so we end up with a number of steps for this query.
After doing all these transformations, you realize that this data is only for the first hundred best seller movies because that web page doesn’t have the remaining movies. To get the remaining, you need to navigate to page 2, which has a different URL, but the same data structure.
Well, what you need to do? You have to do all those steps on page 2 as well. Let’s keep this example static and basic, (Because in complex scenarios when you have many pages, you may use functions and parameters to loop through all pages and combine them all together. If you are interested to learn about that, read my post here). Let’s say you want to do all those steps that you have done for page one, now for page two. To do that; you can leverage Duplicate.
Create a duplicate of Box Office Mojo (I called it; Box Office Mojo Page 1)
When you create the Duplicate query, it will be an exact copy of the first query, with all steps of it. These two queries are exactly like each other. No difference!
Duplicate copies a query with all the applied steps of it as a new query; an exact copy.
After creating the copy, then you can go to the source step to change the URL:
Using Duplicate, you managed to copy a query with all steps in it, and then make changes in your new query. Your original query is intact.
Duplicate is the option to choose, when you want to copy a query, but do a different configuration in steps.
Reference is another way of copying a query, However, the big difference is that; When you reference a query, the new query will have only one step: sourcing from the original query. A referenced query, will not have the applied steps of the original query. Let’s see this option in action. Continuing the example above; let’s say we want to create a new query that is the result of combining the page 1 and page 2 result. However, we do NOT want to change any of the existing queries, because we want to use those as the source for other operations.
With a right click on Box Office Mojo Page 1, I can create a Reference.
Reference will create a new query which is a copy of the Box Office Mojo Page 1, but only contains one single step:
The only step in the new query is sourcing the data from the original query. What does it mean? It means if you make changes in the original query, then this new query will be impacted.
Reference will create a new query which has only one step: Getting data from the original query.
Now we can use this query, to append to the Box Office Mojo Page 2;
The result would be a query that contains both pages;
To learn more about append and the difference of that with Merge, read my blog post here. In this example; we used Reference option to create a copy of the original query, and then continue some extra steps. There are many other usages for the Reference.
Reference is a good choice, when you want to branch a query into different pathes. One path that follows a number of steps, and another that follows a different steps, and both are sharing some steps in the original query.
After doing the append in this example, it is a good idea to uncheck the enable load on Page 1 and Page 2 queries to save some memory in Power BI.
Finding out that which query is dependent (or referenced from) which query can be a bit challenging when you have too many queries. That is why we have the Query Dependency menu option in the View tab of Power Query;
For our example above, this is the query dependency diagram;
Now that you know there are two options when you copy a query, let’s have a closer look at their difference.
Isolation from the Original or Dependency to the Original
Duplicate creates a new copy with all the existing steps. The new copy will be isolated from the original query. You can make changes in the original or the new query, and they will NOT affect each other. Reference, on the other hand, is a new copy with only one single step: getting data from the original query. If you make a change in the original query, the new query will be impacted. For example; If you remove a column from the original query, the new query will not have it if it used the Reference method for copying.
Limitation of the Reference
You can not use referenced queries in all situations. As an example; If you have a Query 1, and then you created a reference from that as Query 2. You cannot use the result from Query 2 in Query 1! It will create a circular reference. You are combining a query with a reference to the query itself, It is impossible!
Some actions that invoke Reference or Duplicate
There are some actions in the Power Query that trigger Reference or Duplicate, let’s check those options:
Append Queries as New / or Merge Queries as New is a Reference action
These two actions are creating a reference from the original query and then they do Append or Merge with other queries.
Add as New Query is a Duplicate action
Believe it or not, when you right click on a column or cell and select Add as New Query, you are creating a duplicate of the original query.
This can be misleading sometimes, because you may expect the new query to source from the original, and with the change of original, this query also to change. However, the truth is that this is a duplicate action, and after this action, your original query and the new copy will be isolated from each other.
Copy and Paste is Neither Duplicate Nor Reference!
This is another misconception that Copy and Paste are similar to Duplicate. It is not, and it is not Reference either. When you do this action on a simple query (I mean a query that is not sourced from any other queries), then you get a result similar to Duplicate.
But when you do the Copy and Paste on a query that is sourced from other queries; the result is a copy of all original queries. Here is the result of Copy and Paste on Box Office Mojo All Pages (which is sourced from Page 1 and Page 2);
Duplicate and Reference are two different actions, and they are also different from Copy and Paste of a query. Duplicate will give you an exact copy of the query with all steps, Reference will create a reference to the original query instead as a new query. Duplicate is a good option to choose when you want the two copies to be isolated from each other, Reference is a good option when you create different branches from one original query. There are some actions in Power Query that trigger Duplicate or Reference as listed in this blog post. Hope this was a good post for you to understand the difference between these two actions clearly, and use them wisely from now on.
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
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.
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.
But what exactly happened when you copy a query using Reference option:
- 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:
> 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:
- Only the SOURCE step will be available in the new Reference table and can’t be edited.
- 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.
- 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.
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.
However, any custom steps in the Reference table will not be applied or affect the original query.
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:
- If you need to take a copy from the original query without custom steps however you can add different steps.
- If you need to take a copy from the original query that still referenced and not isolated from the original query.
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.
But what exactly happened when you copy a query using Duplicate option:
- 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:
> 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:
- The Duplicate query requires more processing because it creates a new object in the memory.
- 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.
- Unlike the Reference query, you can change the query source in the Duplicate query without affecting the original query.
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:
- If you need to take an exact copy from the main query with all applied steps.
- If you need to take an isolated copy from the original query.
- 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!
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.
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“.
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.
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 the BIPX file that used in the article from GitHub at Duplicate vs Reference vs Copy in Power BI.
Bi reference vs duplicate power
No blood, no groans, no problem. How old are you, don Juan. Enough to stick. Oh man.duplicate and reference queries in power bi - difference between duplicate and reference queries
Only He is given the right to guide us. Humble yourself, sister, and pour out your soul to your mentor in Christ. The priest graciously invited the woman to a small gorenka next to the main hall of the parish. After following him, Efrosinya took it off with a shell and laid it on an oak table.
You will also be interested:
- 55 chevy 4 door wagon
- Animal crossing nintendo switch item
- The librarians season 5 2019
- Toyota tundra leveled on 35s
- Xbox one s fan size
- Easy reading worksheets for kindergarten
- Old honda bike for sale
- Sleep mode fitbit inspire 2
- 2 timoteo 4 1 2
- Realtors in elizabethtown, nc
- Bend, oregon unemployment rate
- How to clone samsung phone
I could not think of anything as sex. I started fucking myself with my fingers. The driver saw it and only smiled. -What do you want to be next.