Advanced › Record.Field, Table.AddColumn46 / 47

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

MediumFreeRecord.FieldTable.AddColumn

Record.Field: commission rate per category

Rina sells on a marketplace that charges a different commission for each product category. She has already stored the rates as a TarifKomisi record in the starter code, with one field (in percent) per category. Now she wants the commission for each product.

Task

Add a Komisi (commission) column with Table.AddColumn holding [HargaJual] times the rate of the product's category, divided by 100 (the rates are percentages). Take the rate from the TarifKomisi record with Record.Field. Result: 8 columns (the 7 Katalog columns in their original order, then Komisi) and 12 rows in the source order.

Data sourcesKatalog

Required functionsRecord.FieldTable.AddColumn

Expected result

Loading the expected result…

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

Hint 1
A record is a set of name = value pairs in square brackets. TarifKomisi[Minuman] takes a field whose name is written directly in the code.
Hint 2
Here the field name changes on every row (the value of Kategori), so use Record.Field(record, fieldName), which takes the field name as a text value such as [Kategori].
Hint 3
#"Added Commission" = Table.AddColumn(Source, "Komisi", each [HargaJual] * ___ / 100, type number)

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 = Katalog,
    TarifKomisi = [Minuman = 5, Bahan = 4, #"Makanan Ringan" = 6, Bumbu = 5, Makanan = 6, Kesehatan = 7, Fashion = 10, Kerajinan = 8],
    #"Added Commission" = Table.AddColumn(Source, "Komisi", each [HargaJual] * Record.Field(TarifKomisi, [Kategori]) / 100, type number)
in
    #"Added Commission"

Explanation

For Batik Tulis Pekalongan, [Kategori] is "Fashion", so Record.Field(TarifKomisi, "Fashion") returns 10 (percent) and the commission is 450,000 × 10 / 100 = 45,000. Writing TarifKomisi[Kategori] does not work, because M would look for a field literally named "Kategori" and fail.
A field whose name contains a space is written #"Makanan Ringan" inside the record. If a category has no rate, Record.Field raises an Error; use Record.FieldOrDefault(TarifKomisi, [Kategori], 0) when you want a fallback. A record as a mini lookup table is handy for a few rates that rarely change; with many rates, keep them in a separate table and use Merge.

Power Query· sources: Katalog

let
    Source = Katalog,
    TarifKomisi = [Minuman = 5, Bahan = 4, #"Makanan Ringan" = 6, Bumbu = 5, Makanan = 6, Kesehatan = 7, Fashion = 10, Kerajinan = 8]
in
    Source

Loading the Advanced Editor…

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

Validation result