Budget vs actual reporting with Power Query
Use case · By Tahir Nas · Examples run in our simulator · Updated
10 min read
A budget vs actual report in Power Query needs both sides at the same grain before you compare them: unpivot the budget so each department and month is one row, total the actual transactions to department and month, then merge the two with a full outer join and calculate the variance. The full outer join is the part people miss, and it is what stops unbudgeted spending from disappearing.
The two tables you start with
Budgets usually arrive as a planning spreadsheet: one row per department and one column per month. Actuals come from the ledger as transactions: a date, a department (or cost centre) and an amount. When the actuals start as a bank export, turning a bank statement CSV into ledger-ready rows shows the cleaning that comes first. The two shapes don't line up, so the report starts by reshaping each side.
The examples below use a three-month budget for Marketing, IT and Facilities, and ten actual transactions from July to September 2026. One of those transactions belongs to Legal, which has no budget at all.
Step 1: unpivot the budget
Unpivoting turns each month column into rows. Table.UnpivotOtherColumns keeps the columns you name and unpivots everything else, which Microsoft describes as translating "all columns other than a specified set" into attribute-value pairs (Table.UnpivotOtherColumns, Microsoft Learn). Naming only Department means a December column added next quarter is unpivoted too, with no change to the query.
let
Budget = #table(
{"Department", "2026-07", "2026-08", "2026-09"},
{
{"Marketing", 5000, 5000, 6000},
{"IT", 8000, 8000, 8000},
{"Facilities", 3000, 3000, 3000}
}
),
Long = Table.UnpivotOtherColumns(Budget, {"Department"}, "Period", "Budget"),
Typed = Table.TransformColumns(Long, {
{"Period", each Date.FromText(_ & "-01"), type date},
{"Budget", Number.From, type number}
})
in
TypedResult
| Department | Period | Budget |
|---|---|---|
| Marketing | 2026-07-01 | 5000 |
| Marketing | 2026-08-01 | 5000 |
| Marketing | 2026-09-01 | 6000 |
| IT | 2026-07-01 | 8000 |
| IT | 2026-08-01 | 8000 |
| IT | 2026-09-01 | 8000 |
| Facilities | 2026-07-01 | 3000 |
| Facilities | 2026-08-01 | 3000 |
| Facilities | 2026-09-01 | 3000 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The result has nine rows, one per department and month, with Period as a real date on the first of each month. Turning the header text into a date matters: the actuals will be matched on that column, and text "2026-07" never equals a date. The unpivot guide covers the refresh-proof pattern, and the lesson on turning unpivoted headers into dates handles header formats such as "Jul 26".
Step 2: total the actuals to the same grain
The budget is per department per month, so the actuals must be too. Date.StartOfMonth returns "the start of the month" that contains a date (Date.StartOfMonth, Microsoft Learn), which gives a month key that matches the budget's Period. Then group by department and month.
let
Actuals = #table(
type table [Date = date, Department = text, Amount = number],
{
{#date(2026, 7, 3), "Marketing", 2100}, {#date(2026, 7, 21), "Marketing", 2650},
{#date(2026, 8, 12), "Marketing", 6400}, {#date(2026, 9, 9), "Marketing", 5200},
{#date(2026, 7, 1), "IT", 8000}, {#date(2026, 8, 1), "IT", 8000}, {#date(2026, 9, 1), "IT", 9150},
{#date(2026, 7, 15), "Facilities", 2900}, {#date(2026, 9, 30), "Facilities", 3400},
{#date(2026, 8, 18), "Legal", 1200}
}
),
WithPeriod = Table.AddColumn(Actuals, "Period", each Date.StartOfMonth([Date]), type date),
Grouped = Table.Group(WithPeriod, {"Department", "Period"}, {{"Actual", each List.Sum([Amount]), type number}})
in
GroupedResult
| Department | Period | Actual |
|---|---|---|
| Marketing | 2026-07-01 | 4750 |
| Marketing | 2026-08-01 | 6400 |
| Marketing | 2026-09-01 | 5200 |
| IT | 2026-07-01 | 8000 |
| IT | 2026-08-01 | 8000 |
| IT | 2026-09-01 | 9150 |
| Facilities | 2026-07-01 | 2900 |
| Facilities | 2026-09-01 | 3400 |
| Legal | 2026-08-01 | 1200 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The two July Marketing lines become one row of 4,750. Facilities has no August row, because nothing was posted, and Legal appears in August with 1,200. Grouping first is what keeps the merge one-to-one. If you merged raw transactions onto the budget, each budget row would repeat once per transaction and the budget would be counted several times. The lesson on predicting row counts after a merge explains how to spot that before it happens.
Step 3: merge with a full outer join
A full outer join keeps every row from both tables. Microsoft's description is that it "brings in all the rows from both the left and right tables" (Full outer join, Microsoft Learn). That is exactly what a variance report needs: budget lines with no spending (Facilities in August) and spending with no budget (Legal) both show up.
After a full outer join, the key columns from the left table are null on the rows that only exist on the right. The query below expands the right table's keys too, then takes whichever side is filled in. Before the Zeros step, the two unmatched rows look like this:
| Dept | Month | Budget | Actual |
|---|---|---|---|
| Facilities | 2026-08-01 | 3,000 | null |
| Legal | 2026-08-01 | null | 1,200 |
Table.ReplaceValue then turns those nulls into zeros so the subtraction works.
let
Budget = #table(
{"Department", "2026-07", "2026-08", "2026-09"},
{{"Marketing", 5000, 5000, 6000}, {"IT", 8000, 8000, 8000}, {"Facilities", 3000, 3000, 3000}}
),
Actuals = #table(
type table [Date = date, Department = text, Amount = number],
{
{#date(2026, 7, 3), "Marketing", 2100}, {#date(2026, 7, 21), "Marketing", 2650},
{#date(2026, 8, 12), "Marketing", 6400}, {#date(2026, 9, 9), "Marketing", 5200},
{#date(2026, 7, 1), "IT", 8000}, {#date(2026, 8, 1), "IT", 8000}, {#date(2026, 9, 1), "IT", 9150},
{#date(2026, 7, 15), "Facilities", 2900}, {#date(2026, 9, 30), "Facilities", 3400},
{#date(2026, 8, 18), "Legal", 1200}
}
),
BudgetLong = Table.TransformColumns(
Table.UnpivotOtherColumns(Budget, {"Department"}, "Period", "Budget"),
{{"Period", each Date.FromText(_ & "-01"), type date}, {"Budget", Number.From, type number}}),
ActualByMonth = Table.Group(
Table.AddColumn(Actuals, "Period", each Date.StartOfMonth([Date]), type date),
{"Department", "Period"}, {{"Actual", each List.Sum([Amount]), type number}}),
Joined = Table.NestedJoin(BudgetLong, {"Department", "Period"}, ActualByMonth, {"Department", "Period"}, "A", JoinKind.FullOuter),
Expanded = Table.ExpandTableColumn(Joined, "A", {"Department", "Period", "Actual"}, {"A.Department", "A.Period", "Actual"}),
Keys = Table.AddColumn(
Table.AddColumn(Expanded, "Dept", each if [Department] = null then [A.Department] else [Department], type text),
"Month", each if [Period] = null then [A.Period] else [Period], type date),
Clean = Table.SelectColumns(Keys, {"Dept", "Month", "Budget", "Actual"}),
Zeros = Table.ReplaceValue(Clean, null, 0, Replacer.ReplaceValue, {"Budget", "Actual"}),
Variance = Table.AddColumn(Zeros, "Variance", each [Actual] - [Budget], type number),
Pct = Table.AddColumn(Variance, "VariancePct", each if [Budget] = 0 then null else Number.Round([Variance] / [Budget], 3), type nullable number),
Sorted = Table.Sort(Pct, {{"Dept", Order.Ascending}, {"Month", Order.Ascending}})
in
SortedResult
| Dept | Month | Budget | Actual | Variance | VariancePct |
|---|---|---|---|---|---|
| Facilities | 2026-07-01 | 3000 | 2900 | -100 | -0.033 |
| Facilities | 2026-08-01 | 3000 | 0 | -3000 | -1 |
| Facilities | 2026-09-01 | 3000 | 3400 | 400 | 0.133 |
| IT | 2026-07-01 | 8000 | 8000 | 0 | 0 |
| IT | 2026-08-01 | 8000 | 8000 | 0 | 0 |
| IT | 2026-09-01 | 8000 | 9150 | 1150 | 0.144 |
| Legal | 2026-08-01 | 0 | 1200 | 1200 | null |
| Marketing | 2026-07-01 | 5000 | 4750 | -250 | -0.05 |
| Marketing | 2026-08-01 | 5000 | 6400 | 1400 | 0.28 |
| Marketing | 2026-09-01 | 6000 | 5200 | -800 | -0.133 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The result has ten rows: nine budget lines plus the Legal row. These are the lines worth reading:
| Dept | Month | Budget | Actual | Variance | VariancePct |
|---|---|---|---|---|---|
| Facilities | 2026-08-01 | 3,000 | 0 | -3,000 | -1 |
| IT | 2026-09-01 | 8,000 | 9,150 | 1,150 | 0.144 |
| Legal | 2026-08-01 | 0 | 1,200 | 1,200 | null |
| Marketing | 2026-08-01 | 5,000 | 6,400 | 1,400 | 0.28 |
| Marketing | 2026-09-01 | 6,000 | 5,200 | -800 | -0.133 |
With a left join on the budget, the Legal row would be missing and total actual spending would be 1,200 short of the ledger. The lesson on merging on composite keys explains the two-column key used here.
Why the merge uses two keys
Department alone would match every budget month to every actual month for that department. Both columns go in the key list, in the same order on both sides: {"Department", "Period"}. If your budget uses cost-centre codes and the ledger uses names, add a mapping table and merge on the code instead; matching on names breaks the first time someone renames a department.
Step 4: decide favourable or adverse
A positive variance is not always bad. Spending more than budget is adverse, but earning more income than budget is favourable. The query above uses Actual minus Budget, so the sign tells you which way actuals moved; the account type tells you whether that is good news.
let
Lines = #table(
type table [Department = text, Account = text, Kind = text, Budget = number, Actual = number],
{
{"Marketing", "Event income", "Income", 4000, 4600},
{"Marketing", "Venue hire", "Expense", 2500, 2900},
{"IT", "Licences", "Expense", 8000, 7600},
{"IT", "Support income", "Income", 1500, 1200}
}
),
Variance = Table.AddColumn(Lines, "Variance", each [Actual] - [Budget], type number),
Status = Table.AddColumn(Variance, "Status", each
if [Variance] = 0 then "On budget"
else if ([Kind] = "Income" and [Variance] > 0) or ([Kind] = "Expense" and [Variance] < 0) then "Favourable"
else "Adverse", type text)
in
StatusResult
| Department | Account | Kind | Budget | Actual | Variance | Status |
|---|---|---|---|---|---|---|
| Marketing | Event income | Income | 4000 | 4600 | 600 | Favourable |
| Marketing | Venue hire | Expense | 2500 | 2900 | 400 | Adverse |
| IT | Licences | Expense | 8000 | 7600 | -400 | Favourable |
| IT | Support income | Income | 1500 | 1200 | -300 | Adverse |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Event income came in 600 over budget (favourable) while venue hire was 400 over (adverse). Licences were 400 under (favourable) and support income 300 under (adverse). The brackets around each and pair aren't strictly needed, because and binds more tightly than or in M's operator precedence table (M language operators, Microsoft Learn), but they make the intent obvious. You do need brackets when an or sits inside an and, such as [Kind] = "Income" and ([A] > 0 or [B] > 0). The exercise on and/or conditions practises that grouping.
Where budget vs actual queries go wrong
Dividing by a zero budget
In many languages, dividing by zero raises an error. In M, number division follows IEEE 754 floating-point rules, and the language specification's examples show 8 / 0 returning #infinity and 0 / 0 returning #nan (M language operators, Microsoft Learn). The simulator agrees:
let
VariancePct = 1200 / 0
in
VariancePctResult
Infinity
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The result is infinity, not an error, so nothing fails at refresh. A negative variance over a zero budget gives negative infinity, and a line with zero budget and zero actual gives NaN. It only shows up later as a strange value in a chart or a total. That is why the variance percentage in Step 3 checks if [Budget] = 0 then null first, which covers all three cases. Null is honest: a percentage of nothing has no meaning.
Budget amounts stored as text
Planning spreadsheets often contain numbers typed with thousands separators, which Power Query reads as text. Subtracting from text fails:
let
Budget = #table({"Department", "Budget"}, {{"Marketing", "5,000"}}),
Variance = Table.AddColumn(Budget, "Variance", each 4750 - [Budget])
in
Variance{0}[Variance]Result: an error
Expression.Error: We cannot apply operator - to types Number and Text.
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The simulator returns We cannot apply operator - to types Number and Text. Convert the column with the right culture before the merge. The guide to data type pitfalls covers the fix.
Month keys that don't quite match
If the actuals carry datetimes (2026-07-03 14:20) and the budget has dates, the merge finds nothing, because a datetime never equals a date. Date.StartOfMonth keeps the type it was given, so convert with Date.From first when the ledger exports datetimes. The same applies to periods held as text in one table and dates in the other.
Reading the budget from the workbook
In a real file, the budget usually lives in an Excel table and the actuals in a ledger export. If the budget sits in a separate file, getting data from another Excel workbook covers the connection. Only the first step of each query changes:
let
Budget = Excel.CurrentWorkbook(){[Name = "Budget"]}[Content]
in
BudgetKeep that source step separate and reference it from the transform queries, as the lesson on staging and reuse shows. Then the source can change from a workbook table to a SharePoint file without touching the variance logic.
Practise it end to end
The project exercise budget versus actual by department and month asks for the same pattern on a school's budget, and unpivot only the quarter columns practises Step 1 on a messier sheet. On Power Query Exercises, both are graded against hidden data, so a query that only works for the sample rows will show up as a fail.
Frequently asked questions
Should the variance be Budget minus Actual or Actual minus Budget?
Either works if you are consistent and label it. Actual minus Budget is easier to read alongside the actuals: a positive number means actuals were higher. Then use the account type, as in Step 4, to say whether that is favourable.
Can I show year-to-date as well as the month?
Yes. Filter Month to the months up to the reporting month, then group the merged table by department. If the financial year doesn't start in January, add fiscal year and quarter columns and filter on those. For a month-by-month view, add a running total per department ordered by month instead.
Should I calculate the variance in Power Query or in DAX?
For a fixed monthly report in Excel, Power Query is enough. In Power BI, where users slice by department, period and account, load the budget and actuals as tables and calculate variances as measures so totals respond to the filters. The Power Query M vs DAX guide sets out where each calculation belongs, and the post on where each step belongs across SQL, Power Query and DAX adds the database.
Sources
- Table.UnpivotOtherColumns, Microsoft Learn (learn.microsoft.com)
- Date.StartOfMonth, Microsoft Learn (learn.microsoft.com)
- Full outer join, Microsoft Learn (learn.microsoft.com)
- M language operators, Microsoft Learn (learn.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- Allocate shared costs across departments with Power QueryAllocate rent, IT and other shared costs in Power Query: driver tables, department shares, a merge that spreads each cost, and rounding that ties out.
- Currency conversion with an exchange-rate table in Power QueryConvert foreign-currency amounts in Power Query: merge a monthly or daily rate table, take the last rate on or before each date, and round with intent.
- Build an aged receivables report with Power QueryBuild an aged debtors report in Power Query: days overdue from a fixed as-of date, ageing buckets from a table, part payments and one row per customer.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.