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.
Hi. I'm not sure If I understand it because you haven't share a source or text about these "changes". I really don't think creating data model with local excels, sharepoint or onedrive will stop working at Power Bi Service. I think getting data with power bi desktop to connect an excel might be one of the most used sources at the tool.
Scheduling power bi dataset refreshes of excel files won't stop working.
I hope that helps, because you are asking for confirmation of a feature that works right now and I couldn't find something online saying it will stop working
ibarrau , The source I was referring to is:
Heads up: Changes to Excel workbook support in Power BI workspaces | Microsoft Power BI Blog | Microsoft Power BI
I know as of right now we can't use the "Upload" feature on the Power BI Service to add in Excel Data Models for refresh anymore. The alternative method of adding an Excel Datamodel for refresh on the Power BI Service is to import the Data Model from the Excel Workbook in Power BI Desktop and then Publish that to the Service. That will turn that datamodel into a "Dataset".
I haven't tested this out, but you can schedule the refresh of the DataSet in Power BI Service, then on the Excel side you have to relink to that Dataset as the source in order for refresh to go through.
That is at least what I gather from it.
Alright. Yes. They are deprecating this feature that would allow uploading and view an excel file inside a Power Bi Workspace.
If you want to work with excel you should get data from file/sharepoint/onedrive with Power Bi Desktop. Build your visuals and publish.
If you use a cloud source like sharepoint or onedrive, you can just edit credentials online at Power Bi Service in order to configure the schedule refresh. If you want to keep the files local, you must install a Data Gateway and add the excel as source at Power Bi Service in order to configure a schedule refresh.
If you have workbooks at a Power Bi Workspace, I would recommend creating a Power Bi Dataset with a table view (if you want to keep that visual).
I hope that make sense
ibarrau , thank you for the suggestions. All of our Excel files are stored on Sharepoint. The method you describe makes sense if you are creating New Excel workbooks for publishing.
What do we do with reports that already exist and built (that were already refreshing before on the service)?
Do we have to rebuild the Workbooks in the new way and publish the DataSet to the Service?
When you said:If you have workbooks at a Power Bi Workspace, I would recommend creating a Power Bi Dataset with a table view (if you want to keep that visual).
I hope that make sense
Would you mind elaborating on that?Considering they are deprecating the way of viewing excel models in a Power Bi Workspace without using PowerBi Desktop, I would totally rebuilt them using desktop and publish. If the deprecation is not now it will be at some point in a future. It's always good to update the technology you are using.
Regarding my comment I meant if your workbooks items are inside workspaces like this:
then rebuild. Create a new power bi desktop and get data from that workbook.
Regards,