Refreshing Pivot Tables after a PQ refresh

M

Michlus

New member
Joined
May 10, 2016
Messages
12
Reaction score
0
Points
0
I have a complex PQ set of queries that eventually return a set of data, which is used in pivottables.

I wrote a macro (triggered by a button) that:
a. Refreshes the PQ connections
b. Refreshes the Pivottables.

I read that I need to disable the background refreshing of the PQ connections, which I did.

Yet, when I use a large amount of data source, it doesn't work well.
I obtain a weird set of pivot tables. It looks like the pivottables were engaged while the data wasn't ready yet.
When I then rightclick on the pivottable and hit Refresh (or re-engage the macro), then it is OK.

I've seen some references to this, and some advises (like 'DoEvents'), but it doesn't seem to work ...

Thanks for any assistance.
 
Office Professional Plus 2013.
But my workbook will also be used by people with Excel 2010.
 
Okay, so a couple of things...

If you're not on the latest version of Power Query (v2.32.4307.xxx) it may be worth a download. There are a couple of things in there to help.

The first is Fast Data Load (which has been around for a while). You want to make sure that box is checked:
  • Power Query --> Options --> Data Load --> Fast Data Load

The second, (I think this is the newer one,) is the Background Data setting, which you want to turn OFF:
  • Power Query --> Data Load --> Background Data --> Allow data preview to download in the background

Turning this off prevents the loading of the preview data, which may be interfering with the refresh. Hopefully setting both of these should help to allow the refresh to complete.

Also, from a comment on my blog, Alex came up with some code that may help (modified to show where to update the Pivot):

Code: 
On Error GoTo ErrorExit
For Each cn In ThisWorkbook.Connections
If Left(cn, 13) = "Power Query -" Then cn.EnableRefresh = True
cn.Refresh
Application.CalculateUntilAsyncQueriesDone
Next cn

Application.CalculateFullRebuild
Application.CalculateUntilAsyncQueriesDone

'Do your PivotTable update here

I would be very curious to hear if any/all of these help to solve your issue.
 
Thank you, Ken.
1. I was already with the latest version of Power Query.
2. 'Fast Data Load' didn't help. (Mind that it's not enough to change in the Options, as this is only the default settings for new queries. But I did go into property of all the existing queries and switched to Fast Data Load. It didn't solve the issue).
3. Background settings to OFF didn't solve it either.
4. After applying Alex; code - it DID WORK!!!
2 notes:
a. The .EnableRefresh = True caused a debug error. I removed that.
b. Alex' code on itself isn't enough. I've tried it after reverting back the other settings, and it failed to present the expected pivot tables. Only the combination made it work!

KUDOS!!!
 
You must log in or register to reply here.
Back
Top