Power Query and Pivots require double refresh

[MushroomFace]

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!

 

[Ken Puls]

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:

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,

 

[MushroomFace]

Thanks for your reply Ken! Much appreciated. Amy

 

[Ken Puls]

No worries Amy, happy to help!