Power Query Problems

How to Update Only Changed Rows Between Two Excel Worksheets

Tauseeq Magsi Sep 30, 2026 869 views

Question details

The user needs to compare recurring vendor workbooks against a master working plan to identify and update only the changed records while keeping manually added tasks intact.

Update Only Changed Rows Between Two Excel Worksheets Using Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Importing data updates from a newly received vendor workbook into an existing tracking worksheet that contains additional manually entered internal data.
Observed behavior: The goal is to merge the two datasets based on a unique ID and country key, isolating only the modified rows so the destination working plan updates automatically without overwriting the user's manual inputs.

Before you start

Ensure both your original working plan and the updated vendor workbook are saved locally or on a stable network drive, and verify that both tables share at least one common column (such as an ID) to serve as a matching key.

Solution 1Recommended

Use Power Query Merge to Identify and Update Changed Rows

This method utilizes Power Query to join your original and updated tables, allowing you to filter out unchanged data and output only the rows that require updates.

By leveraging Power Query's merge feature, you can perform an exact comparison between two datasets. By joining the old and new tables via a unique identifier, you can easily isolate columns that have different values.

This process is ideal for maintaining internal tasks in your master sheet because you only update specific data points from the vendor file without replacing the entire table.

Load both tables into Power Query

Open Excel, navigate to the 'Data' tab, and click 'Get Data' > 'From File' > 'From Workbook'. Import both your previous working plan and the newly updated vendor workbook into the Power Query Editor.

Create a unique matching key

If a single ID column is not perfectly unique, go to 'Add Column' > 'Custom Column' in both queries to concatenate two fields (e.g., ID and Country) into a single unique identifier string.

Merge the two queries

Select your updated workbook query, go to the 'Home' tab, and click 'Merge Queries'. Select your previous working plan query from the dropdown, highlight the unique identifier column in both previews, and choose a 'Left Outer' or 'Full Outer' join.

Expand and compare the data

Click the expand icon at the top of the newly created merged column. Select the specific data columns you want to compare (e.g., Status, Date) and uncheck 'Use original column name as prefix'.

Filter for changed rows and load

Add a conditional column to flag rows where the old value does not match the new value. Filter the table to keep only these changed rows, then click 'Close & Load' to return the updated data set back into your Excel destination sheet.

Use Power Query Merge to Identify and Update Changed Rows

Automate Future Updates: Once this query is built, you can process future vendor updates by simply saving the new file over the old one and clicking 'Refresh All' in Excel.

Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Management

If you find Microsoft Excel's advanced Power Query features too complex or resource-heavy for your daily tasks, WPS Office provides a free, lightweight, and highly compatible alternative. It offers powerful data comparison tools, robust formula support, and an intuitive interface that makes managing updates across multiple worksheets easier.

1. Download the software: Visit the official WPS website and download the free WPS Office installer for your operating system.
2. Install and launch: Follow the on-screen instructions to install the suite, then open WPS Spreadsheet.
3. Open your files: Drag and drop your existing Excel workbooks directly into the application to continue your data comparison without losing formatting.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Lightweight application that uses minimal system memory.Familiar tabbed interface allows seamless transition from MS Office.Built-in advanced filtering and lookup functions for easy data comparison.

microsoft office alternative - wps office

Frequently Asked Questions

What if my tables don't have a single unique ID column?

You can create a composite unique key directly within Power Query by concatenating multiple columns. For example, combining an 'Employee Name' column and a 'Department' column can serve as a unique identifier for the merge.

Will using Power Query delete the manual notes I typed next to my data?

No, provided you output the Power Query results as a separate update table or use a Left Outer join to append updates strictly to matched rows. You can design the query to pull in new data while leaving your manual entry columns isolated in the master workbook.

Do I have to rebuild the query every time I receive a new workbook from the vendor?

No. Power Query saves the steps you performed. For future updates, simply ensure the new vendor file has the same name and is in the same folder path, then click 'Data' > 'Refresh All' in Excel to run the exact same update process automatically.

Excel & Data Problems