Power Query loading time

[yd83000]

Hi,

My apologies in advance if my problem seems obvious!
I'm using Excel's Power Query (and not Power BI's).
I have two questions:

- When you set up a query in Power Query, the preview of the data is updated at each new step. However, even though it's a preview, for some steps it looks like Power Query is applying all the data. As I'm working with very large data, these operations can take a long time, especially for joins/fusion/merge. But as we're still in Power Query, we don't need these steps to be performed on all the data. Is there a way of forcing Power Query not to perform these steps on all the data? The aim is to save time in Power Query, because once the query has been set up, all these steps are performed on all the data when the data is loaded into a pivot table in Excel, for example.

- More generally, I sometimes have problems with data loading times in Excel (in a pivot table for example), which can be particularly long (over an hour). Sometimes Excel freezes and closes itself. Is there a way of optimising this and preventing Excel from freezing? Is it a memory problem?

Thanks for your help!

[collinsg]

Good day @yd83000 ,

Here are a couple of thoughts. An approach some people use is to create a parameter "Debug" which may be set true or false. Then, early in the script use this code (I assume your previous step is called "Source").

= if (Debug = true) then Table.FirstN(Source,30) else Source

 This will load the first 30 rows if Debug is true, otherwise it will load all the data.

Another suggestion is to check the option "Never allow data previews to download in the background" - this option may be set globally or for the current workbook.

I've only partially addressed your questions but hope this helps.