Module 14
Pivot and unpivot
Reshape wide and long tables and survive new columns on refresh.
You'll be able to
- Unpivot month columns robustly
- Pivot with aggregation
- Explain schema changes on refresh
Lessons
- 1.Unpivot columns so new months are picked upTurn wide data long in a way that survives new columns.
- 2.Pivot a column with an aggregationPivot long data into a cross-tab with the right row grain.
- 3.Pivot without aggregation and the duplicate errorDiagnose and fix duplicate key/value pairs before or during a pivot.
- 4.Unpivot, then turn header text into real datesTurn unpivoted header text into typed dates that survive new periods.
Take the pivot and unpivot quiz (8 questions)
Exercises
- intermediate~8 minPro
Unpivot month columns into rows (hardware)
Turn one column per month into Month and Units rows, in a way that picks up new months automatically.
- intermediate~8 minPro
Unpivot month columns into rows (pharmacy)
Turn one column per month into Month and Units rows, in a way that picks up new months automatically.
- intermediate~8 minPro
Unpivot month columns into rows (cafe)
Turn one column per month into Month and Units rows, in a way that picks up new months automatically.
- advanced~10 minPro
Pivot order statuses into columns (kite shop)
Count orders per region with one column per status (Complete, Cancelled, Pending).
- advanced~10 minPro
Pivot order statuses into columns (bakery)
Count orders per region with one column per status (Complete, Cancelled, Pending).
- advanced~10 minPro
Pivot order statuses into columns (bike rental)
Count orders per region with one column per status (Complete, Cancelled, Pending).
- intermediate~9 minPro
Pivot months into columns, summing repeated entries (bakeries)
Each store can have several sales lines per month. Pivot Month into columns with the total Sales per store and month.
- intermediate~9 minPro
Pivot months into columns, summing repeated entries (garden)
Each store can have several sales lines per month. Pivot Month into columns with the total Sales per store and month.
- advanced~10 minPro
Unpivot only the quarter columns, whatever else the sheet has (budget)
The sheet has quarter columns (Q1 2026 …) and descriptive columns that change over time. Unpivot just the quarters.
- advanced~10 minPro
Unpivot only the quarter columns, whatever else the sheet has (targets)
The sheet has quarter columns (Q1 2026 …) and descriptive columns that change over time. Unpivot just the quarters.
- advanced~15 minPro
Reshape month-and-measure columns into a tidy table (kiosks)
Columns look like "2026-01 Sales" and "2026-01 Units". Produce one row per item and month with Sales and Units columns.
- advanced~15 minPro
Reshape month-and-measure columns into a tidy table (stalls)
Columns look like "2026-01 Sales" and "2026-01 Units". Produce one row per item and month with Sales and Units columns.
- intermediate~8 minPro
Turn attribute–value rows into one row per record (intake)
A form tool stores answers as (RecordID, Field, Value) rows. Pivot Field so each record becomes one row.
- intermediate~8 minPro
Turn attribute–value rows into one row per record (feedback)
A form tool stores answers as (RecordID, Field, Value) rows. Pivot Field so each record becomes one row.
- advanced~10 minPro
Predict which cells an unpivot will drop (stock sheet)
Before unpivoting a sheet with blank (null) cells, list exactly the item–month cells that will disappear.
- advanced~10 minPro
Predict which cells an unpivot will drop (timesheet)
Before unpivoting a sheet with blank (null) cells, list exactly the item–month cells that will disappear.
- advanced~12 minPro
Pivot categories and add a total that includes future columns (consultancy)
Pivot expense categories into columns (summing amounts), then add a Total across every category column, including new ones.
- advanced~12 minPro
Pivot categories and add a total that includes future columns (school)
Pivot expense categories into columns (summing amounts), then add a Total across every category column, including new ones.