Power Query - conditional cumulative sum

[Mateusz_]

Hi,

I am asking for help in creating a cumulative sum in Power Query based on conditions. I am attaching a screenshot with sample data. In PowerQuery I have columns user_id, quantity, main_flag, continue_flag, new_flag. To illustrate this better, I created auxiliary columns sum_index and sum_range.

The excel condition for sum_index:
=IF([@[main_flag]]=0;0;
IF([@[new_flag]]=1;1;
IF([@[continue_flag]]=1;F1+1)))

The cumulative total is calculated based on these indexes and the value from the quantity column. If main_flag = 0, then cumulative sum = 0.

[v-junyant-msft · Accepted solution]

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.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...