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.
ibarrau , thank you for the help. I think you are assuming we are using the Power BI Service to publish our workbooks to our Workspaces in order for us to refresh the Dashboards out there.
We do that on some of our reports, but the scenario I am talking about it specifically uploading the data model of the Excel Workbook to the workspace so that the Power BI Service can autorefresh/schedule refresh the Datamodel and in essense the Excel Workbook. That is it.
We use this because how limited we are on the datasources and licenses we have available to us. We also have limited IT support for a lot of our Microsoft Platforms.
This automated / scheduled refresh allows us to free up time that would be otherwise spent opening up Excel Workbook Reports individually and having to remember to manually refresh them on a daily/weekly cadense.
If this is going away, what do you recommend we do to perform the same tasks as the Power BI Service was doing for us all this time to automatically refreshing these Excel Workbooks?
Most of the Datasources we are using are from Sharepoint Document libraries and Sharepoint Lists.
We have a very few sources from a SQL database (which uses a gateway).
I'm sorry, I don't understand how is that different 😞 . I mean... the goal at the end of the day is to have the data up to date in a platform you can view, isn't it?. Right now, you are said you are uploading an excel as workbook just to help you keeping it up to date at power bi workspace. If you change that with a power bi dataset, you still have your data refreshing up to date at the same place. You won't need a different license if you limited. It's just a different content in the same place.
I might be missunderstanding something in my english reading, but it looks like no matter where the workbook comes from or is connecting, the way of sharing and reading reports future is power bi datasets. You can keep excels at cloud sure, but I wouldn't recommend connecting excels with sources, excel should be a source itself and if you have another one, just connect that to power bi dataset.
I hope I'm not missing the poin here and make sense for you 🙂
You can get in the Transform data menu where you run the power quer ysteps in excel and right click tables to copy. Then open Power Bi Desktop and Transform data. You can just paste there the queries:
https://www.youtube.com/watch?v=d-Ft_dvw-zI
That way you can easily migrate your transformations of excel to power bi datasets.
Then you can just publish to power bi service and schedule refresh. I'm not sure why you made it that way in the first place, but this should be the way to go with.
I hope that make sense
ibarrau , Thank you for your insight.
I just want to understand the path forward. If the current method of keeping our Excel Spreadsheets up to date using the Power BI Workspace is going away, how can we adapt the new method? Sorry I'm not quite getting this.
For example, lets say I have an Excel Workbook that is connecting to a Sharepoint List for a Source. There is some Power Query in there that has the output loaded to a Data Model and Pivot Table. Before I would just upload the Workbook (Datamodel) to the Power BI Workspace and schedule the refresh. Let's say this is updated each day at 6am to pull in the latest records from Sharepoint. Also the Excel file resides on a Sharepoint Document Library.
How would I do this now with the new changes?ibarrau , These Excel workbooks were made this way because that was how we knew how to do it.
I have done this as a test before, but the issue we have is that anything that is refreshed by the service doesn't get pushed back down to the Excel Workbook.
This method is fine if you are publishing a Dashboard of the data. But arn't doing that. We have a piviot table in excel that needs to be updated on a schedule. That is what we have been using it for.
Does this new method or prefered method not do this?
The thing is, you don't need the excel. The excel is making the flow messy. If you want you can connecto with excel to the power bi dataset and create any pivot table with the tables structures created at Power Bi. You can also use "Analyze in excel" clicking on Power Bi Service to download the excel already connected.
https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-analyze-in-excel
Excel should be just for analysis, it should be a critical step on the flow of the data.
Regards,