A few days ago, a reader asked a question about using parameters in Power Query; they wanted to auto-refresh Power Query whenever a parameter changes.
“One question, how do you set the query to refresh automatically when a value changes.”
I’ve not tried this before, but I can understand the benefits. It reduces the need to click Refresh and creates behavior that is closer in nature to standard Excel formulas. So, it sounded like a question that deserved its own post.
This is the result we are trying to achieve:
Download the example file
I recommend you download the files which support this post, as you’ll be able to work along with examples. This is the best way to learn. You’ll be able to see the solutions in action, plus the file will be useful for future reference. The support files are available for FREE to newsletter subscribers.
Click below to subscribe and gain access to the subscriber area. You will also receive:
- My favorite tips and tricks direct to your inbox
- Exclusive content (which is only available to subscribers)
- FREE tools and downloads
If you’re already a subscriber, click here to log-in to the subscriber downloads area.
The filename for this post is 0013 Auto refresh Power Query.zip
Auto refresh power query with a macro
There are two ways for Excel to trigger events when cell values change:
- User Defined Function (UDF)
- Worksheet change event
I love using UDFs to control objects in Excel, such as changing tab colors, or setting the min and max of a chart axis. However, I tried a UDF for this auto-refresh scenario, but it just wasn’t stable and caused Excel to crash. If you manage to achieve this, please let me know in the comments so we can all learn from your VBA mastery 🙂
That leaves option 2, the worksheet change event.
- Go into the Visual Basic Editor by clicking Developer -> Visual Basic (if you’ve not got the Developer ribbon visible, you can use the keyboard short cut Alt + F11).
- Open the code module for the worksheet that contains the parameter. In the example file, the Query Output tab contains the cell with the parameter.
- Copy the following code into the sheet module.
Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Me.Range("G6").Address _ Or Target.Address = Me.Range("G3").Address _ Then ActiveWorkbook.Connections("Query - SalesData").Refresh End If End Sub
- Next, adapt the code to your scenario by changing the following:
The cell references (highlighted purple in the code above) are the cells containing your parameters. In the example file, there are two parameters in cells G6 and G3.
The name of the query (highlighted green in the code above) will be the string “Query – “ + name of the query to refresh. In the example file, the query is called SalesData. Therefore the full string is Query – SalesData
If you need more or less parameters, then add or remove instances of the following line of code:
Or Target.Address = Me.Range("G3").Address _
- Close the Visual Basic Editor
That’s it; now the macro is ready for use. Change the parameter and watch the macro automatically update the query.
Workbooks that contain macros cannot be saved as .xlsx files. Instead, we need to use the .xlsm format.
As standard Excel behavior, when a macro executes, the Undo stack is cleared. Therefore you will not be able to use the Undo feature after the macro runs.
Another option – refresh every n minutes
Another option, which doesn’t quite meet the reader’s requirements, but which might meet yours, is to automatically refresh the query at set intervals. Doing this:
- Avoids macros and therefore we can use the standard .xlsx file type
- Still avoids the need to click refresh
- Won’t provide an instant update, so does not emulate formula behavior of automatically recalculating
To implement this:
- From the ribbon select Data -> Queries & Connections
- Right-click on the query and select Properties… from the menu.
- Tick the Refresh Every n Minutes option and enter a time interval.
- Click OK to close the Query Properties dialog box.
The query will now automatically refresh at the set interval. It if meets your requirements, it is a better option than a macro.
If your query is loaded directly into a Pivot Table, then this post by Jon Acampora will be a good next step.
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: