Power Query - conditional cumulative sum

Hi Mateusz_ ,

Have you solved your problem? If not, you can try this.

Here is my sample data:

Do not perform any operations after the original table is imported. First I add a new table following the next steps:

Put all of the M function into the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVNJRAmEDOI7VISRuiiRniCRuDBU3RFNvgkMcp71GGBLm5kZYNSDEUR0EETcyxLCZsEHEiVsYmgPZlhgWQ8TNcIjjMgenOKrlJBsUCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, quantity = _t, main_flag = _t, continue_flag = _t, new_flag = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"quantity", Int64.Type}, {"main_flag", Int64.Type}, {"continue_flag", Int64.Type}, {"new_flag", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([continue_flag] = 1)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"ID"}, {{"Index", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"quantity", "main_flag", "continue_flag", "new_flag", "Index"}, {"Index.quantity", "Index.main_flag", "Index.continue_flag", "Index.new_flag", "Index.Index"})
in
    #"Expanded Index"

And we will get such a new table:

 

Still select Blank Query:

And now let put another M function into the Advanced Editor:

let
    Source = Table.NestedJoin(Origin, {"ID", "quantity", "main_flag", "continue_flag", "new_flag"}, Table, {"ID", "Index.quantity", "Index.main_flag", "Index.continue_flag", "Index.new_flag"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(Source, "Table (2)", {"Index.Index"}, {"Table (2).Index.Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table (2)", "sum_index", each if [new_flag] = 1 then 1 else [#"Table (2).Index.Index"] + 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Table (2).Index.Index"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each if [sum_index] = 1 then [quantity] 
else List.Sum(
    Table.SelectRows(
        #"Added Custom",
        (row) => row[ID] = [ID] and row[sum_index] <= [sum_index]
    )[quantity]
)),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom1",null,0,Replacer.ReplaceValue,{"ID", "sum_index", "Custom"})
in
    #"Replaced Value"

Then we will get the final output as below ( The results are in a different order than your screenshot, but the data results are the same ) :

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.