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 Mariham,
Check your query properties and be sure what "Enable background refresh" in your main queries properties is unchecked. With background refresh disabled the query complete refresh before other queries are refreshed and as well before pivot tables are refreshed.
SergeiBaklan Hi Sergei, my excel has 8 queries (with no errors) and I have unchecked the background refresh. Yet my existing pivot table does not reflect new data completely. I am showing data for 5 years and the data for two latest years is not getting updated. However, if I create a new pivot using the same power query table, the data is accurate. I cannot upload the excel as it is confidential but would be more than happy if you could help find a solution here.
Hello SergeiBaklan, I wanted to post some VBA code I use to disable background refresh for all of my connections in case anyone would find it useful. I run this code on a regular basis as Excel seems to change this every time I decide to touch a query ☺ When you have many queries, this sure does come in handy!
Sawcy1
HSawcy1 I have same issue, Refresh All is not refreshing my the data source in my power query which is connected to my pivot table in excel.
I followed the same steps you provided which is to uncheck the box enable background refresh but it did not help.
Do you know any further steps I can take to solve this please?
- Hello Yemisi13, turning off background refresh will not fix an issue with the data source itself not being updated as you mentioned is happening in your situation. I would check the query that provides the data to your pivot table. When you open the Workbook Queries pane, or go into the Power Query Editor, does that query have errors? If so, they will need to be corrected. After fixing any query issues, I recommend running the VBA code I provided to make sure background refresh is turned off again for all your queries. I have found that Power Query often turns background refresh back on again when you touch a query.
Sawcy1
Thanks for the quick reply! I really appreciate it! Could you possibly walk through how to check my queries properties?
Figured out how to check query properties and the "Enable background refresh" tab is unchecked already. This did not make a change.
Hi Mariham,
Let me clarify
1) With Refresh All not all PivotTable are refreshed. If you click RefreshAll once more (without changing any data) does that refreshes PivotTable?
2) You disable "Enable background chacking" only for queries which upload data from external sources into data model or for all queries?
