Lesson 8 of 9 · Lab
Compare a wide budget sheet to actuals
Transpose melts a wide sheet into long rows of name and value, which you can filter and total. Cross Tab turns long rows back into columns. A wide sheet often has a column that isn't data, and melting it counts it like one.
In Transpose, Key columns repeat on every row. Data columns are melted into Name and Value. Skip columns are left out.
Marit's budget at fictional Kramerica Bikes has a row per shop and line, Hires or Repairs, a column per month and a Total, all net of VAT. She wants September's hires against what each shop took, with repairs beside it. The lower Manual Input holds September's net and repairs per shop, worked out from the till files and the workshop sheet. You build the net yourself in the next lesson.
6 rows with 12 months each should melt into 72 rows. Will they?
No, 78. Total is melted like a thirteenth month, so any sum over the long table counts the year twice.
Melt, pick a month, pivot back
- The lab is a live Strata flow (Oplema's browser app for data prep) with Transpose selected:
shopandlineare Key and every month andTotalare Data. The preview has 78 rows. Cross Tab, Join and Formula stay red until you set them up. - Select Summarize: a
budgetof 497800 for Hires, over 39 rows. - Find the column that isn't a month and keep it out of the melt. Check: Summarize reads 248900 over 36 rows.
If you're stuck
In Transpose, set
Total's Role to Skip. - Select casts Transpose's text
Valueto BIGINT, and Filter keepsName = 'Sep': 6 rows. - Select Cross Tab and set Name Column to
lineand Value Column toValue. Group By isshop, so you get a row per shop withHiresandRepairs. - Drag from Cross Tab to Join's upper left dot. Its key is
shopon both sides. - Select Formula, the last block: the variance, in cents, as its Output type is DECIMAL(18,2).
Swipe sideways to see the canvas, or open this page on a larger screen to work with it.
In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).
What Formula should show
| Shop | Hires budget | Net | Variance | Repairs budget | Repairs actual |
|---|---|---|---|---|---|
| Harbour | €14,000.00 | €12,938.00 | −€1,062.00 | €600.00 | €1,120.00 |
| Station Road | €21,500.00 | €21,168.80 | −€331.20 | €800.00 | €884.00 |
| Old Town | €6,000.00 | €6,163.20 | +€163.20 | €300.00 | €969.00 |
Long to calculate, wide to read
Only Old Town beat its share of September's €41,500.00 hires budget, by €163.20. Harbour was €1,062.00 under and Station Road €331.20, net against a net budget.
Repairs sit beside the budget for reading, not as a variance: every shop spent more than planned. The long table was for filtering and summing; the wide one is for Marit, and a Total column belongs in what you show her, never in what you melt.
Your line for Marit: "Only Old Town beat its hires budget, by €163.20. All three spent more on repairs than planned."
See the Transpose and Cross Tab references. For later: Wide vs long data compares the two shapes.
Common reshaping mistakes
- Melting a total. A Total, Average or Notes column isn't another month. Skip it before melting.
- Summing text. If Summarize can't sum
Value, cast it first. - Joining on a column the reshape removed. After Cross Tab, the months are gone and
shopis the only key left. Check the key exists on both sides.
Unpivot and pivot in SQL and Excel
SELECT shop, line, value
FROM (UNPIVOT budget ON COLUMNS(* EXCLUDE (shop, line, Total)) INTO NAME month VALUE value)
WHERE month = 'Sep';
PIVOT september ON line USING sum(value) GROUP BY shop;EXCLUDE (Total) is the Skip role. In Excel, Power Query's Unpivot Other Columns melts the sheet, and a PivotTable with line in Columns turns it back.