Power QueryExercises

Running totals and cumulative sums in Power Query

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

Jump to the practice tasks

Should it be in Power Query?

Three dated amounts of 100, 40 and 60 become a Running column of 100, 140 and 200: each row's total includes every earlier row in date order.
A running total depends on a defined order, so sort before you accumulate.

In Power BI, a running total that should respond to slicers belongs in a DAX measure. Compute it in Power Query when the order and grouping are fixed: a ledger balance, cumulative units produced, a stock position by date.

The simple version (small tables)

Sort, add an index, and sum the first N values for each row. Easy to read, but each row re-sums everything before it, so the work grows with the square of the row count:

let
    Days = #table({"Day", "Units"}, {{1, 5}, {2, 3}, {3, 0}, {4, 7}}),
    Indexed = Table.AddIndexColumn(Days, "i", 1, 1, Int64.Type),
    Running = Table.AddColumn(Indexed, "Cumulative", each List.Sum(List.FirstN(Indexed[Units], [i])), type number)
in
    Table.RemoveColumns(Running, {"i"})

Result

DayUnitsCumulative
155
238
308
4715

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

The scalable version

Build the whole cumulative list in one pass with List.Accumulate, then put it next to the original columns with Table.FromColumns:

let
    Days = Table.Sort(#table({"Day", "Units"}, {{2, 3}, {1, 5}, {4, 7}, {3, 0}}), {{"Day", Order.Ascending}}),
    Cumulative = List.Accumulate(Days[Units], {}, (acc, u) => acc & {(if List.IsEmpty(acc) then 0 else List.Last(acc)) + u}),
    Result = Table.FromColumns(Table.ToColumns(Days) & {Cumulative}, Table.ColumnNames(Days) & {"Cumulative"})
in
    Result

Result

DayUnitsCumulative
155
238
308
4715

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

The unsorted input is sorted first; a running total is meaningless without an explicit order.

Running total per group

Wrap the same logic in a function and apply it to each group's rows with Group By:

let
    AddRunning = (t as table) as table =>
        let
            Sorted = Table.Sort(t, {{"Day", Order.Ascending}}),
            Cum = List.Accumulate(Sorted[Units], {}, (acc, u) => acc & {(if List.IsEmpty(acc) then 0 else List.Last(acc)) + u})
        in
            Table.FromColumns(Table.ToColumns(Sorted) & {Cum}, Table.ColumnNames(Sorted) & {"Cumulative"}),
    Output = #table({"Line", "Day", "Units"}, {{"A", 1, 5}, {"B", 1, 2}, {"A", 2, 3}, {"B", 2, 4}}),
    Grouped = Table.Group(Output, {"Line"}, {{"Rows", each AddRunning(Table.RemoveColumns(_, {"Line"})), type table}}),
    Expanded = Table.ExpandTableColumn(Grouped, "Rows", {"Day", "Units", "Cumulative"})
in
    Expanded

Result

LineDayUnitsCumulative
A155
A238
B122
B246

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

Whichever method you use, check the last cumulative value against a plain total: List.Last(Cumulative) = List.Sum(Days[Units]). If they differ, a null or a sort problem is hiding in the data.

For very long tables, List.Generate builds the same list with less copying than List.Accumulate's acc & {…}, at the cost of slightly harder code. Start with the version you can read, and optimise only when refresh time shows you need to.

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.