Refreshing PowerQuery in a Shared Excel Workbook stored on SharePoint

Question

I've created a number of Power Query data pulls in an excel file hosted on Teams (SharePoint backend) that our team use constantly every day, it is typical that at least 2-4 people have this open on their laptop all day every day... I want to make sure people are always looking at the most correct data, so I had set the Queries to refresh on file open and every 60 minutes, so that even if I'm not around the data is valid. However, it throws up an error and says refresh is paused due to multiple people being in the file at the same time...

My question is, I can manually hit refresh all and have it refresh all the data fine regardless of how many people are in the worksheet, so is there any workaround for this automated error? N.B. I tried automating a script to refresh it with Power Automate, but excel online doesn't seem to like refreshing the queries to external datasources...

Answer 1

Hi Paul Hetherington (paul.hetherington):

We are very sorry for the problem you encountered. First, the web version of Excel does not support PowerQuery.

Secondly, we also did a test in our end. When multiple people open the shared workbook at the same time, I can still refresh the data. At the same time, I also found a similar post："Refresh Paused"? - Microsoft Community

One of the users said it might have something to do with the settings, you can try turning this setting off to see if the problem still occurs

If the problem persists, there are some questions we need to check so that it will help me understand the scenario.

1. May I know whether this error happens to a specific file or all the worksheets?
2. Could you provide the screenshot of this error message?

Hope this will help you.

Tin

Answer 2

Hi Tin,

Thanks for your response! I have checked my pivot tables and made sure that box is unchecked for all. The error I receive is as below screenshot:

You can see in top right myself and 1 colleague are in the file, and it's causing the refresh to pause. The setting that I have enabled is as per below screenshot:

I can confirm that I can manually refresh the data through clicking "Refresh All" however what I want is to automate the refresh of data without anyone clicking anything.

Answer 3

Hi Paul Hetherington (paul.hetherington):

Thank you for your reply, according to your description, we understand that the query table can be automatically refreshed.

There are currently two ways to achieve your purpose, one is to automate by creating a workflow using PowerAutomate and the other is to automate by writing VBA code：

We also found related documents for your reference, hope to help you：Solved: Flow To Refresh Power Query - Power Platform Community (microsoft.com)&Auto refresh Power Query on parameter change - Excel Off The Grid

Best regards,

Tin

Answer 4

Hi Tin,

Neither of these articles are really appropriate - unless I'm missing something?

The first link: https://powerusers.microsoft.com/t5/Building-Flows/Flow-To-Refresh-Power-Query/td-p/697862 literally says in the 'accepted response' that it can't be done. Are you essentially confirming the same?

I can't use Macros either as utilizing XLSM files causes other parts of the Power Platform to break. Additionally I don't want it to refresh too often as it can take a while to run and I don't want the negative performance impact. The 2nd method it describes in the article is what I already tried and evidenced as failing in my screenshot.

It seems like the answer is it can't be done - which is disappointing but kind of expected.

Answer 5

Hi Paul Hetherington (paul.hetherington):

I'm sorry my suggestion didn't help you.

If you are use the OneDrive for Business, we kindly recommended you can raise a service request in Microsoft 365 admin center, which the support there has the related permission and resource to help you.

Best regards,

Tin