Auto-refresh Power Query Featured Image

Auto refresh Power Query on parameter change

by

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:

Auto refresh Power Query

Download the example file: Join the free Insiders Program and gain access to the example file used for this post.

File name: 0013 Auto refresh Power Query.zip

Get access

Auto refresh power query with a macro

There are two ways for Excel to trigger events when cell values change:

  1. User Defined Function (UDF)
  2. 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.

  1. 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).
    Developer - Visual Basic
  2. 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.
    Visual Basic Editor
  3. 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
  4. 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 _
  5. 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.

NOTES:

  1. Workbooks that contain macros cannot be saved as .xlsx files. Instead, we need to use the .xlsm format.
  2. 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:

    1. From the ribbon select Data -> Queries & Connections
      Data - Queries and Connections
    2. Right-click on the query and select Properties… from the menu.
      Query Properties
    3. Tick the Refresh Every n Minutes option and enter a time interval.
      Refresh every x minutes
    4. 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.

What next?

If your query is loaded directly into a Pivot Table, then this post by Jon Acampora will be a good next step.

Or, if you want to know more about using the Power Query editor, check out this post.

What next?

Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Find out more
Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Find out more
Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

Find out more

18 thoughts on “Auto refresh Power Query on parameter change”

  2. Hello. Could you help me please?
    I want to know if I set the refresh every n minutes on background, when I close the sheet, does it work or not?

    Reply

  3. Can I use this to refresh a second query on a different sheet? I’ve tried:

    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 – Query1”).Refresh
    ActiveWorkbook.Connections(“Query – Query2”).Refresh

    End If

    End Sub

    This doesn’t refresh Query2. No errors appear.

    Reply

    • Hi Andrew

      Yes, you can refresh a query on another page using this method.

      But it will only work if the query parameter is also pointing to the cell on the other page, or cell linked to the parameter. Otherwise it will keep refreshing the query using the same parameters.

      Reply

  5. Another option – refresh every n minutes
    Hi, hope you are doing good.
    I have created a report using power query and it is connected to a csv file. The report is saved in a SharePoint folder. I want that power query of the report should be be refreshed in every 30 minutes, so per as your tutorial I go the query and click on properties and set –refresh every 30 minutes. But unfortunately it didn’t work. Could you please tell me what is the problem here or what I am missing? I need to sort this urgently . Please reply at earliest.
    Thanks in Advance!

    Reply

  6. Greetings, first of all, thanks for the amazing article. I learned so much from this real-world example.

    I am trying to achieve the VB-script option, but i’m struggling. Maybe you can assist me with the debug. I am trying to adapt the refresh-code.

    My case:
    – I have an Excel which keeps track of hours worked by volunteers. These hours are noted down in a table on the ‘hours worked’ sheet.
    – I have 1 output sheet (like in the example)
    – On the output sheet, I have 13 tables, one for each volunteer. The table uses a query to retrieve all their specific worked hours from the ‘hours worked’ tab.
    -Each table has their own connections. I renamed all the connections ‘Hours_volunteer1’ to ‘Hours_volunteer13’.
    -Each table has 1 input variable, the name of the volunteer.

    I attempted to adjust the VBA script in the following way:
    Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Me.Range(“J2”).Address _
    Then

    ActiveWorkbook.Connections(“Query – Hours_volunteer1”).Refresh

    ElseIf Target.Address = Me.Range(“J14”).Address _
    Then

    ActiveWorkbook.Connections(“Query – Hours_volunteer2”).Refresh

    End If

    End Sub

    Unfortunately I am receiving an ‘Subscript out of range (Error 9)’ error.

    Do you have any suggestions what I could change to get this to work?

    Thanks in advance!,
    Marco

    Reply

    • The ‘Subscript out of range (Error 9)’ is because VBA can’t find something that you’ve told it to find. Based on the code, it looks like it cannot find the queries that you’ve named (i.e. the names are wrong, or maybe have a typo)

      I have written the following macro for you which will list the queries in the activeWorkbook. Then you can compare the names you’ve used to ensure they are identical.

      Sub GetConnectionNames()

Dim obj As Object
Dim str As String

For Each obj In ActiveWorkbook.Connections

    If str = "" Then
        str = obj.Name
    Else
        str = str & vbNewLine & obj.Name
    End If

Next obj

MsgBox str
Debug.Print str

End Sub

      Hope that helps.

      Reply

  7. I’m wondering what I’m doing incorrectly. I followed the instructions. When I change the parameter, something does happen, I see a flash of “Calculating ….” in the status bar but nothing happens to the data. I downloaded the example and got the same thing – some calculation is going on but nothing happens to the data, unlike in your clip. Am I missing something or my settings are different?

    Reply

    • Hi Kavin – I’ve rechecked the file and it’s still working for me.

      I’m not sure what might cause that, but the things I would check are:
      (a) Are you in manual calculation mode
      (b) Have you enabled the content (clicking the yellow button at the top).
      (c) Are macros enabled

      Reply

  8. When I do a refresh…refreshed cell is automatically highlighted after each refresh and how to disable automatic highlighting of refreshed cell

    Reply

  9. I downloaded the file you provided but upon selecting a different name under sold by, the information does not change. That being said, I’m not sure the download works?

    Reply

Leave a Comment