I have created my first PQ in an Excel Workbook, and implemented the desired data columns and layout, and it works perfectly. I would like to re-use it, so I don't have to create a new query & layout each new month, which would be laborious.
Overview of the Source Data
In the workbook, I have a pivot table which connects to an external data cube, and I have it set up to display in columns 'Team' and '# of Tickets' for the given year & month selected in the filters. From that table, I Right-Click > Additional Options > once for each team. This step executes a backend custom report, which generates a new worksheet for each team. Typically I end up with about 10 new worksheets containing the data produced by the custom report from the data cube.
I created the query using the steps: Data > Get Data > From other sources > Blank query using the following string in the formula bar: =Excel.WorkBook()
The main worksheet where the query places the cleaned and processed data is called 'Repository'. The individual team worksheets are named 'Sheet1' through 'SheetN'.
The final report 'Repository' needs to be produced mid-month, and end-of-month. At end-of-month, a whole new set of worksheets will be produced by the custom reports, which will include the same data as the mid-month report.
My questions are: (1) How to update the query in this workbook to use the new end-of-month worksheets? (2) How to avoid duplicating the mid-month data? (3) I want to use the same query every month, producing a separate workbook for each month. Can it be done?
My workbook is 34MB so unfortunately it appears to exceed the upload file size limit.
Hi Mark,
I'm having a hard time picturing your file set up in regards to these 'custom reports' and how you want to use them for the mid-month process. However, one option is to save a copy of the mid month file for your end of month process, then edit the step in the Applied Steps pane that selects the sheets, selecting the next batch of sheets you need.
If that doesn't help, or you have more questions you could share screenshots to help us understand what you're working with.
Mynda
Mynda,
Thanks so much for replying during your long weekend.
I'm pretty sure your suggestion will do the trick and I'm working on it now. Just one quick update: my blank query formula string was quoted incorrectly above, and should be as follows:
Data > Get Data > From other sources > Blank query using the following string in the formula bar: =Excel.CurrentWorkbook()
Thanks a lot! I'll keep you posted on the results, and maybe send through a reduced report so you can see for yourself what's going on behind the scenes with the custom reports.
Mark.
Great!
I figured you meant Excel.CurrentWorkbook() 😉
Mynda
Mynda,
Thanks heaps - your suggestion was spot on.
PQ is fantastic! I'll be demoing to my team in a couple of weeks how I used it to cut down a task from 60 minutes to 15 minutes. Multiply that by the many reports my team does and the savings really add up. There will be even more savings as I learn to automate a few other things I am doing manually, like VLOOKUP, and adding new columns that are not in the original data.
Thanks again! MOTH is the BEST.
Awesome, James! Great to hear you've had so much success with Power Query. Keep spreading the word 🙂
