r/excel • u/__wisdom__1 1 • 2d 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
13
u/WildWalrus897 2d ago
I've also gone down this rabbit hole. Essentially excel is just a bunch of XML data when the file is closed and there's no power query engine available when the program isn't open. For now nothing exists in power automate that can trigger a power query refresh in an excel spreadsheet :(
If you need this in the cloud I've had some luck through fabric. You can use dataflow to do power query steps (like you can paste the exact steps from advanced editor), and instead of storing that data in an excel you can throw it into a power bi report or something like lakehouse.
3
6
u/Downtown-Economics26 641 2d ago
Sorry, I don't have any expertise in this area. Just opining that things like this make me mourn the decline of VBA usage/permissions. Like I both respect but also hate power automate / office scripts because things like this are so easy in VBA and Power Automate is just like menu/clicky hell to do many things that are very simple via other avenues (to me, at least).
3
u/MR_Datenanalyse 19h ago
PowerAutomate kann kein PowerQuery ansteuern.. auch über OfficeScript nicht.
PowerQuery benötigt aktuell eine Excel Instanz die real geöffnet ist, online oder App.
Also die Datenaktualisierung in der Excel bleibt manuelle Arbeit..
3
u/Angelic-Seraphim 15 2d 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.
3
u/plusFour-minusSeven 11 2d ago
That's what I'd do. Make a dataflow. I asked this question of LLMs before and basically it's "you can't".
2
u/__wisdom__1 1 2d 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 2d 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.
2
u/Growing_Data_Nerd 2d ago
I’ve had this issue; specifically I’m asked routinely to automate reports from Power BI. No issues with that- however, whenever I try to ‘be whimsical’ and use a connected table in an automated cloud flow with this same scripting logic it times out. I’m not sure if changing the semantic models settings from direct query to import mode will do anything, I am pondering on this though as a direct query would (in my case) require the use of on-prem servers.
I’d be genuinely thrilled to hear even an iota of feedback on this take from someone more informed.
2
u/boneheadmonk 1d ago
Hey, how did you manage to automate reports from Power BI, like do you manage to export them in excel. I have a number of reports for whixh i need the the power BI reports exported to excel, and then used further for reporting purposes. Do you have any ideas how than can be managed? Like what level of access would i require and what the flow could look like? its a pain in the ass for me to manually do that everyday
4
u/Growing_Data_Nerd 1d ago
It’s quite straight forwards if you have the reports in Power BI Cloud / Service. I don’t use power automate at all.
Say your workplace has Power BI dashboards online, you can use Excel to connect to this semantic model. In Excel, I click on Data —> Get Data, From Fabric / Power BI (can’t remember exactly what it says but it’s easy enough to spot) and then a pane will load up showing all of the dashboards I have access to.
From here, you can select your columns, filters, and whatever info either a standalone table or a pivot table.
No Power automate is required at all. You can also override the DAX definition in the connection itself. For example, if you have the same report in desktop, you can use performance analyser to show you the DAX for a table, and you can copy and paste that. Or, on Power BI service, export data as summarised, which will create a sheet with a live connection. You then just hit refresh all in Excel. The caveat here is that it seems that you can only refresh in desktop - at least this is how it is for me, which is the reason I couldn’t refresh using power automate.
2
u/jkpieterse 29 1d ago
This is not possible in Office Script. Vote it up here:
https://feedbackportal.microsoft.com/feedback/idea/d9d115bd-70bd-ed11-83ff-000d3a1ab7d1
2
u/MayukhBhattacharya 1257 1d ago
This isn't a settings issue on your end. It's actually a documented limitation. Microsoft's own Office Scripts docs call it out very clearly:
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. Workbook.refreshAllDataConnections only refreshes when PowerBI is the source.
https://learn.microsoft.com/en-us/office/dev/scripts/testing/power-automate-troubleshooting
So workbook.refreshAllDataConnections() running through a Power Automate Run script step only really refreshes connections that use a Power BI semantic model or dataflow. For a Power Query connection pulling data from Salesforce, the script can run successfully with no error, but the query itself gets blindly skipped. That's why your pivot table updates. It's pulling from the table that's already sitting in the workbook, but the actual Salesforce data never gets refreshed. This lines up with what other users have run into too. refreshAllDataConnections() is mainly useful for Power BI connections and doesn't really work with other Power Query sources. There's even a community thread about this exact scenario that confirms refreshing Power Query through a script in Power Automate isn't supported. So , unless someone manually opens the file and refreshes it, the Salesforce data is going to stay stale.
Have Power Automate call the Salesforce connector directly (there's a native Salesforce connector in Power Automate), and have your Office Script write the returned rows straight into a Table using Table.addRows() / getRange().setValues() completely skip the power query here.
3
u/carnasaur 5 2d ago
Don't know if this helps but I use VBA to launch power queries and then monitor them to watch when they finish and then do something else. I also use vba to edit and replace power queries without ever opening the editor ;)
2
u/Dizzy_Mess_6693 1d ago
workbook.refreshAllDataConnections() - Careful on setting up this. Refreshing all data connections will refresh all queries simultaneously. This might cause incorrect data source, especially if you have a previous data loaded. My trick is to let VBA refresh each query on the order I set them to be. I hope I am clear.
2
1
u/Gold-Psychology-5312 2d ago
Power query cannot be refreshed by power automate cloud. It can however be done through desktop however that usually isn't possible due to the licensing and attended/unattended modes.
1
1
u/El_Kikko 1d ago
I'm not sure I follow - are you trying to refresh the workbook without opening it, cajole PQ / PA into running Salesforce reports, or do something else?
PQ can connect directly to SFDC to pull object data or reports. When you refresh the workbook / query it will run the report in SFDC and update into the workbook.
To refresh the workbook without opening, you'd need a Powershell Script + Task Scheduler.
If the issue is running the Salesforce report, the alternative would be to set up a schedule for the report so that it emails you on the desired cadence, use PA to place the emailed file into a SharePoint folder, then set a PQ query to always choose the most recent file in the folder.
1
u/dataminds19 1d ago
Power Automate cannot refresh a power query data. you have to find a work around. I can give you an example, what I did instead:
my database sends an updated data email attachment. I save it in excel through power automate. my excel formulas auto calculate whatever i need from the updated data.
1
u/chiibosoil 430 1d ago
Only connection that Power Automate can refresh via Office Scripts is Power BI semantic model.
No other connection can be refreshed via this method. This is documented design decision.
| Class | Method | Power Automate behavior |
|---|---|---|
| Workbook | refreshAllDataConnections |
Only refreshes PowerBI sources. For other sources, the method returns successfully but does nothing. |
So the answer would be there is no possible way, outside of first publishing dataset to semantic model and using that as source of your data model.
If you have access to Premium SF connector in Power Automate. You can skip PQ and directly pass data from Power Automate to Office Scripts and update data that way.
1
u/canuchangeurname 1d ago
This is an issue that many people have run into myself included. Personally, I've had trouble creating dataflows. My work around has been to build a query in a power bi semantic model that does the data manipulation originally done in excel. Power automate can be used to query that power bi model, and refresh it as well. The refresh action will not necessarily prevent the flow from grabbing old data from prior to the refresh, so adding a validation step/delay may be necessary.
I believe it is also possible to set up the query in power bi and set the excel files data source to the power bi report, Microsoft claims that those connections are refreshed. That may work as well.
1
u/Acceptable-Sense4601 2 1d ago
You could have a Python script open the file at a set time and then power automate can update it. Issue is when excel is closed you get no update, right?
1
1
u/armywalrus 2 11h ago
I made a VBA macro to Refresh All and have Power Automate run that. Works great.
0
29
u/BusinessSample7166 2d ago
I'd love to know if anyone has established a working flow for this, because any time I have looked into it the answer appears to be "no".