Keep Historical Web Data When Refreshing Excel Power Query
Question details
Preserve previously imported web records while adding new records during Excel Power Query refreshes.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A user refreshes a query that imports changing data from a website and wants to retain earlier records.
- Observed behavior
- A standard refresh replaces the query output with the current source data instead of automatically preserving earlier records.
Identify the columns that uniquely identify a record and check that the web source and archive will use matching column names. If you need to retain changes to the same record over time, include a snapshot date or another version identifier in the key.
Append the web query to a separate historical archive
Use a separate archive workbook to retain records between refreshes without making the query depend on its own output.
The archive must be updated after each refresh. Power Query does not automatically write the combined result back to its source, and a refresh cannot recover web records that disappeared before they were captured.
In a separate Excel workbook, create a table with the same column headers as the web data. Add any existing historical records, save the workbook, and close it.
In the reporting workbook, select Data > Get Data > From Other Sources > From Web, enter the website address, and select the data to open it in Power Query.
Select Data > Get Data > From File > From Workbook, choose the archive workbook, and select its history table in Navigator.
In Power Query Editor, select Home > Append Queries as New and choose the archive and web queries. Select the columns that form the record key, then select Home > Remove Rows > Remove Duplicates.
Select Home > Close & Load. Copy the combined output and paste it as values over the data rows in the archive table, then save and close the archive workbook.
Use Data > Refresh All to get current web data and the saved archive. After the refresh, copy the combined output as values into the archive table again, then save and close the archive.

Review Historical Spreadsheet Data in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility, making it easy to move existing files. It does not fix Excel Power Query refresh behavior or automatically maintain the archive described above.

Frequently Asked Questions
Why does my Power Query refresh erase older web records?
Refresh retrieves the current source data and replaces the query output. To retain earlier records, import a separately saved archive and append the current web query to it.
Can Power Query use its own output as the history source?
Directly using the output as an input can create a circular dependency. Use a separate archive workbook or another independently maintained data source instead.
How do I prevent duplicate records after appending?
In Power Query Editor, select the columns that uniquely identify a record, then choose Home > Remove Rows > Remove Duplicates. Include a date or version column in the key if changed versions must be kept.
Will the archive update automatically when I select Refresh All?
No. Refresh All reads the saved archive and combines it with current data, but it does not write the result back to the archive. Copy the combined output as values into the archive table and save it after each refresh.




