Power QueryExercises

How to refresh Power Query automatically in Excel

How-to · By Tahir Nas · Examples run in our simulator · Updated

9 min read

To refresh Power Query automatically in Excel, open the query's connection properties and tick Refresh data when opening the file, Refresh every n minutes, or both. Both settings work while the workbook is open in Excel; for a refresh at a fixed time you can add a short VBA macro, and for a refresh with nobody at a computer the queries need to move to a service that can schedule them.

The options at a glance

OptionSet up inNeeds the workbook openFits a Data Model query
Refresh when the file opensConnection properties, Usage tabYes, it runs on openingYes
Refresh every n minutesConnection properties, Usage tabYesYes, but without background refresh
Timed refresh with VBAA macro and Application.OnTimeYes, with Excel runningYes, through RefreshAll
Scheduled refresh in a servicePower BI, after moving the queriesNoNot applicable: the model lives in the service

If you are new to refreshing queries in Excel, the guide to managing Power Query queries in Excel shows where Refresh All, the Queries & Connections pane and the Properties dialog are.

Option 1: refresh when the workbook opens

Microsoft's article on refreshing an external data connection in Excel gives these steps:

  1. Select Data > Queries & Connections and open the Connections tab.
  2. Right-click the query and select Properties.
  3. On the Usage tab, under Refresh control, tick Refresh data when opening the file.

Repeat for each query that should refresh. The same tab also has Remove data from the external data range before saving the workbook, which saves the query definition without the data. That keeps the file small, but anyone who opens it without refreshing sees an empty table, so use it only when refresh on open is also ticked.

Refresh on open can be blocked by Excel's security settings. Microsoft's article explains that external data may be disabled on a computer, and that to refresh when a workbook opens you must enable the data connections from the Trust Center bar or keep the workbook in a trusted location. If the setting seems to do nothing, check for the yellow security bar first.

Option 2: refresh every few minutes

On the same Usage tab, tick Refresh every and enter the number of minutes between refreshes. This suits a workbook left open on a shared screen, or one that reads a source updated during the day.

Keep the interval longer than the refresh itself takes. A query that needs four minutes should not be set to refresh every two.

Background refresh

Enable background refresh, also on the Usage tab, returns control of Excel to you while the query runs. Microsoft notes two limits: background refresh is not available for OLAP queries, or for any connection that retrieves data for the Data Model. A query loaded to the Data Model always refreshes while you wait. The post on choosing whether to load to a table or the Data Model covers that trade-off.

Option 3: refresh on a timer with VBA

When the result must be ready at a fixed time, a macro can do the scheduling. Excel's Application.OnTime method runs a procedure at a given time of day or after a given delay. The workbook must be saved as a macro-enabled .xlsm file, and Excel must stay open. If the macro does more than refresh, Power Query vs VBA macros helps decide what stays in VBA.

Put this in a standard module:

Public Sub ScheduleRefresh()
    Dim nextRun As Date
    nextRun = Date + TimeValue("07:00:00")
    If nextRun <= Now Then nextRun = nextRun + 1
    Application.OnTime nextRun, "RefreshQueries"
End Sub

Public Sub RefreshQueries()
    RefreshQueryTables
    ScheduleRefresh
End Sub

ScheduleRefresh books the next 07:00, today if it is still to come and tomorrow otherwise. RefreshQueries runs the refresh and then books the following day. Call ScheduleRefresh once, for example from Workbook_Open in the ThisWorkbook module, to start the cycle. RefreshQueryTables is defined in the next section; it is used instead of ThisWorkbook.RefreshAll for a reason.

Catch failed refreshes

Workbook.RefreshAll has a catch that matters for unattended runs. Microsoft's reference says that if a Power Query query loading to a worksheet table fails, RefreshAll raises no run-time error: the other connections still refresh and the failed table keeps its old data. A report can then look current when it isn't.

The same page gives the fix: refresh each table's query separately with QueryTable.Refresh and BackgroundQuery set to False, and handle any error it raises.

Public Sub RefreshQueryTables()
    Dim ws As Worksheet, lo As ListObject, qt As QueryTable
    Dim logSheet As Worksheet
    Set logSheet = ThisWorkbook.Worksheets("Refresh log")
    For Each ws In ThisWorkbook.Worksheets
        For Each lo In ws.ListObjects
            Set qt = Nothing
            On Error Resume Next
            Set qt = lo.QueryTable
            Err.Clear
            If Not qt Is Nothing Then
                qt.Refresh BackgroundQuery:=False
                If Err.Number <> 0 Then
                    logSheet.Cells(logSheet.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 3).Value = Array(Now, lo.Name, Err.Description)
                    Err.Clear
                End If
            End If
            On Error GoTo 0
        Next lo
    Next ws
End Sub

The macro writes one row per failure (time, table name, error message) to a sheet called Refresh log and carries on with the next table, so one bad source does not stop the rest. Add a sheet with that exact name before the first run: if it is missing, the macro stops with a run-time error before anything refreshes. Tables that Power Query did not load have no QueryTable, so the loop skips them.

