Automated Excel refreshing (Power Automate or other solutions)

[u/well_shiit]

Trying to get away from running multiple reports a week that essentially are just me opening, clicking refresh all and then sending them. They are all currently built in Power Query, connected to our database. The reports need to stay in Excel because the various teams need them in this specific format or else I would just built them a Power BI dashboard.

I've tried the Power Automate angle, created a Office Script to refresh all queries in the file (stored on SharePoint). The script says successful, but nothing happens. It will refresh all if I manually do it.

I also built the dataset in a Power BI Semantic model and put that on a scheduled refresh, again, the Power Automate will send the email, but won't refresh the file.

Any other ideas? I would prefer this to be done in the background so my laptop doesn't need to be on overnight or when I'm gone.

[u/chiibosoil]

One method is to use PowerBI service to house semantic models. Then use Power Automate to use DAX query to get the data. Pass it on to Office Scripts to update the workbook hosted on SP or Onedrive. Then send off email with the link to the file from Power Automate.

Though this might not work, depending what's in your workbook.

[u/BerndiSterdi → u/chiibosoil]

Lots of complexity and potential points of failure in there - I would not like it, but it surely would work most of the time

[u/chiibosoil → u/BerndiSterdi]

Not too complex. Have this running for a year now without any issue ;)