Defer Updating of Pivot Tables until ALL Power Queries Refreshes have been completed

[Ted Murphy]

Hi Ken,

I have created a VBA Macro that refreshes the volatile Tables in a PowerPivot model.

Each Refresh is executed using the following type of Code.

ActiveWorkbook.Connections("Power Query - Customers").Refresh

ActiveWorkbook.Connections("Power Query - Calendar").Refresh



I have quite a lot of Pivot Tables in the Workbook and these appear to be updated as each of the Power Query Refreshes is completed.

Is there a way of suppressing the Pivot Table updates until after ALL of the refreshes have been completed?

This would help reduce Refresh time.

Thanks,

Ted

ps
Using Excel 2013 and Power Query Version: 2.34.4372.163 32-bit

 

[SteelReyn]

Have you tried disabling background refresh for your queries? I think that will resolve the issue. On the Data tab, click Connections. For each query, select one in the Workbook Connections window and click Properties. Uncheck the "Enable background refresh" option and click OK.

 

[Ted Murphy]

Hi SteelReyn,

I have just checked and for all the Queries that load to the Data Model, the "Enable background refresh" button is "grayed" out. So you don't have any discretion in relation to it.

I had disabled the Screen Updating using Application.ScreenUpdating = False while the VBA code is running, and in that scenario you are not aware that the Pivot Tables are being refreshed after every PowerQuery Data Load.
Once you enable the ScreenUpdating again you see the Pivot Table refreshes.

So your suggestion is not the answer ... but thank you for the interest.

Regards,

Ted.

 

[SteelReyn]

My mistake. I forgot to load to the data model when trying to recreate the issue. The option is available if not loading to Power Pivot. Since it's grayed out and unchecked, I would assume all queries would update before the pivot tables. Are you seeing values in the pivots change and update, or just the screen "blip" between query refreshes? Just making sure you've confirmed the tables are actually updating. That's about the extent of my refresh knowledge. Maybe Ken can weigh in.

 

[Ted Murphy]

While a Power Query refresh is taking place ... the text "RETRIEVING DATA... (PRESS ESC TO CANCEL)" appear on the right hand side of the Excel Status Line.

Once the update is complete the text "READING DATA... (PRESS ESC TO CANCEL)" flashes repeatedly; presumably while each of the Pivot Tables and Slicers is refreshed.

The pattern is the same for each subsequent Power Query refresh.

So while I cannot see individual Pivot Table being refreshed ... because I have the Macro Kick-Off Screen on display ... I am very confident that the Pivot Table refresh takes place after each of the Power Queries.

Ken may have access to the minds in Microsoft who could throw some light on this.

I believe that it is applicable to any Excel User using VBA to drive multiple Power Query updates to a model.