Excel Datamodel scheduled refresh

[arkinex]

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_]

I have the same concern as @arkinex. 

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. 

[Alan_]

Update - I have found a way to do this:

1. Convert the Excel data model into a dataset using Power BI desktop and publish it to the service
2. Rebuild the Excel report to use the dataset from Power BI 
3. Using Power Automate:
   1. Step #1 - Create a flow which refreshes the Power BI dataset
   2. Step #2 - Add a delay of x minutes to allow the refresh to complete
   3. 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 

[arkinex · Accepted solution]

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

[arkinex]

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:

1. Convert the Excel data model into a dataset using Power BI desktop and publish it to the service
2. Rebuild the Excel report to use the dataset from Power BI 
3. Schedule the refresh of the Power BI dataset on the Power BI Service
4. Using Power Automate:
   1. 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.
   2. 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

[arkinex]

@Alan_ , that's awesome!

For Step #3, Are you using the "Run Script From Sharepoint Library" or the "Run Script" to get that to work?  Flow something like this?  

What Office Script code are you using?

Is it this?

function main(workbook: ExcelScript.Workbook) {
    // Refresh all data connections
    workbook.refreshAllDataConnections();
}

[Alan_]

I'm using Run Script and calling Refresh File:

With this, I can reference files in SharePoint libraries. I haven't tried Run Script from a SharePoint library but that might work too. 

[arkinex]

@Alan_ , Will this work with Datasources that are from SQL if published to a Power BI Dataset?

[Alan_]

Can't see why not. The datamodel is refreshed on the PowerBI service as normal ableit as a PowerBI dataset rather than an Excel Datamodel. 

The Excel workbook refresh is just a refreshing in whatever the Power BI service has. 

[arkinex]

@Alan_ , that makes sense.  Any reason why we need to schedule the Power BI Dataset refresh in Power Automate rather than scheduling the refresh in the Power BI Service and then perform the Power Automate Refresh script?  If you roughly know how long the refreshes take, then you can schedule the Dataset refreshes way early...and then run the refresh on the Excel files later.

[Alan_]

Yes, you can do that. My preference is to run refreshes from Power Automate but your way will work fine. 

Interestingly I have converted a few Datasets and they seem to refresh much quicker. 

[arkinex]

@Alan_ , thats great!  I'm wondering if its because of the conversion to the newer Data Model style that is more optimized than the one in Excel.

Another thing to note on the rebuilding of the reports...The Power BI Data Set in Excel seems to require everything to be a Pivot table.  Anyway to also output to a standard Table Ouput? I know I could probably just build it "like" a table in the pivot view....but just a question.

[Alan_]

Yeah, I have both options to add a pivot or a table. One thing with a table the query defaults the table column names to dataset+column. I did edit the DAX to remove the dataset name, so that's an option..

[arkinex]

@Alan_ , That looks like its from Power BI Desktop.  I'm talking about from Excel itself.  Mine looks like this when I'm rebuilding a report and choosing the Power BI Dataset:

When you click on it, a Pivot Table is automatically created.

[Alan_]

It's Excel. Steps are:

1. Data tab

2. Get Data dropdown

3. From Power Platform

4. From Power BI

5. Side pane opens where I can search for a dataset

Could be you may need to update Excel?

I'm running 365 version 2309

[arkinex]

@Alan_ , that might be it.  I'm on 2303. I'll update and check again.

[Alan_]

There's an issue with this method - Excel needs to be able to connect to the data if using pivots so it is limited.. One to be aware of. 

[Powerbyosh]

Hi @arkinex,

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.

[arkinex]

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

[Powerbyosh]

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

[arkinex]

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