Running total

[Mederic]

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

[Mederic]

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"

[Mederic]

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]

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

[spinfuzer · Accepted solution]

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.