Forum Discussion
Problem : Power QUery will notload to Data Model
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.
25 Replies
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.
Hi Epee_Sharkey
What version of Excel are you using ?
If you go to Data Model > Manage Data Model does Power Pivot open up
Are you able to load a simple table into the data model?
In terms of caching older CSVs unfortunately this is not currently possible. The entire data set has to be refreshed each time.
Hi Wyn Hopkins thanks for the quick reply.
I'm using 64-bit Excel 2016(standalone version, not the O365 one). I don't have the laptop open right now, but it is up to the latest, or at least a recent patch.
The Load to Data Model issue is that I can load to Data Model, but on refresh of a table (where Load to DM is ticked), then the query doesn't work, just stays in that kind of starting position loop I mentioned. The only way out is to untick load to DM and then re-tick. Then the query will refresh and the data loads to DM. But in the meantime pivot(s) based on the DM not to mention measures specific to the table are gone !
I found this happen even on a 'toy' version where I made some much smaller files (10,000 rows an a lot fewer columns). So maybe this is something more generally at fault with my Excel config -- it is very frustrating since without being able to load to DM, I cannot make proper Measures etc.
Thanks for confirming the situation with caching of the CSVs -- I did eventually find something in the blogosphere explaining that in a bit more detail. Frustrating not to be able to find a simple comprehensive explanation of what are the capability AND limitations of G&T.
Are there any knowledge resources that explain this in more detail ? While my CSVs are large (~100MB each), my actual final table is much more compact (10,000s of rows and 10s of columns). If there was a way of reading them in more effiicently, that could work really well. (or at least establish what is the 'art of the possible' ). Maybe I can find something that works solidly for 13 weeks (1 Q), then ask my IT colleagues to provide some kind of data-warehouse or such to extend the solution.
You will likely find that the O365 version of Power Pivot is more stable as a lot of work has been done over the last few years.
Are you able to get access to the Diagram view in the Power Pivot model?
Maybe Ed Hansberry can shed some light on his use of a Data Lake to store CSV files.
