Forum Discussion
Excel Datamodel scheduled refresh
Alan_ , so I'm not sure what the issue is, but the Power Automate task is failing on the refresh. It worked when I tested it, but on the schedule for the last 2 weeks, it has been failing:
{ "error": { "code": 504, "source": "flow-apim-msmanaged-na-northcentralus-01.azure-apim.net", "clientRequestId": "****" "message": "BadGateway", "innerError": { "status": 504, "message": "Request to Graph API has timed out.\r\nclientRequestId: ****", "error": { "message": "Request to Graph API has timed out." }, "source": "excelonline-ncus.azconn-ncus-001.p.azurewebsites.net"The Service Refresh is working fine. The Power Automate task runs after about an hour after the service refreshes the Dataset. The flow has 2 retries.
** UPDATE ** Turns out the refresh Office script is timing out due to it taking longer than the 2 minutes limit that Power Automate has for Office scripts. Changing the Timeout duration on the Run Script step does not fix this.
So knowing this, this "solution" is no longer the solution we thought it was. We need another way to do this.
** UPDATE 2 **
Turns out that I got the Power Automate to finally work! It takes about an hour and a few retries, but it finally refreshed successfully. I had to set Fixed Intervals on the Run Script Action Step, but that seemed to work. Will have to monitor the flow's performance next week. I upped the Count to 20. On the sucessful run it retried 5 times.
Update - I have found a way to do this:
- Convert the Excel data model into a dataset using Power BI desktop and publish it to the service
- Rebuild the Excel report to use the dataset from Power BI
- Using Power Automate:
- Step #1 - Create a flow which refreshes the Power BI dataset
- Step #2 - Add a delay of x minutes to allow the refresh to complete
- Step #3 - Create a step to run an office script pointing it at the new Excel report and calling the refresh file script
And there it is - an updated Excel file
Alan_ , so I'm not sure what the issue is, but the Power Automate task is failing on the refresh. It worked when I tested it, but on the schedule for the last 2 weeks, it has been failing:
{
"error": {
"code": 504,
"source": "flow-apim-msmanaged-na-northcentralus-01.azure-apim.net",
"clientRequestId": "****"
"message": "BadGateway",
"innerError": {
"status": 504,
"message": "Request to Graph API has timed out.\r\nclientRequestId: ****",
"error": {
"message": "Request to Graph API has timed out."
},
"source": "excelonline-ncus.azconn-ncus-001.p.azurewebsites.net"
The Service Refresh is working fine. The Power Automate task runs after about an hour after the service refreshes the Dataset. The flow has 2 retries.
** UPDATE ** Turns out the refresh Office script is timing out due to it taking longer than the 2 minutes limit that Power Automate has for Office scripts. Changing the Timeout duration on the Run Script step does not fix this.
So knowing this, this "solution" is no longer the solution we thought it was. We need another way to do this.
** UPDATE 2 **
Turns out that I got the Power Automate to finally work! It takes about an hour and a few retries, but it finally refreshed successfully. I had to set Fixed Intervals on the Run Script Action Step, but that seemed to work. Will have to monitor the flow's performance next week. I upped the Count to 20. On the sucessful run it retried 5 times.