Is the experience of using Power Query going to improve?

[gazpage]

In theory I love Power Query. I've run training etc with examples of ugly data that's a few hundred rows and it all works like dream. In the real world, when I have an excel with 500k rows, using it is agony.

I have no problem with lengthy processing time, I can run that over lunch or overnight, but the actual delay to just create the steps makes me want to tear my hair out. My workflow now is often to go into the actual source data and make a much reduced copy (ie cut 500k rows down to 100), create the query, and then change the source back to my actual file. Or equivalently, put only two files into a folder and create a 'from folder' query and then dump all the files into the folder.

Am I doing something wrong here? I can't wait 10 minutes between creating steps. Should I really need to doctor my data to make queries. I ca get access to Alteryx, should I be looking into that?

 

[Kyle123]

I'm in the same boat, I really want to like PQ, I want to empower my users to use PQ as a front end to our data warehouse, but it really isn't good enough.

I like the theory, but the practical application just lets me down every time. For me, it's having no control over query folding, an inner join will work fine, a left join will require a join on the client - this isn't pretty for millions of rows in each set.

I'd like to use it as a replacement to MS Query, unfortunately everytime I need to create a refreshable spreadsheet for someone, I find another issue so I end just writing SQL and dumping it into MS Query, you can't do that with PQ. You can write native queries, but then I still run into odd error messages, that no-one seems to have a clue how to fix, for example: https://www.excelforum.com/excel-general/1211563-power-query-native-query-paramter.html

I really want to like it too and have used it successfully on small datasets for transforming and cleaning up data, I just can't make it work in the real world though

 

[martingaleh]

This is the solution I use. It works wonders. I have a double screen. On the left screen I use Power Query. Now people that don't know think that using power query means you can't use excel for anything else. That's fine. On the other screen I have amazon kindle. Now you can't pick a difficult book, but any simple book that you can read in ten to fifteen minute chunks is perfect. After every command, read the book. Then if someone asks why you aren't working, say you're working on the power query. The productivity improvement of power query is worth having to read the kindle while you're waiting. This isn't poor design or a bug. This is a feature. Compare it to python pandas and jupiter notebooks for your data manipulation. The execution is so fast, you have no excuse not to work. But with Power Query you can get through books in no time.