Power Query Problems

How to Combine Current and Previous Tables Without Duplicates in Power Query

Amos Gikunda Oct 12, 2026 868 views

Question details

The user wants to combine a current-month and a previous-month Power Query table, prioritizing the current month's records while retaining any unique identifiers that only exist in the previous month's data.

How to Combine Current and Previous Tables Without Duplicates in Power Query

Product: Excel
Device & OS: not provided
Scenario: Updating a monthly dataset or report where new data supersedes old data, but historically unique records from previous reports must not be lost.
Observed behavior: The goal is to generate a consolidated table free of duplicate identifiers, where overlapping records take the current values and non-overlapping old records are safely appended.

Before you start

Ensure both the current and previous month tables are loaded into Power Query as connections and that they share at least one common unique identifier column (e.g., ID, SKU, or Email) to perform the join accurately.

Solution 1Recommended

Use Left Anti Join to Filter and Append Unique Records

This method isolates the records that only exist in the previous month's table and appends them to the current month's table, effectively preventing duplicates while prioritizing new data.

A Left Anti join is perfect for finding the delta between two datasets. By using it to compare the previous table against the current table, you can easily filter out the records that have already been updated in the current month.

Merge Queries as New

Open Power Query, select the "Previous Month" query, and navigate to Home > Merge Queries > Merge Queries as New.

Configure the Left Anti Join

In the Merge dialog, select the "Current Month" table from the second dropdown. Click the unique identifier column in both tables to map them. Under Join Kind, select "Left Anti (rows only in first)" and click OK.

Remove the Merged Column

In the newly created query, right-click the header of the newly merged column (which contains table references) and select "Remove".

Append the Filtered Records

Select the "Current Month" query, go to Home > Append Queries > Append Queries as New, and select the filtered Left Anti query you just created to form the final combined dataset.

Use Left Anti Join to Filter and Append Unique Records

Clean Append Complete: This workflow ensures that overlapping identifiers use the updated current-month values while fully preserving any identifiers uniquely found in the previous month.

Solution 2

Append Both Tables and Remove Duplicates Based on Report Date

If your data includes a Report Date column, you can append both tables entirely and use Power Query's built-in duplicate removal logic to keep only the most recent entry per identifier.

Free Microsoft Office alternative

Manage Data efficiently with WPS Office

While Power Query is a specialized Microsoft Excel tool, WPS Office provides a robust, lightweight, and completely free alternative for handling large datasets, combining tables, and managing reports with full compatibility.

Fully compatible with Microsoft Excel (.xlsx, .xls) and CSV formats.Built-in advanced data tools for removing duplicates and consolidating data effortlessly.Free and lightweight software that runs smoothly without heavily consuming PC resources.Familiar ribbon interface requires zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

What exactly does a Left Anti join do in Power Query?

A Left Anti join compares two tables and returns only the rows from the first (left) table that do not have a matching value in the second (right) table, based on the selected identifier column.

Why not use a Full Outer join and conditionally remove duplicates?

While a Full Outer join combines everything, handling the logic to decide which duplicate record to keep requires creating complex conditional columns. The Left Anti join method followed by a simple append is much faster and easier to maintain.

Can I automate this table combination process for future months?

Yes. Once you build this merge and append logic in Power Query, you can simply update the underlying data files in your source folder and click 'Refresh All' in Excel to automatically generate the new combined table.

Why do I need to remove the merged column after performing the Anti Join?

The merged column contains the nested table data from the right table (which is empty in a Left Anti join anyway). Removing it cleans up the query structure so the column headers match perfectly when you append it to the current month's table.

Excel & Data Problems