Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Steve_D
New Member

Power Query Causing File To Freeze At Regular Intervals

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

8 REPLIES 8
v-jingzhang
Community Support
Community Support

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

vjingzhang_0-1686635538898.png

 

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

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.

Hi @Steve_D 

 

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

vjingzhang_0-1687425757886.png

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

Steve_D_0-1687506243584.png

It has me really puzzled!

Steve_D
New Member

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

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.

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
Super User
Super User

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.