How to Copy a Power Query query from one Excel workbook to another one

Have you ever wanted to repeat in another workbook a query that you already built in one Excel workbook? Have you worried that you would have to recreate all the query steps again? Or maybe you managed to copy the M code, but you had to repeat that process as many times as queries you had to transfer.

In this post, I will share with you an awesome tip on how to easily copy queries from one workbook to another one without having to recreate every step manually!

Finding the queries to copy and checking if the query we want to copy depends of other queries

Open the two workbooks side by side and open the Queries and Connections pane in each workbook.

For that, go to the Data tab, and then click the button Queries and Connections.

On this pane, you can see the Power Query queries available in each workbook.

We can check how many data sources each one of the queries has, and how the queries relate to each other by going to the Data tab, and then Get Data> Launch Power Query Editor.

Once into the Power Query Editor, o to the View tab, click on the button Query Dependencies.

A new window will open, presenting us with a diagram with all the queries that we have on the workbook and the dependency between them.

In the example shown in the picture above, we can see that query Stores (query #3) comes from one external file only; in this case, an Excel file. Also, the Stores query “feeds” the SalesFinal query (query #4).

In the same example, we also have the query Sales Report Data (query #5). By clicking on the query #5 shape, Power Query highlights all the queries that come before and after the Sales Report Data query. We can see that this query is reading data from a folder of files.

When we build a query for which the data source is a folder of files, Power Query creates helper queries (queries 6, 7, 8 and 9). For query Sales Report Data (5) to work, it needs the queries Sample File (7) and Transform File (6). Queries (8) and (9) are not needed because query Transform File (6) is, in fact, a copy of the query Transform Sample File (9) converted into a Power Query function. Therefore, query Sales Report Data (5) only needs the queries Sample File (7) and Transform File (6) to work.

The Sales Report Data (5) also feeds Sales Final (4) query. So ultimately, query Sales Final (4) depends on all the queries highlighted in light green (1, 2, 3, 5, 6 and 7) to work properly (see picture below.)

Copying a query that does not depend on other queries

The most simple case is when we want to copy a query that does not depend on other queries, like query Products (2) in the example.

By double-clicking in query Products to edit it, we can see that it has 4 steps. However, the process that I am going to show you, will work for queries with any number of steps.

These are the steps that we need to follow to copy a query:

On the Queries and Connections pane of the workbook that has the query that we want to copy, let’s right-click on top of that query and select Copy. Then, go to the Queries and Connections pane in the second workbook, right-click on the pane and choose Paste. And that is it!

If we open the new query, we will see that it has the same steps as the original query. Everything was copied and we don’t need to waste time recreating all the steps from scratch.

It is very important to point out that if this query that we copied needed a table that existed on workbook one and we do not have the same table in the second workbook, we will receive an error alert notification on the second workbook saying that something is missing. To fix that error, we can recreate the table on the second workbook, or point the query on the second workbook to the table in the first workbook.

Copying a query that brings data from a folder or depends on other queries

In the example shown in the picture, query Sales Report Data (5) reads data from a folder of files and, therefore, depends on the helper queries.

To copy the query Sales Report Data, we click on top of it in the Queries and Connections pane in the first workbook, right-click, and select Copy.

Then, we go to the Queries and Connections pane in the second workbook, right-click, and select paste. And that’s it!

Since the query Sales Report Data (5) depends on the queries Transform File (6) and Sample File (7), when we pasted the query #5 on the second workbook, Power Query, not only copied query #5 but also all the other queries that it needs to work properly (queries #6 and #7.)

What happens when we copy a query that depends on all the other queries to work?

On the example presented, the query Sales Final (4) depends on queries numbers Employees(1), Products(2) and Stores(3.)

The procedure to copy query #4 is the same. On the Queries and Connections pane in the first workbook, right-click on query Sales Final (4), select Copy, go to the second workbook and paste in on the Queries and Connections pane. It brings with it all the queries that the Sales Final (4) report needs to work.

We can confirm that all the relationships between the different queries were preserved by going to the Query Dependencies window on the second workbook using the steps explained above. In summary, all the work done in the queries in the first workbook was copied into the second workbook and we don’t need to spend time recreating all the queries.

Copying the queries M code

If we just need to work with the M code of the queries that we have on the first workbook, we can have it all copied at once without needing to copy the M code from the advanced editor window, query by query.

To achieve this, on the Queries and Connections pane select the query that you want to copy, right-click on it and choose Copy.

Then, open your Notepad app or your any other text editor program, right-click on the text editor area and select Paste.

Watch the explanation in video here:

• Learn how to automate your Excel reports
• Get this ready-to-use Excel solution for 2 and 3 columns of Dependent Dropdown Lists
• Check other posts about Power Query and Excel in this Blog,