Power Query: Durable Duplicates

Welcome to out Power Query blog. Today, I look at using ‘duplicate’ to replicate queries.

I have a query that I have created to help me analyse what revenue comes from the various items available for rent in my fictional tent business.

I’ve decided I’d like to create a new version of this query for use in some reports that have been requested. However, I happen to know that this query is used by other existing reports, so I need to keep it the way it is. Therefore, the answer is to create a copy.

In the ‘Workbook Queries’ pane in Excel, I can right click on my queries. I have several copy options: ‘Copy’, ‘Duplicate’ and ‘Reference’ (these options are available from the Query Editor too, on the ‘Home’ tab in the ‘Manage’ dropdown).

The icons next to the copy options indicate what will happen if I use them. ‘Reference’, which I will look at in more detail next week, has an attachment icon (paperclip). This means it will be attached to the original query. This week, I am concentrating on ‘Duplicate’, which although it has the same icon as ‘Copy’ is not quite the same, as I will show later. I use ‘Duplicate’ when I want to leave my original query alone and create a new query that I will maintain separately.

I select the ‘Duplicate’ option.

I have an exact copy of my original query, and I can add or change steps which will have no effect on my original query. If I go back to the Query pane, I can see how ‘Copy’ behaves differently.

I can see the single new query that ‘Duplicate’ created. If I choose ‘Copy’ and ‘Paste’, the results will be different.

I have not just one, but three new queries! To see why, I need to look at the query dependencies.

I can see the query dependencies for my workbook in the ‘View’ tab.

The first point to notice is that my original query is dependent on ‘ACCT_Order_Charges’ and ‘Items’. When I used ‘Duplicate’, Power Query created a connection from these queries to my new query, so my duplicated query is also dependent on the same queries.

When I used ‘Copy’, all the queries that my original query is dependent on are also copied. Hence, my copied query is dependent on the copies of ‘ACCT_Order_Charges’ and ‘Items’. This would be useful if I was planning to change these queries too.

If I want to create an independent copy of my query and I know my query is not dependent on other queries, or I don’t plan to change any queries it is dependent upon, then I would use ‘Duplicate’. If I want to create a copy of my query and I know my query is dependent upon other queries that I want to change independently too, then I can use ‘Copy’. I would use this with caution though, as I could potentially have a lot of queries to maintain if I did want to keep ‘Items’ and all its copies in line.

‘Reference’ is a step further towards dependence, and I will look at an example using reference queries next time.

Going back to my original query, I also have the option of creating a new query from a column of data, by right clicking and choosing ‘Add as New Query’:

When I create my new query, I can still see all the steps from my original query.

I have therefore created a list which is duplicate query of my original.

Come back next time for more ways to use Power Query!