Power Query Problems

How to Compare Two Excel Tables in Power Query and Remove Unchanged Rows

Amos Gikunda Oct 9, 2026 869 views

Question details

The user wants to compare a Master table and a Last Save table to extract only the records that have changed, been added, or been removed, while omitting the unchanged rows.

How to Compare Two Excel Tables in Power Query and Remove Unchanged Rows

Product: Excel Power Query
Device & OS: not provided
Scenario: Comparing two versions of an Excel dataset to identify modifications, additions, and deletions without keeping identical data.
Observed behavior: The goal is to output only differing records, effectively filtering out any rows that remain completely identical between the two tables.

Before you start

Ensure both the Master and Last Save tables share a unique identifier (such as an ID column) to serve as a reliable key for merging and comparing the data.

Solution 1Recommended

Merge Queries and Filter for Changes

Merge both tables in Power Query using a Full Outer join, compare the columns, and filter the results to show only modified, added, or deleted records.

By merging your two tables with a Full Outer join, Power Query will line up matching records and include unmatched records from both sides. You can then expand the columns and build a conditional comparison to filter out rows that have not changed.

Load both tables into Power Query

Select your Master data in Excel, go to the Data tab, and click 'From Table/Range'. Repeat this step for the Last Save data table so both are loaded into the Power Query Editor.

Merge the Queries

In the Power Query Editor, go to the Home tab and click 'Merge Queries as New'. Select your Master table from the first dropdown and the Last Save table from the second dropdown.

Select a common key and join kind

Click the unique ID column in both table previews to establish the relationship. Under 'Join Kind', select 'Full Outer (all rows from both)' so that additions and deletions are both captured, then click OK.

Expand and compare columns

Click the expand icon at the top of the newly merged column and select the fields you want to compare. Go to Add Column > Custom Column, and write a conditional formula (e.g., if [ColumnA] <> [ColumnB] then "Changed" else "Unchanged") to check for differences.

Filter out unchanged rows

Click the filter dropdown on your new custom column, uncheck 'Unchanged', and keep only the 'Changed' values (or nulls). This removes all identical rows from the final output.

Handling Null Values in Outer Joins: Rows that were added or deleted will return null values in the expanded columns. Ensure your comparison logic correctly identifies nulls as changed or new records.

Free Microsoft Office alternative

Manage and Analyze Data Seamlessly with WPS Office

While Power Query is highly specific to Microsoft Excel, WPS Spreadsheet offers a fast, lightweight, and highly compatible alternative for data analysis. It provides built-in tools for comparing datasets, managing duplicates, and analyzing differences for free.

1. Download and install WPS Office: Get the free WPS Office suite from the official website and install it on your device.
2. Open your datasets: Launch WPS Spreadsheet and open your Master and Last Save .xlsx files with seamless compatibility.
3. Compare data using built-in tools: Use features under the Data tab, such as 'Highlight Duplicates' or VLOOKUP functions, to quickly identify unchanged or missing rows.

Free, lightweight, and fast data processingHigh format compatibility with Microsoft Excel (.xlsx and .xls)Familiar user interface with zero learning curveBuilt-in data comparison and duplicate management tools

microsoft office alternative - wps office

Frequently Asked Questions

Why did my merge not remove the unchanged rows?

If unchanged rows still appear after filtering, check if your merge key is truly unique. Invisible trailing spaces, different text cases, or data type mismatches (e.g., text vs. number) can cause Power Query to treat identical values as different.

Can I compare multiple columns at once in Power Query?

Yes. When merging queries, you can hold down the CTRL key and select multiple columns in the same sequence for both tables. This creates a composite key for a more accurate comparison.

What join type should I use to find only newly added rows?

To find only new rows, use a 'Left Anti' or 'Right Anti' join. For example, if you select your Last Save table first and the Master table second, a Left Anti join will return only the rows that exist in the Last Save data but are completely missing from the Master data.

Excel & Data Problems