Power Query Problems

Preserve API Data History in Excel Power Query

Phi Hung VoPhi Hung Vo Nov 18, 2026 868 views

Question details

The user needs to retain each set of API values retrieved by a Power Query connection in a historical Excel table.

How to Preserve API Data History in Excel Power Query
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
An API returns only its latest data, and the user wants to record successive snapshots every few minutes.
Observed behavior
Refreshing the query replaces the previous results, so earlier values are not retained.
Before you start

Use desktop Excel with VBA macros enabled, and confirm that the Power Query result loads to a worksheet table. Decide which columns to retain and whether the API supplies a timestamp that identifies each snapshot.

Solution 1Recommended

Refresh the query and append snapshots with a scheduled macro

Use this approach to save each scheduled refresh to a separate history table while the workbook is open.

Power Query normally replaces its loaded output on refresh; it is not an append-only history store. Referencing the history table from the same query that writes back to it can create a circular or unreliable refresh process.

The macro below refreshes the current-data table first, then copies its rows to a separate history table. It schedules the next capture five minutes after the current one finishes.

1
Create the current-data table

In Excel, load the API query to a worksheet named Current using Home > Close & Load To > Table in Power Query Editor. Select the resulting table and use Table Design > Table Name to name it CurrentData.

2
Create the history table

On a worksheet named History, create an Excel table with the same columns in the same order as CurrentData, and name it HistoryData under Table Design > Table Name. Remove any empty data row from the history table before starting.

3
Set up the refresh

Open Data > Queries & Connections, open the query's Properties, and disable periodic automatic refresh and background refresh if those options are available. The macro must control the refresh so it can append the completed result.

4
Add the macro

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Press Alt+F11, choose Insert > Module, and paste this code: Public NextCapture As Date Public Sub CaptureSnapshot() Dim currentTable As ListObject Dim historyTable As ListObject Dim sourceRow As ListRow Dim newRow As ListRow On Error GoTo CaptureError Set currentTable = ThisWorkbook.Worksheets("Current").ListObjects("CurrentData") Set historyTable = ThisWorkbook.Worksheets("History").ListObjects("HistoryData") If currentTable.ListColumns.Count <> historyTable.ListColumns.Count Then Err.Raise vbObjectError + 1, , "Current and history tables must have the same number of columns." End If If Not currentTable.QueryTable.Refresh(BackgroundQuery:=False) Then Err.Raise vbObjectError + 2, , "The query refresh did not complete." End If For Each sourceRow In currentTable.ListRows Set newRow = historyTable.ListRows.Add newRow.Range.Value = sourceRow.Range.Value Next sourceRow NextCapture = Now + TimeSerial(0, 5, 0) Application.OnTime NextCapture, "'" & ThisWorkbook.Name & "'!CaptureSnapshot" Exit Sub CaptureError: MsgBox "Snapshot stopped: " & Err.Description, vbExclamation End Sub Public Sub StopSnapshots() On Error Resume Next Application.OnTime NextCapture, "'" & ThisWorkbook.Name & "'!CaptureSnapshot", , False On Error GoTo 0 End Sub

5
Start and verify captures

In Excel, open Developer > Macros, select CaptureSnapshot, and click Run. Check HistoryData after the first run and again after the next scheduled run. Use Developer > Macros > StopSnapshots to cancel the next scheduled capture.

Refresh the query and append snapshots with a scheduled macro
Keep the workbook open: The schedule runs only while Excel and the workbook remain open. Run CaptureSnapshot once after reopening the workbook. Each run appends all returned rows, including unchanged values; it cannot recover API data that disappeared between captures.
Free Microsoft Office alternative

Review Excel Data Tables with WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface for opening and working with Excel files. It can help you review saved API snapshots, but it does not replace the Excel Power Query and VBA workflow described above.

Review historical API snapshots in spreadsheet tablesOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar interface for a smoother move from Microsoft OfficeWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query overwrite the previous API result?

A loaded Power Query table represents the query's current result. When the query refreshes, Excel updates that output rather than automatically adding it to an archive.

Can I append the history table to the query and load it back to the same table?

That self-referencing setup can create a circular or unreliable refresh. A separate history table populated after the current-data query refreshes is a safer approach.

How do I change the capture interval?

In the VBA module, change TimeSerial(0, 5, 0) to the desired interval. For example, TimeSerial(0, 10, 0) schedules the next capture 10 minutes after the current capture finishes.

Will the history continue updating when Excel is closed?

No. The scheduled macro requires the workbook and Excel to remain open. For unattended collection, use an external process or data store that can call the API and save results independently.

How can I tell when each snapshot was captured?

Include a timestamp in the API result or add a timestamp column to the query output before loading it. Make sure HistoryData has the same columns in the same order as CurrentData.