In a previous post we explored the relationship between Power Automate and Power Query, explaining that Power Automate and Power Query serve different purposes in workflow automation and data management:
To recap, Power Automate is designed to trigger actions automatically based on specified events, such as new file creation. It’s ideal for real-time responses and can handle complex workflows with extensive integration across services. However, it requires a deeper understanding and more initial setup.
On the other hand, Power Query is focused on extracting and transforming data at the time of query refreshes, making it well-suited for static reports and periodic analysis. It is simpler to use and set up, primarily integrating with data sources that are directly accessible to Excel.
With this distinction between Power Query and Power Automate in mind, let’s take a look at updating Power Query refreshes on a schedule with the help of Power Automate and Office Scripts. You can get started by downloading the exercise file below:
In this example, we have a relatively simple transaction dataset, and we want to set up a schedule for the query to refresh automatically every Monday morning.
To keep things straightforward, I’ve already created a basic query in Power Query that reads from the daily_summary raw table and loads it back into another table. Of course, you’re welcome to add any transformations or load options you’d like — the focus here is not on Power Query itself but on automating it with Power Automate. The process starts with a relatively simple Office Script, like the one shown below:
Go ahead and paste this code into a new Office Script by navigating to Automate > New Script. Save the script with a name like “Refresh All Connections.” As the script suggests, it will refresh all data connections in your workbook, including the one you created with Power Query.
Go ahead and test this on an ad-hoc basis by adding a few rows to the source data on the ‘Data’ worksheet. Run the script, and you’ll see those rows get added through the query update to the Power Query output. Nicely done!
This on its own might not seem too exciting, as the user could simply refresh the connections using Excel’s built-in interfaces. However, let’s enhance this by setting up the script to run on a schedule using Power Automate. This is one of the few Power Automate tasks that can be configured directly from within the workbook without needing to access the Power Automate editor.
Simply go back to the Automate tab on the ribbon and select Automate Work. Ensure that your workbook is saved to a OneDrive location and that you have a Power Automate license.
In the Automate Work pane, select “Schedule an Office Script to run in Excel” from the sidebar that appears.
Confirm your connections and name the script something like “Refresh all data connections.” Hit Next.
Finally, you’ll set the frequency for the script to run and specify which script to execute. As mentioned earlier, I’ll use the “Refresh All connections” script and schedule it to run every Monday morning.
Go ahead and click Create flow. You will now see this listed under ‘Flows’ in this workbook. Please note that if you run the flow by clicking ‘Run,’ you’ll need to wait until Monday at 8 AM, according to the scheduled flow, to see if it executes.
The best way to test it sooner is to manually trigger the flow in Power Automate. To do this, click Edit in Power Automate, and your flow will appear in a pop-up window.
Here, you can modify the flow’s parameters, such as which script to run and how often, and you can also test the flow by clicking “Test.”
From now on, you have a reliable way to refresh your connections on a schedule. Since this process runs in the cloud, it works 24/7 without depending on your local computer, and the updates will automatically sync to OneDrive.
Do you have any questions about automating data connections for Power Query using Power Automate and Office Scripts? Or about how to synchronize these tools more effectively? Feel free to ask in the comments.
Leave a Reply