Merge & append › Table.NestedJoin, JoinKind.Inner, Table.ExpandTableColumn29 / 47

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

MediumFreeTable.NestedJoinJoinKind.InnerTable.ExpandTableColumn

Inner merge: only registered customers' transactions

Nadia from marketing wants to compare sales by customer segment (Retail, Grosir/wholesale, Korporat/corporate). The segment is stored in the Pelanggan query, while transactions are in Penjualan. Transactions without a customer name or from unregistered buyers must be left out of the analysis.

Task

Merge Penjualan (left) with Pelanggan (right), matching the left Pelanggan column to the right Nama column, using Table.NestedJoin with JoinKind.Inner. Expand only the Segmen column. Result: 11 columns, the 10 Penjualan columns in their original order followed by Segmen, and 108 rows (only transactions whose customer is registered). Row order is not checked.

Data sourcesPenjualanPelanggan

Required functionsTable.NestedJoinJoinKind.InnerTable.ExpandTableColumn

Expected result

Loading the expected result…

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

Hint 1
An inner join keeps only the rows whose key exists in both tables. Transactions with a null customer or an unregistered name drop out automatically.
Hint 2
The key columns may have different names in each table: write {"Pelanggan"} for the left and {"Nama"} for the right. The join kind is the sixth argument.
Hint 3
Table.NestedJoin(Source, {"Pelanggan"}, Pelanggan, {"Nama"}, "DataPelanggan", ___), then expand "DataPelanggan" with the list {"Segmen"}.

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 = Penjualan,
    #"Merged Queries" = Table.NestedJoin(Source, {"Pelanggan"}, Pelanggan, {"Nama"}, "DataPelanggan", JoinKind.Inner),
    #"Expanded DataPelanggan" = Table.ExpandTableColumn(#"Merged Queries", "DataPelanggan", {"Segmen"})
in
    #"Expanded DataPelanggan"

Explanation

Out of 120 transactions, 12 have no match in the Pelanggan table: 3 are null, 2 come from "Ibu Wulan" and 7 from "Toko Baru Jaya". JoinKind.Inner drops them all, leaving 108 rows that all have a Segmen. With Left Outer those 12 rows would stay with a null Segmen and could show up as a "(Blank)" category in Power BI visuals.
A join does not remove duplicates: the 4 duplicate transactions in the source stay duplicated. Also watch out for non-unique keys in the right table; a transaction gets multiplied if a customer name appears twice in Pelanggan.

Power Query· sources: Penjualan Pelanggan

let
    Source = Penjualan
in
    Source

Loading the Advanced Editor…

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

Validation result