How to add a cumulative count (running count) in Power Query

[AshleyJ17]

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 Count column based on the group of 3 Columns, SuborderID, ActivityCategoryID and Postcode ID and the count from the date ascending

As you can see the first 3 rows highlighted  all have the same SuborderID, ActivityCategoryID and PostcodeID and the RunningCount column is ascending from 1 to 3 based on the date, the same for next 2 rows

Could someone provide some insight on how to do this in Power Query? for context my table has ~8 Million Rows

Thank you in advanced

SuborderID	ActivityCategoryID	PostcodeID	DateID	RunningCount
253657	132435	36748	20200101	1
253657	132435	36748	20200201	2
253657	132435	36748	20200301	3
321987	789321	75412	20230918	1
321987	789321	75412	20230921	2
463783	823712	67543	20240221	1
253657	789321	67543	20200101	1

[ronrsnfld]

• GroupBy the three relevant columns
• For each group, aggregate with a list of numbers {1..RowCount}

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc9BDoAwCATAv/TcA+zSQt9i/P83RGuM8WJPbGBCYNsKGnvzUosSxpaB3S2yQiCiomWvvwxrjDcjdMTJPEbmMzRTTEYZGksMc5t1ejC7Afo17uk4mQnwue3Z9mL3p/sB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SuborderID = _t, ActivityCategoryID = _t, PostcodeID = _t, DateID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SuborderID", Int64.Type}, {"ActivityCategoryID", Int64.Type}, {"PostcodeID", Int64.Type}, {"DateID", 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"

Using your data above, excluding the last column:

[AshleyJ17]

Hey ronrsnfld

Thanks for the response, i just added that code to my advanced editor and im getting a 'Token Identifier exepcted' error which i cant work out what  is missing, could you help please? my data is from an SQL Server so im just removed the server name from the code, the issue is on line 5 and the second let is highlighted just before the '_t'

let
    Source = Sql.Databases("ServerName"),
    DatabaseName = Source{[Name="DatabaseName"]}[Data],
    Fact_Activity = DatabaseName{[Schema="Fact",Item="Activity"]}[Data],
    let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SuborderID = _t, ActivityCategoryID = _t, PostcodeID = _t, DateID = _t]),
    
    #"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"

[ronrsnfld]

Did your code work before adding the extra code? It does look odd, but I don't have an SQL database to check it against. Maybe I can set something up later today. Perhaps if you show your working code before you added mine (with confidential info obfuscated), I might be better able to ascertain the problem.

[AshleyJ17]

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"

[ronrsnfld]

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"

[AshleyJ17]

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

[ronrsnfld · Accepted solution]

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

[ToddChitt]

Do you need this in DAX or Power Query?

In DAX, try the ROWNUMBER function: ROWNUMBER function (DAX) - DAX | Microsoft Learn

Pay attention to the PARTITION BY parameter as that will determine when the numbers 'start over'.

Or check this post:

Solved: How to add Row_number over partition by Customer -... - Microsoft Fabric Community

[AshleyJ17]

Hey ToddChitt,

Thanks for reply, i do need this in Power Query, ill take a look at the post you shared and let you know how it goes