none
Pivot tables dependent on Power Query do not refresh when a user selects Data-->Refresh All RRS feed

  • Question

  • Excel 2013, Power Query Version: 2.27.4163.242

    When a user refreshes a Power Query, and new data is returned (regular worksheet data, not Power Pivot). Dependent Pivot Tables do not refresh. The user has to select the pivot table and select refresh. Is there a way to automatically have the pivot tables update?

    Monday, November 23, 2015 9:02 PM

Answers

  • Hello Rich,

    Did you create the PivotTable from the range or from the Power Query related connection? From the latter, you are completely correct that a Refresh All should ideally do the job.

    We are aware of this limitation, and will consider supporting this scenario in future updates to Excel 2016.

    Wednesday, November 25, 2015 4:25 PM

All replies

  • AFAIK, you can only do this with VBA. One method consists in leveraging the "QueryTable AfterRefresh" event. 

    Below are links you may find useful.https://support.microsoft.com/en-us/kb/213187

    https://excelandaccess.wordpress.com/2014/01/18/create-beforeafter-query-update-events/

    Tuesday, November 24, 2015 2:34 PM
  • In the current version of VBA (Excel 2016), there is no QueryTable object associated with Data Model pivot tables. Only Worksheets, ListObjects, and PivotCaches (the data layer of pre-data model pivot tables) support QueryTable Objects. 
    Tuesday, November 24, 2015 4:37 PM
  • Hello Rich,

    Did you create the PivotTable from the range or from the Power Query related connection? From the latter, you are completely correct that a Refresh All should ideally do the job.

    We are aware of this limitation, and will consider supporting this scenario in future updates to Excel 2016.

    Wednesday, November 25, 2015 4:25 PM
  • Hi Gil,

    It may be an old thread, but is this limitation fixed?

    I am using Office 365 and experiencing the same behaviour of a Pivot, based on Power Query connection.

    Thank you in advance!

    Thursday, December 6, 2018 11:10 AM