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.

     

ibarrau's avatar
ibarrau
Super User
2 years ago

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    ibarrau
    Super User
    2 years ago

    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,