Power QueryExercises

Power Query for finance: month-end recipes

Recipes · By Tahir Nas · Examples run in our simulator · Published · Updated

Jump to the practice tasks

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
    Aged

Result

BucketAmount
1-30300
31-60150
60+90
Not due60

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

TxCurrencyAmountGBP
T1EUR10087
T2USD5039.5
T3EUR40null

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

T3 has no November rate yet. A null is a visible gap; a default rate would be a silent error.

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.

Lessons

More resources

Sources

Microsoft's official documentation for the features and functions used on this page.

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.