none
Power Query Refresh via VBA / Pivots refreshing before query completes. RRS feed

  • Question

  • Hi,

    I am using the code below to trigger an update to my Power Query table. Then updating my pivot tables.

    Dim Pivot As PivotTable Dim ws As Worksheet Dim lo As ListObject For Each ws In ThisWorkbook.Worksheets For Each lo In ws.ListObjects lo.QueryTable.Refresh BackgroundQuery:=False Next Next DoEvents For Each ws In ThisWorkbook.Worksheets For Each Pivot In ws.PivotTables Pivot.RefreshTable Pivot.Update Next Next

    It appears that the Power Query update is still running when the Pivot tables begin to refresh which is not correct. BackgroundQuery:=False does not seem to work with Power Query.

    Does anyone have any suggestions on how to get Excel to wait until the Power Query is complete?

    Tuesday, September 16, 2014 5:48 PM

Answers

  • Try the following code:

        Dim Connection As WorkbookConnection
        For Each Connection In ThisWorkbook.Connections
            Connection.Refresh
        Next Connection

    Note: In the scenario that I use this code, all QueryTables and PivotTables have background query refresh turned off in the connection properties of each Table or PivotTable. Also, my PivotTables are sourced from Power Query. 

    Tuesday, September 16, 2014 7:51 PM
  • The code that I provided was intended to be used for Pivot Tables created from the Data Model with data loaded from Power Query. I'd suggest that you create your Pivot Tables via this method.
    Wednesday, September 17, 2014 1:42 PM

All replies

  • Try the following code:

        Dim Connection As WorkbookConnection
        For Each Connection In ThisWorkbook.Connections
            Connection.Refresh
        Next Connection

    Note: In the scenario that I use this code, all QueryTables and PivotTables have background query refresh turned off in the connection properties of each Table or PivotTable. Also, my PivotTables are sourced from Power Query. 

    Tuesday, September 16, 2014 7:51 PM
  • Thanks for the reply 

    Unfortunately works the same, the query will start to execute then, before all the data is loaded to worksheet the pivot tables start updating.

    When you say "background query refresh turned off in the connection properties of each Table or PivotTable", under the Data tab (Excel 2010) I only have a single connection (which is the Power Query Connection). I do not have Enable background refresh checked. This is the only place I see this option (not on each of the pivot tables).



    • Edited by ccmd00d Tuesday, September 16, 2014 8:19 PM
    • Proposed as answer by kazyakj Saturday, November 22, 2014 6:01 PM
    Tuesday, September 16, 2014 8:18 PM
  • What source or sources are your Pivot Tables using?

    Tuesday, September 16, 2014 9:43 PM
  • What source or sources are your Pivot Tables using?


    Power Query result table loaded to a worksheet.
    Wednesday, September 17, 2014 12:49 PM
  • The code that I provided was intended to be used for Pivot Tables created from the Data Model with data loaded from Power Query. I'd suggest that you create your Pivot Tables via this method.
    Wednesday, September 17, 2014 1:42 PM
  • Thanks for the reply 

    Unfortunately works the same, the query will start to execute then, before all the data is loaded to worksheet the pivot tables start updating.

    When you say "background query refresh turned off in the connection properties of each Table or PivotTable", under the Data tab (Excel 2010) I only have a single connection (which is the Power Query Connection). I do not have Enable background refresh checked. This is the only place I see this option (not on each of the pivot tables).



    I am having the excact same issue.

    I am connecting to an O-Data connection from Excel 2010 and I have to waite for the Power qurey to finish and then make a new refresh to get the Pivot opdated. Does any of you have an solution to this?

    Thanks in advance


    Bent G Andersen

    Thursday, December 18, 2014 2:51 PM
  • Anyone ever figure this one out?
    Thursday, July 30, 2015 3:31 PM
  • I am having the same issue and proposed solution does not work. This is a massive problem for my tool, as it requires to update pivot tables only after Power Queries have run through. Right now this is a huge deficit for a tool I built. I basically have to tell people to refresh once and then trigger another process manually afterwards, which often leads to issues as people trigger the next process to early.
    Thursday, December 3, 2015 1:07 PM
  • Hi All. If the macro doesn't work after you ensure that Enable background query is turned off in the Data Model Connection Properties dialog box, try the following modification:

    Sub RefreshAllConnections()  
        Dim Connection As WorkbookConnection
        For Each Connection In ThisWorkbook.Connections
            Connection.Refresh
        Next Connection
        Workbooks(<"WorkbookFullName">).Model.Refresh
    End Sub

    WorkbookFullName = Workbook name + File extension e.g. MyWorkbook.xlsx

       
    Thursday, December 3, 2015 3:19 PM