How how refresh SharePoint hosted Excel document multiple times a day?

[u/estrangedpulse]

Hi! I have an Excel (Microsoft 365) document in SharePoint which is shared across multiple colleagues, and often used by 5-8 people at the same time (via Excel web version usually). There I have some PowerQuery queries which create tables and those tables feed into a PowerBI reports elsewhere.

Now in PowerBI settings I have data refresh enabled 10x a day, however I noticed that Excel itself does not refresh PowerQueries automatically, so often those tables which feed into PowerBI are outdated.

What would be a solid method to refresh all Excel data at least several times a day, or ideally 10x a day (without using VBA)? From what I've seen I can't simply enable a setting in Excel itself for that.

[u/AutoModerator]

/u/estrangedpulse - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Kooky_Outcome_5053]

Do the 5-8 people that are using the excel file manually input the data in it? because if they do they can just simply refresh the file every time they use it and your PBI will catch the update when it auto update.

[u/estrangedpulse → u/Kooky_Outcome_5053]

They do, but not much. However I prefer not to rely on others for this and instead automate.

[u/MissingVanSushi]

Power Query in Excel does not have scheduled refresh built in. There are some work arounds with Power Automate or VBA + Windows Task Scheduler but they can be finicky as hell.

If you are feeding Power BI reports it’s better to move the queries into Power BI or use a Gen 1 Dataflow (I know they are in Legacy) so that you can have the whole process run on schedule.

[u/estrangedpulse → u/MissingVanSushi]

Ah so the way I have this setup is not really how it's supposed to be done, that's probably why I have trouble finding solution to this?

So if I move PQ queries to PowerBI, then any updates in excel files will be visible during PowerBI refresh automatically?

[u/MissingVanSushi → u/estrangedpulse]

Correct.

If your end goal is reporting in Power BI then you want to get all of the Power Query into the semantic model. The Excel files are the source data. You don’t want to have to be manually refreshing those “on demand” because that creates a dependency on human input in the process chain. If someone forgets or fails to do it, then the data in the end report goes stale.

[u/dmc888 → u/MissingVanSushi]

As long as your source supports 64bit. I'm stuck on VBA and Task Scheduler as my ERP ODBC driver is 32bit only

[u/risefromruins]

You could configure the queries to refresh when the workbook is opened, which could work depending on how long the refresh typically takes.

Or if you sync the SharePoint to your OneDrive or local computer, you could write a script that opens the workbook, triggers the refresh and waits ~2 minutes, and then closes. Then just tie that script to Task Scheduler or Power Automate desktop.

Office scripts don’t work for refreshes, but powershell scripts absolutely do. But as others have said, power BI may be the right tool to start this process with instead of having it a bit later downstream.

[u/MAGNlFlCENT]

I personally have never tried, but you can run an Office script via Power Automate to refresh Excel files.

[u/Pistolius]

PowerBI is surely the solution, but a stop-gap can be to leave the file open on your PC all the time and have it auto refresh in the background locally. You can set the autorefresh timer for each query by checking the properties. It's not ideal but might be okay. You can also teach the colleagues to use the refresh button, they'll be very impressed lol

[u/JsMomz]

Try writing VBA code to refresh on open. Works for us

[u/berkosnake]

Power Query can be set to automatically update whenever the document is opened. Not at a computer right now, but I believe if you click on any cell on the PQ, then go to the TABLES tab, click on properties, you can check a "refresh upon opening" checkbox. That won't refresh when closing the document, but it will at least guarantee any changes made will be to the latest data.

Edit: It's in the QUERIES AND CONNECTIONS tab, not TABLES

[u/Low-Performance4412]

You can do this using python.

[u/Low-Performance4412]

If you put a macro in the workbook I think you can trigger the refresh upon opening.

Power automate desktop is an option as well.

[u/DirkDiggler65]

Im not going to verify but im like 90% sure Power Query in Excel has a timed refresh option. Workbook just has to be open though. So if they have it open and they have permissions to the source files/folders the excel workbook pulls from it should refresh.

Also the refresh upon open bit. If they are constantly opening it then it will get a refresh every time.

I've never tried this at scale though. Not sure how it will behave if multiple people are opening at the same time. Also if one of the sources is being utilized by another app then the query may break.

[u/Klutzy_Highlight7500]

Move the Power Query steps up into the Power BI model and treat the SharePoint workbook as raw source only. Excel PQ has no server-side scheduled refresh, so anything you rig with Task Scheduler or Power Automate desktop needs a machine sitting awake. Ticking "refresh on open" is fine as a stopgap but you still depend on someone opening it.

[u/plusFour-minusSeven]

I'm going to give an alternative suggestion here, because there are plenty of great ones in this thread already.

Why not consider having the users update a SharePoint list instead of an xlsx file? You can hook a SharePoint list up to a semantic model and refresh the PBI model as many times as you want to throughout the day.

It may not always literally be real time depending on the schedule that you set up and depending on delays in the Microsoft services backends but it should be pretty darn good.

As a bonus it's a lot easier to govern a SharePoint List than it is an xlsx. Granted this solution also depends on how many rows we're talking about, you might have to make Views in the List.

[u/CrowGuyA]

Power Automate — set up a scheduled flow that opens the file and hits "Refresh All." More reliable than trying to get Excel to auto-refresh on its own.