CODEWITHSUNDEEP
excelpowerquery
Hilly1
Hilly1

Reputation: 157

Excel Power Query - Re-Use queries in other workbooks

I'm new to Power Query (I'm actually using 2016 Get/Transform), but have learned how to use it to transform a monthly spreadsheet that I receive. I was thinking I could save my first month's report with it's Queries as a template that I could use for the subsequent months. The queries strip out the headers, subtotals, etc. to give me the months raw data and it works fine. I have searched the web and also youtube, but can't find any thing to show an easy way to do this.

I found an old method, which was to cut/paste the syntax from the editor and populate a new query in a spreadsheet, but this will be prone to errors.

Can anyone provide me with some guidance? Even a link which explains it would be very much appreciated. Thank you in advance...

Upvotes: 1

Views: 5771

Answers (1)

teylyn
teylyn

Reputation: 35990

The idea with Power Query is that you use just the one query and refresh it each month, so that you can see the current data.

Duplicating the query just to use a fresh data set is counterproductive to that approach.

Depending on your data source, you could parametrise the query with start and end date, or if your data sources are files in a folder, use parameters to point to a different folder before refreshing the query.

Ken Puls has a good article about using parameters in Power Query here: http://www.excelguru.ca/blog/2014/11/26/building-a-parameter-table-for-power-query/

But if you insist:

To quickly copy a query to another workbook, you can copy the sheet with the query result (right-click the sheet tab > move or copy > create a copy > new book).

Or, save the workbook with the query as an Excel template (.xltx). You will be prompted if you want to clear the query data and refresh it when a new workbook is created from the template.

But really, there is no reason to duplicate the query each month.

Upvotes: 2

Related Questions