Query Editor - Taking a LONG time to refresh as I work on query steps

[porter444]

I have a data set with about 4M rows, stored in 6 CSV files I am pulling into Power Query based on the folder they all sit in. As I am working through the steps to remove columns, create custom columns, group by, merge, and append this set with others I am experiencing very slow refresh times. After steps like group by, merge, and append, in particular the query editor is going through each of the six files one by one... it takes as much as 30 minutes in each step.

Message at the bottom of the screen says, "800 MB FROM 2015.CSV (CLICK HERE TO CANCEL)" as it runs through the 6 files.

If I don't make any mistakes, it means hours to get things set up...

Any suggestions on how to address the speed?

 

[Dan Bliss]

porter444,

Be sure to change settings on queries so they are connection only: do not load onto a worksheet or into a Data Model, at least not until you have completed edits to each Power Query.

There are several discussions explaining how fine-tuning a PQ can speed things up. Here's one:
http://www.thebiccountant.com/2016/11/08/speed-powerbi-power-query-design-process/

I'm thinking even with 4 million rows, you are trying to avoid investing in a database solution?
Of course, 64bit Excel might run things a bit faster.

Dan