Applying an existing query to another data set

[tigerzen]

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.

 

[James006]

Hi,
Could following explanation help:

How to Reference other Power Query queries

I use Power Query to source data over Power Pivot’s built in methods. But I need an easy way to reference other power query queries to do so..

excelguru.ca

 

[jdellasala]

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,

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

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.

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.

 

[tigerzen]

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.