After hours of searching both you tube and via google, I have found the solution to incremental append without going to Power BI. Not straightforward but it appears to work.
My question now relates to how to keep that data refreshed.
I am intending to have the workbook with the complete table as a standalone file that I will use as a basis for other reports. This means that I will not actually be opening the file when I reference it in the other reports.
I want the queries that create the data to refresh every night so that once the overnight report has run, the query refreshes and the data is up to date. I have looked at the Connections dialogue box and in Properties you can refresh every 60 minutes. I can't find anywhere where you can set a time to refresh. Will this refresh the data without opening the file please?
Many thanks
Sue
Hi Sue,
Excel doesn't have an option to refresh the query at a specific time, only time intervals. Nor can a file execute a refresh when it's closed. However, you can set it to automatically refresh upon opening.
Mynda
P.S. I'd be interested to know your solution to incremental refresh without using Power BI!
Hi Mynda
Thanks - I'll have to think about how I'm going to update this information then!
I found the information in a few different places but found it really difficult to follow. Eventually it seems to be working but not quite how either of these solutions seem to show. I think I have a different version of office which is what I suspect the differences are about.
https://www.youtube.com/watch?v=xDVUUcbX13g&disable_polymer=true
The You Tube video is what I mostly followed. Running that on one screen, pausing and replaying while doing it on my data on another screen. I normally put my data into a data model rather loading to a worksheet but I can't see how to do that. I'll live with it!
Hi Sue,
Thanks for sharing. It works well for data that's in your worksheet, but it won't load the data to the data model because it requires a reference back to itself and Power Pivot cannot be a data source.
This application has limited real use because typically the reason for wanting incremental refresh is because you're working with a lot of data and you wouldn't want to be storing large amounts of data in the worksheet. Large datasets really need to be in Power Pivot/Data Model.
Mynda
Ah - thank you for that. My understanding of how things really work is fairly limited but I'm good at googling 🙂
For me it is fine, not huge amounts of data. One school has about 500 students on roll with a fairly high turnover of students so probably 700 max on the "ever on roll during an academic year" table and the other school about double both of those numbers. Perfectly workable at this scale thankfully.
