Forum Discussion
cumulative sum by Date and address
- 1 year ago
The code below will produce your desired results from the data you posted.
You'll need to replace the Source line with your actual data source.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJW0lEyBJKxOiAhI0whuCojYxOokDGmkCmmRjOYkImpGboQWFUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Address Number" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Address Number", Int64.Type}}), //To be able to return to original order #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), //Group by Address Number, then add an Index column to each subtable (starting with `1`) #"Grouped Rows" = Table.Group(#"Added Index", {"Address Number"}, { {"Count", each Table.AddIndexColumn(_,"Order Count", 1, 1, Int64.Type), type table [Date=nullable date, Address Number=nullable number, Index=Int64.Type, Order Count=Int64.Type]}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Date", "Index", "Order Count"}), //Return to original order //Then remove the index column and set the columns to desired order #"Sorted Rows" = Table.Sort(#"Expanded Count",{{"Index", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "Address Number", "Order Count"}) in #"Reordered Columns"
Hi Skyman8880,
Thank you for Reaching out to the Microsoft Fabric Community Forum.
After reviewing your issue, I can confirm that the solution provided by ronrsnfld is a great fit for your scenario. The approach using Power Query to calculate the cumulative order count through grouping and indexing is suited for your dataset.
To make it even easier for you, I’ve attached a sample PBIX file for your reference. Feel free to explore it and let me know if you have any questions.
Also, thank you Deku for sharing your valuable insights.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
- Skyman88801 year agoFrequent Visitor
Thank you for your help. I was able to integrate the code from ronrsnfld and I loved the website that was linked from Deku .
The only remaining issue is on my own machine. The Dataset is not exactly small, so it's lots of cycles to get the data updated.