Hi Mynda,
My workbook has around 18 queries, plus sample queries (not sure why these were created), all to create a dashboard. All these queries were required (I think) to clean and transform source data and append different workbooks to create a single source to create charts etc. Although, I'm sure there could have been a simpler way of doing this.
I now have new Jan 2019 data which needs to be cleaned and appended to the queries for Year, Month, DOW and daypart and I am now totally confused about where this data needs to be and how to upload it to an existing query. I will have new data every month to be appended and I would love to be able to point to a folder and everything is updated automatically.
Links to workbook and new data workbook.
https://1drv.ms/x/s!Ao6UIfBSUm6lljkoQ1qMZWHyMW45
https://1drv.ms/x/s!Ao6UIfBSUm6lljpMc57ZYP-Y8nJl
Do I transform the new data in it's own workbook first or load it from a file or from a folder and then clean and append to the existing queries in tracker workbook?
tnx
Tracy.
Hi Tracy,
Firstly, the 'sample queries' and other queries in the 'Transform File from Data' query folder are automatically generated when you use 'Get Files > From Folder'.
If you want to automatically update your queries by getting the next month's file from a folder, then you need to re-create your queries so they use the 'Get Files > From Folder' method of getting the data.
The challenge you'll have with this is that each of your source files has multiple sheets you want to get the data from, i.e. DOW, DayPart, Month and YR, but the 'Get Files > From Folder' path only gets the data from the first sheet in the file.
'Get Files > From Folder' also requires the layout of the data in each file is identical, i.e. the number of columns, the column labels and order are identical.
So, I would split your January file (and the files from the previous years/periods) into 4 separate files and create a folder for each one; i.e. DOW, DayPart, Month and YR. Then you can use 'Get Files > From Folder' to get the data from each folder and combine it into one table. From there you can create your sub-queries and tables required for your Power Pivot model.
Then when February comes, you'll split the file into 4 separate files and save them in their respective folders, then click Refresh All to update your report.
I know it sounds like a lot of work to recreate everything, but you can also simplify the file by combining the data when you get it from the folder, rather than creating separate queries for each Excel source file and then combining the queries later. e.g. You would only have one 'Year_Table' query that got the data from files containing 2015-2017 and 2018 and 2019 files, rather than having a query for YEAR_TABLE2015_2017 and TABLEYEAR2018.
Be sure to rename the automatically generated queries after you get each set of files from a folder so you can identify which is which.
I hope that points you in the right direction, but let me know if you have further questions.
Mynda
Tip: Session 3.05 shows you how you can visualise your query dependencies, which might make it easier for you to follow the relationships between your queries.
Thanks Mynda,
I understand and will give it a bash.
I did try the dependencies visual and immediately got a headache trying to decipher the connections, so I closed it sent you the mail!
tnx again.
