How to sum consecutive rows in Power Query

Question

I have in Power Query a Column "% sum of all". I need to create a custom column "Sum Consecutive" that each row has as value the "% sum of all" of the current row + the value of "Sum Consecutive" of the previous row.

Current row situation

New Custom Column Expectation

You can see two images that show the current situation and the next situation I need in the Power Query.

Can you please help me find a code/command to create this new column like that? Although there are similar solved questions in DAX, I still need to keep editing the file after that, so it should be performed in M language in power query.

Thank you!

Possible duplicate of How to calculate cumulative Total and % in DAX? — Alexis Olson
Hello Alexis! Although it seems very similar to that link, I need to perform it in M language in Power Query. I still need to keep working on the file after performing this step, so I can't move the editing to Power BI yet. Does it make sense? — Pedro Carvalho
Ah, right. Then you probably want this one instead: stackoverflow.com/questions/31112231/… — Alexis Olson

chillin chillin · Accepted Answer · 2019-01-11T23:54:21

Not sure how performant my approaches are. I would think both should be reasonably efficient as they only loop over each row in the table once (and "remember" the work done in the previous rows). However, maybe the conversion to records/list and then back to table is slow for large tables (I don't know).

Approach 1: Isolate the input column as a list, transform the list by cumulatively adding, put the transformed list back in the table as a new column.

let
    someTable = Table.FromColumns({List.Repeat({0.0093}, 7) & List.Repeat({0.0086}, 7) & {0.0068, 0.0068}}, {"% of sum of all"}),
    listToLoopOver = someTable[#"% of sum of all"],
    cumulativeSum = List.Accumulate(List.Positions(listToLoopOver), {}, (listState, currentIndex) =>
        let
            numberToAdd = listToLoopOver{currentIndex},
            sum = try listState{currentIndex - 1} + numberToAdd otherwise numberToAdd,
            append = listState & {sum}
        in
            append
    ),
    backToTable = Table.FromColumns(Table.ToColumns(someTable) & {cumulativeSum}, Table.ColumnNames(someTable) & {"Cumulative sum"})
in
    backToTable

Approach 2: Convert the table to a list of records, loop over each record and add a new field (representing the new column) to each record, then convert the transformed list of records back into a table.

let
    someTable = Table.FromColumns({List.Repeat({0.0093}, 7) & List.Repeat({0.0086}, 7) & {0.0068, 0.0068}}, {"% of sum of all"}),
    listToLoopOver = Table.ToRecords(someTable),
    cumulativeSum = List.Accumulate(List.Positions(listToLoopOver), {}, (listState, currentIndex) =>
        let
            numberToAdd = Record.Field(listToLoopOver{currentIndex}, "% of sum of all"),
            sum = try listState{currentIndex - 1}[Cumulative sum] + numberToAdd otherwise numberToAdd, // 'try' should only be necessary for first item
            recordToAdd = listToLoopOver{currentIndex} & [Cumulative sum = sum],
            append = listState & {recordToAdd}
        in
            append
    ),
    backToTable = Table.FromRecords(cumulativeSum)
in
    backToTable

I couldn't find a function in the reference for M/Power Query that sums a list cumulatively.

How to sum consecutive rows in Power Query

1 Answers