Pivot Not Refreshing Until Slicer is Changed

T

tmacg7

New member
Joined
May 2, 2016
Messages
9
Reaction score
0
Points
0
Excel Version(s)
Office 365 / V1803
I'm posting this in the Power Query forum because this Pivot Table issue is only happening to me on Pivots that are made from the Power Pivot data model where the data was loaded to the model via Power Query. I've tested it on a regular pivot table straight off the data from a worksheet, and also as a PowerPivot from data loaded straight to the PowerPivot model from the worksheet, and both of those cases work fine.

So the issue is that I have a pivot table that won't show the refreshed results until I interact with the slicer. This has happened on a few complex workbooks recently, and I thought maybe those files had gotten corrupted, but now I made brand new super simple file and it still does it. The source data is in a simple three row, three column table on a worksheet. I then Power Queried that and chose to load to Connection Only and add to the Data Model. I then made a pivot table off of that from within PowerPivot. I added the "Name" column to row labels, the "Amount" column to the values, and put the "Code" column to a slicer, then sliced to the code value of "B". Then I went to the source data table and changed the "Amount" value for the row with code "B". Then I did a refresh all, and the pivot table does not show the updated value. But if I interact with the slicer, even just to click "B" again, the value in the pivot table updates.

Can anyone try this in the attached workbook and let me know if it's doing the same thing to you? I'm on Excel 2013 64-bit, with the most recent version of the Power Query add-in (Version: 2.40.4554.161 Published Feb 7, 2017; Download file name PowerQuery_2.41.4581.1182). I can't tell if I'm doing something wrong, or maybe there's a bug with the latest version of Power Query?

Thank you!
 

Attachments

  • Book1.xlsx
    126.1 KB · Views: 20
tmacg7 said:
... then sliced to the code value of "B". Then I went to the source data table and changed the "Amount" value for the row with code "B". Then I did a refresh all, and the pivot table does not show the updated value. But if I interact with the slicer, even just to click "B" again, the value in the pivot table updates.
Click to expand...

Sorry, meant to say code value of "2", not "B".
 
And while I'm at it, another frustrating thing that has been happening to me with slicers in more complex workbooks is now happening in this super simple workbook too. Sometimes I select a field to add as a slicer and click OK, and then nothing happens -- no slicer shows up. This did not happen in this simple workbook until I deleted the worksheet with the pivot table, then changed to the "Load To" settings on the query from "Connection Only" to "Table" (still keeping it with the "Add to Data Model" setting). Then when I re-made the pivot table from within Power Pivot, and tried to add the "Code" field as a slicer, nothing happens and the slicer doesn't show up. Really hoping someone can confirm if I'm doing something wrong, or there's something buggy with the current version of PQ, or maybe my whole install of Excel has issues and I should reinstall. Ugh. :frusty:
 
Hi tmacg7,

I have had this very same issue (slicer interaction required to trigger refresh) with several Power Pivot / Power Query workbooks at work, so glad to have confirmation from you that others are experiencing it.

I can reproduce the problem with your workbook in my installation at work (Excel 2013 32-bit and Power Query 2.41 32-bit).

I am interested in whether it is an issue in Excel 2016 - will test later at home.

Keen to hear back any updates :)

Cheers,
Owen
 
Thanks for testing, Owen and Paul. The bug has now been confirmed by Microsoft over on the Tech Net thread. Here's what they said:
"Thanks for reporting this issue. It appears that this problem has been fixed in Excel 2016, but not in Excel 2013. I've filed a bug in our bug database so one of our engineers will look into this, and we will get it fixed. Refresh should absolutely update the PivotTable. I suspect we will find that we just need to port a fix that was already made in Excel 2016 back to Excel 2013 and then ship it in one of the monthly updates. In any event, we have the bug filed, (VSO 1413627) and it will get fixed."
 
You must log in or register to reply here.
Back
Top