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.
Anonymous That means, you need to connect Excel through a cloud connection like Sharepoint or Onedrive online portal. Then, you can schedule a refresh through the cloud connections.
We wouldn't be able to open the report which has Excel as a data source with a local path. Hope this is clear
Powerbyosh , That is the confusing part for me. All of the Workbooks that are on the Power BI Service that I am talking about all reside on Sharepoint / OneDrive. All of the Datasources for the Power Queries are all Sharepoint Sources (no local sources). We are not using the Excel Workbook on the Power BI Service as a Data Source.
The output of those Power Queries are output to a Pivot Table and also added to the Data Model for End Users to consume the data.
These workbooks are connected (Uploaded) to the Power BI Service so that we can schedule a daily or weekly refresh of the Data model so that the data on the Spreadsheet is up to date when the end user opens it.
I don't know how else to explain our situation other than how I just explained it.
ibarrau suggested we rebuld all of the reports in Power BI Desktop and then publish that to the Workspace. This option is not feesable for all situations we uses these workbooks for.
Anonymous If you have a data source from SharePoint, I don't think it is a problem. As of now, you have scheduled refresh and working fine, you dont need to change. Because the update is for local data sources.
Powerbyosh , Its currently working as it always has, but after the end of the month that will stop. Also you can't load anything new to the Service.
Other than the post from Alan_, I haven't found a solution. The issue is that after the Power BI Dataset it refreshed, Excel will not pull in the changes from the Service.
Alan_'s option might work....I can schedule the refresh on the Service, and then use Power Automate the refresh. The Office Script for "RefreshAll Data Connections" only works with Power BI Data Sets currently so will need to rebuild and apply Office Scripts is probably the answer.Hi Anonymous , I agree with you.
Before making changes, try to analyze the impact of publishing the report again or using Power Apps for scheduled refresh.
Kindly refer this https://learn.microsoft.com/en-us/power-bi/connect-data/service-excel-workbook-files#migrating-your-excel-workbooks