How can I speed up PQ loading data in Excel

[bingsu]

I have an Excel Workbook where I am performing data transformations in Power Query, but loading the data back to the workbook takes an incredibly long time. When I am in power query editor, the data loads relatively fast for each of my queries, but once I try refreshing my data into the Excel Workbook it takes a very long time. I've tried turning off "load data preview in background" Table.buffers, etc to try and speed it up but have not had significant improvements in speed. 

Any additional ideas would be greatly appreciated. 

[PwerQueryKees]

Great! You found a solution!

Would you mind sharing it for all of us to learn from?

Thanks!

[collinsg · Accepted solution]

Good day @bingsu ,

Imke Feldmann has an article on Power Query performance improvement techniques here - it includes the things you have tried plus others. Quite a straightfoward way of improving performance is described in this video by Paul Turley - reduce the number of steps by gathering like operations into a single step (e.g. avoid multiple re-typing steps and re-type at the end if possible).

Hope these give you some ideas.

[PwerQueryKees]

It really depend on your queries and on the number of rows returned. And, keep in mind that the editor only loads 1000 records, if the real query returns 100000 it will take longer.