Pivot Table Will Not Update Properly

I have data created in PowerQuery that is connected to PowerPivot which generates PivotTable in my excel sheets. I have noticed that my pivot tables do not refresh with the most recent data once I: r...

BI & Data Analysis

excel

Macros and VBA

office 365

Vinod Jagroep
Jun 06, 2018
I always had the exact same problem and finally found the solution, which appears to be embarrasingly simple.
Click Change Data Source. After updating my data (obviously) the data range has expanded! Select the data set again and voilà.

SergeiBaklan

Diamond Contributor

Sep 07, 2017

Hi Brian,

Not exactly. How queries are updated is explained, for example, here https://social.technet.microsoft.com/Forums/en-US/8d5ee632-fdff-4ba2-b150-bb3591f955fb/queries-evaluation-chain?forum=powerquery

Bit different story with PivotTables, if we disable background refresh all queries will be refreshed before PivotTables.

That affects refreshing bahaviour if PivotTable is built on data (table) returned by query. In this example:

Query1 and Query2 return two identical tables based on one source Table1. We have two PivotTables on that tables. In properties of Query2 background refres is disabled and PivotTable2 is refreshed after the first RefreshAll. To refresh PivotTable1 we have to click RefreshAll twice.

Above sample is attached.

background refresh.xlsx151 KB