Forum Discussion
Pivot Table Will Not Update Properly
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à.
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.
"Not Exactly."
That's part of my point.
Additionally, the Background refresh is not a Query property but a Connection property.
Excel was kind enough to set up the connection, but did Excel ever prompt for the refresh method when I commited the Load method? I don't remember it doing so.
As you point out, and the TechNet forum indicates, (two places so many users miss out on) this setting is hugely important to the behaviour of PivotTables fed from Queries, now Get & Transform.
With Get&Transform available to the general user base, this unclear situation will be best solved by:
A: Providing better help references in the sites and references provided to users by Microsoft.
B: Instruct on the fail-safe method of Refresh All twice.
Hi Brian,
In general yes, help/support pages could be better organized. Quite often you can find something if only if you know exactly what and how to find.
As for properties. Yes, more exactly refresh is the connection property, but with Unified G&T underlying connection is hided behind the query, and all connection properties are under Query Properties. Connections tab is for legacy connectors and ThisWorkbookDataModel. I speak now about Queries & Connections pane.
If we are in Existing Connections or in full RefreshAll menu that is still "connection properties". But if you open it the title will be Query Properties.
