"An issue with the Data Model is preventing Microsoft Excel from opening this workbook. Try restarting Microsoft Excel." || "Powerpivot is unable to load the Data Model"

Question

Hi,

I'm encountering the following errors preventing me from effectively using/updating my file. Powerpivot works ok in other files, so it seems isolated to this particular file; feels like the Data Model is corrupt and in need of repair.

Errors given

When I try to do anything that uses the Data Model (click on a pivot table using Data Model data, refresh a query, etc..): "An issue with the Data Model is preventing Microsoft Excel from opening this workbook. Try restarting Microsoft Excel."

When I click on PowerPivot|| Manage: "PowerPivot is unable to load the Data model."

File Description

This is a robust model, ~250mb that has taken many many hours to construct and maintain. The Data Model uses powerquery to import data from multiple excel files whose tables are then related to one another off of which calculated columns and measures are built. The 'master' data table consists of ~50 million records which is summarized in a worksheet via pivot table for use throughout the file using a variety of lookups, etc..

What I've tried so far

I've found similar posts in forums such as these but the recommended fixes have not yet worked. I've thus far:

• rebooted
• performed a quick repair to Office
• performed an online repair to Office
• disabled powerpivot, then re-enabled
• used a different machine
• tried to insert a pivot table from data model, but get first error above
• tried to refresh query, get first error above
• I am able to open PowerQuery editor, but then get the first error above
• I need to maintain the Data Model, so I can't delete the /xl folder when opening as a zip file (as recommended hereand other places)
• I'm not using SQL server
• checked to ensure Office was up to date

Product info:

Device name

Full device name

Processor 11th Gen Intel(R) Core(TM) i7-1185G7 @ 3.00GHz 3.00 GHz

Installed RAM 32.0 GB (31.7 GB usable)

Device ID

Product ID

System type 64-bit operating system, x64-based processor

Pen and touch No pen or touch input is available for this display

Edition Windows 10 Enterprise

Version 21H2

Installed on ‎11/‎17/‎2021

OS build 19044.1526

Experience Windows Feature Experience Pack 120.2212.4170.0

Please help,

Avi

Answer 1

Hi AviLev:

You are welcome to post in our forum.

We are also very sorry for the inconvenience this issue has caused you.

Since you have tried many ways and they still don't work, you can try switching one device to see if the problem is caused by the local environment, if in another device the workbook still won't open,  I am afraid that It's hard for us to fix this file for you here. If you want to take the issue a step further, in this scenario:

We kindly recommended you can raise a service request in Microsoft 365 admin center, which the support there has the related permission and resource to help you check from the backend. Also, the support staff there have more resources to help you fix this workbook.

Thanks for your understanding.

I hope this will help you:)

Tin

Answer 2

This issue is persistent with my computer to specific workbooks. Can anyone help??

Answer 3

None of the provided suggestions resolved the issue; fortunately, I was able to rebuild the file by reverting back to my last working copy, refreshing the data from the various sources and copying/pasting the updated tabs of the workbook from the corrupted file to the last working copy.

Never hurts to have more backups...

Answer 4

I have been experiencing the same error on a similar spreadsheet with query pulls from an external database, etc. It is a shared file, and I suspect that users who have migrated to O365 who open the file corrupt it and make it unusable for those still on Excel 2016. Any chance that could be the case in your instance?

Answer 5

Hi, I don't think that's related to what i had been experiencing. I'm the sole user of the model in question and previous versions were ok. I think something corrupted the file but i'm not sure what.

Thanks,