Case study 2 of 5. Names and figures are made up, and the code is a clean reconstruction. See all the lessons.
A monthly direct-cost redistribution journal is built from three sheets: a pivot summary and two cost-model sheets. The goal was for Power Query to produce the journal sheet each month.
What made it hard
- No tables allowed: the source sheets couldn’t be converted to Excel tables, so the usual source wasn’t available.
- Moving ranges: the number of rows changes every month.
- Fixed credits: credit amounts must never change, so any rounding gap can only be absorbed by debits.
Step 1: A dynamic named range
Power Query can read workbook-level names as well as tables. Define one name per sheet, using INDEX rather than the volatile OFFSET:
ModelA_Data = 'Model A'!$A$1:INDEX('Model A'!$F:$F, COUNTA('Model A'!$A:$A))The range ends at the row given by COUNTA, so it grows with the data. This assumes column A has no gaps, because a blank cell there would cut off the last row.
If a name doesn’t appear in Excel.CurrentWorkbook(), check that it’s scoped to the workbook rather than to a sheet.
Step 2: Read each name with one function
let
GetRange = (n as text) as table =>
Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name=n]}[Content],
[PromoteAllScalars=true]),
Shape = (t as table) as table =>
let
Kept = Table.SelectColumns(t, {"Account","Class","Debit","Credit"}, MissingField.UseNull),
Typed = Table.TransformColumnTypes(Kept,
{{"Account", type text}, {"Class", type text},
{"Debit", type number}, {"Credit", type number}})
in
Table.SelectRows(Typed, each [Account] <> null and [Account] <> ""),
Journal = Table.Combine({Shape(GetRange("Pivot_Data")),
Shape(GetRange("ModelA_Data")),
Shape(GetRange("ModelB_Data"))}),
Zeroed = Table.ReplaceValue(Journal, null, 0, Replacer.ReplaceValue, {"Debit","Credit"}),
Rounded = Table.TransformColumns(Zeroed,
{{"Debit", each Number.Round(_, 2), type number},
{"Credit", each Number.Round(_, 2), type number}}),MissingField.UseNull means a sheet that has no Credit column gets an empty one, instead of failing the refresh.
Step 3: Plug the cents, with a guard
Measure the gap, then add it to the largest debit line only:
Gap = Number.Round(List.Sum(Rounded[Credit]) - List.Sum(Rounded[Debit]), 2),
Checked = if Number.Abs(Gap) > 0.05
then error "Debits and credits differ by more than rounding"
else Rounded,
Indexed = Table.AddIndexColumn(Checked, "i", 0, 1),
PlugRow = Table.Max(Indexed, "Debit")[i],
Balanced = Table.ReplaceValue(Indexed,
each [i] = PlugRow, null,
(val, isPlug, _) => if isPlug then Number.Round(val + Gap, 2) else val,
{"Debit"}),
Final = Table.RemoveColumns(Balanced, {"i"})
in
Final| Check | Before plug | After plug |
|---|---|---|
| Total credits | 125,400.00 | 125,400.00 |
| Total debits | 125,399.98 | 125,400.00 |
| Largest debit line | 48,210.15 | 48,210.17 |
| Difference | 0.02 | 0.00 |
Why the guard matters
A plug will balance anything. Without a limit, a missing line worth 5,000 would quietly land on your largest debit and be labelled rounding.
The error line stops the refresh when the gap is bigger than cents could explain. Set the limit to suit the size of your journal: it should be large enough for genuine rounding and small enough to catch a real problem.
The design in one line
Each constraint set a design choice: no tables led to named ranges, and fixed credits led to a debit-only plug. Writing the constraints down first made both decisions straightforward.