Department budget (wide months)
A budget workbook with one column per month. Unpivot it into a tidy table that keeps working when finance adds July.
intermediate · Skills: Unpivot other columns, Month names to numbers, Refresh-proof column handling · Updated 2026-11-04
Data files
- budget.xlsx — Sheet Budget: Department, CostCentre and Jan–Jun amounts.Download XLSX (5.7 KB)
Queries
Validated in the Power Query Exercises simulator against the files above. Paste them into Excel or Power BI yourself; the header comments explain each source binding.
Budget as a long table budget-long.pq
Download .pqOne row per department and month with the month number. Returns 30 rows: Department, CostCentre, Month, Amount, MonthNo.
// Budget as a long table
// Pack: Department budget (wide months) (Power Query Exercises, https://powerqueryexercises.com/downloads/department-budget-wide)
//
// Source bindings
// In the Power Query Exercises simulator each name below is bound directly to a pack file. In Excel or
// Power BI Desktop the first steps of this query do that binding instead:
// BudgetWorkbook the pack file budget.xlsx -> File.Contents(PackFolder & "budget.xlsx")
// Set PackFolder to the folder where you extracted the pack (keep the trailing backslash).
//
// Validated in the Power Query Exercises simulator (engine 0.1.0) against this pack's files; it returns
// 30 rows with columns Department, CostCentre, Month, Amount, MonthNo.
// It has not been run in Excel or Power BI by us. This is query text, not a ready-to-refresh workbook.
// Independent practice material; not affiliated with or endorsed by Microsoft.
let
PackFolder = "C:\PQE\department-budget-wide\",
BudgetWorkbook = File.Contents(PackFolder & "budget.xlsx"),
Source = Excel.Workbook(BudgetWorkbook, true),
#"Budget" = Source{[Item = "Budget", Kind = "Sheet"]}[Data],
#"Unpivoted" = Table.UnpivotOtherColumns(#"Budget", {"Department", "CostCentre"}, "Month", "Amount"),
#"Month number" = Table.AddColumn(#"Unpivoted", "MonthNo", each List.PositionOf({"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}, [Month]) + 1, Int64.Type),
#"Changed Type" = Table.TransformColumnTypes(#"Month number", {{"Amount", type number}})
in
#"Changed Type"
Follow along in a host
Follow along in Excel
- Download the pack and extract the zip, for example to C:\PQE\<pack name>\.
- In Excel (Microsoft 365 or 2016+ on Windows), go to Data → Get Data → From Other Sources → Blank Query.
- In the Power Query Editor choose Home → Advanced Editor, select everything and paste the contents of the .pq file.
- Change the PackFolder line to the folder you extracted to, then choose Done.
- If Excel asks about privacy levels or file access, allow access to that folder. Then choose Home → Close & Load.
Follow along in Power BI Desktop
- Download the pack and extract the zip.
- In Power BI Desktop choose Home → Transform data to open the Power Query Editor, then Home → New Source → Blank Query.
- Choose Home → Advanced Editor, paste the contents of the .pq file and change the PackFolder line to your folder.
- Choose Done, check the preview against the expected rows in the query header, then Close & Apply.
Then try this
- Add a Jul column to the Budget sheet, save and refresh: July rows appear because the query only names the columns to keep, not the months.
- Compare with Unpivot Columns on Jan–Jun selected: that version silently ignores July.
Related lessons
Original fictional data, free to use for learning and teaching. Power Query Exercises is not affiliated with or endorsed by Microsoft.