Power Query Causing File To Freeze At Regular Intervals

[Steve_D]

Hoping someone can offer some advice...

I have an excel file that handles a large amount of data via the Power Query Editor and puts it into a series of pivot tables. I use power query because there is too much information for one Excel data sheet to handle, so i have multiple excel sheets that Power Query merges together and carries out a series of lookups to pull it all together.

The file works fine when you first use it (apart from it taking quite a while to load after you select a pivot table filter for the first time - but that's usual with heavy pivot tables). Then after a while it freezes up the whole Excel program every so often as if its processing / loading something (blue spinning cursor icon). It does this same thing every so often at fairly regular intervals, often lasting a couple of minutes. Autosave is turned off so its not that getting in the way. It gets annoying because its useful to have the 'problematic' file open while I'm working on other excel sheets (for reference / data etc). While I'm working on another excel document or sharing screen with colleagues, Excel is working fine and then the regular freeze happens for a couple of minutes

I haven't ever had this problem before with a file. The file contains sensitive information so i cant share the file itself. Has anyone had a similar problem that you have managed to solve?

Many Thanks for the help

[v-jingzhang]

Hi @Steve_D 

You may try the solution from this thread Excel Power Query Stuck - Microsoft Community

Uncheck Enable background refresh

Uncheck Refresh every xx minutes

Uncheck Enable Fast Data Load

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Steve_D]

Thanks v-jingzhang

Two of the boxes were already unchecked. I unchecked enable background refresh and have used the file again. The same thing happens - the pivot tables refresh fine but the file then freezes up every 15 mins for 1/2mins. it still happens every 15 mins.

Interestingly The file also freezes every 15mins after i have changed a dropdown selection in a pivot table. 

If the file is opened and I do not refresh the data and if I also dont change/ manipulate a pivot table / pivot table dropdown the freezing does not occur.

[v-jingzhang]

Hi @Steve_D 

How about checking the Connection Properties in the PivotTable Analyze tab of the Ribbon? 

[Steve_D]

Thanks @v-jingzhang. This shows the same properties -the boxes are unchecked

It has me really puzzled!

[Steve_D]

Thanks Ibendlin for the reply. 

When i run the power query it updates my pivot table ok - no issues. After the pivot tables refresh, the file then freezes every 15mins for 1-2 mins each time - this locks up the whole excel program (spinning blue circle icon). 

Its as if something triggers every 15 mins in the background. It happens regardless of what other excel files are open (it does it if zero other files are open, or if many other files are open). The laptop could also be running no other programs (other than background processes) and it still happens.

Is there something within power query sourced pivot tables that would trigger something every 15 mins?

I have never had this with a pivot table before (no matter how heavy the data is) so I'm thinking its more power query related than the pivot itself

The only program that spikes during the freeze is Excel. Disk and CPU usage rises dramatically when its spinning and then these clear when its finished.

Thanks for the help

[lbendlin]

Use the resource monitor to check which activities write to disk at the same time.  your periodic freezes may also be caused by swapping activities.

[Steve_D]

Thanks Ibendin. 

Apologies i hadnt used the resource monitor before (just the task manager).  I have gone in and had a look.

When i refresh the data i get a higher CPU usage from Microsoft.Mashup.Container.Loader.exe. In Disk i also get much higher System write usage. 

When the freezing happens i dont see Microsoft.Mashup.Container.Loader.exe there. Excel CPU usage spikes massively though and flips between Running and not responding throughout the freeze. Things then settle down again once the spinning blue icon goes away and everything is fine for 15mins until it starts again.

[lbendlin]

Remember that Power Query operates on disk.  Use the resource monitor to check which activities write to disk at the same time.  your periodic freezes may also be caused by swapping activities.