Power Query Problems

How to Automatically Append Daily Web Data to an Excel Table Using Power Query

Huda Qurayshi Oct 10, 2026 869 views

Question details

The user wants to import daily internet data into an Excel table, appending new rows while preserving historical data and removing duplicates.

How to Automatically Append Daily Internet Data to an Excel Table Using Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking daily data updates (like diesel prices) from a web source without overwriting past records.
Observed behavior: Need a scalable method to combine existing worksheet data with newly imported web data seamlessly on a daily basis.

Before you start

Ensure you have a reliable URL for the web data source and that your existing historical data is formatted as an official Excel Table (Ctrl+T) with matching column headers.

Solution 1Recommended

Use Power Query to Append New Web Data to Historical Records

This method involves loading both your existing Excel table and the live web data into Power Query, appending them, and filtering out duplicates to maintain a clean historical record.

Power Query is the ideal tool for this task because it can automate the extraction, transformation, and loading (ETL) of data. By establishing two queries—one for your local history and one for the web data—you can merge them dynamically.

Load Existing Data into Power Query

Select your historical Excel table, go to the 'Data' tab, and click 'From Table/Range'. Once the Power Query Editor opens, click 'Close & Load To...' and choose 'Only Create Connection'.

Import the Daily Web Data

Go to the 'Data' tab, click 'From Web', and enter the URL containing your daily data (e.g., daily diesel prices). Select the relevant table from the Navigator window and click 'Transform Data'.

Append the Queries

In the Power Query Editor, navigate to 'Home' > 'Append Queries' > 'Append Queries as New'. Select your historical data connection as the Primary table, and the new web data query as the Secondary table.

Remove Duplicate Dates

In the newly appended query, select the Date column (or another unique identifier column). Right-click the column header and choose 'Remove Duplicates'. Then, sort the column in descending order to keep the newest data at the top.

Load the Final Output

Click 'Close & Load To...' and select 'Table' to output the combined, deduplicated data into a new Excel worksheet.

Use Power Query to Append New Web Data to Historical Records

Automate Your Updates: To update your table in the future, simply go to the 'Data' tab and click 'Refresh All'. Power Query will fetch the latest web data, append it to your history, and strip out duplicates instantly.

Free Microsoft Office alternative

Try WPS Office for Efficient Data Management and Spreadsheet Tasks

While advanced Power Query web scraping features are specific to Microsoft Excel, WPS Office provides a lightweight, highly compatible, and completely free suite for handling large datasets, external data imports, and daily spreadsheet tracking.

1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite for your operating system.
2. Open WPS Spreadsheet: Launch the application and open your existing .xlsx file to continue managing your daily records.
3. Manage External Data: Use the 'Data' tab to import external files, text, or CSVs, utilizing built-in tools to remove duplicates and organize your historical data.

100% compatible with Microsoft Excel (.xlsx, .xls) formatsLightweight architecture that runs smoothly on older or slower devicesBuilt-in advanced data sorting, filtering, and pivot tables for historical trackingFree to download and use with a highly familiar tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Can I automate the Power Query refresh when I open the workbook?

Yes. In Excel, go to Data > Queries & Connections, right-click your final appended query, select 'Properties', and check the box labeled 'Refresh data when opening the file'.

Why are my duplicates not being removed properly in Power Query?

Duplicates might remain if there are subtle differences in the data, such as hidden spaces or distinct time stamps within the date column. Ensure you trim the text or change the data type to strictly 'Date' (excluding time) before applying the 'Remove Duplicates' step.

Will the newly appended data overwrite my historical manual corrections?

If you manually edit the final output table generated by Power Query, those edits will be overwritten upon the next refresh. To preserve manual changes, you must apply them directly in the original source historical table before refreshing the query.

Excel & Data Problems