Cumulative sum in Power query editor

[Anonymous]

Hello all,

How I can do the cumulative sum in power query editior as shown below

I need to calculate the 3rd column as shown above.

Thanks,

Karthik

[amitchandak · Accepted solution]

@Anonymous , refer this blog for power query running total

https://www.myonlinetraininghub.com/quickly-create-running-totals-in-power-query

[Rickmaurinus]

A bit late to the party, you may enjoy reading these articles. With increasing complexity, here's how to: 

- Create running totals (4 methods): https://gorilla.bi/power-query/running-total/

- Apply running total by category: https://gorilla.bi/power-query/running-total-by-category/

- Create running totals in bulk: https://gorilla.bi/power-query/creating-multiple-running-totals/

The list.generate versions are fast. List.Accumulate helps in creating multiple columns. And there's a smart method to combine the tables again. 

Hope it helps,

Rick

[user10456]

Thanks, good suggestion! I ended up replacing the `List.Accumulate()` step with the one-liner `=List.Accumulate(Source[Amount], {}, (state,current) => state & {List.Last(state, 0) + current})`, combined with your method for adding the whole column to the table at once.

[rodrigochaves]

@amitchandakI followed your blog solution and it was awesome! Thanks for sharing your knowledge!

[v-cazheng-msft · Accepted solution]

Hi @Anonymous 

You can try these steps.

Add Index to your query and create a Custom column

Or you can use following M query in the Advanced Editor:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ssv0zUyMDJQ0lEyM7LUMzdVitWJVnJJTYaJGhrpGZuDBb0S80CChkBBYwtLPRNjsKhbahJM1NDAzFTPwgQs7JtYBBc2N9EzApobCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YearMonth = _t, Amount = _t]),

    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),

    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Amount", type number}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Running Total", each List.Sum(List.FirstN(#"Changed Type"[Amount],[Index])))

in

#"Added Custom"

The result looks like this:

For more details, you can refer the attached pbix file. For further understanding running total in Power Query, you can refer these links:

Quickly Create Running Totals in Power Query

Grouped running total with Power Query M

Best Regards

Caiyun Zheng

If this post helps, please consider make it as the solution by Accept it as Solution. Really appreciate!

[Anonymous]

Thank you so much @v-cazheng-msft . I somehow resolved it in a different way but will try your way next time. Is there a guide where I can start understanding M code from scratch? 

[v-cazheng-msft]

@Anonymous 

You can refer these official documents Power Query M formula language, it may help you.

Best Regards

Caiyun Zheng

[Anonymous]

Thank you so much

[amitchandak · Accepted solution]

@Anonymous , refer this blog for power query running total

https://www.myonlinetraininghub.com/quickly-create-running-totals-in-power-query

[ShubhamK280394]

Thanks for the solution but here my index again start at 0 when the year change. What should be m query formula then?