Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
AshleyJ17
Frequent Visitor

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

AshleyJ17_0-1712776790392.png

 

 

SuborderIDActivityCategoryIDPostcodeIDDateIDRunningCount
25365713243536748202001011
25365713243536748202002012
25365713243536748202003013
32198778932175412202309181
32198778932175412202309212
46378382371267543202402211
25365778932167543202001011
1 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

 

View solution in original post

9 REPLIES 9
ronrsnfld
Super User
Super User

  • 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:

 

ronrsnfld_0-1712782226890.png

 

 

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"

 

 

 

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.

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

 

ToddChitt
Super User
Super User

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




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors