Power Query for finance: month-end recipes
Recipes · By Tahir Nas · Examples run in our simulator · Published · Updated
1. Signed amounts
Many systems store credit notes as positive amounts with a type column. Make the sign explicit once, and total the signed column everywhere:
let
Docs = #table({"Doc", "Type", "Amount"}, {{"I-1", "Invoice", 500}, {"C-1", "Credit", 120}, {"I-2", "Invoice", 80}}),
Signed = Table.AddColumn(Docs, "Signed", each if [Type] = "Credit" then -[Amount] else [Amount], type number)
in
List.Sum(Signed[Signed])Result
460
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
2. Aged receivables from a fixed report date
Age from a report date parameter, not from today, so last month's ageing can be reproduced exactly:
let
ReportDate = #date(2026, 10, 31),
Open = #table({"Invoice", "Customer", "Due", "Amount"}, {
{"I-1", "C1", #date(2026, 10, 20), 300},
{"I-2", "C1", #date(2026, 9, 10), 150},
{"I-3", "C2", #date(2026, 7, 1), 90},
{"I-4", "C2", #date(2026, 11, 15), 60}
}),
Days = Table.AddColumn(Open, "Days overdue", each Duration.Days(ReportDate - [Due]), Int64.Type),
Bucket = Table.AddColumn(Days, "Bucket", each if [Days overdue] <= 0 then "Not due" else if [Days overdue] <= 30 then "1-30" else if [Days overdue] <= 60 then "31-60" else "60+", type text),
Aged = Table.Group(Bucket, {"Bucket"}, {{"Amount", each List.Sum([Amount]), type number}})
in
AgedResult
| Bucket | Amount |
|---|---|
| 1-30 | 300 |
| 31-60 | 150 |
| 60+ | 90 |
| Not due | 60 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
3. Currency conversion by month
Merge a monthly rate table on currency and month, then multiply and round per policy. An anti join on the same keys finds missing rates before they become nulls in a total:
let
Tx = #table({"Tx", "Date", "Currency", "Amount"}, {{"T1", #date(2026, 10, 3), "EUR", 100}, {"T2", #date(2026, 10, 20), "USD", 50}, {"T3", #date(2026, 11, 2), "EUR", 40}}),
Rates = #table({"Month", "Currency", "ToGBP"}, {{#date(2026, 10, 1), "EUR", 0.87}, {#date(2026, 10, 1), "USD", 0.79}}),
WithMonth = Table.AddColumn(Tx, "Month", each Date.StartOfMonth([Date]), type date),
Joined = Table.ExpandTableColumn(Table.NestedJoin(WithMonth, {"Month", "Currency"}, Rates, {"Month", "Currency"}, "R", JoinKind.LeftOuter), "R", {"ToGBP"}),
GBP = Table.AddColumn(Joined, "GBP", each if [ToGBP] = null then null else Number.Round([Amount] * [ToGBP], 2, RoundingMode.AwayFromZero), type number)
in
Table.SelectColumns(GBP, {"Tx", "Currency", "Amount", "GBP"})Result
| Tx | Currency | Amount | GBP |
|---|---|---|---|
| T1 | EUR | 100 | 87 |
| T2 | USD | 50 | 39.5 |
| T3 | EUR | 40 | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Keep the rates table in one place (a shared file or a staging query) so every report converts with the same rates. If finance publishes a corrected rate, one refresh updates every output that uses it.
Age by due date for collection work and by invoice date for some audit views; say which in the column name. Keep the buckets as text labels for display, and add a numeric sort column (1, 2, 3, 4) so 60+ never sorts before 1-30 in a report.
4. Control totals
End every month-end query with a check that the transformed totals equal the source totals per period, and fail or flag when they don't. See validate data and fail loudly.
Practise it in the simulator
Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. These tasks are part of Pro.
- intermediate~8 minPro
Calculate reimbursement dates (consultancy)
Claims are paid 10 days after the end of the month they were made in. Add Pay Date.
- intermediate~7 minPro
Add 20% VAT and round to cents (kite shop)
Calculate a gross price including 20% VAT, rounded to two decimals.
- advanced~10 minPro
Show each region's share of total revenue (kite shop)
Total revenue per region, then each region's percentage of the grand total.
Related pages
Lessons
Sources
Microsoft's official documentation for the features and functions used on this page.
- Combine files overview (Concept, Microsoft Learn)
- Grouping or summarizing rows (Concept, Microsoft Learn)
- Date functions (Concept, Microsoft Learn)
- Table.AddColumn (Function reference, Microsoft Learn)
- List.Sum (Function reference, Microsoft Learn)
- Duration.Days (Function reference, Microsoft Learn)
- Table.Group (Function reference, Microsoft Learn)
- Date.StartOfMonth (Function reference, Microsoft Learn)
- Table.ExpandTableColumn (Function reference, Microsoft Learn)
- Table.NestedJoin (Function reference, Microsoft Learn)
- Number.Round (Function reference, Microsoft Learn)
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.