Forum Discussion
Excel Datamodel scheduled refresh
I know that changes are coming that are going to affect Excel Scheduled refreshes.
I have a few questions on this since we use this feature a lot at our corporate location.
If all of the Excel Workbooks are stored on Sharepoint/OneDrive, linked to the Power BI Service so that the Data Models can be refreshed, is this feature going away?
Its not super clear on the announcement. It does say Excel Workbooks that are Uploaded to the Service are affected, but not Data Models that are linked to the Excel Workbooks.
Can someone confirm?
We have a lot of Excel Workbooks that refresh on a schedule for a lot of reporting we do. If this is taken away, it will greatly hinder our ability to free up work time for our employees. This feature is super conveinent as we can pretty much set it up and forget it and know that the workbook is refreshed so we can reference updated data.
If this is removed, will there be another way to refresh Excel Workbook Datamodels (hopefully in a non-premium manner)? Power Automate would be a great way to address this.
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.
36 Replies
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
Hi Anonymous,
From Oct 31, 2023,
- Scheduled refresh and refresh now for existing Excel files that were previously configured for scheduled refresh will no longer be allowed.
- Local workbooks uploaded to Power BI workspaces will no longer open in Power BI.
So, you need to download the pbix file, upload it as new report where Excel sheet data will be configured as Power BI Data set. And, then have schedule refresh.
Powerbyosh , anyway you can elaborate a bit on that? You say download the PBIX file....That is usualy for Dashboards or PowerBI Projects on the workspace. For the Excel refrehses, there is not a Power BI Visual or dashboard its using. Just the workbook.
The Service was refreshing the workbook on a schedule. If we are moving to the newer Power BI Datasets, how can we get the same funcationality where the refreshes are pushed to the Excel Workbook after the Power BI Dataset refreshes on a schedule?
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
I have the same concern as Anonymous.
I have several Excel files in which I have built reports using Power Query and Power Pivot, uploaded to the PowerBI service to be automatically refreshed meaning when I open these Excel reports saved in my OneDrive I see the latest data.
I understand there is a workaround to upload the model to the PowerBI service and set up an automatic refresh. I do not see a way to automatically keep my source Excel report up to date.
I trust this is clear and if there is any way to keep my source Excel report up to date automatically please let me know.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
I just wanted to confirm with you all that the solution posted by Alan_ worked!!!
The only difference is that I have scheduled the Power BI Dataset refresh on the Power BI Service rather than incorporate it as a part of the Power Automate.
Things to note:
A) You have rebuild all of your Excel Files using the new Power BI Dataset by importing all of the queries and data model into Power BI Desktop and publishing it to the Service.
B) When rebuilding or reworking the Excel file, the output seems limited to just outputing to a Pivot Table (which should be fine for more cases). Aparently you have the option to output as a Table on Office 365 Build version 2309. I am running 2308 and do not have the option.
C) The nice thing is that the Refresh of the Excel file seems to be much faster and you no longer need any queries on the Excel file, however I believe any changes you would need to make you need to do in Power BI Desktop by opening up the PBIX file.
D) There is 1 Drawback. You will need to give all users access to the Power BI Dataset on the Service if anyone is opening up the Excel file on Sharepoint. Main reason is because anyone that clicks on a Slicer, it is essentially performing a "mini" refresh to filter the data from the Dataset...and then you get the result. This is because ALL of the data is now residing on the Dataset side on the Service rather than in the Excel file like it used to. I don't like this, but there isn't much we can do about it. At least until Power Automate can use Office Scripts to refresh data sources other than Power BI Datasets
Steps:
- 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
- Schedule the refresh of the Power BI dataset on the Power BI Service
- Using Power Automate:
- Step #2 - Once you figure out how long the refresh takes on the Service, you can schedule the Power Automate to run on a schedule anytime after the time it takes to refresh.
- Step #3 - Create a step to run an office script pointing it at the new Excel report and calling the refresh file script
Looks like there is an article on the Microsoft site that covers some more aspects of this as well:
https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-connect-excel-power-bi-datasets
