Power QueryExercises

Compare two snapshots: new, removed and changed rows

Recipes · By Tahir Nas · Examples run in our simulator · Published · Updated

Jump to the practice tasks

Customer lists, price lists and staff rosters arrive as full exports each week. The useful question is usually what changed? Three joins on the key answer it, and appending the results gives one change log.

An old snapshot has P1 at 10, P2 at 20 and P3 at 30; a new one has P1 at 10, P3 at 35 and P4 at 40. Joined on ID: P1 is the same, P2 was removed, P3 changed price and P4 was added.
Joining the two snapshots on the key sorts every row into same, changed, added or removed.
let
    LastWeek = #table({"ID", "Name", "Tier"}, {{"C1", "Ana", "Gold"}, {"C2", "Ben", "Silver"}, {"C3", "Cleo", "Silver"}}),
    ThisWeek = #table({"ID", "Name", "Tier"}, {{"C1", "Ana", "Gold"}, {"C2", "Ben", "Gold"}, {"C4", "Dev", "Bronze"}}),
    New = Table.AddColumn(Table.SelectColumns(Table.NestedJoin(ThisWeek, {"ID"}, LastWeek, {"ID"}, "L", JoinKind.LeftAnti), {"ID"}), "Change", each "New", type text),
    Removed = Table.AddColumn(Table.SelectColumns(Table.NestedJoin(LastWeek, {"ID"}, ThisWeek, {"ID"}, "T", JoinKind.LeftAnti), {"ID"}), "Change", each "Removed", type text),
    Both = Table.ExpandTableColumn(Table.NestedJoin(ThisWeek, {"ID"}, LastWeek, {"ID"}, "L", JoinKind.Inner), "L", {"Name", "Tier"}, {"Old Name", "Old Tier"}),
    Changed = Table.SelectRows(Table.AddColumn(Both, "Change", each Text.Combine(List.Select({
        if [Name] <> [Old Name] then "Name: " & [Old Name] & " → " & [Name] else null,
        if [Tier] <> [Old Tier] then "Tier: " & [Old Tier] & " → " & [Tier] else null
    }, each _ <> null), "; "), type text), each [Change] <> ""),
    Log = Table.Combine({New, Removed, Table.SelectColumns(Changed, {"ID", "Change"})})
in
    Log

Result

IDChange
C4New
C3Removed
C2Tier: Silver → Gold

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

How it works

  • New: rows in this week with no match last week (left anti from this week's side).
  • Removed: rows in last week with no match this week (left anti from last week's side).
  • Changed: rows in both, where at least one compared field differs. Unchanged rows (C1) produce an empty change text and are filtered out.

Make it robust

  • Check the key is unique in both snapshots first; duplicates make every comparison ambiguous.
  • Clean the key (trim, case) in both snapshots, or a reformatted ID shows up as one removal and one addition.
  • Compare cleaned values too: a trailing space shouldn't count as a name change.
  • Handle null: null <> "Gold" is true, which is correct, but "Tier: " & null is null; use Text.From or a placeholder when old values can be missing.

Keeping snapshots

Power Query doesn't store history: each refresh reads the current files. Keep dated exports in a folder (customers-2026-10-28.csv, customers-2026-11-04.csv) and select the latest two by name, so the comparison refreshes itself each week.

Practise it in the simulator

Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. These tasks are part of Pro.

Lessons

More resources

Function reference

Sources

Microsoft's official documentation for the features and functions used on this page.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.