Forum Discussion
Problem : Power QUery will notload to Data Model
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 ?
Thanks Ed Hansberry and Wyn Hopkins
It seems a good suggestion to try making a new workbook, but I'm struggling to try it out because PQ essentially has become unworkable in the original book.
One thing I did notice is under Options & Settings/Query Options/Data Load : Data Cache Management options. The cache 'tops out' at 217 MB, I've tried clearing that several times and it always goes back to 21.7. The maximum allowed is at the default 4096.
IN my earlier question about caching the CSV files, I was not expecting a cache to be persistent session to session (would be nice though !), but at leat persistent within one session would be good. As far as I can see, there is no caching at all happening and therefore any change on the query is causing the entire set of files to be loaded again. Is this top out at 21/7 a usual thing ? Anything I can do to fix that?
@Why Hopkns : you sayO365 version may be more stable - that may be so (I have it on my home PC), but my company is not ready for O365; I had to make a business case for getting Office 2016 Pro+ on my laptop. It is a useful upgrade and the G&T capabilities are truly impressive. But if I cannot get some good info on what exactly are the capabilities and limitations of G&T and Data Model, then it becomes a whole lot less useful. To be clear I'm not expecting this to 'just work'; Initially when I tried this import with G&T on a single CSV file, I was impressed and knew I would run into a limit on #csv files given their size. And I was impressed it kept going so well over so many iterations. But now that it's broken, I cannot even seem to get back to a stable working solution
