Now: Getting KeyTips ready for release
Nazmul HosenSay hello

Power Query · 30 Oct 2026

Replacing 11 query branches with one rules table

Every mapping change used to mean editing code. Moving the logic into an editable rules sheet produced one query that finance users can maintain themselves.

Case study 3 of 5. Names and figures are made up, and the code is a clean reconstruction. See all the lessons.

Expense journal data from an operational system has to become an import file for the accounting system, through a third-party import tool. Each transaction type maps to different accounts, classes and debit or credit sides. The old database workflow had to be replaced with Power Query.

What made it hard

  • Branch sprawl: the logic sat in 11 separate query branches, one per transaction type, so every mapping change meant editing code.
  • Messy types: dates arrived as day-first text, and some amounts were text with thousand separators.
  • A strict import format: the tool expects exact column names and order, and fails or mis-maps otherwise.
  • It has to balance: the file is only usable if total debits equal total credits.

Move the decisions into a sheet

Every mapping decision now lives in tbl_Rules, with one row per rule:

Priority TxnType AccountPrefix TargetAccount Class Side
1 SERVICE 51 5100 Service Cost Operations Dr
2 RENT 52 5200 Rent Expense Admin Dr
3 * 2 2100 Accrued Payables Head Office Cr

* matches any transaction type. Adding or changing a mapping is now a row edit, with no M involved.

The query

let
    Rules    = Table.Sort(Excel.CurrentWorkbook(){[Name="tbl_Rules"]}[Content],
                 {{"Priority", Order.Ascending}}),
    RuleList = List.Buffer(Table.ToRecords(Rules)),

    Src      = Excel.CurrentWorkbook(){[Name="tblJournal"]}[Content],

    // Fix types first: dd/mm/yyyy text dates, and amounts stored as text
    Dated    = Table.TransformColumns(Src,
                 {{"TxnDate", each if _ is date then _
                                   else Date.FromText(Text.From(_), [Culture="en-GB"]), type date},
                  {"Amount",  each try Number.From(Text.Remove(Text.From(_), {","}))
                                   otherwise null, type number}}),

    // The first matching rule wins
    Match    = (r as record) as nullable record =>
                 List.First(List.Select(RuleList, each
                   ([TxnType] = "*" or [TxnType] = r[TxnType]) and
                   Text.StartsWith(Text.From(r[SourceAccount]), Text.From([AccountPrefix]))), null),

    WithRule = Table.AddColumn(Dated, "Rule", each Match(_)),
    Mapped   = Table.SelectRows(WithRule, each [Rule] <> null and [Amount] <> null),
    Expanded = Table.ExpandRecordColumn(Mapped, "Rule", {"TargetAccount","Class","Side"}),
    DrCr     = Table.AddColumn(Expanded, "Debit",
                 each if [Side] = "Dr" then [Amount] else null, type number),
    Lines    = Table.AddColumn(DrCr, "Credit",
                 each if [Side] = "Cr" then [Amount] else null, type number),

    Gap      = List.Sum(Lines[Debit]) - List.Sum(Lines[Credit]),
    Final    = if Number.Abs(Gap) > 0.005 then error "Journal does not balance" else Lines
in
    Final

Three parts of this are doing the important work.

Types are fixed first. Everything after that step can trust the data. Amounts as text and misread dates caused errors and silent nulls, which is why this comes before any logic.

Lines that can’t be processed get set aside. A separate review query should use the same steps but keep rows where [Rule] = null or [Amount] = null. This matters: try ... otherwise null turns a bad amount into a null, and without a review list that line would silently vanish from the journal. Load that query next to the output, and check that it’s empty.

The balance check stops the refresh. If debits don’t equal credits, you get an error instead of a file that fails at import.

Things that bit

  • Import mapping: the import tool rejected or misplaced columns until the output headers and order matched its template exactly. Build the final Table.SelectColumns from the template, not from memory.
  • Speed: matching rules row by row can slow down on large files. Buffering the list with List.Buffer, as above, usually helps.

The result

One query replaced 11 branches and produced a complete import file of several hundred lines, with debits equal to credits. The bigger gain is ownership: the people who know the mappings can now change them.

← All tips