Running total

Hi scsos 

 

You need to refer to the column of Incidents using the name of the last step.  Assuming that last step was adding the Index column, your code would look like this

 

= List.Sum(List.FirstN(#"Added Index"[Incidents] , [Index]))

 

And you need to change the Index column so that it starts at 1, not 0.  If you start it at 0 the running total will be incorrect

 

 

Regards

 

Phil

 

scsos 

Alternatively, you can use List.Accumulate for calculating running total. You dont need an extra index column for this. Below code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRMlCK1YlWcktNArKNwGzfxCI427EAxDaGilcC2YZgtlcpQq9XaQ5CfWk6kG0CZgenFsDV+yeXwM3xyy8DssFMl9RkMDMWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Incident = _t]),
    TypeChanged = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Incident", Int64.Type}}),
    Result = 
    let 
varListConversion = List.ReplaceValue(TypeChanged[Incident],null,0,Replacer.ReplaceValue),
varRunningTotal  = 
Table.FromColumns(
Table.ToColumns( TypeChanged) &
{
List.Skip(List.Accumulate( 
varListConversion,
{0},
(s,c) => s & {List.Last(s)+c}
),1) }, Table.ColumnNames(TypeChanged) & {"RunningTotal"}
)
in
varRunningTotal
in
    Result

 

List.FirstN function is very slow function, if you are dealing with large dataset..Alternatively you can use List.Accumulate or List.Generate for that.

 

Hope this helps.

 

Regards,

sanalytics

Edit: sanalytics is absolutely correct in their response that FirstN is a suboptimal approach. I didn't think closely enough about it initially. Updated below to use List.Generate to pass forward running total, which I believe will be most performant, which really will only be noticible if you are dealing with a non-small table (5k+ rows).

 

let
    Source = Sample,
    Incidents = List.Buffer(Source[Incidents]),
    Run = List.Generate(
        ()=> [ i = 0, run = List.First(Incidents) ], 
        each [i] < List.Count(Incidents), 
        each [ i = [i]+1, run = [run] + (Incidents{i}??0) ], 
        each [run]
    ),
    Combine = Table.FromColumns( 
        Table.ToColumns(Source) & { Run }, 
        type table [Month=text, Incidents=Int64.Type, Running Total=Int64.Type] 
    )
in
    Combine

 

 

Old suboptimal FirstN code for reference:

let
    Source = Sample,
    Incidents = List.Buffer( Source[Incidents] ),
    Run = List.Transform( 
        List.Positions(Incidents), 
        each List.Sum( List.FirstN( Incidents, _+1 ) ) 
    ),
    Combine = Table.FromColumns( 
        Table.ToColumns(Source) & { Run }, 
        type table [Month=text, Incidents=Int64.Type, Running Total=Int64.Type] 
    )
in
    Combine