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

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

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

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