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.SelectRows(#"Changed Type", (x)=> x[Qty]<=[Qty])[Qty])

 

 

Thanks in advance

 

Best Regards

 

 

Date	Qty
12/06/2022	32
13/06/2022	45
14/06/2022	67
15/06/2022	56
16/06/2022	23
17/06/2022	36
18/06/2022	78
19/06/2022	42
20/06/2022	69
21/06/2022	98

 

 

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.  

     

     

4 Replies

  • spinfuzer's avatar
    spinfuzer
    Solution Sage
    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

       

       

  • Mederic's avatar
    Mederic
    Post Patron
    2 years ago

    Thank you spinfuzer ,

     

    I didn't manage to get the expected result with your 2nd solution,
    But I adapted it as below and it works,
    You've done most of the code that I didn't know and I thank you.

    Best Regards

    let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Qty", Int64.Type}}),
    running_total = List.Accumulate(#"Changed Type"[Qty],{},(s,c) => s & {(List.Last(s) ?? 0) + c}),
    Custom1 = Table.FromRows(List.Zip({#"Changed Type"[Date]} & {running_total}), {"Date","Running Total"}),
    #"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"Date", type date}})
in
    #"Changed Type1"