Power query, iterate over the column records to apply a custom cumulative calculation

Question

Using Power Query in Excel. I am trying to implement a custom column that would iteratively calculate the row based on the previous row's value of the same column.

I have a 3 column table and the 4th column will be the calculation column that I am failing to implement.

The calculation is very easy to apply in Excel which goes as follows:

Formula in cell D3 --> = =IF(A3=1,C3+6.4,IF(C3+D2>=12.8,12.8,IF(C3+D2<=1.28,1.28,C3+D2)))

The same formula is applied to the whole column by dragging.

The idea behind it:

• For each category, I have an index column starting from 1,
• If Index = 1, then Calculation is Value + 6.4,

else if Value + Value(previous row Custom cumulative) >= 12.8 then 12.8

else if Value + Value(previous row Custom cumulative) <= 1.28 then 1.28

else Value + Value(previous row Custom cumulative)

• So, the calculation is a cumulative sum with an upper and lower cap built into it.

How can I implement this in Power Query and M-Language?

I really appreciate your help!

I have tried to use List.Generate and List.Accumulate features, however, I was stuck with creating records that has values from multiple columns in it.

Answer 1

Try this

(edited to make more efficient with single pass process)

let Source = Excel.CurrentWorkbook(){[Name="Table15"]}[Content],

process = (zzz as list) => let x= List.Accumulate( zzz,{0},( state, current ) => 
    if List.Last(state) =0 then List.Combine ({state,{6.4+current}}) else  
    if List.Last(state)+current >=12.8 then List.Combine ({state,{12.8}}) else  
    if List.Last(state)+current <=1.28 then List.Combine ({state,{1.28}}) else  
    List.Combine ({state,{List.Last(state)+current}})
) in x,

#"Grouped Rows" = Table.Group(Source, {"Category"}, {{"data", each 
    let a=process(_[Values]) 
    in Table.AddColumn(_, "Custom Cumulative", each a{[Index]}), type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Index", "Values", "Custom Cumulative"}, {"Index", "Values", "Custom Cumulative"})
in  #"Expanded data"