Forum Discussion
Problem : Power QUery will notload to Data Model
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.
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 ?
Definitely a helpful article, thanks for linking
Of the 3 options, #1 (save as .xlsx not .xls) and #3 ('circular references' in powerpivot) don't apply.
#2 (temp file possible corruption) looks interesting. Checking out the location specified indeed I found plenty of the those strangely name folders I deleted all of them, and restarted.
No Dice. Still same weird symptom, the PQ runs fine (it is actually running a lot better than in the previous incarnation) but just will not load into DM.
Frustrating, I think I have to give up for now. The time that Excel G&T has saved me is being used up in this fruitless pursuit of an answer.
Thanks Wyn Hopkins & Ed Hansberry for your efforts.