Hi,
I have a template file which collects forecast sales and profit data. The file is distributed to many people who are required to input their forecasts and then return it to me.
What I want to do is save all of the files in to one folder and then use Power Query to consolidate all of the data in the files in to one data table.
I can deal with the combining the files from the folder part but what I need help with is how to attach some of the header data in the template files to the completed forecast rows.
I have attached an example file. In the file there are two worksheet. One is called Forecast, this is the one that is distributed for completion. The other is called Required, this shows the data table format that I am hoping to achieve.
If you look at the Required sheet you will see two columns headed Region and Week. The Region "Sheffield" is the content from cell A4 in the forecast sheet and the Weeks are the row one column headers from the Forecast sheet. Note that the weeks numbers are in merged cells so the values are only actually in alternate cells, C1, E1, etc. also they are just numeric values that have been formatted as W#,##0 to show as WK1 etc..
What I need your help with is how do I get Power Query to get the static Region and Week values from the Forecast sheet against every row in my Required sheet data table?
Thanks in advance for any help.
Bax
Ooh, Bax, you've broken a fundamental data structure rule that is to never nest headers across more than one row, and this is making the task of consolidating much more difficult than it need be.
Personally, I'd fix the layout of your templates so your headers are in one row, but if you want to persevere with them the way they are then you need to use the Unpivot step explained in the 'Nested Headers' scenario of this post:
https://www.myonlinetraininghub.com/power-query-unpivot-scenarios
You can make this unpivot transformation in the 'sample' query produced when you 'get fiiles from a folder' as described in step 6, here: https://www.myonlinetraininghub.com/power-query-get-files-from-a-folder
Mynda
Hi Mynda,
Slap on the wrist accepted regarding breaking the rules. However in my defense in the full file there is a lot of VBA and formulas that use the information in the header cells. I will review and see if I can work it another way.
Thanks for the links really useful.
Regards
Bax
