Why power query consumes all my resources?

[Jsanta07]

Hello all!!

I'm having a problem using power query: i have 12 .CSV files in a folder, i import them using the folder load capabilities, but, when i try to do more operations in the query (merging, grouping), the entire system lags, even the windows clock gets freezed.

The final appended query is about 6 million rows, i use excel 2016 64 bits version, my machine is a Dell Lattitude 32 Gb RAM Core i7 7th gen.

When i pull data from a SQL source i could pull over 30 million rows, no lagging whatsoever.

Am i doing something wrong?

Thank you

[#2]

did you close and load as a connection?

[#3 · Accepted solution]

@Jsanta07 

If you use modular approach that could help. Use separate query/queries to load data, another query to merge (one for each merging), another to combine, etc. Grouping could be critical for the performance, check if it could be optimized. Remove unnecessary columns on early stages.

SQL connector in general faster and perhaps query folding works in this case, that means transformations are done on the server.