Power Query Problems

How to Add New Excel Records in Power Query Without Changing Historical Data

Emma Brown Oct 8, 2026 869 views

Question details

The user needs to append incoming weekly data in Power Query without overwriting manual edits made to older, historical Excel rows, while also handling duplicate records in the source files.

How to Add New Excel Records in Power Query Without Changing Historical Data

Product: Excel
Device & OS: not provided
Scenario: Importing new weekly data sets using Power Query while ensuring manual historical adjustments are preserved.
Observed behavior: Standard Power Query refreshes overwrite the output table entirely, failing to reliably preserve manual edits made directly in the loaded table.

Before you start

Before proceeding, ensure you have a backup of your original Excel workbook containing the manual adjustments, and identify a unique identifier (like an ID column) to accurately map your historical data to the new records.

Solution 1Recommended

Use a Self-Referencing Query Setup

Create a setup where your manual adjustments are kept in a separate table, merged with incoming data, and output together to prevent overwriting during refreshes.

Power Query does not directly preserve manually edited output rows upon refreshing. To work around this, you must merge the new source data with the existing table containing your manual edits.

Load Source Data as a Connection

Import your weekly data into Power Query. Remove duplicates based on your unique ID column, and choose 'Close & Load To...' > 'Only Create Connection'.

Load Current Data to a Worksheet Table

Output your initial query to a standard Excel table in a worksheet. Make your manual edits or add new columns directly to this worksheet table.

Create a Self-Referencing Query

Click anywhere inside your newly edited worksheet table, go to the 'Data' tab, and click 'From Table/Range' to load this edited table back into Power Query as a secondary query.

Merge the Queries

In Power Query Editor, select your Source Data query, choose 'Merge Queries as New', and merge it with your self-referencing query using a Left Outer Join based on the unique ID column.

Expand and Output

Expand the merged table to include your manually edited columns. Finally, 'Close & Load' this new merged query to your final worksheet, which will now safely preserve edits during future refreshes.

Stable Keys Required: This method relies heavily on having a stable, non-repeating unique ID (primary key). Always ensure you remove duplicates from your source data first.

Solution 2

Maintain a Separate Historical Mapping Table

Store all manual edits in a completely separate Excel table and join it with the raw Power Query output using Excel lookup formulas.

Free Microsoft Office alternative

Try WPS Office for Advanced Data Management

If you are struggling with complex data refreshes and overwriting issues, WPS Office provides a lightweight, highly compatible alternative for managing large datasets. Enjoy familiar spreadsheet interfaces, seamless data consolidation, and full compatibility with Microsoft Excel formats.

1. Download and Install: Download WPS Office for free from the official website and run the quick installation.
2. Open Your Excel File: Launch WPS Spreadsheets and open your existing .xlsx file containing your data tables.
3. Manage Data Seamlessly: Use built-in data tools like Remove Duplicates, Advanced Filter, and PivotTables to manage your historical data without complicated query setups.

100% compatible with Microsoft Excel formats (.xlsx, .xls)Familiar, intuitive interface requiring no learning curveBuilt-in data consolidation and duplicate removal toolsLightweight application that runs smoothly on any device

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query overwrite my manual changes?

By design, Power Query treats the source data as the single source of truth. When you refresh a query, it completely replaces the data in the destination worksheet table, erasing any manual edits or typed data made directly to those specific cells.

How do I remove duplicate records in Power Query?

To remove duplicates, select the column containing your unique identifier in the Power Query Editor, go to the Home tab, click 'Remove Rows', and select 'Remove Duplicates'. This ensures only the first occurrence of each record is kept.

Can I use VBA macros instead of a self-referencing query?

Yes. You can write a VBA macro that checks incoming raw data against your master sheet and only appends new, unseen rows. This completely bypasses Power Query's overwrite behavior, allowing you to manually edit older rows freely.

Excel & Data Problems