Macro to refresh Power Query when new data is added

calof11 

Power Query converts the source data into an Excel Table if the source data range is not formatted as an Excel Table and this table has a name. Let's assume that name of the source Excel Table is "Table1" and it is on the Sheet1.

Let's also assume that the name of the Query Connection is "Query - Table1" which you want to refresh automatically whenever the data in the Table1 gets changed. Refer to the screenshot below to know how to get the correct name of the Query Connection to update the Code that would be used to refresh the Query automatically.

Now if the source table "Table1" is placed on Sheet1. right click the Sheet1 Tab ans choose View Code and then place the code given below into the opened code window.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tbl As ListObject

'Name of the source Excel Table on the worksheet, Power Query imports the data from
Set tbl = ActiveSheet.ListObjects("Table1")

If Not Intersect(Target, tbl.DataBodyRange) Is Nothing Then
    'Replace the "Query - Table1" with the name of the query connection to refresh
    ThisWorkbook.Connections("Query - Table1").Refresh
End If
End Sub

The above code is for Sheet Change Event and that means whenever you change the data in the Table1, the "Query - Table1" will be refreshed automatically.

Pay attention to the comments added in the code to know how you can tweak this code to make it work for you.