Forum Discussion

AshleyJ17's avatar
AshleyJ17
Helper II
2 years ago
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 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

 

 

SuborderIDActivityCategoryIDPostcodeIDDateIDRunningCount
25365713243536748202001011
25365713243536748202002012
25365713243536748202003013
32198778932175412202309181
32198778932175412202309212
46378382371267543202402211
25365778932167543202001011
Need Help
Tips and Tricks
Tutorial Requests
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    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

     

9 Replies

    • AshleyJ17's avatar
      AshleyJ17
      Helper II
      2 years ago

      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

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago
    • 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's avatar
      AshleyJ17
      Helper II
      2 years ago

      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's avatar
        ronrsnfld
        Super User
        2 years ago

        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.