Forum Discussion
Problem : Power QUery will notload to Data Model
In terms of editing queries there are a couple of settings you can switch off so try these ( see screenshots)
Another question would be how much RAM do you have on your machine. Hopefully at least 16GB.
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?
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
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.
Wyn Hopkins - thanks - that is very helpful.
So far re-creating the queries in a new blank workbook seems to be yielding promising results. I can download all the CSV into a table which is successfully going into DM as well.
I've changed settings to match your suggestions, which hopefully will help a little bit.
My laptop is a basic, i5 8GB RAM. If that was definitely a limiting factor I could possibly justify going to 16GB. But I'm not keen to start down that route without some clear evidence. Are there any tools or indications to benchmark performance ? My impression, based on the fact that a new workbook is performing well (albeit I've not added all my filtering and adjustment steps)
is that the issue was one with the original workbook somehow getting 'clogged up' - it had been going for 3 - 4 months with a steadily increasing folder full of these big CSV files.
- Regarding RAM - the more te better when using 64 BIt Excel. Given your operating system will use 4GB then that doesn’t leave much for large data sets
Essentially with only 8GB of RAM you’re not necessarily benefitting too much from having 64 bit Excel.
If you open up Task Manager and monitor memory performance while your query is running you’ll probably see it maxing out
If you’re performing Merge operations then these are the slowest most memory intensive operations.Wyn Hopkins Yes, merges... I am merging a column of my table (considerably reduced by earlier filtering and col removal) with a table from the excel workbook ( v small 'helper' table).
In fact I am doing this a few times.
Merge may be slow, but before discovering that technique I had been using a list comparison where it was looking for values in each row of a given column in a Excel table viewed as a list.
So I reckon merge probably looks speedy compared with that.
Anyway, my rebuilt query (in brand new workbook) is basically performing 'OK' in terms of time to load.
But it isn't loading to Data Model, only to a table.
Out of curiosity I tried loading that table into Data Model, and that throws this error :
Is that a routine message because it is a violation (why) to load a table which is created by PQ but NOT loaded to DM into the Data Model ?
Or is it a clue why I cannot load this table into the Data Model ?
Hmm,
Ed Hansberry Wyn Hopkins I found that re-building the query from the ground up in a new workbook :
- overall refresh time is a lot faster (partly because I am filtering more data out)
-...but I still cannot load the resulting table to DM without the problem described earlier.
What I noticed is :
- while I was doing ordinary point-and-click steps (finding the folder of files, promoting headers, removing unwanted columns, simple filtering etc) all seemed fine the table would load to DM.
~- when I added some more complex steps where I was filtering the loaded table against some helper tables (which are small, only ~10 rows and 1 col), then the issues seems to arise.
The query still works, but the table will no longer go into the Data Model.
Any clues as to why this might be happening ?
One thought I have is that I could move the steps from Power Query and add them into the Data Model ?
- Can you explain a bit more about filtering using helper tables. Are you doing merges?
