Power Automate refresh data connection et TCD on Excel file

[francl_lunch]

Hi, I have excel files that contain pivot tables. They are connected to a powerbi sementic model.
I use Power Automate online and office scripts to update all of this, as well as refresh calculations.
The problem: I sometimes get either an error return or a successful execution but with data not properly updated. At first I thought it was due to the requested volume when I looked at the observation files: Office scripts work fine locally and online, but they don't work well with Power Automate.
Which microssoft online solutions do you suggest?
How with Microssoft Graph?

[Shruti_D]

Hi @francl_lunch ,

Apart from the troubleshooting options correctly suggested by @andrewsommer 

You might want to troubleshoot the error that you are getting. It might be because the TCD is not used properly?

Here are the 4 fundamental rules for properly using a pivot table

1.Never in a TCD should you have data already aggregated.
2.Data must be granular.
3.No line of your data should be empty
4.All column headers must be populated with a unique name

Reference-  https://www.excel-exercice.com/en/rules-for-constructing-a-pivot-table/

Regarding Power Automate online:

Refresh is not fully supported in Power Automate
Office Scripts can't refresh most data when run in Power Automate.
Most refresh methods, such as PivotTable.refresh, do nothing when called in a flow.
Additionally, Power Automate doesn't trigger a data refresh for formulas that use workbook links.

You can check

1. Azure Logic Apps-Logic Apps might provide better flexibility, especially for scenarios where you need advanced integrations

Azure Logic Apps

2. Graph API with Power Automate- You can automate the refresh of Power BI datasets by calling specific Graph API endpoints, and this could be done through Power Automate as an HTTP request.

Follow these steps to learn how Graph API works with Power Automate-

https://sharepains.com/2023/01/03/microsoft-graph-api-the-power-platform

Hope this helps!

If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[andrewsommer]

Why this might be happening:

1. Timing Issues: Office Scripts execute before the pivot table fully refreshes or the connection to the semantic model is re-established.
2. Excel Online Limitations: Excel Online APIs are sandboxed and asynchronous. When scripts are run through Power Automate, they might hit throttling or not await dependent operations like pivot refreshes.
3. Refresh Latency: The semantic model refresh or data load may not have completed before the script reads the results.
4. Lack of Error Signaling: Office Scripts may return a “success” status even when operations fail silently, especially in web contexts.

The first solution I would try is adding a delay to your script.  Since this is asynchronous, you’ll want to add a timed delay or retry loop before accessing refreshed pivot data. Unfortunately, Office Scripts doesn't support setTimeout natively, so you need to build your own polling with context.sync() and time tracking.

Please mark this post as solution if it helps you. Appreciate Kudos.

[francl_lunch]

i would like another solution without office script if possible.
I use catch error in my power automate flux but take huge and randm time. And i have a delay to finis this execution. I have starter's hour, and delay to publish.

[andrewsommer]

The only other solution I would have is to use Power Automate Desktop but that would require premium licensing I believe.  

[francl_lunch]

Thanks, but i want full online solution. Actualy, we use VBA to do that.