The loop only covers queries loaded to worksheet tables. Queries loaded only to the Data Model, or only to a PivotTable, have no worksheet table, so they are not refreshed by it. If the workbook has those, keep a ThisWorkbook.RefreshAll call for them, and remember that it will not report their failures either.

When nobody opens the workbook

Every option above needs Excel running with the workbook open. If the report must refresh overnight on its own, desktop Excel is the wrong host for the schedule. For acting on refreshed data rather than refreshing it, Power Query vs Power Automate explains where a flow fits. Two common routes are:

  • Keep a computer signed in with Excel open and use the OnTime macro. This works, but it depends on that machine staying on and the macro staying enabled.
  • Move the queries to Power BI, where the service can refresh a published semantic model on a schedule. The lesson on refresh in Excel, Power BI Desktop and the service explains how refresh differs between those hosts, and the guide on moving a query between Excel and Power BI shows how to copy the M across.

In Excel for the web, Microsoft's page on using Power Query in Excel for the web describes refreshing with Data > Refresh All or from the Queries pane. It does not describe a timed or on-open refresh setting there.

Make the query safe to refresh unattended

An automatic refresh runs your query against data you have not seen. Two failures are common enough to guard against before you switch it on.

A column disappears

If a source drops or renames a column, any step that names it fails the whole query (handling changing column headers covers the defences):

let
    Source = #table({"Date", "Region", "Units"}, {{"2026-10-01", "North", 4}, {"2026-10-02", "South", 6}}),
    Kept = Table.SelectColumns(Source, {"Date", "Region", "Units", "Channel"})
in
    Kept

Result: an error

Expression.Error: The column 'Channel' of the table wasn't found.

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The simulator raises Expression.Error: The column 'Channel' of the table wasn't found. When an optional column may be missing, tell the step what to do instead:

let
    Source = #table({"Date", "Region", "Units"}, {{"2026-10-01", "North", 4}, {"2026-10-02", "South", 6}}),
    Kept = Table.SelectColumns(Source, {"Date", "Region", "Units", "Channel"}, MissingField.UseNull)
in
    Kept

Result

DateRegionUnitsChannel
2026-10-01North4null
2026-10-02South6null

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Now both rows come through with Channel as null. Use this only for columns that are genuinely optional; a missing required column should still stop the refresh. The lesson on MissingField options covers when to use each.

A value will not convert

A text value in a number column becomes an error cell after a type change. The query still loads, but the error cell loads into Excel as an empty cell, so a total can quietly come out too low. A small check query lists the bad rows:

let
    Source = #table({"Date", "Region", "Units"}, {{"2026-10-01", "North", 4}, {"2026-10-02", "South", "n/a"}}),
    Typed = Table.TransformColumnTypes(Source, {{"Units", Int64.Type}}),
    Problems = Table.SelectRowsWithErrors(Typed, {"Units"})
in
    Problems

Result

DateRegionUnits
2026-10-02SouthError: DataFormat.Error: We couldn't convert to Whole Number.

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

It returns the one South row, where Units holds the conversion error (the simulator reports DataFormat.Error: We couldn't convert to Whole Number.; Excel's wording can differ slightly). Excel also flags error cells after a load with an errors link next to the query in the Queries & Connections pane, but a separate check query keeps the bad rows in view. Load a query like this to its own sheet and glance at it after each refresh. The refresh-proof checklist lists other checks worth adding.

Show when the data was refreshed

A one-cell query that records the refresh time tells readers whether they are looking at today's numbers. It needs the real clock, so it only runs in Excel:

let
    Refreshed = #table(type table [#"Last refreshed" = datetime], {{DateTime.LocalNow()}})
in
    Refreshed
Shown for Excel and Power BI Desktop only: this code needs a data source or language feature the simulator doesn't run, so no output is shown.

Load it to a table near the report title. Because it refreshes with the other queries, a stale time means the refresh did not run.

Practise refresh-proof queries

The refresh and performance exercises on Power Query Exercises practise the habits that make an automatic refresh trustworthy, and the challenge debug a query that works today and breaks on refresh is a good test of them.

Frequently asked questions

Why doesn't refreshing in the Power Query Editor update my sheet?

Microsoft's refresh article explains that refreshing inside the editor updates the preview and Power Query's local cache, not the table in the worksheet or the Data Model. Close the editor and use Data > Refresh All, or Query > Refresh on the loaded table.

Can I stop one query from refreshing with Refresh All?

Yes. In the same Properties dialog, clear Refresh this connection on Refresh All. The query then refreshes only when you refresh it directly.

How do I stop a refresh that is taking too long?

Press Esc. For a background refresh, Microsoft's article says to double-click the status bar message and select Stop Refresh.

Sources

About the author

Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.

Keep reading

Practise Power Query in the browser with graded exercises checked against fresh data.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.