locked
PowerPivot data tables disappeared after closing and re-opening saved workbook RRS feed

  • Question

  • I spent a couple of hours using PowerPivot to get data from a SQL Server database, and setting up relationships between the tables.  I then added a pivot table (through the PowerPivot interface) to a new worksheet, and when I tried adding fields from one of the data tables, I kept getting an error message.  I tried refreshing the pivot table and saving the workbook multiple times, but the error persisted.  So I closed the file, and Excel, and then re-opened it, but to my dismay I found that the PowerPivot tables were completely gone.

    Does anyone know why this might have happened and how to prevent it?  I was pressing the Save button in the Excel Quick Access Toolbar, because I thought this would also save the PowerPivot data (which it seemed to have done in the past) - should I also be pressing the Save button in the PowerPivot interface?

    By the way - I don't remember the error I was getting when trying to add fields to the pivot table, because I can't replicate it.

    I'm using the 2012 version of PowerPivot, in a virtual environment running Windows Server 2008 R2 Enterprise.

    Monday, December 3, 2012 5:40 AM

Answers

  • This happens all to often.  It's very frustrating.  The formula is clearly valid but Excel does not like it.  Close then reopen the workbook and the formula is gone.  Try to recreate the formula using the same name and excel says its already in use (even though it's nowhere to be seen in the measure list.)  So, I need to recreate the measure and give it a new name.  Then cross my fingers and hope it works. 
    Wednesday, January 9, 2013 3:46 PM

All replies

  • Hi hotbread,

    Thank for your question.

    I have never encountered such issue. Can you reproduce this issue on your environment? If so, please post the detail error message to us when you try to save the PowerPivot file.

    Regards,


    Elvis Long
    TechNet Community Support

    Friday, December 7, 2012 8:07 AM
  • I haven't been able to reproduce the issue yet, but this may be because I'm using PowerPivot less than before.  I've been having other PowerPivot problems (as per the thread "Unable to change PowerPivot data query - SQL statement is not valid") and I've been in contact with Microsoft support about them.  At this stage, they have recommended that I install the Office 2010 service pack 1, the Excel 2010 hotfix package, and the most recent version of PowerPivot.  Unfortunately however, I don't have admin privelages to install these updates myself, and I'm waiting to find out if my work IT department are willing to install them. Very frustrating...
    Thursday, December 20, 2012 1:14 AM
  • This happens to me now and again, usually after adding a measure or an extra table to an existing model. I get the error message:

    Unable to add 'blahblahblah' to Pivottable as the formula is invalid.

    At this point my only option is to save and close but I know when it has happened any changes I made will have disappeared. Bizarre. 

    Thursday, December 20, 2012 8:21 AM
  • This is so incredibly frustrating!  After I added new data to the existing PowerPivot data model, I got the following error message when I tried adding one of the new fields to the pivot table: "The command was cancelled. Please press F1 to get the help topic "Excel Window: PowerPivot Field List" for more details."

    I tried the fixes listed on the following web page: http://www.powerpivotpro.com/2010/08/two-common-powerpivot-error-messages/

    Unfortunately, the first 3 fixes didn't work, and after trying fix number 4 (save, close then reopen the workbook) the new data was completely gone again.

    Wednesday, January 9, 2013 7:38 AM
  • This happens all to often.  It's very frustrating.  The formula is clearly valid but Excel does not like it.  Close then reopen the workbook and the formula is gone.  Try to recreate the formula using the same name and excel says its already in use (even though it's nowhere to be seen in the measure list.)  So, I need to recreate the measure and give it a new name.  Then cross my fingers and hope it works. 
    Wednesday, January 9, 2013 3:46 PM
  • Hi Elvis. I hope you are doing well.

    Did this issue / BUG get resolved? We are in 2017 and I am using Microsoft Professional Plus 2016 64Bits and this issue persists. Really? :0 :0

    Friday, February 3, 2017 8:50 PM
  • Hi Elvis. I hope you are doing well.

    Did this issue / BUG get resolved? We are in 2017 and I am using Microsoft Professional Plus 2016 64Bits and this issue persists. Really? :0 :0

    Hi Jimmy,

    We have closed this thread, I would suggest you open a new thread and elaborate your issue with more detail. It will help to solve your case.

    Monday, February 6, 2017 3:06 AM