Group By & aggregation › Table.Group, Table.RowCount22 / 47

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

EasyFreeTable.GroupTable.RowCount

Table.Group: headcount per city

The outing committee needs to book buses from each office city. Mrs. Lestari is asked to count how many employees work in each city.

Task

Group the table by Kota (city) with Table.Group and create a JumlahKaryawan (headcount) column holding the number of rows in each group (use Table.RowCount). Result: two columns, Kota and JumlahKaryawan, with the cities in order of first appearance in the data (Bandung, Jakarta, Surabaya).

Data sourcesKaryawan

Required functionsTable.GroupTable.RowCount

Expected result

Loading the expected result…

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

Hint 1
Group By turns many rows into one row per key value. Here the key is the Kota column.
Hint 2
The pattern is Table.Group(table, {"Key"}, {{"NewName", each aggregation, type}}). Inside each, _ is a sub-table holding all the rows of that group.
Hint 3
Table.Group(Source, {"Kota"}, {{"JumlahKaryawan", each ___(_), Int64.Type}})

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 = Karyawan,
    #"Grouped Rows" = Table.Group(Source, {"Kota"}, {{"JumlahKaryawan", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Explanation

For every city, Table.Group collects its rows into a sub-table, then each Table.RowCount(_) counts them: Bandung 10, Jakarta 19, Surabaya 1. This is the same code as the Group By menu with the Count Rows operation.
List.Count([Nama]) gives the same result here, but Table.RowCount(_) does not depend on any particular column. To count unique values, use List.Count(List.Distinct([Column])).

Power Query· sources: Karyawan

let
    Source = Karyawan
in
    Source

Loading the Advanced Editor…

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

Validation result