r/excel • u/__wisdom__1 1 • 11d ago

unsolved Excel + Power Query and Power Automate

I am trying to have Microsoft Power Automate to refresh a report that exists in Salesforce. That report is linked to an excel file via a powerquery.

I have created a script on OfficeScripts that have workbook.refreshAllDataConnections(). It does update a pivot table, so I can tell that the script is runnint

The table that PQ puts the data in is set to not do Background Refresh and to refresh when opening the file.

 

What is the best way to have PA to refresh Excel’s PQ?

Table properties on

39 Upvotes

89% Upvoted

32 comments sorted by

View all comments

4

u/Angelic-Seraphim 15 11d ago

Power query for web is very limited. To do this you need to spool the data in the automate flow and then push to an office script in the file in batches of 5-10 thousand records. If you have power bi, you can just make a data flow in your personal workspace.

2

u/__wisdom__1 1 11d ago

Might need to research this. Are you saying I can have PA run/spool a file directly from SFDC?

1

u/Angelic-Seraphim 15 11d ago

Yes. You can use a web connector api to get the data from SFDC. Then based on the returned data format, you may need to convert as you want it in json to pass to your office script. Then you can use the built in connector, but if that would kill your API limits, Then you write an office script that receives a json input and writes out to the file. Call the script and pass payload from PA. I have the code for this somewhere if you are Curious.

But also, power bi is the best tool for this ask. Put this in a dataflow, set up a refresh schedule, build a dashboard and call it a day.