PowerQuery File Hang

[wineguytwo]

Hello, first of all, thanks so much for a robust solution to exactly what you outlined in your notes.

I'm having issues getting this to refresh a somewhat heavy PowerQuery file. Starting the scheduler successfully launches the refresher, and from there the refresher successfully opens a new instance of Excel and I can see the file begin to load, however it stops responding shortly thereafter. I have no option but to kill the task.

Typically when opening the file, it does take a bit for the data model to load, however based on your instructions, this shouldn't be an issue. Any and all help is highly appreciated. Thank you.

[IvanBond]

Hi there!
First of all, thank you for using my modest solution for Excel automation.

Any special addins you using in your Excel or that file?
What happens when you manually open and update it, does update go smoothly?

Keen to try semi manual way to see if macro can actually update your file?
Something like adding Thisworkbook.RefreshAll into Workbook_Open event.

Another suggestion:
Go to Power Query options and switch off Background Data download.

^ off the top of my head. There are usually so many elements that can affect performance.
BTW, hope you using 64bit office.

Cheers,
Ivan

[wineguytwo]

Thank you for the reply!

Hm, I do have the Jet Reports addin. When I open the file, it does take ~30 seconds for the data model to load fully, however after that, refreshing all goes smoothly.

That's a great idea trying the macro to see if that goes just as well as a manual run - I will report back the results of this test.

As a final try, I will disable Background Data download.

And yes indeed, 64bit all the way!

[roller-coder]

Hi IvanBond,

I love this solution. Thank you so much for sharing it. I encountered the same issue as the OP and my only workaround so far has been to create a macro to do the refresh. I set it to skip the refresh all and just set a "Macro_After" that does the refresh.

I turned off background data in Power Query and that didn't help. I'm using 64bit Excel and It's a 20mb file with 20 or so queries, lots of relationships, lots of measures and and lots of pivot tables. Takes about 12 minutes to refresh usually. Hope this helps diagnosing the problem.

Thanks again! Using a macro is not really an problem for me so I'm really pleased! Your solution helps me claim back some of my working day and use the time more effectively.

Cheers,
Nick