Power QueryExercises

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
    Typed

Result

DepartmentPeriodBudget
Marketing2026-07-015000
Marketing2026-08-015000
Marketing2026-09-016000
IT2026-07-018000
IT2026-08-018000
IT2026-09-018000
Facilities2026-07-013000
Facilities2026-08-013000
Facilities2026-09-013000

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
    Grouped

Result

DepartmentPeriodActual
Marketing2026-07-014750
Marketing2026-08-016400
Marketing2026-09-015200
IT2026-07-018000
IT2026-08-018000
IT2026-09-019150
Facilities2026-07-012900
Facilities2026-09-013400
Legal2026-08-011200

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:

DeptMonthBudgetActual
Facilities2026-08-013,000null
Legal2026-08-01null1,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
    Sorted

Result

DeptMonthBudgetActualVarianceVariancePct
Facilities2026-07-0130002900-100-0.033
Facilities2026-08-0130000-3000-1
Facilities2026-09-01300034004000.133
IT2026-07-018000800000
IT2026-08-018000800000
IT2026-09-018000915011500.144
Legal2026-08-01012001200null
Marketing2026-07-0150004750-250-0.05
Marketing2026-08-015000640014000.28
Marketing2026-09-0160005200-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:

DeptMonthBudgetActualVarianceVariancePct
Facilities2026-08-013,0000-3,000-1
IT2026-09-018,0009,1501,1500.144
Legal2026-08-0101,2001,200null
Marketing2026-08-015,0006,4001,4000.28
Marketing2026-09-016,0005,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
    Status

Result

DepartmentAccountKindBudgetActualVarianceStatus
MarketingEvent incomeIncome40004600600Favourable
MarketingVenue hireExpense25002900400Adverse
ITLicencesExpense80007600-400Favourable
ITSupport incomeIncome15001200-300Adverse

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
    VariancePct

Result

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
    Budget
Shown for Excel and Power BI Desktop only: this code needs a data source or language feature the simulator doesn't run, so no output is shown.

Keep 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

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

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.