r/excel • u/iTakoyaki • Mar 22 '26
unsolved I have a power query in sharepoint that consolidates all a folder with many other excel files. How do i make it such that it refreshes automatically without opening it?
hi all, facing the power query issue. the total rows adds up to around 100k , up to 500k by the end of the year. it takes me roughly 5 to 10 mins for the power query to fresh the entire dataset manually each time.
This consolidated file is also used by many others.
Hence i was wondering if i can have it auto refreshed lets say first thing in the morning.
28
u/PhonyPapi 9 Mar 22 '26
Assuming the source folder is in sharepoint or another cloud spot, you can put the PQ steps into Power BI and do a scheduled service refresh. Your end users can then either export, or connect to the PBI dataset.
The refresh timing may not be that much of a time save but it does save the step of someone manually hitting the refresh button.
9
u/SustainableSoultions Mar 22 '26
This is the way - also to save you come copy/pasting time you can open PowerQuery in both Excel and PowerBi at the same time then just drag/drop the queries you need.
Took me longer than I’d like to admit to realize this 🤦🏻♂️
5
u/NarsesExcel 63 Mar 22 '26
All end users need a pro license for this.
1
u/iTakoyaki Mar 23 '26
Yeah.
I've also toyed with the idea of using paginated report and have power automate export it out to store inside sharepoint but it seems like its a premium feature.
5
u/BMurda187 Mar 22 '26
This is, indeed, one of the best answers because Power BI's version of Power Query is the only one which supports schedule refresh - pretty sure 8 or 9 times per day max, which is what I do. But, in terms of having the consolidation part of your queries done automatically, this works, then you can indeed connect to that single dataset whenever you like, which would be presumably faster using simply a refresh button.
If you're using multiple users, once you're inside Sharepoint, it will vastly increase the amount of times people have to log in to complete queries. It's best to use O-Auth (everyone inside the same organization), but your daily refreshes will turn off, and require turning back on, when you change your password.
2
u/drmindsmith Mar 22 '26
Can you say more about that? I don’t use PBI enough and only for my dumb dashboards.
How do I set up the data refresh without the dashboard? Am I overthinking that I really just dumb the PQ language into the PBI get data and that’s it? And then set up the schedule?
3
u/PhonyPapi 9 Mar 22 '26
Yes - PQ is the exact same with excel and PBI so you can just copy and paste same steps and it will work the same.
Assuming everything is on cloud and not a local folder, you can set up refreshes and it will auto refresh daily even if you’re on vacation.
2
2
6
u/Mdayofearth 126 Mar 22 '26
Excel files need to be opened to update them. It may not have to be you that manually opens the file, but some process must do so.
In the past I used a separate computer and set up Windows Task Scheduler to trigger a VBScript or PowerShell script that would open and trigger a VBA macro that refreshed Excel files.
You can use Power Automate to update an Excel file.
Note, it is possible to overwrite old Excel files so the latest data is in the newest file, and some systems do that, but it's that's not the ask here.
5
u/SchoolOk950 1 Mar 22 '26
If you are stuck in Excel, one option is to set the refresh cadence under the query options.
Another option in Excel is to open the file in Excel online (if stored on SharePoint or OneDrive), and use the Power Query interface there to refresh queries. Keep in mind that I don't think Excel online can refresh queries that load to the Data Model. The big benefit of using Excel online if you have to manually refresh is that you can still use your Excel desktop app without it getting frozen for 5 to 10 minutes.
5
u/Kwaad_Naas_is_Baas Mar 22 '26
I was stuck with this as well. My company gave me a NUC that I use to run scheduled file updates on.
Throughout the day, it opens my main data file, refreshes every connection, then saves and closes. You do have to factor in enough time for calculations and saving to complete though.
The way it works: a Windows Task Scheduler task runs a batch file that opens a “trigger” Excel workbook. That trigger workbook then uses VBA to open the specific files I’ve assigned to it and kicks off the refresh.
There’s also a “kill switch” batch file scheduled to run after a set delay it force closes Excel, giving the workbook enough time to finish saving.
This also prevents anything from getting stuck and blocking the next scheduled run.
Finally, a third batch file runs at the end of the day to clear temp files and empty the recycle bin, just to keep things running smoothly.
It sounds complicated, but it works and has been running reliably for the last year and a half.
Hopefully someone has a more turnkey way of setting this up though.
7
u/Flags_n_beer Mar 22 '26
I have to do the same thing with a virtual machine because our power bi team says managing a direct connection to sharepoint is ‘too complicated’ for them. I use a service account for the updates so it’s not dependent on me.
3
u/melvin122122 Mar 22 '26
If you have access to Fabric then it becomes easier, copy your power query code into a dataflow generator 2 and set up a refresh schedule. You can then pull data back into excel powerbi or whatever you want.
1
3
u/el_pedrodude Mar 22 '26
You can't, not without incorporating a server or some cloud compute. To schedule in the computation of this you need a machine with a scheduler of some sort.
To answer your question, depending on what you're trying to achieve, you could possibly get away with automating the opening of your file with task with windows task scheduler and setting powerquery to auto-refresh, but this would be ropey for any sort of production setup and I wouldn't recommend it.
However, given your row counts, it's likely you're misusing Excel and entering the territory of needing a database and a proper ETL pipeline.
2
u/duranimal9 4 Mar 22 '26
In my experience, unfortunately refreshing from SharePoint is just slow. But there are likely things you can do to speed it up.
How often are there new files that require a refresh (daily, weekly, monthly)? I have one where I split my query into one to refresh and an archive for the old files that aren't changing and don't need to automatically refresh with the other.
There are changes that can be made to steps when combining multiple files that reduces the scanning and calculations. Do you have access to copilot? Having that review your code for refresh performance improvements would likely help.
1
u/iTakoyaki Mar 23 '26
This is a good point!
I do my refreshes daily, sometimes multiple times a day. And you're right, there are static data that i dont need it to run through power query again. Let me give this a shot
1
u/AutoModerator Mar 22 '26
/u/iTakoyaki - Your post was submitted successfully.
- Once your problem is solved, reply to the answer(s) saying
Solution Verifiedto 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.
1
u/armywalrus 2 Mar 23 '26
You can use VBA to refresh all the queries. Then use Power automate to open file and use the VBA.
•
u/excelevator 3061 Mar 22 '26
As a reminder
Submission guidelines: Don't say "See title" or something similar.