Forum Discussion
Problem : Power QUery will notload to Data Model
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.
Using Data Lake works faster for me for CSV files (at least in my limited experience one larger data set), but I'm not sure if it is any more stable for loading into tables or data models.
I'd be inclined to test this with a new workbook. In the old workbook, open Power Query and select ALL queries and copy. Close Power Query, then open it again in the new workbook and Paste.
Now load your desired query to the data model. That will be a fresh load. You could also test this with Power BI by pasting the queries there in its Power Query module, then load only the one you want.
Unfortunately, even on the Office 365 version of Power Pivot, I've seen issues where the Data Model gets munged.
Unfortunately if you do this and it works, you have to recreate your measures manually.
PS Ed Hansberry -- what is 'Data Lake' and where can I find more info about it ?
