# Thread: Running count Thanks:  2 Post #5261316 (1)Post #5261333 (1) Likes:  2 Post #5261316 (1)Post #5261333 (1)

1. ## Running count

Hi All,

New to power query in Excel. Is there a way I can get a running count based on a reoccuring value in a column, and start a new running count based on new values?

Cannot find the equivalent of count/countif in PQ.

Here's an example of what I'm trying to achieve.

Input
Name Values
A.N.Other01 13
A.N.Other01 23
A.N.Other01 100
A.N.Other01 122
A.N.Other01 156
A.N.Other01 347
A.N.Other01 2135
A.N.Other02 45
A.N.Other02 124
A.N.Other02 783
A.N.Other02 2345
A.N.Other03 5
A.N.Other04 35
A.N.Other05 67
A.N.Other06 23
A.N.Other06 23
A.N.Other06 32
A.N.Other06 58
A.N.Other06 67

Output
Name Values Col1 Col2
A.N.Other01 13 1 0
A.N.Other01 23 1 1
A.N.Other01 100 1 2
A.N.Other01 122 1 3
A.N.Other01 156 1 4
A.N.Other01 347 1 5
A.N.Other01 2135 1 6
A.N.Other02 45 2 0
A.N.Other02 124 2 1
A.N.Other02 783 2 2
A.N.Other02 2345 2 3
A.N.Other03 5 3 0
A.N.Other04 35 4 0
A.N.Other05 67 5 0
A.N.Other06 23 6 0
A.N.Other06 23 6 1
A.N.Other06 32 6 2
A.N.Other06 58 6 3
A.N.Other06 67 6 4

Thanks,
Jon

2. ## Re: Running count

something like this?

 Name Values CntGrp CountOver A.N.Other01 13 1 0 A.N.Other01 23 1 1 A.N.Other01 100 1 2 A.N.Other01 122 1 3 A.N.Other01 156 1 4 A.N.Other01 347 1 5 A.N.Other01 2135 1 6 A.N.Other02 45 2 0 A.N.Other02 124 2 1 A.N.Other02 783 2 2 A.N.Other02 2345 2 3 A.N.Other03 5 3 0 A.N.Other04 35 4 0 A.N.Other05 67 5 0 A.N.Other06 23 6 0 A.N.Other06 23 6 1 A.N.Other06 32 6 2 A.N.Other06 58 6 3 A.N.Other06 67 6 4

function: fnRunningTotCountOver
Code:
```(MyTable as table) as table =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "RunningTotCountOver", each null, type number)),
Cumulative = List.Skip(List.Accumulate(Source[Count],{0},(Cumulative,Count) => Cumulative & {List.Last(Cumulative) + Count})),
in
table:
Code:
```let
Source = Excel.CurrentWorkbook(){[Name="Table19"]}[Content],
Index = Table.AddIndexColumn(Source, "Index", 1, 1),
Grp = Table.Group(Index, {"Name", "Values", "Index"}, {{"Count", each Table.RowCount(_), type number}}),
TType = Value.Type(Table.AddColumn(Grp, "RunningTotCountOver", each null, type number)),
Grp2 = Table.Group(Grp, {"Name"}, {{"AllData", fnRunningTotCountOver, TType}}),
ExpAllData = Table.ExpandTableColumn(Grp2, "AllData", {"Values", "RunningTotCountOver"}, {"Values", "RunningTotCountOver"}),
Extract = Table.AddColumn(ExpAllData, "Last Characters", each Text.End([Name], 1), type text),
Reorder = Table.ReorderColumns(Extract,{"Name", "Values", "Last Characters", "RunningTotCountOver"}),
Ren = Table.RenameColumns(Reorder,{{"Last Characters", "CntGrp"}}),
Subtract = Table.TransformColumns(Ren, {{"RunningTotCountOver", each _ - 1, type number}}),
Rename = Table.RenameColumns(Subtract,{{"RunningTotCountOver", "CountOver"}})
in
Rename```

3. ## Re: Running count

Hi Sandy,

Yes. Thank you. That's perfect for Col2, how would I get Col1 where I want All ANOther1 to have a value of 1, All ANOther2 to have value of 2 etc...

Would it be possible to share the workbook on which you created the above code and output?

Many thanks,
Jon

5. ## Re: Running count

Hi Sandy,

Thank you very much.

Regards,
Jon

6. ## Re: Running count

You are welcome

Have a nice day

if it works for you don't forget to use Thanks/Like buttons