Running count

jonh

New Member
Joined
Mar 2, 2005
Messages
42
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
 
Last edited:

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,083
something like this?
(with your example)

NameValuesCntGrpCountOver
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:
[SIZE=1](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})),
    AddedRunningTotCountOver = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
    AddedRunningTotCountOver[/SIZE]
table:
Code:
[SIZE=1]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[/SIZE]
 
Last edited:

jonh

New Member
Joined
Mar 2, 2005
Messages
42
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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,083
You are welcome

Have a nice day

if it works for you don't forget to use Thanks/Like buttons :)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,096,245
Messages
5,449,225
Members
405,557
Latest member
AJTatsuo

This Week's Hot Topics

Top