Power Query Problems

Import Only New Excel Records and Update Existing Rows with Power Query

Algirdas JasaitisAlgirdas Jasaitis Nov 14, 2026 868 views

Question details

Import new records and update source-controlled fields without losing user-maintained data.

Import Only New Excel Records and Update Existing Rows with Power Query
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
A current table and a periodic update table are stored in separate Excel files and share a unique record identifier.
Observed behavior
The update table can contain records that already exist as well as new records; user-maintained fields must remain associated with their original IDs.
Before you start

Identify the unique ID and decide which columns are source-controlled and which users maintain. Check both files for blank or duplicate IDs, and keep editable user data in a separate table rather than typing it into a Power Query output.

Solution 1Recommended

Merge updates by ID and append only new records

Use this workflow to update existing source fields, add new IDs, and reconnect user-maintained fields during refresh.

Power Query refreshes its output, so manually edited values in the output table are not a reliable place to store user data. Keep those values in a separate editable table keyed by ID.

Keep the current master data independent of the query output. If each update file is replaced, persist the refreshed results to the separate master before the next update; otherwise, previously imported records may disappear.

1
Prepare the source tables

In the current workbook, create a separate Excel table containing each ID and its user-maintained columns. Keep the current master and update data in their own tables or files, with matching ID data types.

2
Load the tables

In the destination workbook, use Data > Get Data > From File > From Workbook to select the current master, update, and user-data tables. Open them in Power Query Editor.

3
Update existing records

Select the current master query, then choose Home > Merge Queries. Match its ID to the update table's ID using a Left Outer join. Expand the update ID and source-controlled columns, then use Add Column > Custom Column to select update values when the expanded update ID is present and current values otherwise.

4
Find and append new records

Select the update query and choose Home > Merge Queries. Match its ID to the current master ID and select Left Anti to retain only IDs absent from the master. Align its source-controlled columns with the updated current-record query, then use Home > Append Queries to combine them.

5
Reconnect user data and load

Merge the combined query with the separate user-data table on ID using a Left Outer join. Expand the user-maintained columns, then select Home > Close & Load to create the result table.

6
Test and maintain the master

Test with a new ID, an existing ID with changed source values, and an existing ID with user data. After reviewing the result, copy the source-controlled columns and IDs and use Paste Special > Values to update the separate master table; save that file before replacing the next update file. Continue editing user fields only in the separate user-data table.

Merge updates by ID and append only new records
Refreshing is not a database update: Data > Refresh All reruns the queries; it does not automatically write results back to the separate master or preserve edits made directly in the query output. Persist the master separately or use a database or another write-back process for full automation.
Free Microsoft Office alternative

Review and Edit Excel Record Tables with WPS Office

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface for opening and editing Excel files. Its format compatibility helps users move everyday spreadsheet work between applications; it does not replace the Excel Power Query workflow described above.

Review and edit record IDs and user-maintained columns in spreadsheetsOpen and save Microsoft Excel XLSX filesWork in a familiar, easy-to-use spreadsheet interfaceMove everyday spreadsheet files between WPS Office and Microsoft Office
QA img-9

Frequently Asked Questions

How do I identify records that are not already in the current table?

In Power Query Editor, select the update query, choose Home > Merge Queries, match the ID columns, and select the Left Anti join. It returns update rows whose IDs are absent from the current table.

Can I type notes directly into the Power Query result table?

Do not rely on edits in the result table to survive refresh. Store notes in a separate editable table keyed by ID, then merge that table into the result in Power Query.

What happens if an update contains an existing ID?

The Left Outer merge matches it to the current record. Use the expanded update ID to detect the match and select the update values for source-controlled columns; the separate user-data merge restores user-maintained fields.

Will Refresh All permanently add new records to my master file?

No. Data > Refresh All recalculates the query output but does not write the new records back to an independent source file. Copy the reviewed IDs and source-controlled values to the separate master, or use a write-back process.

Why should I check for duplicate IDs before merging?

Duplicate IDs can produce multiple matching rows and duplicate the merged output. Check the ID columns in both source tables and resolve duplicates before using Home > Merge Queries.