Now: Getting KeyTips ready for release
Nazmul HosenSay hello

Power Query · 30 Oct 2026

Rebuilding a database P&L crosstab in Power Query

A 16-column monthly P&L over about 300,000 ledger rows. Step order makes the refresh workable, and one Table.Pivot argument keeps the layout from breaking.

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

The monthly P&L came from a crosstab query in a desktop database, with accounts down the side and months across the top. That database workflow was being retired, so the same output had to come from Excel and Power Query.

What made it hard

  • Volume: with roughly 300,000 ledger rows, a careless step order makes refreshes very slow.
  • Matching the old logic: the new output had to agree with the old SQL, including sign conventions and account grouping.
  • Fixed layout: the report needs 16 columns in a set order (group, sub-group, account, 12 months, total), even when a month has no postings.
  • Dates: posting dates had to become reliable month keys, however they were stored.

The query

let
    FYStart = #date(2025, 7, 1),
    MonthKey = (d as date) as text => Date.ToText(d, [Format="MMM-yy", Culture="en-US"]),

    Ledger  = Excel.CurrentWorkbook(){[Name="tblGL"]}[Content],
    Slim    = Table.SelectColumns(Ledger, {"PostDate","Account","Amount"}),
    Typed   = Table.TransformColumnTypes(Slim,
                {{"PostDate", type date}, {"Account", type text}, {"Amount", type number}}),
    FY      = Table.SelectRows(Typed, each [PostDate] >= FYStart
                                       and [PostDate] < Date.AddMonths(FYStart, 12)),
    WithM   = Table.AddColumn(FY, "Mon", each MonthKey([PostDate]), type text),

    // Chart of accounts: Account | Group | SubGroup | Sign
    COA     = Table.Buffer(Excel.CurrentWorkbook(){[Name="tblCOA"]}[Content]),
    Joined  = Table.NestedJoin(WithM, "Account", COA, "Account", "c", JoinKind.LeftOuter),
    Exp     = Table.ExpandTableColumn(Joined, "c", {"Group","SubGroup","Sign"}),
    Signed  = Table.AddColumn(Exp, "Amt",
                each [Amount] * (if [Sign] = null then 1 else [Sign]), type number),

    Summed  = Table.Group(Signed, {"Group","SubGroup","Account","Mon"},
                {{"Amt", each List.Sum([Amt]), type number}}),

    Months  = List.Transform({0..11}, each MonthKey(Date.AddMonths(FYStart, _))),
    Pivoted = Table.Pivot(Summed, Months, "Mon", "Amt", List.Sum),
    Filled  = Table.ReplaceValue(Pivoted, null, 0, Replacer.ReplaceValue, Months),
    Total   = Table.AddColumn(Filled, "Total",
                each List.Sum(Record.ToList(Record.SelectFields(_, Months))), type number),
    Ordered = Table.SelectColumns(Total, {"Group","SubGroup","Account"} & Months & {"Total"})
in
    Ordered

Why the order matters

At this volume, each step should hand less data to the next one:

  1. Trim columns so you only carry three of them.
  2. Filter to the period before any merge.
  3. Buffer the small lookup table so it isn’t read again and again.
  4. Group before pivoting. Pivoting data that’s already summed to account and month is far faster than pivoting the raw ledger.

The argument that protects the layout

Most examples build the pivot’s column list from the data. That means a month with no postings has no column, and the report layout shifts.

Here, Months is generated from the year start, so all 12 always exist:

Months  = List.Transform({0..11}, each MonthKey(Date.AddMonths(FYStart, _))),
Pivoted = Table.Pivot(Summed, Months, "Mon", "Amt", List.Sum),

Column order comes from the same list, so a quiet month shows zeros and the 16 columns stay in place. Change FYStart and the whole report rolls forward.

Group SubGroup Account Jul-25 Aug-25 Sep-25 … Total
Revenue Services 4100 Service Income 820,000 835,500 841,200 … 10,050,000
Direct Cost Operations 5100 Service Cost 310,000 312,400 309,800 … 3,725,000
Admin Premises 6200 Rent Expense 45,000 45,000 45,000 … 540,000

Two checks before you retire the old query

  • Look for unmapped accounts. A new account missing from tblCOA gets a null group and a sign of 1, and it still lands in the report. Add a small query that lists rows where [Group] = null, and check that it’s empty.
  • Compare against the old output. Reconcile by account and month, not just the bottom line. A sign error on two offsetting accounts can leave the total looking right.

The problems along the way

Type coercion, slow refreshes and date handling all caused trouble during the build. All three were fixed by the same method: set types once at the start, shrink early, and generate month keys in one place.

← All tips