Forum Discussion

Mederic's avatar
Mederic
Post Patron
2 years ago
Solved

Running total

Hello, I have a Power query code that works in the 1st case but not in the 2nd (see screenshot below) How can I please modify this code so that it works in both cases ?     =List.Sum(Table.Sele...
Need Help
  • spinfuzer's avatar
    spinfuzer
    2 years ago

    The formula only works when the [Column] is in Ascending Order (e.g. next row is always greater than the next).  If you want to use that formula, Add an [Index] column before adding the running total. 

     

    =List.Sum(Table.SelectRows(#"Changed Type", (x)=> x[Index]<=[Index])[Qty])

     

    The formula is not efficient, but it will work if you are a beginner and you have a small data set.  

     

     

Mederic's avatar
Mederic
Post Patron
2 years ago

Hello spinfuzer ,

Thank you for your solution, which works,
I thought there might be a code without going through Index

I also had a solution with Index with the code below:

=Table.AddColumn(#"Index ajouté", "Résultat", each List.Sum(List.Range(#"Index ajouté"[Qtés],0,[Index])))

Best Regards

  • spinfuzer's avatar
    spinfuzer
    Solution Sage
    2 years ago

    The only way to do it without an index is to edit the M in the advanced editor.  If you are familiar with editing the M code in the advanced editor it is more efficient to use something like below:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc6xDQAhDEPRXVIjAYYEMgti/zUOyVe4fXL0c4511BYVDbBiA3bLsyE2nTbFYtFczIMWYhi0pY1/t8XWpqV2+QuadpPWxfLd3g8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Qty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Qty", Int64.Type}}),
    running_total = List.Accumulate(#"Changed Type"[Qty],{},(s,c) => s & {(List.Last(s) ?? 0) + c}),
    add_column = Table.From(
        Table.ToColumns(#"Changed Type") & {running_total}, 
        Table.ColumnNames(#"Changed Type") & {"Running Total"}
        )
in
    running_total