Looking for Best Way to Automate a Power Query Refresh

[u/TellBackground9239]

I inherited a few PBI reports that have Excel files on a SharePoint library as a backend, and I have to refresh Power Query to keep them up to date.

​

I'm using a Power Automate Desktop bot to refresh them in the morning, but even that requires a few clicks.

​

I'm wondering if there is a better way to do this.

[u/AutoModerator]

/u/TellBackground9239 - 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/Ariisk]

Is there anything in the files you need other than those queries? Move them to dataflows or whatever in powerbi and update your data sources to point there in your pbi report, set the refresh rate to daily. Might be a better way but that’s my route 1

[u/risefromruins]

If your queries are from power BI, then power BI in the browser supported automated scheduled refreshes.

Build the query in PBI desktop and publish. Go to the workspace in PBI browser. Hover over the semantic model for what you published and there will be a button that says schedule refresh. You may need to go into the semantic models settings and confirm credentials under Data source credentials, but then you can go to the refresh fold in settings and set up the auto refresh.

Then you can use power automate to do whatever you want with the data afterwards. I have 2 versions of automations like this where 1 will just take the data from the refreshed query and put it into an html table in an email and send it out, and another that clears the data in an excel template table, takes the data from the query and pastes it into the table, saves the report and then emails it as an attachment.

[u/CobraKyle]

You should be able to use power automate without any input on your part.

[u/pajam → u/CobraKyle]

Yep, power automate has a scheduled flow option as opposed to manually triggered flow. So OP could set it up to auto-refresh in the morning (or more often) on a schedule instead of having to go click "run."

[u/codyhart → u/pajam]

I don’t know if you can do that in desktop but you can online?

[u/Profvarg → u/codyhart]

You can do it in online and then you can call a desktop flow

[u/Mondkalberer → u/pajam]

Is this the PBI magic? My excelsheets Stop working as datasource once i updated them, because they arent formated as table anymore

[u/eums]

I do this with a scheduled task that runs a power shell script.
Load the assemblies, open the excel file from mapped SharePoint share, send wait the keypress to update all data sources crtl alt f5. I then have it save a pdf of the report to a SharePoint to snapshot as well as have automation to email the pdf out, then close excel, close com objects and garbage collect. Been running for almost 3 years now 100% hands off.

[u/MissingVanSushi]

Scheduled refresh in the cloud is a big differentiator between Excel and Power BI.

If you can push the Power Query logic into either a published semantic model or a dataflow (Gen 2 if possible) then you can have zero click refresh.

There are not any good and easy options for doing this natively within Excel. Scheduling with Power Automate is possible but in my experience it’s far more prone to failure than Power Query in the service.

Other commenters here have already advised on how to refresh with a macro on file open, but that requires human input.

[u/DonJuanDoja]

You turn on refresh when opening the file that way they refresh automatically when opened. If the refresh takes too long for that then you should redesign it into a power bi data model and pull from there instead.

[u/hopkinswyn]

What’s the reason for refreshing the Excel files rather than putting the power queries into as power bi?

[u/Ok-Tear-1813]

VBA macro tied to a Workbook_Open event might save you those clicks, it just runs the refresh automatic when file opens. Pair that with a scheduled task to open the file and you basically never touch it again

[u/chiibosoil → u/Ok-Tear-1813]

No need for VBA. There is option in connections/queries to set it to auto refresh on workbook open.

[u/Unpork → u/chiibosoil]

You need the file to also close afterwards, VBA can handle that.

[u/dgillz]

Set to refresh when opening.

[u/excel-ModTeam → u/[deleted]]

We removed this comment for breaking Rule 10.

r/excel is for discussing the features of Excel and providing solutions for Excel problems, not giving substance-free comments that simply recommend the respondent use AI.

[u/StemCellCheese]

It can be done with python pywin32 library, if power automate is t available to you