Power Query and Pivots require double refresh

MushroomFace

MushroomFace

New member
Joined
Jun 12, 2018
Messages
6
Reaction score
0
Points
0
Location
UK
Excel Version(s)
2013
Hi

Issue: I have a Power Query (PQ) Table, that feeds into a pivot table (I have tried both methods - Pivot and Power Pivot (PP)) however once the data has refreshed, I have to 'refresh All' again to update the pivots. I'm not keen as it's prone to human error across the team!

Firstly, Am I correct in assuming the below?
  • Loading directly to PP from PQ, will auto refresh data and pivots the first time.
  • Do my thing in PQ, load this to a table in excel *then* take this to PP - will not auto refresh the Pivots the first time
  • same as above but use Pivot tables instead of PP will not auto refresh the first time

If these are true, the reason I can't load straight to PP from PQ is because the reports need to be saved down monthly and unlinked from an audit view, with these types of documents I find it easier to have a master worksheet with a loaded table in excel, save as and then unlink the table (as this is easy for people with no PQ knowledge).

Any suggestions to overcome this?

Thanks!
 
Hey there,

Yes, you're correct.

So the only workaround I can really think of is to code a macro to refresh the Power Query, then refresh one of the PivotTables connected to the data model afterwards. Something like this:

Code: 
Sub RefreshPQ2XL2PP()

    Dim cn As WorkbookConnection


    With ActiveWorkbook
        Set cn = .Connections("Query - Sales")
        With cn
            .OLEDBConnection.BackgroundQuery = False
            .Refresh
            .OLEDBConnection.BackgroundQuery = True
        End With
        
        .Model.Refresh
        
    End With
End Sub

HTH,
 
Thanks for your reply Ken! Much appreciated. Amy
 
You must log in or register to reply here.
Back
Top