Excel Online: Pivot Table not refreshing

[Christopher Sundar]

Reposting from Microsoft Answers to get more traffic and hopefully an answer to the problem.

I created a file in Excel (365 v1809) and put it on SharePoint.  The team using the file is accessing it through Excel Online.  

Users enter data on one tab, and then other tabs hold pivot tables (with shared pivot cache) which create different operational views of the data. 

Scenario1: User adds a new row to the data entry table and refreshes the pivot table.

Outcome: Pivot table refreshes with new data.

Scenario2: User modifies data in existing row of the data entry table and refreshes the pivot table.

Outcome: Refresh does not update the data within the pivot table.

Scenario3: Added a new column to the source data table with a formula that performs a lookup to another tab on the same file.  The original value when I added the column (no data) is still present in the Pivot Table even if I refresh through Excel Online.  The lookup values in the source data is updating, but not the Pivot Table.

Solutions attempted:
1 - Deleted the Pivot Table and rebuilt
2 - Turned off caching of data for the pivot table

3 - Created distinct pivot cache for each table

4 - Copied file to another SharePoint site, then deleted/replaced the file on the original site (no other changes).  This temporarily fixed the issue but it has come back again.  

When I use the file from my desktop Excel install the tables refresh normally.

Please help.

[#2]

@Christopher Sundar have you figured out a solution yet? I'm having a very similar problem.

[#3]

@adnic2019 No.  I ended up linking the file to PowerBI and leveraging that tool for reporting.

[#4]

@Christopher SundarI realize this was posted in 2019, but I'm having the same issue in 2022. Anyone find an answer?

[#5]

@Christopher Sundar I am having the same issue, and I think it's because the 'Enable Background Refresh' option is turned on for the query connections. This is what I found:

When we have the “Enable Background Refresh” property enabled, we are telling the system that every time we ask it to refresh the Power Query query, we authorize the system to move on with other tasks, without needing to wait for that query refreshing process to be complete.

The pivot table shows a summary of the data in the Power Query table. However, the system is not waiting for the Power Query table to be finished with the data updating before starting with the pivot table’s updating process. Therefore, the pivot table is being refreshed at the same time as the Power Query table is. Because the two processes start around the same time, the pivot table is not updated correctly because the data it has access to at the beginning of the refreshing process has not been updated yet with the most current information.

When the property “Enable Background Refresh” is disabled:

If the property “Enable Background Refresh” is disabled, the system will refresh the Pivot Table query first, and when the table is updated the pivot table gets also updated with the new information.

This way, both the Power Query table and the pivot table get refreshed at once.

I am yet to see if this works for my case but wanted to provide the info here in case it helps!