Solved: Re: Excel Datamodel scheduled refresh - Page 2

Anonymous · ‎10-05-2023

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.

Anonymous · ‎11-01-2023

@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.

View solution in original post

Powerbyosh · ‎10-13-2023

@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.

Anonymous · ‎10-13-2023

@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.

Powerbyosh · ‎10-15-2023

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

ibarrau · ‎10-05-2023

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Anonymous · ‎10-05-2023

@ibarrau , The source I was referring to is:

Heads up: Changes to Excel workbook support in Power BI workspaces | Microsoft Power BI Blog | Micro...

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.

ibarrau · ‎10-05-2023

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Anonymous · ‎10-05-2023

@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?

ibarrau · ‎10-05-2023

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,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Anonymous · ‎10-05-2023

@ibarrau , got it. I tried the experiment this morning. I imported all of the queries and data model from a report, then published it to the Power BI Service as a Dataset.

I then opened up the Excel file and pulled in that Dataset, recreated the pivot and saved it.

I then did a manual refresh on the Dataset in Power BI Workspace. From what I read it can take about an hour to sync the changes to sharepoint/onedrive.

Its now been over an hour and I don't see the file on Sharepoint with a new date/time stamp so the refresh of the file hasn't happened.

The Power BI Service shows the refresh was successful. Do I have to manually refresh the workbook? I thought the refresh would push back to the file attached to the Dataset from the Power Bi Service?

ibarrau · ‎10-05-2023

Importing excel data in a Power Bi Dataset means you have asyncronic data refreshes. You can configure schedule refresh to let the tool run up to 8 refreshes a day (with regular user licences) or 48 times with premium.

Make sure you are connecting this way

https://www.youtube.com/watch?v=t4TzHu8THoA

That way you don't need a gateway. Open Power Bi Service. Check the dataset settings and edit credentials in there. You can schedule refresh after that.

If you were already doing all that, make sure you click "refresh visuals" or the whole browser just in case you had some cache in there.

Regards

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Anonymous · ‎10-05-2023

@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).

ibarrau · ‎10-06-2023

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 🙂

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Anonymous · ‎10-10-2023

@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 · ‎10-10-2023

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Anonymous · ‎10-10-2023

@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?

ibarrau · ‎10-10-2023

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,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Excel Datamodel scheduled refresh

Helpful resources

Join us at the Microsoft Fabric Community Conference

We want your feedback!

Microsoft Fabric Community Conference 2025

A Year in Review - December 2024

Join us at the 2025 Microsoft Fabric Community Conference

Excel Datamodel scheduled refresh

Helpful resources

Join us at the Microsoft Fabric Community Conference

We want your feedback!

Microsoft Fabric Community Conference 2025

A Year in Review - December 2024