Spreadsheets

Merge queries in Power Query to put budget beside actual spend

Merge queries in Power Query to put each category's budget next to actual spend, add a variance column, and pick the right join kind from one short table.

Editorial illustration for Merge queries in Power Query to put budget beside actual spend

To show budget beside actual spend, load two queries (monthly Actuals and a Budget table) and merge them in Power Query. Pick Category in both, choose the Left Outer join so every actual row stays, expand the Budget column, then add a custom column for Budget minus Actual. The result updates whenever you refresh.

What should the two tables look like?

QueryColumnsSample row
ActualsCategory, ActualGroceries, 19300
BudgetCategory, BudgetGroceries, 20000

Both come from Excel tables (select the data and press Ctrl+T), then Data, From Table/Range. The Category text must be written the same way in both, since the merge compares exactly and does not ignore capitals. Groceries and groceries do not match, and neither does Groceries with a trailing space. Use Transform, Format, Trim on the Category column in each query first.

How do I merge them step by step?

  1. Open the Actuals query in the Power Query Editor.
  2. Choose Home, Merge Queries, Merge Queries (not "as New", unless you want a separate query).
  3. In the dialog, pick Category in the top table, choose Budget in the second dropdown, and click Category there too. Both columns turn highlighted.
  4. Set Join Kind to Left Outer (all from first, matching from second).
  5. Click OK. A new column appears, full of tables. Click the expand arrow at the top, tick only Budget, untick "Use original column name as prefix", then OK.
  6. Set the Budget column type to Decimal Number or Fixed Decimal Number.

Excel records the same steps in M, which you can see in the formula bar, so the merge repeats on each refresh.

Which join kind should I pick?

Join kindKeepsUse it when
Left OuterAll Actuals rows, matching BudgetYou want every spend category, budget or not
Right OuterAll Budget rows, matching ActualsYou want every budget line, even with no spend
Full OuterEverything from bothYou want both gaps visible
InnerOnly categories in bothYou want a tidy comparison and accept losing the rest
Left AntiActuals with no BudgetYou want a list of unbudgeted spending

For a budget review, Full Outer is the most honest: a budgeted category with no spend and a spent category with no budget both show up. Left Outer is the usual starting point.

How do I add the variance?

Go to Add Column, Custom Column, name it Variance and use:

  • [Budget] - [Actual]

Using the sample row, 20000 minus 19300 gives 700, which means ₹700 left. A negative number means overspending. For categories with no budget, Budget is null and the result is null as well. If you prefer 0, first use Transform, Replace Values to change null to 0 in the Budget column. In M the step reads Table.AddColumn(PreviousStep, "Variance", each [Budget] - [Actual], type number).

What if the names do not line up?

  • Use Merge with a fuzzy match only as a last resort; it can pair the wrong names without telling you.
  • Build a small mapping table that translates your statement categories to budget categories, and merge through it.
  • Merge on two columns, Category and Month, by holding Ctrl and clicking each in both tables, in the same order.

Where does this fit?

This view suits a tidy monthly review, with a budget you set once and actuals you refresh. If you would rather track budgets inside an app, SpendixAI has budgets, bills and savings goals on its Pro plan, alongside the spending reports.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports