Applying an existing query to another data set

tigerzen

Board Regular
Joined
Mar 8, 2023
Messages
209
Office Version
  1. 365
Platform
  1. Windows
I have researched this through Google and it isn't really obvious but how does one use an existing query on new data that is presented in the same manner upon which the old query was created.
So I have created a query with numerous steps to clean and transform the csv file that I get sent, another csv file for another department will be coming soon and I wish to apply the same query that I created previously. I couldn't find any videos that specifically explained this but it would seem that somehow I create a new excel workbook and then copy the query from the other workbook?
If there is a video that explains it or other link can you please let me know.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
I think you were looking for something other than referencing another existing query. While certainly useful, what referencing a query gives you is the results of the query referenced.
What I think you're asking for is to be able to re-use an existing query in a new Workbook.

So the simple and awkward way to do that is to simply open the original query in the Advanced Editor, copy the query, and then open a new query in the new notebook and paste the code into the Advanced Editor after clearing the initial code. You'd have to update the source if needed, but otherwise the query would behave exactly as it did in the original.

Similarly, with the original and new Workbooks open as well as their Queries & Connections pane, you can Right Click on the source workbook's Query and select Copy,
1679995037078.png

then in the new Workbook, right click in the Queries & Connections pane and select Paste.
1679995110071.png

If the Query you copy depends on other queries, they will also be copied into the new Workbook.

While that method is simple, a little more streamlined method is to save the query as a Connection file. In the Workbook with the existing Query, from Excel open the Queries & Connections pane, right click on the query and select Export Connection File.
1679994319534.png

The file will be saved in the default Documents\My Data Sources folder with the name "Query - NameOfQuery.odc". Then any new Workbook has the query available under Data -> Existing Connections. This will require the same source as the original, and any queries it depends on will also be included in the file.

If the source is a local or shared file, the filename can be included in a cell in the original Workbook, that cell can then be given a Named Range like SourceFile, and that Named Range can be brought into Power Query and used as a Variable in the main Query. Then set up the same named range with the new file name (set that up before opening the Existing Connection), and then bring in the Existing Connection and Refresh.

This is a lot more complex to explain than it is to do. Using Existing Connections is really easy! Setting up the Source File is a little more involved but still easier to do than explain.
 
Upvote 0
Solution
I think you were looking for something other than referencing another existing query. While certainly useful, what referencing a query gives you is the results of the query referenced.
What I think you're asking for is to be able to re-use an existing query in a new Workbook.

So the simple and awkward way to do that is to simply open the original query in the Advanced Editor, copy the query, and then open a new query in the new notebook and paste the code into the Advanced Editor after clearing the initial code. You'd have to update the source if needed, but otherwise the query would behave exactly as it did in the original.

Similarly, with the original and new Workbooks open as well as their Queries & Connections pane, you can Right Click on the source workbook's Query and select Copy,
View attachment 88498
then in the new Workbook, right click in the Queries & Connections pane and select Paste.
View attachment 88499
If the Query you copy depends on other queries, they will also be copied into the new Workbook.

While that method is simple, a little more streamlined method is to save the query as a Connection file. In the Workbook with the existing Query, from Excel open the Queries & Connections pane, right click on the query and select Export Connection File.
View attachment 88497
The file will be saved in the default Documents\My Data Sources folder with the name "Query - NameOfQuery.odc". Then any new Workbook has the query available under Data -> Existing Connections. This will require the same source as the original, and any queries it depends on will also be included in the file.

If the source is a local or shared file, the filename can be included in a cell in the original Workbook, that cell can then be given a Named Range like SourceFile, and that Named Range can be brought into Power Query and used as a Variable in the main Query. Then set up the same named range with the new file name (set that up before opening the Existing Connection), and then bring in the Existing Connection and Refresh.

This is a lot more complex to explain than it is to do. Using Existing Connections is really easy! Setting up the Source File is a little more involved but still easier to do than explain.
Thanks James I would have thought this was a common task and PQ would largely automate it. Your explanation is very thorough and much appreciated.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top