Case study 1 of 5. Names and figures are made up, and the code is a clean reconstruction. See all the lessons.
Each month, shared overhead costs are spread across cost centres using allocation percentages. That sheet then has to become a journal upload in the accounting system’s import format. The goal was to build the upload directly from the allocation sheet, with no re-keying.
What made it hard
- Shape: the source is wide, with one column per cost centre. The upload is long, with one row per debit or credit line.
- Rounding: splitting a balance by percentages creates fractions of a cent.
- Totals that didn’t tie: output debits differed from the source Line Balance total, and that gap had to be explained.
The approach
I split the work into small queries, because a later query can reference an earlier one but can’t reach inside its steps.
Clean loads the source, sets types and drops blank or zero lines:
let
Src = Excel.CurrentWorkbook(){[Name="tblAlloc"]}[Content],
Typed = Table.TransformColumnTypes(Src,
{{"Account", type text}, {"Memo", type text}, {"Line Balance", type number}})
in
Table.SelectRows(Typed, each [Line Balance] <> null and [Line Balance] <> 0)DrLines unpivots the cost-centre columns into rows and calculates a rounded debit for each:
let
Long = Table.UnpivotOtherColumns(Clean, {"Account","Memo","Line Balance"}, "Class", "Pct"),
Debits = Table.AddColumn(Long, "Debit",
each Number.Round([Line Balance] * [Pct], 2, RoundingMode.AwayFromZero), type number)
in
Table.SelectColumns(Debits, {"Account","Class","Memo","Debit"})Upload adds one credit line per source line and stacks them with the debits:
let
CrLines = Table.FromRecords(
List.Transform(Table.ToRecords(Clean), each
[Account = [Account], Class = "Head Office", Memo = [Memo],
Credit = [Line Balance]]))
in
Table.Combine({DrLines, CrLines})Recon compares source and output for each account, and returns only the accounts that disagree:
let
SrcTot = Table.Group(Clean, {"Account"},
{{"Source", each List.Sum([Line Balance]), type number}}),
OutTot = Table.Group(DrLines, {"Account"},
{{"Output", each List.Sum([Debit]), type number}}),
Joined = Table.NestedJoin(SrcTot, "Account", OutTot, "Account", "O", JoinKind.FullOuter),
Expand = Table.ExpandTableColumn(Joined, "O", {"Output"}),
Diff = Table.AddColumn(Expand, "Diff",
each (if [Source] = null then 0 else [Source]) -
(if [Output] = null then 0 else [Output]), type number)
in
Table.SelectRows(Diff, each Number.Abs([Diff]) > 0.005)Reading the reconciliation
| Account | Source | Output | Diff |
|---|---|---|---|
| Office Rent | 1,000.00 | 999.99 | 0.01 |
| Utilities | 2,450.00 | 2,450.00 | 0.00 |
| Software Licences | 600.00 | 540.00 | 60.00 |
With the filter removed, you can see each account like this, and the two gaps have very different causes:
- Office Rent is rounding. A 1,000.00 line split three ways at 33.333% gives 333.33 × 3 = 999.99.
- Software Licences is a setup error. Its percentages only add up to 90%.
A grand-total check would show one number, 60.01, and you’d have no idea where to start. By account, each gap points to its cause. The usual suspects are rounding, rows filtered out as null or zero, and percentages that don’t add up to 100%.
Watch the rounding mode
By default, Number.Round rounds halves to the nearest even number, so 0.125 becomes 0.12. Excel’s ROUND gives 0.13. If your figures need to agree with a worksheet, pass RoundingMode.AwayFromZero, as above.
What this doesn’t do
This reconciliation explains a gap, but it doesn’t close it. A rounding gap like Office Rent’s still leaves the journal out of balance by a cent, so you need a rule for where those cents go. That’s the subject of the next case.