Import Only New Excel Records and Update Existing Rows with Power Query
Question details
Import new records and update source-controlled fields without losing user-maintained data.

- 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.
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.
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.
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.
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.
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.
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.
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.
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.

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.

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.




