Build an aged receivables report with Power Query
Use case · By Tahir Nas · Examples run in our simulator · Updated
12 min read
An aged receivables report in Power Query takes a list of open invoices, works out how many days each one is past its due date on a chosen as-of date, puts each invoice into an ageing bucket, and then pivots the buckets into columns with one row per customer. The core is four steps: subtract dates, look up a bucket, pivot with a sum, and add a total, plus one more if your export lists invoices at their original value.
What the finished report looks like
The aged debtors report (also called an aged receivables or AR ageing report) has one row per customer and one column per ageing bucket. The columns usually run Current, 1-30, 31-60, 61-90 and 90+ days overdue, followed by a total that should agree to the receivables balance in the ledger.
The input is the open-items list your accounting system exports: customer, invoice number, due date and the amount still outstanding. The examples below use six invoices for three customers and an as-of date of 31 October 2026. This is the finished report they produce:
| Customer | Current | 1-30 | 31-60 | 61-90 | 90+ | Total |
|---|---|---|---|---|---|---|
| Alder Ltd | 1,200 | 450 | 0 | 0 | 0 | 1,650 |
| Birch & Co | 0 | 0 | 980 | 0 | 300 | 1,280 |
| Cedar plc | 0 | 2,100 | 0 | 0 | 640 | 2,740 |
| Total | 1,200 | 2,550 | 980 | 0 | 940 | 5,670 |
If your export lists invoices at their original value with receipts in a separate table, start with the section on netting off part payments and then come back to Step 1.
Step 1: work out days overdue and the bucket
Subtracting one date from another in M returns a duration. Duration.Days takes the days part of that duration, which Microsoft documents as "the days portion" of the value (Duration.Days, Microsoft Learn). The as-of date is a step of its own, so it can later come from a parameter, such as an Excel cell used as a Power Query parameter.
let
AsOf = #date(2026, 10, 31),
Invoices = #table(
type table [Customer = text, Invoice = text, DueDate = date, Amount = number],
{
{"Alder Ltd", "INV-101", #date(2026, 11, 14), 1200},
{"Alder Ltd", "INV-087", #date(2026, 10, 10), 450},
{"Birch & Co", "INV-064", #date(2026, 9, 20), 980},
{"Birch & Co", "INV-031", #date(2026, 7, 15), 300},
{"Cedar plc", "INV-092", #date(2026, 10, 25), 2100},
{"Cedar plc", "INV-012", #date(2026, 5, 2), 640}
}
),
WithDays = Table.AddColumn(Invoices, "DaysOverdue", each Duration.Days(AsOf - [DueDate]), Int64.Type),
WithBucket = Table.AddColumn(WithDays, "Bucket", each
if [DaysOverdue] <= 0 then "Current"
else if [DaysOverdue] <= 30 then "1-30"
else if [DaysOverdue] <= 60 then "31-60"
else if [DaysOverdue] <= 90 then "61-90"
else "90+", type text)
in
WithBucketResult
| Customer | Invoice | DueDate | Amount | DaysOverdue | Bucket |
|---|---|---|---|---|---|
| Alder Ltd | INV-101 | 2026-11-14 | 1200 | -14 | Current |
| Alder Ltd | INV-087 | 2026-10-10 | 450 | 21 | 1-30 |
| Birch & Co | INV-064 | 2026-09-20 | 980 | 41 | 31-60 |
| Birch & Co | INV-031 | 2026-07-15 | 300 | 108 | 90+ |
| Cedar plc | INV-092 | 2026-10-25 | 2100 | 6 | 1-30 |
| Cedar plc | INV-012 | 2026-05-02 | 640 | 182 | 90+ |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
INV-101 is due on 14 November, so it shows -14 days and lands in Current. INV-012 is 182 days overdue and lands in 90+. The order of the if tests matters: each test only runs when the ones above it failed, which is why <= 30 safely means "1 to 30" here. The lesson on date parts and durations covers date subtraction in more depth.
Age from the due date or the invoice date?
Most aged debtors reports age from the due date, because "overdue" is what credit control acts on. Some businesses age from the invoice date instead, which shows how old the debt is regardless of terms. The formula is the same; only the column changes. Pick one, name the report columns to match ("days past due" or "days since invoice"), and keep it consistent between months.
Step 2: keep the buckets in a table
The nested if works, but the boundaries are buried in code. When someone asks for a 91-120 bucket, you have to edit the logic and hope nothing else breaks. A bucket table holds each bucket's first day, and each invoice takes the last bucket whose first day is not greater than its days overdue.
The same table also feeds the pivot. Table.Pivot takes the list of values that become columns as its second argument (Table.Pivot, Microsoft Learn). Passing Buckets[Bucket] there means the 61-90 column exists even though no invoice falls in it this month, and the columns come out in bucket order rather than in the order each bucket first appears in the data.
let
AsOf = #date(2026, 10, 31),
Buckets = #table(
type table [FromDay = number, Bucket = text],
{{-99999, "Current"}, {1, "1-30"}, {31, "31-60"}, {61, "61-90"}, {91, "90+"}}
),
Invoices = #table(
type table [Customer = text, Invoice = text, DueDate = date, Amount = number],
{
{"Alder Ltd", "INV-101", #date(2026, 11, 14), 1200},
{"Alder Ltd", "INV-087", #date(2026, 10, 10), 450},
{"Birch & Co", "INV-064", #date(2026, 9, 20), 980},
{"Birch & Co", "INV-031", #date(2026, 7, 15), 300},
{"Cedar plc", "INV-092", #date(2026, 10, 25), 2100},
{"Cedar plc", "INV-012", #date(2026, 5, 2), 640}
}
),
WithDays = Table.AddColumn(Invoices, "DaysOverdue", each Duration.Days(AsOf - [DueDate]), Int64.Type),
WithBucket = Table.AddColumn(WithDays, "Bucket", (row) =>
List.Last(Table.SelectRows(Buckets, each [FromDay] <= row[DaysOverdue])[Bucket]), type text),
Slim = Table.SelectColumns(WithBucket, {"Customer", "Bucket", "Amount"}),
Pivoted = Table.Pivot(Slim, Buckets[Bucket], "Bucket", "Amount", List.Sum),
Zeros = Table.ReplaceValue(Pivoted, null, 0, Replacer.ReplaceValue, Buckets[Bucket]),
Total = Table.AddColumn(Zeros, "Total", each List.Sum(Record.FieldValues(Record.SelectFields(_, Buckets[Bucket]))), type number)
in
TotalResult
| Customer | Current | 1-30 | 31-60 | 61-90 | 90+ | Total |
|---|---|---|---|---|---|---|
| Alder Ltd | 1200 | 450 | 0 | 0 | 0 | 1650 |
| Birch & Co | 0 | 0 | 980 | 0 | 300 | 1280 |
| Cedar plc | 0 | 2100 | 0 | 0 | 640 | 2740 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The result has one row per customer. Alder Ltd owes 1,650 (1,200 current and 450 in 1-30), Birch & Co owes 1,280 (980 in 31-60 and 300 in 90+), and Cedar plc owes 2,740 (2,100 in 1-30 and 640 in 90+).
A few details in that query are worth knowing:
- The bucket column uses
(row) => ...instead ofeach, because the innerTable.SelectRowshas its owneach. Naming the outer row avoids confusing the two_values. - The bucket table must be sorted by
FromDay, becauseList.Lasttakes the last matching row. Slimkeeps only the customer, bucket and amount. Any other column left in the table (such as Invoice) would become part of the row key, and the pivot would give one row per invoice instead of one per customer.Table.PivotwithList.Sumadds up several invoices for the same customer and bucket. Without an aggregation function, two invoices in the same cell raise the "too many elements in the enumeration" error that Microsoft describes for the Don't aggregate option (Pivot columns, Microsoft Learn).- Empty cells come out of the pivot as null.
Table.ReplaceValuewithReplacer.ReplaceValueturns them into zeros in just the bucket columns (Table.ReplaceValue, Microsoft Learn).
The mapping tables vs nested if guide explains why a lookup table stays easier to maintain than a chain of conditions, and the pivot with aggregation lesson walks through the pivot step on its own.
Step 3: add a grand total row
Readers of an aged debtors report usually want a total line at the bottom. You can add it in the report layer (a PivotTable or a Power BI matrix does this for you), or add a row in Power Query when the output is a plain table.
let
Report = #table(
{"Customer", "Current", "1-30", "31-60", "61-90", "90+", "Total"},
{
{"Alder Ltd", 1200, 450, 0, 0, 0, 1650},
{"Birch & Co", 0, 0, 980, 0, 300, 1280},
{"Cedar plc", 0, 2100, 0, 0, 640, 2740}
}
),
AmountColumns = List.Skip(Table.ColumnNames(Report), 1),
Totals = Record.FromList({"Total"} & List.Transform(AmountColumns, each List.Sum(Table.Column(Report, _))), Table.ColumnNames(Report)),
WithTotal = Table.InsertRows(Report, Table.RowCount(Report), {Totals})
in
WithTotalResult
| Customer | Current | 1-30 | 31-60 | 61-90 | 90+ | Total |
|---|---|---|---|---|---|---|
| Alder Ltd | 1200 | 450 | 0 | 0 | 0 | 1650 |
| Birch & Co | 0 | 0 | 980 | 0 | 300 | 1280 |
| Cedar plc | 0 | 2100 | 0 | 0 | 640 | 2740 |
| Total | 1200 | 2550 | 980 | 0 | 940 | 5670 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The total row reads 1,200 current, 2,550 in 1-30, 980 in 31-60, 0 in 61-90 and 940 in 90+, which adds up to 5,670. That figure is the check: it should match the receivables control account at the as-of date. If it doesn't, the open-items export and the ledger disagree, and the guide to reconciling two tables shows how to find the invoices that differ.
If your export shows original amounts: net off receipts first
Many exports list invoices at their original value with receipts in a separate table. If you age the original amounts, a customer who has paid most of an old invoice still looks badly overdue. Group the receipts per invoice, merge them onto the invoices with a left outer join, and keep only what is still open.
let
Invoices = #table(
type table [Invoice = text, Original = number],
{{"INV-087", 450}, {"INV-064", 1680}, {"INV-031", 500}, {"INV-045", 600}}
),
Receipts = #table(
type table [Invoice = text, Paid = number],
{{"INV-064", 500}, {"INV-064", 200}, {"INV-031", 200}, {"INV-045", 600}}
),
PaidByInvoice = Table.Group(Receipts, {"Invoice"}, {{"Paid", each List.Sum([Paid]), type number}}),
Joined = Table.NestedJoin(Invoices, {"Invoice"}, PaidByInvoice, {"Invoice"}, "R", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Joined, "R", {"Paid"}),
WithAmount = Table.AddColumn(Expanded, "Amount", each [Original] - (if [Paid] = null then 0 else [Paid]), type number),
StillOpen = Table.SelectRows(WithAmount, each [Amount] <> 0)
in
StillOpenResult
| Invoice | Original | Paid | Amount |
|---|---|---|---|
| INV-087 | 450 | null | 450 |
| INV-064 | 1680 | 700 | 980 |
| INV-031 | 500 | 200 | 300 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
INV-064 was raised for 1,680 and has two receipts totalling 700, so 980 is still open. INV-031 has 300 left after a 200 receipt. INV-087 has no receipt, so Paid is null and the full 450 stays open. INV-045 was paid in full and drops out. The open amounts match the ones used in Step 1, so the Amount column from StillOpen is what feeds the WithDays step (keep Customer and DueDate from your export alongside it).
Grouping before the merge matters: merging the raw receipts would give INV-064 two rows and count its amount twice. The lesson on predicting row counts after a merge explains why. Table.NestedJoin performs a left outer join when no join kind is given (Table.NestedJoin, Microsoft Learn); writing JoinKind.LeftOuter anyway makes the intent clear to the next reader.
Credit notes and unallocated cash are a business decision rather than a Power Query one. Some teams show them as a negative amount in Current; others age them like invoices. Whichever you choose, the report total should still agree to the ledger.
Where aged debtors queries go wrong
Due dates arrive as text
CSV exports often bring dates through as text. Subtracting text from a date fails, and the error names the types:
let
AsOf = #date(2026, 10, 31),
Invoices = #table({"Invoice", "DueDate"}, {{"INV-087", "10/10/2026"}}),
WithDays = Table.AddColumn(Invoices, "DaysOverdue", each Duration.Days(AsOf - [DueDate]))
in
WithDays{0}[DaysOverdue]Result: an error
Expression.Error: We cannot apply operator - to types Date and Text.
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The simulator returns We cannot apply operator - to types Date and Text. The fix is to convert the column with the right locale before ageing, because "10/10/2026" reads the same either way but "03/04/2026" does not. The guide to changing type using locale covers this.
The as-of date moves on its own
It is tempting to use today's date so the report is always current:
let
AsOf = Date.From(DateTime.LocalNow())
in
AsOfThat works in Excel and Power BI, but the same file gives different buckets on different days, so last month's report can't be reproduced. The value also depends on where the query runs: Microsoft notes that Power Query Online returns UTC time because cloud machines are set to UTC (DateTime.LocalNow, Microsoft Learn), a trap covered in time zones and DateTimeZone. For month-end reporting, keep the as-of date in a one-cell parameter table or a query parameter and set it to the period end. The month starts, month ends and period keys lesson shows how to derive month ends with Date.EndOfMonth.
Boundaries that overlap or leave gaps
With an if chain, writing < 30 in one test and >= 31 in the next leaves day 30 unassigned, and in a chain of range tests it falls through to the final else. The bucket table avoids this because every whole number of days maps to exactly one row. Still, add a quick check after the pivot: the sum of the Total column should equal the sum of the open amounts before the pivot. The lesson on validating data and failing loudly shows how to raise an error when a check like that fails.
Practise the steps
The individual skills in this report each have a matching exercise. Calculate reimbursement dates practises date arithmetic, and pivot categories and add a total that includes future columns practises the pivot-and-total pattern used above. On Power Query Exercises, each of those exercises is checked against hidden test data, so you can see whether your query still works when the rows change.
Frequently asked questions
Should "Current" include invoices due today?
In the queries above, an invoice due on the as-of date has 0 days overdue and lands in Current. If your credit policy treats the due date itself as overdue, change the "1-30" row's FromDay from 1 to 0 and relabel it "0-30". In the if chain, the equivalent change is <= 0 to < 0 in the first test, with the second test's label changed to "0-30" as well. Write the rule down next to the bucket table so the next person knows which convention you used.
Can I age by customer and by currency?
Yes. Keep the currency column in the Slim step, and the pivot will give one row per customer and currency. Convert to a single reporting currency first, as in currency conversion with an exchange-rate table, if you need one total; the receivables balance in each currency should still agree to the ledger.
Why use Power Query rather than formulas for the aged debtors report?
Formulas work for a fixed list, but the open-items export changes every month. A query applies the same steps to whatever rows arrive, and the bucket table and as-of date are the only things you edit. Each step is also visible in Applied Steps, which makes the report easier to review.
Sources
- Duration.Days, Microsoft Learn (learn.microsoft.com)
- Table.Pivot, Microsoft Learn (learn.microsoft.com)
- Pivot columns, Microsoft Learn (learn.microsoft.com)
- Table.ReplaceValue, Microsoft Learn (learn.microsoft.com)
- Table.NestedJoin, Microsoft Learn (learn.microsoft.com)
- DateTime.LocalNow, Microsoft Learn (learn.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- Allocate shared costs across departments with Power QueryAllocate rent, IT and other shared costs in Power Query: driver tables, department shares, a merge that spreads each cost, and rounding that ties out.
- Budget vs actual reporting with Power QueryBuild a budget vs actual report in Power Query: unpivot a wide budget, total actuals by month, full outer join both sides, then label variances correctly.
- Build a monthly HR headcount report with Power QueryTurn an employee list with start and leave dates into month-end headcount, FTE by department, and starters and leavers that reconcile, using Power Query.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.