locked
PowerPivot in Excel 2016 Data Model Lost -- Empty RRS feed

  • Question

  • I am facing a most frustrating error with Excel 2016. Random loss of the data model. It happens most frequently if you open more then one instance of Excel. I make constant backups as I go but tonight even the most recent backup did not have the data model and this time I was working on a single file. What is going on? I never once lost the data model in 2013.

    Hours of work is lost by this bug! Does anyone have a fix? 

    Thanks,

    Ken

    Monday, October 19, 2015 2:34 AM

Answers

  • Thanks, Ken. We think we've identified a problem that could've lead to this, and are working on getting the fix out quickly. As you noticed, this tends to happen sometimes when multiple files are opened.

    thanks!

    ash

    Program Manager, Excel


    Program Manager, SQL Server Analysis Services

    • Proposed as answer by George123345 Friday, October 30, 2015 5:39 AM
    • Marked as answer by George123345 Tuesday, November 10, 2015 2:06 AM
    Wednesday, October 28, 2015 1:02 PM

All replies

  • Hi Ken,

    Im part of the Excel group. I want to thank you for sharing your experience, and to update that we are aware of this issue and are actively working to fix it. You may find the solution in the next Excel 2016 CU.

    if possible, it would be most helpful to get more details on your environment and exact repro steps

    Thanks

    Aviv
    Monday, October 19, 2015 5:49 PM
  • Hi Aviv,

    Thanks for your reply. I have been using PowerPivot for 2 years in IR for my university. I have developed hundreds of interactive reports that have truly revolutionized the way data driven decisions are made on our campus. It has only been since upgrading to Excel 16 that my problems started - in fact the first time I was stunned when I saved a report, uploaded it to SharePoint, over an existing report, only to discover everything was lost. I thought it was odd as I never had had this happen to me before. I started checking and discovered if I open another Excel file or had recently opened a file while my report was open that caused a problem. Just today someone sent me a spreadsheet - not a PowerPivot I opened it and then remembered I had a PowerPivot file open. On checking, sure enough, the data model was gone. Fortunately all I had to do was close the file without saving and then reopen it and the data model was back. 

    Most of the files I work with are in the 100 MB range - primarily under 2 million rows. I use a Lenovo Yoga 2 Pro (i7, 8GB) with a 1TB SSD. 

    Appreciate your help.

    Cheers,

    Ken

    Tuesday, October 20, 2015 12:36 AM
  • Thanks, Ken. We think we've identified a problem that could've lead to this, and are working on getting the fix out quickly. As you noticed, this tends to happen sometimes when multiple files are opened.

    thanks!

    ash

    Program Manager, Excel


    Program Manager, SQL Server Analysis Services

    • Proposed as answer by George123345 Friday, October 30, 2015 5:39 AM
    • Marked as answer by George123345 Tuesday, November 10, 2015 2:06 AM
    Wednesday, October 28, 2015 1:02 PM
  • I have been experiencing the same thing, do we know when the fix is likely to be rolled out?
    Monday, November 2, 2015 10:30 PM
  • Hi Ash,

    Thinking it might be appropriate to warn users as I have never had Microsoft software that actually destroyed files. It truly is a nightmare if you are a regular user of PowerPivot. You would not know the headache this has caused. It conditions one hold their breath to see if the data model will open with data - not fun.

    Looking forward to the fix!

    Thanks!

    Ken


    Ken

    Thursday, November 5, 2015 4:02 PM
  • Hi Ash -

    Gave up on 2016 - downgraded to 2013. Really surprised Microsoft would release software that corrupts files. Last straw was my discovery that even if you don't save a file the fact that you open it in 2016 you run the risk of the data model being deleted. Ouch!

    Let me know when a fix is available as I do like the new features.

    Cheers,

    Ken


    Ken

    Monday, November 9, 2015 12:55 AM
  • I have experienced the same problem.

    I am using Excel 2016 (Office 365) on Windows 10 HP G20 laptop with 8 GB RAM.

    The data model is flat (one table) that is populated from an MDX query to SQL Analysis services.  I noticed this last week after spending several hours on a spreadsheet.  I went back in to refresh it and the model was lost. 

    I have recreated it and made many backups.  I just experienced it again and I did have multiple spreadsheets open - very frustrating but at least I have the backups.

    Is there an ETA on a fix for this - it is dangerous that it is corrupting the file.  It brings back memory of Excel v1.0 for Macintosh (I am a long time Excel user :)).

    Monday, November 16, 2015 7:39 PM
  • Hi Ken,

    Sorry to say I just had it happen in 2013.  Had a couple large excel files open at the same time and Excel crashed.

    Lost the data model.

    Tammy

    Tuesday, November 17, 2015 5:54 PM
  • Experiencing the same problem and I have only 1 file open. The data model is lost and I am getting an error stating that the Excel file is corrupted.

    Friday, November 20, 2015 6:35 PM
  • Hi Ash

    Any luck with this fix.

    I have been facing this issue, since the day I upgraded from 2013 to 2016.

    Harnidh

    Friday, November 27, 2015 6:25 AM
  • Hi Aviv

    Has this issue been fixed. I am still facing the same problem. Have already lost important data models.

    Thanks

    Harnidh

    Friday, November 27, 2015 6:27 AM
  • Hi Ashvini,

    Has a fix been found for this?

    I have had a very similar problem, so that when I select Manage Data Model I get the messages:

    "We couldn't load the Data Model.  This may be because the Data Model in this workbook is damaged."

    "PowerPivot is unable to load the Data Model."

    I am running Excel 2013 64 bit with 16GB of RAM. I have tried all of the following fixes:

    1. Re-staring my PC.
    2. Removing and reinstating the Powerpivot Add-in.
    3. Running excel as an admin.
    4. Repairing Office 2013.
    5. Installing hotfix 2880450
    6. Installing 4 Excel BI updates from Dec 14 - KB2910929, KB2899498, KB2899505 and KB2920734.
    7. Checking that the “USERS” group is allowed for Increase a process Working Set.

    Also, although I may not want to hear it, can you advise whether there is likely to be any chance of recovering the schema/metadata for this data model , as there are numerous measures and calculated fields, or should I give up hope on that front?


    • Edited by Cokane Thursday, December 3, 2015 3:36 PM
    Thursday, December 3, 2015 3:30 PM
  • This has happened to me repeatedly, causing hours/days of work to be lost.

    Very surprised that 2016 was released with this issue.

    Any ETA of a fix going in?

    Monday, December 21, 2015 11:03 AM
  • I have the same problem and it happened just with 1 file opened. I started to lose models since I started to work with Office 2016.
    Wednesday, December 30, 2015 2:55 PM
  • Thanks, Ken. We think we've identified a problem that could've lead to this, and are working on getting the fix out quickly. As you noticed, this tends to happen sometimes when multiple files are opened.

    It is now 2016 and this is still an issue.  Do you have an ETA when this will be fixed?  Randomly losing data models that take hours/days to build is beginning to make it impossible to get any work done.  

    Would downgrading to Excel 2013 fix this issue? 

    -Anthony

    Tuesday, January 5, 2016 7:28 PM
  • Hi Ash,

    Any update on fix?

    Thanks!

    Ken


    Ken

    Saturday, February 6, 2016 5:47 PM
  • I have the same problem.
    Sunday, February 28, 2016 7:32 PM
  • I have experienced data model loss in Excel 2016 Professional Pro. I had five tables open. I attempted to refresh all and the model ceased to function. FWIW: One symptom that I have not seen mentioned in this forum was a greyed out box that appeared that indicated it was NetFrameWork but nothing else was readable. After that disappeared the model was gone. What versions of NetFrameWork is required for PowerPivot??

    Thanks for any help.

    ===================

    Gary

    Friday, March 11, 2016 7:57 PM
  • Just experienced this error myself. Very frustrating. Using Office 2016, all updates installed. 
    • Proposed as answer by Sander_B Saturday, April 9, 2016 2:18 PM
    • Unproposed as answer by Sander_B Saturday, April 9, 2016 2:18 PM
    Wednesday, April 6, 2016 10:14 AM
  • Hi,

    Last week I had several times that a excel file with a powerpivot table inside it was working fine, until I tried to reopen it at an other time......and the famous : "powerpivot is unable to load the data model" popped up. very frustrating....

    I am working with Excel 2016. all patched, admin rights, etc.

    Another thing that was strange that although I saved my file repeatedly via O365, I have to go back many versions before I found a earlier version that was still OK (while I was saving my file I tested every time that the pivot was able to run. Despite this, the corruption must be popping up when you load the datamodel when opening the excel file from scratch.)  However the Table Data and Pivot layout and connected slicers I had built of the earlier version were all outdated... so I did not look forward in building this up again.

    Looking at the comments on the internet, there was some direction in opening the excel file with 7 zip and remove the Data Model (it is located in XL\Model\item.data)

    You can delete the file and reopen the Excel file after it. The Excel file automatically repairs, but you have lost your table relationships and I did not bother in rebuilding this again.

    Then I thought, lets copy via 7 zip the item.data file from a previous version that is not corrupted...... And that worked! I can now open (finally) the latest version of the powerpivot including all its functionality and data.

    Now the disclaimer is that I have not thorougly tested this (just found it out an hour ago), so I dont know if there are any side effects, but I wanted to share this anyway as people with more inside knowlegde of the datamodel can confirm this or use it to come up with an even better solution.

    Some other have claimed that opening 2 excel files at the same time caused the powerpivot Data Model to become corrupt. This might be true....altough I have not been able to test it yet.

    Have a nice weekend.

    Sander

    • Proposed as answer by BlackRabbit 7 Friday, October 7, 2016 12:11 PM
    • Unproposed as answer by BlackRabbit 7 Friday, October 7, 2016 12:11 PM
    Saturday, April 9, 2016 3:06 PM
  • Hi Ash,

    I have this problem but it is not because of multiple excel opening at the same time (I didn't try this). I realized that this problem only happen when I click close then save. When I re-open the data model will be blank. My file size is around 200MB.

    I believe there is a bug on the "click close [x] and save" process. This will never happen if I save (with or without PowerPivot windows appearing) then close.

    I will update here if I run into the same problem again.

    Best regards,

    Eric

    • Proposed as answer by Simon_G_IRI Tuesday, November 21, 2017 8:57 AM
    Friday, October 7, 2016 12:24 PM
  • Thanks, Ken. We think we've identified a problem that could've lead to this, and are working on getting the fix out quickly. As you noticed, this tends to happen sometimes when multiple files are opened.

    thanks!

    ash

    Program Manager, Excel


    Program Manager, SQL Server Analysis Services

    it is now December 2016, I have spent the last 3 days constantly rebuilding a pivot model that simply vanishes for no reason. single file small data set with little complexity, however it just vanishes.    

    All I have seen is Microsoft quoting that we are working on a fix, but no fix patches???

    I have read a number of articles around this ongoing issue (over a year now) and I have disables solver and analysis plugins.  but this has not stopped the model just vanishing...

    Where is the fix for this???   


    Wednesday, December 14, 2016 6:24 AM
  • Ash, Does MS has any update about this issue? 

    It is very frustrating for your client having the newest version of this always great application.

    In my case I am Business Coordintor. I have a team of 9 employees. Recently, I got the authorization from Head of Brazil's operation to go ahead and purchase Microsoft Professional Plus 2016 (16.0.4266.1001) 64Bits.

    Now I am facing this BIG problem. What can Microsoft do for your clients?

    Friday, February 3, 2017 8:41 PM
  • So it is now June 2017. This problem has cost us so much lost time and data that I am recommending that we completely abandon PowerPivot.

    I have been developing software for over 40 years. In that time I have learned that there is a worst sin of all sins and that is losing data.

    Software that loses data typically becomes shelf-ware. This is where PowerPivot belongs for anyone who values their data and time.



    • Edited by EdCrowson Thursday, June 1, 2017 5:47 PM
    Thursday, June 1, 2017 5:44 PM
  • I experienced the same problem so many times, every time losing so much time in rebuilding the model.  Initially I suspected that connections might have been interrupted while saving, but having made sure that the workbook was saved with all connections open, the moment that I closed, the file size dropped and on re-opening the workbook the model was gone.

    Is Microsoft working on a solution?

    Monday, June 19, 2017 9:59 AM
  • Same here. Also experience this kind of problem. Lost randomly data models. Absolutly FRUSTRATING. Save. Nothings happends, no error. But data model is gone.
    Monday, July 24, 2017 10:39 AM
  • Same here!  I just lost a ton of work due to the vanishing Data Model supporting my pivot tables and charts in my Excel workbook. The vanishing data model feature in Excel has been with us for two years. A fix was promised by Ash the Excel Program Manager at Microsoft. in Oct 2015. I guess Microsoft thinks this issue has been resolved because there is a promise to fix it.  MICROSOFT! I am paying a monthly O365 subscription for the last two years for a product that regularly loses productivity for me. Please remove this undesirable feature from your product! I need a working tool to do my job.

    App Dev Manager

    Thursday, August 3, 2017 11:23 AM
  • This just happened to me - does Microsoft have a fix for this yet?

    - Tim

    Thursday, August 10, 2017 11:11 AM
  • I just lost twos day work.  Can't even get into the model to recover the queries used.  Very ordinary.


    • Edited by Athol66 Tuesday, September 12, 2017 11:03 PM
    Tuesday, September 12, 2017 11:02 PM
  • Hi Eric

    I have also come to the same conclusion. If I save then close, I have no issues. If however I close then click 'save' the data model disappears, and the file size is much smaller. It almost seems as if the program saves the Excel part of the document, then closes Excel before saving the PowerPivot model into the document.

    Kind regards,

    Simon

    Hi Ash,

    I have this problem but it is not because of multiple excel opening at the same time (I didn't try this). I realized that this problem only happen when I click close then save. When I re-open the data model will be blank. My file size is around 200MB.

    I believe there is a bug on the "click close [x] and save" process. This will never happen if I save (with or without PowerPivot windows appearing) then close.

    I will update here if I run into the same problem again.

    Best regards,

    Eric


    Tuesday, November 21, 2017 8:54 AM
  • Thanks, Ken. We think we've identified a problem that could've lead to this, and are working on getting the fix out quickly. As you noticed, this tends to happen sometimes when multiple files are opened.

    thanks!

    ash

    Program Manager, Excel


    Program Manager, SQL Server Analysis Services

    2 years since original post and still an issue. 

    What happened to 'getting a fix out quickly' !!??

    This is extremely frustrating and after multiple failed attempts to restore my data model I have little faith in the data integrity of this tool.

    Thursday, January 11, 2018 10:13 PM
  • I managed to get the company I work for to buy me a Pro License for Excel 2016 as I required Power Pivot, but have had nothing but problems since installing. I'm on Windows 7 64-bit using Excel 2016 with latest updates installed.

    Any attempt to access the Data Model immediately crashes Excel, regardless of whether I have the Power Pivot plug-in enabled. You can click Queries & Connections to open the Query Panel, but refreshing or attempting to access the Data Model via the Manage button will immediately crash Excel.

    This fault leaves Excel completely unusable as all of my workbooks have multiple queries and all use the Data Model. After exhausting every option we could think of (re-installing Excel does not help), we deleted and recreated my Windows profile before re-installing Excel.

    This worked for about a week. Now I'm back to square one with the exact same problem.

    I work in a busy accounts department of a successful company and cannot afford this level of lost work and productivity; when will this be fixed? It's been 2 years since users were promised Microsoft would get a fix out quickly.

    We just paid for a Pro Excel license because we needed the functionality, the product we received is not fit for purpose and it is utterly ridiculous that your customers are forced to put up with this.

    Tuesday, January 23, 2018 5:09 PM
  • I wonder how quick the fix will be, I am experiencing the same, data model was gone!
    Wednesday, January 31, 2018 6:51 PM
  • As somebody mentioned above, if you save the workbook using file-> save and then close the work book pressing do not save on the dialog box that will pop up, the data model is preserved.

    If how ever you press the save button on that dialog box it will remove the data model. I have just tested this after having to rebuild my model twice and it seems to have held true!

    Thursday, February 1, 2018 10:08 AM
  • I can confirm this as well.  Thanks to the solver.  Now, Microsoft, please implement what looks like to be a simple fix.
    Friday, February 23, 2018 5:10 PM
  • Thanks, Ken. We think we've identified a problem that could've lead to this, and are working on getting the fix out quickly. As you noticed, this tends to happen sometimes when multiple files are opened.

    thanks!

    ash

    Program Manager, Excel


    Program Manager, SQL Server Analysis Services

    This issue is still not resolved and it does not only happen when multiple files are open, it happen every time we want to edit the existing connection. e.g add more tables from an existing connection by writing a query to import more data. The data gets imported fine, but the changes made cannot be saved. Please fix it has been 3 years since the original bug report!
    Tuesday, March 20, 2018 11:20 PM
  • I think I just got bit by this bug. Another user modified my workbook and clicked X to close the workbook and saved changes via the existing pop up dialog box. I reopened the workbook later and the Data Model is gone. Nothing exists.  The file size shrunk considerably from what it was. Also, the user who made the edits does not have Power Pivot installed.

    Any update on a resolution?

    Friday, July 20, 2018 8:46 PM
  • Hi Ash,

    I have a similar problem. Not sure if a solution was found.

    I am using Excel 2016 MSO (16.0.4738.1000) 64-bit.

    Regards,

    Mohamed

    Monday, October 1, 2018 1:11 PM
  • I have a similar problem. Did anyone find a solution?
    • Edited by [Mohamed] Monday, October 1, 2018 1:14 PM
    Monday, October 1, 2018 1:13 PM
  • I encountered the same problem with Excel O365 (1803, 9126.2315).  I shared my workbook with 2 co-workers over OneDrive for Business.  

    They both got the Data Model error from a copy of the file.  They did not have other Excel workbooks open.  When I open the copy, I do not get the same error.

    Friday, December 7, 2018 9:32 PM
  • Accountants in our Finance team have now started to experience this problem since we updated them to Excel 2016. It's extremely frustrating when they lose hours of work for no real reason, especially a bug which was first reported over three years ago. Please provide us with an update, Microsoft
    Wednesday, December 19, 2018 3:58 PM
  • WHY, am I having this exact problem in 2019(!)

    Third time my entire data model have been erased in a relativly short amount of time. No point in trying to recover as it seems to be completly gone.
    So many hours of work lost. Why is this so difficult to fix? I do not have the same problem With Power BI, witch also is a Microsoft Product. 

    Monday, January 28, 2019 1:23 PM
  • 3.5 years later, Ashvini is still working for Microsoft, but not on this bug.  Either it's not a meritocracy over there or their focus is power bi
    Monday, January 28, 2019 3:04 PM
  • Hi have you found the answer? i've just encountered the same problem, the whole data model is gone after i close and reopen it, many hours of work down the drain... have been searching around solution from internet, trying my luck to get back the data model
    Thursday, October 17, 2019 4:02 PM
  • no news from Microsoft about this issue then? I keep losing losing connection in PowerPivot which means I cant update my tables... making the whole build a complete waste of time. #binexcel 
    Monday, November 25, 2019 11:21 AM
  •  NOV 2019 here and this is still happening.  Is there a FIX yet??
    Wednesday, November 27, 2019 5:33 PM
  • Hi there,

    February 2020 and still troubles on that topic.

    Actually, I was working on Office 365 up to last week. Office 365 is provideing an ALMOST equal version of Excel 2016 on prem.

    But way more stable. I never had this kind of trouble with Cloud version of EXCEL. Unfortunately, we had to move back recently to the offline version of MS Office... Meaning for me discovering the joy of 2016 on prem version.

    And, what I can say is that it is a nightmare. Most of powerpivot connected to access database are facing this issue.

    Here is what happen: I open the data model built with Online version of office. I update one connection/table linked to an access database. Fine so far, everything working.

    I save the file. No problem so far. I reopen it, tadaaa, model vanished.

    Any patch to solve this ?? Additionally, plenty of my colleagues are facing stability issues using pivotable or even using old excel files built on former versions of excel... (they just cannot be opened...)

    Tuesday, February 4, 2020 7:39 PM
  • Hello from March 2020! Simple copying and pasting a file (be it binary or xlsm) containing a data model makes it dissapear from the Data->Queries and connections tab and while refreshing, some of the tables in the established connections are also being removed for no apparent reason whatsoever. This bug is so annoying and unacceptable that because of it I am switching to a such a better BI tool that Excel semi-integrated is zero match to! The point is it's unacceptable for a company like you to leave such bugs untreated for so long as if you don't care at all! Especially since I am paying money for it. And yeah my frustration is beyond conceivable.
    Tuesday, March 10, 2020 10:13 AM
  • What version do we need to install for this fix?  Or is it same version update/bugfix?  I'm losing datamodels we've spent days creating, this is not ok.
    Thursday, August 6, 2020 7:06 PM