locked
"PowerPivot is unable to load the Data Model" in Excel 2016 and Windows 10 RRS feed

  • Question

  • You cannot vote on your own post                    
    0
                

    I have an Excel workbook with sheets with PivotTables using PowerPivot to fill them. The source are SQL Server views and a stored procedure. Somehow I receive now an error message:

    - An issue with the Data Model is preventing Microsoft Excel from opening this workbook. Try restarting Microsoft Excel.

    followed by

    - PowerPivot is unable to load the Data Model

    I have Windows 10 and Office 2016. The first message I can't find on the internet. On the second one I found several tips (repair Office, reboot, etc), but nothing helps. Also on a colleagues computer this problem occurs (so it is in the workbook itself). An old version from 2 weeks ago, still works.

    Any idea? Any suggestion?

    Thanks in advance,

    Regards Gerben

    Note: this question is also on: https://social.technet.microsoft.com/Forums/office/en-US/fb6bb39b-3c45-4704-86cc-79b183dade73/powerpivot-is-unable-to-load-the-data-model?forum=excel

    Update: also happens when only 1 Excel file is open; analysis and other SQL Server services are stopped, etc.

    Thursday, November 19, 2015 7:02 AM

Answers

  • Not sure if this helps but putting my experience here for anyone else looking at similar issues.

    In my particular situation I was no longer using the Data Model.  I had converted my pivots to reference tables, etc that were loaded to sheets by power query.  In any case, i began getting the same Data Model errors described by the OP.  All I wanted to do was remove the Data Model as these errors were preventing Power Query from refreshing other external connections.  

    Finally I found a way to get rid of the Data Model completely.  I should preface that I am using Excel 2016.  Latest updated installed, etc.  The 'fix' was...

    - close the xlsx in question

    - open the xlsx with favorite zip tool (i used 7zip)

    - delete the "/xl/model" folder

    - open xlsx with Excel again and respond to prompts that Excel needs to make repairs to the file

    - and that was it

    For me I am left with a perfectly working xlsx.  But the Data Model is gone, so for anyone needing/wanting to preserve their Data Model, do not use this approach.

    Cheers all,

    -m@

    Friday, April 1, 2016 1:37 PM
  • The .xlsx file format is actually .zip in disguise. When you rename the Excel file to .zip, you will be able to open the file as a zip archive.
    Monday, August 8, 2016 10:54 AM
    Answerer
  • I literally have no idea why anybody would even think to try this, but it worked for me too.

    And just so my post is some use to somebody, my file was .xlsm which retained macros following this process.

    Tuesday, December 6, 2016 8:43 PM

All replies

  • Hi Gerben,

    Did Excel crash sometime during the last two weeks? It sounds like the data model has become corrupt, which sometimes happens (more often in 32-bit Excel).

    I'm afraid the only solution is to revert back to your old version and rebuild the model from there. General advice when using Power Pivot in Excel: use 64-bit Excel, and save often.

    Thursday, November 19, 2015 8:26 AM
    Answerer
  • Yes it happened the last 2 weeks (on 11 Nov the latest Office updates were installed). It crashed yesterday several times. I have a version where I can access PowerPivot and the datamodel, then I make a change and after the save I can't access it any more.

    I am using a 64 bit version of Excel. I also save a lot and make copies of it :-)

    Thursday, November 19, 2015 9:50 AM
  • Not sure if this helps but putting my experience here for anyone else looking at similar issues.

    In my particular situation I was no longer using the Data Model.  I had converted my pivots to reference tables, etc that were loaded to sheets by power query.  In any case, i began getting the same Data Model errors described by the OP.  All I wanted to do was remove the Data Model as these errors were preventing Power Query from refreshing other external connections.  

    Finally I found a way to get rid of the Data Model completely.  I should preface that I am using Excel 2016.  Latest updated installed, etc.  The 'fix' was...

    - close the xlsx in question

    - open the xlsx with favorite zip tool (i used 7zip)

    - delete the "/xl/model" folder

    - open xlsx with Excel again and respond to prompts that Excel needs to make repairs to the file

    - and that was it

    For me I am left with a perfectly working xlsx.  But the Data Model is gone, so for anyone needing/wanting to preserve their Data Model, do not use this approach.

    Cheers all,

    -m@

    Friday, April 1, 2016 1:37 PM
  • What did you mean "open xlsx with zip tool"?  When I open Excel, I did not even open any actual file, just blank sheet, I kept getting this error message. Could you let me know how to fix it?

    Thanks

    Sunday, August 7, 2016 3:39 PM
  • The .xlsx file format is actually .zip in disguise. When you rename the Excel file to .zip, you will be able to open the file as a zip archive.
    Monday, August 8, 2016 10:54 AM
    Answerer
  • I literally have no idea why anybody would even think to try this, but it worked for me too.

    And just so my post is some use to somebody, my file was .xlsm which retained macros following this process.

    Tuesday, December 6, 2016 8:43 PM