Scheduling Excel Workbook Connection (Dataflow) Refresh

[florianh]

Hello,

I have an Excel workbook connected to a Power BI Dataflow, which is saved in a SharePoint folder. I’m looking for a way to schedule the refresh of the workbook’s connections  automatically every night, without needing my computer to be on.

From my understanding, this isn’t something that can be done using Power Automate (link). Is there any alternative solution or approach to achieve an automatic scheduled refresh of the excel workbook connection?

Many thanks in advance 🙂 

[v-saisrao-msft]

Hi @florianh,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others.

Thank you.

[v-saisrao-msft]

Hi @florianh,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @florianh 

Thanks for reaching out to Microsoft forum community.

Could you please confirm if your query have been resolved? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you.

[florianh]

definitely not solved yet. Would be really helpful if power query would be supported by excel online, then I could solve it with power automate.

[v-saisrao-msft]

Hi @florianh,

Thank you for your follow-up question! Currently, Power Query is supported in Excel Desktop but has limited functionality in Excel Online.

However, there are a few workarounds you can consider: 

• Please perform your data transformations using Power Query in the desktop version of Excel. Additionally, save the file to OneDrive or SharePoint to ensure it syncs with Excel Online. The transformed data will be accessible in Excel Online; however, please note that you will not be able to edit the Power Query steps there. 
• While Power Query isn’t available in Excel Online, you can still use Power Automate to automate data workflows. 
• Import data from various sources like SharePoint, SQL into Excel Online. Conduct basic data manipulations using Excel formulas or scripts and then export or further process the data as needed. 

If this post has helped you, accept it as the correct solution so other members can find it quickly. 

Hope this helps! 

Thank you.

[florianh]

"While Power Query isn’t available in Excel Online, you can still use Power Automate to automate data workflows. "

This is the issue and not the answer!!! here a link from Microsoft saying that it is not possible with Power Automate: 

https://learn.microsoft.com/en-us/office/dev/scripts/testing/power-automate-troubleshooting#refresh-...

[v-saisrao-msft · Accepted solution]

Hi  @florianh,

I would like to apologize for my earlier response and the delay in addressing your concerns. I now understand that my previous reply did not fully address your issues. Thank you for your patience and understanding. 

After thoroughly reviewing your query and the Microsoft documentation provided, it is clear that Power Automate does not fully support refreshing Excel workbook connections when the workbook is stored online. This presents a significant limitation for your needs. My previous recommendation to use Power Automate was not suitable given these constraints. 

1. According to Microsoft, Power Automate is unable to refresh most Excel data connections when the workbook is stored on SharePoint or OneDrive. 
2. Power Query is not supported in Excel Online, which restricts your capability to execute transformations and refresh data within the cloud environment. 
3. The current viable solutions include utilizing Power Automate Desktop (which necessitates a running computer) or transferring your data transformations and refreshes to Power BI and employing the “Analyze in Excel” feature. 

Please provide your valuable suggestions in the Ideas Forum so Microsoft can consider them for future releases. 

Home 

If this post helps you, please mark it as the solution and give a kudos so that other members of the community can easily find it helpful.

Thank you for being a part of the Fabric Community. 

[christinepayton]

Your best best is to use Power Query in Power BI instead of Excel, and then let people use the "Analyze in Excel" feature if they want Excel. You can schedule refresh pretty easily this way. But no, I haven't heard of anyone being able to automate the connection updates in Excel outside of using physical automation (e.g. Power Automate Desktop on a VM that clicks the refresh button and saves, or scheduled scripts on a VM). 

[nilendraFabric]

@florianh 

Use Power Automate with Office Scripts

Power Automate, combined with Office Scripts, can automate the refresh of Excel workbook connections stored in SharePoint or OneDrive.

• Steps:
  1. Write an Office Script in Excel that refreshes all data connections:
     function main(workbook: ExcelScript.Workbook) { workbook.refreshAllDataConnections(); }
  2. Save the script and upload the workbook to OneDrive or SharePoint.
  3. In Power Automate, create a scheduled flow:
     • Trigger: "Recurrence" (e.g., daily at midnight).
     • Action: "Run Script" (select the workbook and the script).
  4. Test the flow to ensure it runs successfully.

Leverage Power BI Service

If your workbook is already connected to a Power BI Dataflow, you can use the Power BI Service to handle scheduled refreshes.

• Steps:
  1. Publish your dataflow and connect it to the Power BI Service.
  2. Set up a scheduled refresh for the dataflow in Power BI:
     • Go to the workspace containing your dataflow.
     • Click on settings for the dataflow and enable "Scheduled Refresh."
     • Specify the frequency and time zone.

[florianh]

Hi, thanks for your quick reply. I do not know why so many people talking about Power Automate, it is not possible to have Excel connections refreshed with it. Here the link by MS explaining that limitation: 

Refresh not fully supported in Power Automate

Office Scripts can't refresh most data when run in Power Automate. Most refresh methods, such as PivotTable.refresh, do nothing when called in a flow. Workbook.refreshAllDataConnections only refreshes when PowerBI is the source. Additionally, Power Automate doesn't trigger a data refresh for formulas that use workbook links.

also tried it on my own, it does not work. 

Leverage Power BI Service

this will only refresh the dataflow but not the workbook! 

[christinepayton]

It's because there's two versions of Power Automate, cloud and desktop. Power Automate desktop will physically control your computer and can do mouse clicks, so it can actually handle it via literally clicking the refresh button. It doesn't meet the requirement of "doesn't require my computer to be on all the time" though.