Merge & append › Table.NestedJoin, JoinKind.LeftAnti, Table.RemoveColumns30 / 47

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeTable.NestedJoinJoinKind.LeftAntiTable.RemoveColumns

Left Anti: customers who never bought

Fajar, a field sales rep, wants to call registered customers who have not bought anything at all from January to June 2025. The customer list is in the Pelanggan query, transactions are in Penjualan.

Task

Start from Pelanggan as the left table. Merge it with Penjualan (matching Nama to the Pelanggan column) using Table.NestedJoin with JoinKind.LeftAnti, name the join column Transaksi, then remove that Transaksi column with Table.RemoveColumns. Result: the 7 original Pelanggan columns (IDPelanggan, Nama, Kota, Segmen, Bergabung, Email, Telepon) and 3 rows. Row order is not checked.

Data sourcesPelangganPenjualan

Required functionsTable.NestedJoinJoinKind.LeftAntiTable.RemoveColumns

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Run previewCtrlEnter
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
Left Anti is the opposite of Inner: it keeps the rows of the left table that have NO match in the right table.
Hint 2
The left table must be Pelanggan, because you are looking for customers. The nested tables from Left Anti are always empty, so there is nothing to expand; just remove that column.
Hint 3
Table.NestedJoin(Source, {"Nama"}, Penjualan, {"Pelanggan"}, "Transaksi", ___), then Table.RemoveColumns(previousStep, {"Transaksi"}).

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

let
    Source = Pelanggan,
    #"Merged Queries" = Table.NestedJoin(Source, {"Nama"}, Penjualan, {"Pelanggan"}, "Transaksi", JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries", {"Transaksi"})
in
    #"Removed Columns"

Explanation

The result is Apotek Sehat Selalu, Toko Oleh-Oleh Mekar and Galeri Batik Sekar: all three are registered, but their names never appear in the transactions' Pelanggan column. An anti join is the fastest answer to "which ones have not yet" questions: customers who have not bought, products that never sold, or employees who have not filled in attendance.
Direction matters. With Penjualan as the left table you would get transactions from unregistered buyers instead. Key matching is exact too, so tidy the names before joining; otherwise an active customer can look like one who never bought.

Power Query· sources: Pelanggan Penjualan

let
    Source = Pelanggan
in
    Source

Loading the Advanced Editor…

Edit the M code until the result matches, then press Check answer.

Validation result