Forum Discussion
Solved
How to add a cumulative count (running count) in Power Query
Hi Everyone, Looking for some help of which im sure will be a simple solution that i just can quite grasp, i have a table example below but essentially i want to add a Cumulative Count or Running...
You should be able to adapt the code I provided to preserve them.
One method
- duplicate the three columns you want to Group on
- Group on those columns
- Merge back with the original table
- Remove the duplicated columns
Thank you,
this is the code, very simple, just getting the data from the database and sorting the rows mainly, i could also remove the changed type i only added that in as it was in your code to made it easier for me to copy and paste in
let
Source = Sql.Databases("ServerName"),
DatabaseName = Source{[Name="DatabaseName"]}[Data],
Fact_Activity = DatabaseName{[Schema="Fact",Item="Activity"]}[Data],
#"Sorted Rows" = Table.Sort(Fact_Activity,{{"StartDateID", Order.Ascending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"ActivityCategoryID", Int64.Type}, {"SuborderID", Int64.Type}, {"PostcodeID", Int64.Type}})
in
#"Changed Type"Looks like you pasted my code incorrectly and copied part of the line that really belonged to my "Source=" line.
Try:
let
Source = Sql.Databases("ServerName"),
DatabaseName = Source{[Name="DatabaseName"]}[Data],
Fact_Activity = DatabaseName{[Schema="Fact",Item="Activity"]}[Data],
#"Sorted Rows" = Table.Sort(Fact_Activity,{{"StartDateID", Order.Ascending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"ActivityCategoryID", Int64.Type}, {"SuborderID", Int64.Type}, {"PostcodeID", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"SuborderID", "ActivityCategoryID", "PostcodeID"}, {
{"Running Count", each List.Numbers(1, Table.RowCount(_)), type {Int64.Type}}}),
#"Expanded Running Count" = Table.ExpandListColumn(#"Grouped Rows", "Running Count")
in
#"Expanded Running Count"
Thank you, at first look that seems to be doing the trick, however i think the issue is i havent mentioned i have more than just these 3 coloumns in the data, so now ive lost all the other columns
You should be able to adapt the code I provided to preserve them.
One method
- duplicate the three columns you want to Group on
- Group on those columns
- Merge back with the original table
- Remove the duplicated columns