Running Total Distinct Count

[Craigc3814]

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

 

[sandy666]

try adapt to your needs
by Marcel Beug

 

[Craigc3814]

I am not using numbers unfortunately, I am trying to count the occurrences of text.

 

[sandy666]

but you can count rows (count or distinct count) and you will get numbers then ... above

 

[Craigc3814]

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]

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

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

 

[Craigc3814]

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

 

[sandy666]

Exactly theBardd

 

[theBardd]

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]

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