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...
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

     

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.

      • AshleyJ17's avatar
        AshleyJ17
        Helper II
        2 years ago

        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"