Update a power query in excel with varying process times

Help Studio
1

Hi there
I’m trying to automate opening an excel file containing a power query to update. Once refreshed, I want to save the excel file and close it.

Studio does a great job at this when it comes to PivotTables. However, the process does not apply to power queries.

I’ve tried using Send HotKey (Ctrl+Alt+F5) → it starts the refresh, but it’s impossible for me to predict the time it will take to refresh all… it can vary from 2 mintes to 35 minutes!!

I’ve tried running a macro using Refresh and Save commands. But the save seems to attempt before the refresh is completed.

I thought this would be simple to accomplish but getting increasingly frustrated with options.

Any ideas on how this can be accomplished?
Thanks
Thierry

2

@CRETE_Thierry
Were you able to resolve this?

3

Not really… I’ve adjusted the delay activity to give a substancial amount of time.
I’ve though about using a “if element exists” type of activity but finding it difficult to see what element I can use as a reference to ensure PowerQuery refresh is complete.

4

Use refresh excel Data Connections activity within Use Excel File. It will automatically pick up the file name to refresh and it will refresh all power query data connections until done. No need to estimate how long it will take. Hopefully it helps.