Running Total Distinct Count

Craigc3814

Board Regular
Joined
Mar 7, 2016
Messages
211
I have a table I am working with that has project names that are listed multiple times (along with a bunch of other data that should not be relevant here). This is in Power Query

What I want to do is a running total of how many times the project name appears then start the count over when a new project name appears. End result would look like below.

Project A 1
Project A 2
Project A 3
Project B 1
Project B 2
Project C 1
 

Craigc3814

Board Regular
Joined
Mar 7, 2016
Messages
211
I am not using numbers unfortunately, I am trying to count the occurrences of text.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,240
but you can count rows (count or distinct count) and you will get numbers then ... above
 

Craigc3814

Board Regular
Joined
Mar 7, 2016
Messages
211
I do not understand how it would work. The solution from Marcel sums a number column (cost). I do not want to sum anything nor do I have anything to sum. All I want to do is count rows, if I group then do a distinct count, or regular count it groups my project name down to 1 line and just tells me how many times it appears in the list. I already know the answer to that.
 

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
You can insert a column with a value of 1 for each row to sum, this will give you a running count.

I adapted this brilliant solution to a lit of projects to show ho

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="tblProjects"]}[Content],
    countColumnAdd = Table.AddColumn(Source, "Instance", each 1),
    TableType = Value.Type(Table.AddColumn(countColumnAdd, "Running Count", each null, type number)),
    fnGrouping = (MyTable as table) as table =>
    let
        Source = Table.Buffer(MyTable),
        TableType = Value.Type(Table.AddColumn(Source, "Running Count", each null, type number)),
        Cumulative = List.Skip(List.Accumulate(Source[Instance],{0},(cumulative,instance) => cumulative & {List.Last(cumulative) + instance})),
        RunningCount = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
    in
    RunningCount,
    rowsGrouped = Table.Group(countColumnAdd, {"Project"}, {{"AllData", fnGrouping, TableType}}),
    groupsExpanded = Table.ExpandTableColumn(rowsGrouped, "AllData", {"Running Count"}, {"Running Count"})
in
    groupsExpanded
 
Last edited:

Craigc3814

Board Regular
Joined
Mar 7, 2016
Messages
211
So I need to create a custom function for this portion correct

let
Source = Excel.CurrentWorkbook(){[Name="tblProjects"]}[Content],
countColumnAdd = Table.AddColumn(Source, "Instance", each 1),
TableType = Value.Type(Table.AddColumn(countColumnAdd, "Running Count", each null, type number)),
fnGrouping = (MyTable as table) as table =>
 

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
So I need to create a custom function for this portion correct

let
Source = Excel.CurrentWorkbook(){[Name="tblProjects"]}[Content],
countColumnAdd = Table.AddColumn(Source, "Instance", each 1),
TableType = Value.Type(Table.AddColumn(countColumnAdd, "Running Count", each null, type number)),
fnGrouping = (MyTable as table) as table =>
No, I embedded the function I the query for you. Mine should do what you want, barring any other column that you might have.
 

Craigc3814

Board Regular
Joined
Mar 7, 2016
Messages
211
Not sure what I am doing wrong, I copied and pasted your formula (changed project to project name as it is called in my table) in but when it groups it is washing out the multiple amount of times projects appear for example project A appears 3 times but after this code it drops it down to 1 row and returns 1
 

Forum statistics

Threads
1,085,490
Messages
5,383,963
Members
401,868
Latest member
herbalgirlskincare

Some videos you may like

This Week's Hot Topics

Top