Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 Exce...
General Comment
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

Alan_'s avatar
Alan_
Advocate II
2 years ago

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. 

Alan_'s avatar
Alan_
Advocate II
2 years ago

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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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_'s avatar
      Alan_
      Advocate II
      2 years ago

      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. 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.