Problem : Power QUery will notload to Data Model

[Epee_Sharkey]

Background: A PowerQuery had been successfully combining data from a dozen large (101 cols x 100,000 row csv files. The table was extensively cut with ~80% rows filtered out , unwanted cvolumns removed, and some new data columns added. Output was a table loaded to workbook only. This ran successfully over several months with a new CSV data file being added to the list every week. The PQ got somewhat slower, as the data got more extensive, but still ran fine.

Problem : on trying to develop this, I wished to be able to load the table to the Workbook Data Model, in order to add some measures, based on ISO Week calendar (e.g. show variationof latest week against average of preceding weeks). Result : if the Query settings are changed to 'Load to Data Model' (irrespective of load to Table, or Connection-only) then the query goes into a spinning-wheel-of-doom. The query shows 'Retrieving Data...' but the row-count never updates nor does it show any source files being loaded.

The only way to stop it is to hit ESC, in fact sometimes that doesn't work either and I have to start Task Manager and aim to kill the Excel process; it isn't needed to actually kill it, just to issue the Stop Process command seems to eventually interrupt the loop.

Effect : this means I am restricted to loading the combined data into a Table and cannot add context-specific measures.

More generally : I am finding Get & Transform powerful for simple use-cases, but in this example, no alone have I found this 'does not load to Data Model' issue but I'm also suffering from a lack of knowleddge and resources on how to optimise the data-load. I am sure there is some ability to cache the data from the older CSVs (only 1 new one added each week), but it is impossible to fnd any decent resources.

[#2]

Hi
Did you solve your problem?
I use Office Professional 2016 here and a few days ago I had problems very similar to those that were reported here.
I am very concerned about this, as I often use Excel SSBI add-ons, and this problem will keep me from moving forward with my work.

To detail what is happening, a simple import of csv files by power query does not work correctly.
If I request loading to the table it loads a few rows and runs without finishing, so if I right click and I request to update it loads the entire table in the spreadsheet.
If I ask to load into the data model it runs forever, and there's nothing I can do to change it.
But if I load to the table after forcing the update as I mentioned above, and go to the Power Pivot tab and with some cell of the table selected, I ask to load to the data model through the Power Pivot tab button loading happens.
I already reinstalled Office, disabled the add-ins, and enabled it again, but it didn't do anything to make it work as before.

[#3]

Hi @RobsonRogerio There is already two threads on this issue in the following links that you can try looking into:

Technet

answers.microsoft

It seems like the problem happening is:

Trying to load a query from Power Query to a table stumbles but finally loads. Trying to load to the data model, it just throws an error. It happens to ANY query even with the simplest of datasets.

It only happens in Excel 2016 with software updates after september/october 2019. I haven't been able to pinpoint the exact date yet.

I'm using 365 so the issue is not mine, but I teach Excel every week and there is always someone with this problem and it makes almost impossible for him to use Power Pivot because no data would load into it. In the Tehcnet forum someone came up with a workaround, but it is a temporary solution.

Regards.

Duyarra

[#4]

@duyarra 

I am having this issue as well. I have tried to load the query to data model in my work's excel 2010 professional stand alone and my excel (home and student 2016 stand alone) at home. Both are unsuccessful. It has nothing to do with the size of data as you mentioned even for the simplest datasets. There must be some bugs and glitch with Microsoft updates. It's been like this for maybe a couple of months. It seems to me that Microsoft is still not finding a solution or at least to fix it. I am not sure if Microsoft is aware of this. It's been a headache when I really need Data Model to work. 

[#5]

@Wyn Hopkins 

Hello Wyn,

I'm using Office 365 - Excel version 1912 and I'm experiencing the same issue.

The original Query is set to create just a Connection (no actual table loaded into excel tabs).

When the data source is changed, the updated data is getting OK to the POWER QUERY preview just with a single Refresh, but it fails to update the Data Model when Refresing Power Pivot.

[#6]

Hello @Wyn Hopkins,

I did some additional reading and it seems to be related with text case autocorrect options.

The actual amendments at the source data had several text case changes required. These changes were uploaded correctly into the Query table, but when moving into the Power Pivot data model it seem to "autocorrected" the text case.

I have stopped all the auto correct options at the PROOFING section at the EXCEL Options, but it does not seem to solve my problem.

Any advice?

[#7]

Hi @pdelreal365 

I don't have an answer sorry.  All I can suggest is that you "Send a Frown" to report this to the Excel team