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

  1. The lab is a live Strata flow (Oplema's browser app for data prep) with Transpose selected: shop and line are Key and every month and Total are Data. The preview has 78 rows. Cross Tab, Join and Formula stay red until you set them up.
  2. Select Summarize: a budget of 497800 for Hires, over 39 rows.
  3. 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.

  4. Select casts Transpose's text Value to BIGINT, and Filter keeps Name = 'Sep': 6 rows.
  5. Select Cross Tab and set Name Column to line and Value Column to Value. Group By is shop, so you get a row per shop with Hires and Repairs.
  6. Drag from Cross Tab to Join's upper left dot. Its key is shop on both sides.
  7. 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
September against budget
ShopHires budgetNetVarianceRepairs budgetRepairs 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 shop is 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.