Automate PQ Refresh?

[AustinMB]

I have a connection from a Power BI semantic model to my Excel work book via Get Data --> from power platform--> from Power BI.

the semantic model refreshes 6am daily. I'd like to schedule and automate the connection refresh on Sunday to prepare Monday morning (refreshes are very slow company wide on Monday). How can I schedule a once a week refresh (preferably it refresh even if my computer is off, that's why I'm concerned about scheduling via connection properties).

I've tried saving the excel to a share point and using a cloud flow to trigger an office script to "open the workbook" online. (I have connection properties set to refresh on open. But it's not refreshing.

anyone know how I can schedule the connection refresh so it takes place whether I'm online or not? Thanks!

[v-sshirivolu]

Hi @AustinMB ,
Thank you for reaching out to Community Forum.
I understand you would like your Excel workbook, which is connected to a Power BI semantic model, to refresh automatically once a week, even when your computer is off. Currently, Excel Online cannot fully refresh connections to Power BI semantic models, and the refresh on open feature only works in desktop Excel. As a result, your current method using SharePoint and Power Automate with Office Scripts may not consistently trigger the refresh. The most effective solution is to schedule the refresh at the Power BI dataset level within the Power BI Service. You can publish your semantic model to the service and set up a weekly refresh. In Excel, you would then connect to this dataset through Get Data From - Power Platform - From Power BI. This ensures your workbook displays updated data each time it is opened, and the refresh occurs in the cloud, so your computer does not need to be on. Alternatively, if you wish to keep the refresh process within Excel, Power Automate Desktop could be used, but this requires your machine to be running at the scheduled time. Overall, scheduling the refresh in Power BI Service is the most reliable approach for your needs.

Refer to these Microsoft documentation links:
Use OneDrive for work or school links in Power BI Desktop
Schedule a refresh for a dataset in Power BI

Best regards,
Sreeteja.

[v-sshirivolu]

Hi @AustinMB ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[v-sshirivolu]

Hi @AustinMB ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[Aala_Ali · Accepted solution]

Hi @AustinMB 

Try to Schedule a Sunday refresh for your Excel workbook that’s connected to a Power BI semantic model using Power Automate + Office Scripts.

1. Store the workbook on SharePoint/OneDrive.
2. In each PivotTable: PivotTable Options → Data → Save source data with file ON (so Monday opens are instant).
3. Create an Office Script that calls workbook.refreshAllDataConnections(), refreshes all PivotTables, and recalculates (script below).
4. Build a Power Automate cloud flow (Recurrence → Sunday) to Run Script on the file. The file auto-saves server-side after the refresh.
5. Ensure the flow user has Build access on the dataset and edit rights on the file.

function main(workbook: ExcelScript.Workbook) { workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.automatic); workbook.refreshAllDataConnections(); workbook.getWorksheets().forEach(ws => ws.getPivotTables().forEach(p => p.refresh())); workbook.getApplication().calculate(ExcelScript.CalculationType.full); }

If Office Scripts are disabled, run the refresh on a VM with Power Automate Desktop (unattended) to open Excel → Refresh All → Save → Close on Sunday.

If this helps, please mark as Solution and drop a kudos so others can find it faster.

Note: Portions of this answer were edited with QuillBot for clarity.