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

- 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.
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.
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.
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.
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.
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.
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
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.

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.

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.




