SOLVED

Pivot Table Will Not Update Properly

Copper Contributor

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: refresh the table, refresh the powerpivot, or refresh all data. Some data is refreshed but not all. The only way I can actually get correct info is by generating a new pivot table where it will get updated info. I have many pivot tables that are conditionally formated and do not want to have to go through the whole process of recreating my pivot tables one by one and reformating them everytime I refresh my excel data. How do I fix this?

19 Replies

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.

 

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?

 

Depending on the complexity of your Queries, you may have to Update All twice. Excel doesn't know what to update first. So if your Pivot Updates before the Query updates the Data Model or Table, then the Pivot Update will be ineffective the first time.

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-evalu...

 

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:

BackgroundRefresh.JPG

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.

 

best response confirmed by Grahmfs13 (Microsoft)
Solution

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à.

@Sergei Baklan 

 

I can't find how to do this in Excel 2019.

 

Can you help?

Thank you so much !
I also had this problem and the solution is indeed simple once you know it.

Hello @Sergei Baklan,  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

H@Sawcy1  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

@Sawcy1the file does not open could you post it again please.

Thank you 

Yemisi13, it is a macro file so you need to have Excel open and use the VBA editor to see the code.
I am posting the code here for your convenience.

Sawcy1

Sub Set_Connection_Property_Disable_Background_Refresh()
Dim con As Object

For Each con In ActiveWorkbook.Connections
If con <> "ThisWorkbookDataModel" Then
With con
Debug.Print con.Name
If con.OLEDBConnection.BackgroundQuery = True Then
MsgBox "Background Refresh was on for " & con.Name & ". Turning off now."
End If
Debug.Print con.OLEDBConnection.BackgroundQuery
con.OLEDBConnection.BackgroundQuery = False
Debug.Print con.OLEDBConnection.BackgroundQuery
Debug.Print "-----------------------"
End With
End If
Next con
End Sub
Thank you
My pleasure ☺

@Sergei Baklan 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.   

1 best response

Accepted Solutions
best response confirmed by Grahmfs13 (Microsoft)
Solution

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à.

View solution in original post