none
PQ Bug? Pivot with slicers won't refresh if source data loaded to data model via PQ RRS feed

  • Question

  • I'm posting this under Power Query 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 "2". Then I went to the source data table and changed the "Amount" value for the row with code "2". 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 "2" again, the value in the pivot table updates. 

    I found more slicer related issues if I then delete that pivot table, and then change the "Load To" option on the query from "Connection Only" to "Table" (still keeping it with the "Add to Data Model" setting). When I re-make the pivot table from within Power Pivot, and try to add the "Code" field as a slicer, nothing happens and the slicer doesn't show up at all.

    Are these bugs? 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).

    Monday, February 13, 2017 6:42 PM

Answers

  • 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.  Thanks again for letting us know about this.

    Thanks,

    Howie Dickerman, Program Manager on Excel product team

    Thursday, February 16, 2017 7:39 PM

All replies

  • If, instead of using a slicer, you simply refreshed the data twice after making the change in the source data, does the correct data show up in the PivotTable?
    Monday, February 13, 2017 8:52 PM
  • Thanks, but no, that doesn't do it. I know you have to do that if you have the "Enable Background Refresh" turned on, so I'm always turning that off, but in this case, due to the "Load To" settings, that checkbox is grayed out. 
    Monday, February 13, 2017 8:59 PM
  • Would you mind sharing your simple workbook with me? You can attach and send it to pq2fb@microsoft.com, and just reference this thread and mention my name.
    Monday, February 13, 2017 9:35 PM
  • Email sent. Thank you!
    Monday, February 13, 2017 10:10 PM
  • Your symptoms don't sound identical to mine, but I've rebuilt several complex models because of this sort of a problem.

    I then found a tip in Rob Collie and Avi Singh's Power Pivot and Power BI book that suggested going into File, Options, Add-ins, COM Add-ins and disabling Power Pivot, saving changes, then going back in and re-enabling it.  This worked for me and saved me a lot of time and frustration.

    Dale

    Monday, February 13, 2017 11:10 PM
  • Hi Dale -- I just tried that now and unfortunately it didn't work in this case. But I will keep it in mind for other quirks! I've definitely spent a good amount of time rebuilding complex models too -- sometimes it feels like you have to do everything perfectly as you build it, and that if you do too much "remodeling" as you go along, the file starts to act up. I know there are certain rules about how and where things need to be changed between PQ & PP -- I try to follow those as best I can, but I seem to inevitably go wrong somewhere, so once I've figured out everything I'll need from start to finish, I do it all all over again in a fresh workbook to try to avoid having a buggy model. Hoping that as PP/PQ get developed further, they become less sensitive to this kind of stuff. 

    Thanks!

    Tuesday, February 14, 2017 1:37 AM
  • 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.  Thanks again for letting us know about this.

    Thanks,

    Howie Dickerman, Program Manager on Excel product team

    Thursday, February 16, 2017 7:39 PM
  • Great, thank you both, Howie & David, for your help with this!
    Thursday, February 16, 2017 8:09 PM
  • This bug has been plaguing me for about 2 years now.  It only appears to happen on long-ish running PQ scripts ( > ~10 seconds) loading to the data model when there is a slicer involved.  FYI: if there is no slicer (e.g. just using the older pivot table Filter functionality) then the bug never occurs.

    I remember once attempting to write (and failing) a VBA event to trigger a slicer refresh but it was surprisingly difficult trying to (a) detect when a PQ refresh had fully completed and (b) how to actually refresh the slicercache so it fixed the bug every time.

    Howie - will the patch be available to all instances of Excel 2013 or just office 365 users?


    • Edited by Simon Nuss Friday, February 17, 2017 4:31 PM
    Friday, February 17, 2017 2:55 PM
  • The patch for Excel 2013 will be available to all Excel 2013 customers. 

    Office 365 subscriptions are now running Excel 2016 in which this issue has already been fixed.

    Thanks,

    Howie

    Tuesday, February 21, 2017 9:10 PM
  • Is there any update on this bug? I am experiencing the same issues.

    In the beginning I was able to refresh the data without having to click on a slicer. Then I added a table that I created in Excel to Power Query and merged that table with a table from the database. From that moment I had to click on a slicer to refresh the pivot table.

    But strange was that when I added that self created table directly to PowerPivot and linked it with the other table, then it worked. Then I did not have to click on a slicer to update the pivot tables.

    Saturday, June 17, 2017 4:06 PM
  • I'm having the same issue - none of my pivot tables are refreshing when I update the data in power query.  

    Is there a fix or workaround here?

    Monday, June 19, 2017 10:06 PM
  • Can you share the Office and Power Query versions that you are running?
    Tuesday, June 20, 2017 5:57 AM
  • Office Professional Plus 2013

    Power Query Version: 2.42.4611.421 32-bit

    Tuesday, June 20, 2017 2:09 PM
  • Can you try downloading the latest version of Power Query (2.46)? This issue was fixed in that version.
    Tuesday, June 27, 2017 5:36 PM
  • Yes it works! Thanks David!
    Tuesday, July 4, 2017 10:09 AM
  • Hi,

     After updating to the lastest Office pro plus 365 (Excel 2016) Slicers are gone. Same as described, data model points to a PQ table.  I can share the file.


    Patricio

    Wednesday, October 25, 2017 11:31 AM
  • I found a fix for this. I'm running Office 2013 with Power Query so don't have access to any recent patches. I set an auto_open macro to run when the sheet is opened that refreshes the queries and then clears the slicer. This revealed more problems as even setting queries to disable background loading does not seem to work reliably and the code would proceed while the refresh was still running. I found however, calling each query individually with a refresh on its own line and then doevents had the desired effect. You do not want to refresh all, as doevents will not pause as you expect it should. You have to refresh each connection on its own line. Here is my code:

    Sub Auto_Open()
    ThisWorkbook.Connections("Query - Table1").Refresh
    ThisWorkbook.Connections("Query - Table2").Refresh
    DoEvents
    ActiveWorkbook.SlicerCaches("Slicer_Table1").ClearManualFilter

    End Sub

    Disable background loading on all of your queries, and disable Fast Load as well for best results.

    • Proposed as answer by Chris M Carter Thursday, January 18, 2018 4:31 PM
    Thursday, January 18, 2018 4:30 PM
  • This is still a problem in Excel 2016 MSO (16.0.4639.1000) 32 bit.

    When I first add a slicer to a PT that is connected to a PQ query that was added to the data model, the slicers do not change the state of the table.  When I close the file and re-open it though, the problem is gone for one slicer but not all.  After a few clicks, all stop influencing the table. :(


    • Edited by Cool.Blue Monday, April 30, 2018 8:02 AM
    Monday, April 30, 2018 7:57 AM
  • Hi,

    We'd like to better understand the scenario, and also want to figure out if this problem is something that might have been fixed in newer versions of Excel.  (Or if it's something we still need to fix.)

    Build 16.0.4639.1000 is the version that went out in January to customers with a perpetual license.  We tried to reproduce the problem using the latest Office365 subscription build, but it didn't repro for us. 

    If you don't mind, could you describe in as detailed a way as possible how to reproduce this problem?  If you have a workbook that doesn't contain confidential data that you can share with us, then we can open that workbook, follow your instructions, and hopefully reproduce the problem.  Once we can see the problem we can fix it.  The data doesn't need to be your real data, it would be enough to put some random numbers on a worksheet, then use Power Query (Get & Transform) to load that data into the Excel Data Model, then create a PivotTable with some slicers and list the steps that result in reproducing the problem.

    If you can send a sample and instructions to my email, howied@microsoft.com I'll make sure that we have one of our engineers take a look and figure out what's happening. Before doing all this work, you might want to try installing the May update for Office 2016 (16.0.4690.1000) which went out today to see if recent improvements might have already fixed this issue.

    I look forward to getting your repro steps and sample workbook. 

    Thanks!

    Howie

    PS: So I can track this with your reply, we've assigned this a bug number of VSO 2303430.  You won't care, but I'll need this number later.

    Tuesday, May 8, 2018 10:09 PM