Can I refresh Power Query with Office script?

[MohsinBipu]

I have some power queries in excel.  This queries is linked with other excle files.

I need to refresh this excel file with the office script to update the queries and reuse the query in power automate web (not desktop). I am using the below code. But this is not working. Please find.

[takolota]

You can also try just using regular Excel tables & directly refresh them using Office Scripts instead of Power Query:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-...

[Syndicate_Admin]

I have the same problem. The script will say "the script is running ..." with no errors. But afterwards, the data query will remain the same and not refreshed. If I use the online menu -> data -> refresh all, the query will refresh just the same way as desktop app.

So the first reply below lnks to "Power Query refresh is generally available in Excel for the web", whilst it is true, it does not help in this situation if I need to schedule the Office Script containing this refresh command to fully automate the refresh. Anyone from Microsoft care to help with this?

[MohsinBipu]

Thanks for your reply @v-jingzhang, 

Here you have mentioned that "This seems not possible with Power Automate Cloud currently. "

But there is a link "Power Query Refresh is now generally available in Excel for the web (microsoft.com)" As per the understanding, with the web excel menu, I can refresh the power query connection. But there is no office script to refresh the connection.

I am working with the excel batch process by Takolota. Let's see whether I can refresh power queries with this method or not.

[Kanwals]

Hi Mohsin,

Did you find the solution to this? 

[v-jingzhang]

Hi @MohsinBipu 

You may have a look at this thread powerquery - Refresh All Queries and Connections of an Excel Workbook (Excel Online) using Office Sc.... It seems that the behavior doesn't change yet. It doesn't support to refresh queries whose data sources are outside of the Excel file. 

You may consider the solution in message 5 from thread: Auto Refresh an Excel File that has Power Query So... - Power Platform Community (microsoft.com). You can select the Refresh data when opening the file check box from the Connection Properties dialog box >> Usage tab >> Refresh control. Create a flow to open the Excel file and refresh all queries, then close the file. However this requires Power Automate Desktop if all of your Excel files are in the on-premises drive.

This seems not possible with Power Automate Cloud currently. 

Power Query Refresh is now generally available in Excel for the web (microsoft.com)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.