Data model in excel cannot be loaded

[Eagle]

Guys, hope it is right forum.

I have a problem with Data model in excel 2016 64bits. It happened to me two times in five days. In data model i have connected loaded queries. File is not complicated at all. Simple, few tables and few relationships. 

I am working in data model for two years, and besides occasional glitches I have never had problems like now.

Suddenly, when I am trying to refresh model, it throws error that data model cannot be loaded. Something like this. I am ussualy restarting pc after this, but after that even a blank file gives same message when trying to enter Data model. Basically, my excel can be only recovered with reinstalling office. It is problem for me to reinstall it few times in few days. This is not solution. I want to know reason and solution for this. I googled it, and it is visible that some people have expirienced it, but every solution ( group users, trying to switch off powerpivot restart...) is not helping. 

Guys, please help.

thanks

[v-yuezhe-msft]

@Eagle,

As the issue is related to Excel, I would recommend you post the question in the Excel IT Pro Discussions forum. It is appropriate and more experts will assist you.

Regards,
Lydia

[Elena]

Hi Eagle, 

There is a feature in Excel that allows you to query off of a PowerPivot model, but it's not highly advertised for some reason.

Once you have the data in your PowerPivot model, go to your Excel -> Data tab -> Existing Connections -> Tables tab

From there, choose the table that you want to start with. Once that table's data is on your excel sheet, you can actually right click that table -> go to "Table" -> "Edit DAX"

From there you can enter the following DAX function, as an example

EVALUATE
FILTER(SampleData,[Date]=DATE(2016,1,1) && SampleData[Division]="Alpha")

Make sure to choose Command Type=DAX in the drop-down. Here's how it looks on my screen:

To further improve your querying power, you can install the optional "DAX Studio" plugin for Excel, which allows you to write custom DAX queries and then export the results directly back to an Excel sheet.

Thanks & Regards

Elena Lauren