Hi
I'm trying to work out how to get Power Query to combine identically formatted worksheets for a rolling period of 25 reporting months (attached file shows just 3).
Focusing on just one table, I can get pretty far in removing blank/redundant roles, transposing columns and rows, and finally using unpivot columns to create a table that can be added to, but I cannot figure out how to make use of the month year information in cell A2 of each tab (or the tab name) to ensure that all unpivoted information is correctly assigned to the correct month/year.
Also, how do I make use of the Append or other function to programmatically deal with multiple sheets, the names of which will update each month?
Thanks
Bob
Hi Bob,
If you want to consolidate the data into a new worksheet in the current file then the data in each worksheet either needs to be in an Excel Table or a named range so Power Query knows where to find the data. If you name your Tables or Named Ranges with the time period name, then you can discard the top 3 rows that contain the header information as you won't need them because the table name will be populated in its own column.
You can use the Excel.CurrentWorkbook technique as explained in session 2.03 of the Power Query course. See example attached.
Alternatively, you can use the 'From Excel File' technique as shown in session 2.02 to get the data from each sheet. But you'll then have to clean each sheet separately before appending them together. It'd be easier to use the first option.
Mynda
P.S. I've moved this post to the Power Query Course Members forum rather than the public Power Query forum.
Thanks Mynda, your help is invaluable!
The Table.ExpandTableColumn function seems to provide a really neat solution, but I don't understand how it is transforming everything in the way that I want. Not a function I have come across before (no surprise there!)
Presumably, I could write a simple VBA routine to name all the ranges dynamically before applying the query ....?
Bob
Hi Bob,
Yes, I'm sure you could write some VBA to automatically format the data into tables or named ranges.
The Table.ExpandTableColumn function is automatically written for you when you click on the double headed arrow on the Content column to expand the tables. If you watch the tutorial you'll see what I mean.
Mynda
Thanks Mynda - tutorial that explains Table.ExpandTableColumn function is actually PQ 3.02, but I got there in the end ... and no need for VBA!
Bob
Ah, sorry, Bob. Glad you found it.
sorry...another query relate to 3.02 practice.
since all data were consolidated in the new worksheet of same workbook (i called it as "consolidated worksheet" here), will it duplicate by including the consolidated worksheet when i click "refresh" again?
Hi Tan,
Please watch the video again to see how I used a different naming convention for the sheets I'm getting vs the sheet the query output will be placed on. If you used the filters correctly, then it should only be picking up data from sheets ending in '2015', therefore the sheet called 'consolidated worksheet' won't be included.
Mynda
oh yes, you are right. Mynda! thanks for remind me 🙂
