Power Query Problems

Keep Historical Website Prices When Refreshing Excel Data

Natalie TaylorNatalie Taylor Nov 3, 2026 869 views

Question details

The user needs to maintain historical website prices and dates during data refreshes without overwriting the existing records.

Keep Historical Website Prices When Refreshing Excel Data
Product
Excel
Device & OS
not provided
Scenario
Importing and refreshing daily or periodic website prices into an Excel workbook to build a historical database.
Observed behavior
A standard data refresh overwrites the existing data with the current values, deleting the historical price and date records.
Before you start

Ensure your initial web query is successfully loading the website data into an Excel table and that you have a designated table for storing historical records.

Solution 1Recommended

Use Power Query to Append Current Data to History

Create separate queries for your history and current website data, append them together, and remove duplicates to maintain a running historical log.

To prevent Excel from overwriting your data during a refresh, you must break the direct link between the web query and the final table. Instead, you can use Power Query to append the fresh data to your existing historical records.

This self-referencing query technique allows you to continuously stack new data on top of old data every time you refresh the worksheet.

1
Load the initial web data

Load the website data containing the current date and price into a standard Excel table.

2
Create a history query

Create a new query from the existing table containing your historical records. If necessary, break the direct data connection between the original web query and the final output table.

3
Append the queries

In the Power Query Editor, navigate to the Home tab and use the 'Append Queries' feature to combine your history query with the new current-price query.

4
Remove duplicates

Select the relevant identifying columns (such as Date and Product ID), right-click the column headers, and choose 'Remove Duplicates' to prevent redundant entries if multiple refreshes occur on the same day.

5
Close and load

Click 'Close & Load' on the Home tab to output the combined, deduplicated dataset into your workbook. Each future refresh will now append the newest values while preserving the history.

Use Power Query to Append Current Data to History
Managing Circular References: If you encounter a circular reference warning during setup, ensure you load the appended result to a new table first, then use that table as your historical source for subsequent refreshes.
Free Microsoft Office alternative

Manage Data Effectively with WPS Office

While advanced Power Query self-referencing workflows are specific to Microsoft Excel, WPS Office provides a lightweight, highly compatible, and free alternative for managing large datasets, analyzing historical data, and cleaning duplicate records. It offers a familiar interface that makes migrating your spreadsheets completely seamless.

  1. 1. Download and install WPS Office: Visit the official WPS Office website to download the free suite and install it on your device.
  2. 2. Open your spreadsheet: Launch WPS Spreadsheet and open your existing Excel (.xlsx) file containing your historical data.
  3. 3. Manage duplicates easily: Navigate to the Data tab and use the 'Remove Duplicates' tool to clean your appended historical logs.
Fully compatible with Microsoft Excel (.xlsx, .xls) files and standard data formats.Lightweight and fast performance when handling large historical datasets.Built-in data cleaning tools to easily highlight and remove duplicate records.Familiar user interface with zero learning curve for Excel users.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel web query overwrite previous data?

By default, refreshing a web query in Excel replaces the destination table with the latest data from the source. It does not automatically store past snapshots unless you configure an append query or use a VBA macro.

Can I automate the historical data archiving in Excel?

Yes, you can use Power Query to append the current web data to your existing history table. Alternatively, you can write a simple VBA macro that copies the newly fetched values and pastes them at the bottom of a historical log table upon refresh.

How do I remove duplicate dates and prices after appending?

In Power Query, select the columns for Date and Product, right-click the column headers, and choose 'Remove Duplicates'. This ensures that only one record exists per product per day, even if you refresh multiple times in a single day.