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

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"