how to automatically update a pivot table referencing a pivot table referencing query table

[lichtpixel]

I have an xlsx file with multiple sheets that should update everything on load (opening file) - but it doesn't. The workflow:

1. sheet A is static data, sheet B is static data written by a PHP library into the xlsx, that's why everything else needs to update on load.

2. sheet C combines data from sheet A and B with a Power Query, set to update on opening file. this works.

3. sheet D has a Pivot table with some data from sheet C, set to update on opening file. this does not work.

So I need a way to trigger sheet D update when the update of sheet C is finished, but I don't see any settings for that - do I need a macro for that? If so, could you post a function to achieve that? I found some VBA to trigger the update, but I don't know how to call it when the first update is finished.  But of course it would be nicer if it could be achieved without macros. Thanks a lot!

[#2]

@lichtpixel 

In general pivots get refreshed before queries.

You need a macro that refreshes the query first (I believe you have to disable background refresh in the properties) and the pivot second.

[#3 · Accepted solution]

@lichtpixel Does it work to uncheck the "Enable background refresh" property of the query:

[#4]

Thanks a lot, I enabled "fast data load" and disabled "background refresh", now it works fine!