Pivot & unpivot › Table.Unpivot, Table.SelectRows35 / 47

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

MediumFreeTable.UnpivotTable.SelectRows

Table.Unpivot: weekly absence summary

Citra from HR compiles weekly attendance from a supervisor's spreadsheet. It has one column per day holding a code: H (hadir, present), S (sakit, sick), I (izin, excused leave), A (alpa, absent without notice). For the report she only needs the absences: who, which day, and the code.

Task

Unpivot the Senin, Selasa, Rabu, Kamis, Jumat (Monday to Friday) columns with Table.Unpivot (list all five) into Hari (day) and Kode (code) columns, then keep only the rows whose Kode is not "H" with Table.SelectRows. Result: 4 columns Nama, Departemen, Hari, Kode and 6 rows, ordered by employee as in the source and then by day (Senin to Jumat).

Data sourcesAbsensi

Required functionsTable.UnpivotTable.SelectRows

Expected result

Loading the expected result…

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

Hint 1
Unlike UnpivotOtherColumns, Table.Unpivot asks for the list of columns to turn into rows, not the fixed ones.
Hint 2
The pattern is Table.Unpivot(table, {"Col1", "Col2"}, "AttributeName", "ValueName"). After that every day's code sits in one column, so it is easy to filter with each [Kode] <> "H".
Hint 3
#"Unpivoted Columns" = Table.Unpivot(Source, {"Senin", "Selasa", ___, ___, ___}, "Hari", "Kode"), then Table.SelectRows(#"Unpivoted Columns", each ___).

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 = Absensi,
    #"Unpivoted Columns" = Table.Unpivot(Source, {"Senin", "Selasa", "Rabu", "Kamis", "Jumat"}, "Hari", "Kode"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each [Kode] <> "H")
in
    #"Filtered Rows"

Explanation

After the unpivot, 5 employees × 5 days become 25 rows with a single Kode column. Filtering one column is much easier than checking five day columns one by one, and the result is ready to count per employee or per day.
Why Table.Unpivot and not UnpivotOtherColumns? If the supervisor later adds a column such as Catatan (notes), it will not be unpivoted because you only listed the day columns. On the other hand, if a Saturday column appears, you must add it to the list yourself. Pick based on which part is more likely to change. Remember that text comparison is case-sensitive: "h" is not "H".

Power Query· sources: Absensi

let
    Source = Absensi
in
    Source

Loading the Advanced Editor…

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

Validation result