Find New and Deleted Rows Between Two Tables in Power Query M

Compares two versions of a table by key and returns which rows are new and which have been deleted.

Comparing & LookupsIntermediatecomparenew rowsdeleted rowssnapshot

Power Query M code

let
    NewRows = Table.SelectRows(Table.NestedJoin(CurrentTable, {"ID"}, PreviousTable, {"ID"}, "Match", JoinKind.LeftOuter), each Table.IsEmpty([Match])),
    DeletedRows = Table.SelectRows(Table.NestedJoin(PreviousTable, {"ID"}, CurrentTable, {"ID"}, "Match", JoinKind.LeftOuter), each Table.IsEmpty([Match]))
in
    NewRows

Notes

Load NewRows and DeletedRows as two separate queries; both use the same pattern with CurrentTable/PreviousTable swapped.

Requirements

Two tables sharing the same key column: a current snapshot and a previous one.

How to use this snippet

In Power BI Desktop, choose Transform data to open Power Query, then open the Advanced Editor on a query (or start from a blank query).
Paste the snippet and replace the placeholder names (tables, columns, file paths) with your own. Check the requirements above first.
Run it through the Power Query M Formatter to tidy the layout.

Related Power Query M snippets

More Power Query M resources

Browse all 145 snippets in the Power Query M Code Snippets, or search the library for something similar to Find New and Deleted Rows Between Two Tables.