Running count

[jonh]

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

 

[sandy666]

something like this?
(with your example)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Values[/td][td=bgcolor:#70AD47]CntGrp[/td][td=bgcolor:#70AD47]CountOver[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A.N.Other01[/td][td=bgcolor:#E2EFDA]

13​

[/td][td=bgcolor:#E2EFDA]1[/td][td=bgcolor:#E2EFDA]

0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A.N.Other01[/td][td]

23​

[/td][td]1[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A.N.Other01[/td][td=bgcolor:#E2EFDA]

100​

[/td][td=bgcolor:#E2EFDA]1[/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A.N.Other01[/td][td]

122​

[/td][td]1[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A.N.Other01[/td][td=bgcolor:#E2EFDA]

156​

[/td][td=bgcolor:#E2EFDA]1[/td][td=bgcolor:#E2EFDA]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A.N.Other01[/td][td]

347​

[/td][td]1[/td][td]

5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A.N.Other01[/td][td=bgcolor:#E2EFDA]

2135​

[/td][td=bgcolor:#E2EFDA]1[/td][td=bgcolor:#E2EFDA]

6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A.N.Other02[/td][td]

45​

[/td][td]2[/td][td]

0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A.N.Other02[/td][td=bgcolor:#E2EFDA]

124​

[/td][td=bgcolor:#E2EFDA]2[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A.N.Other02[/td][td]

783​

[/td][td]2[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A.N.Other02[/td][td=bgcolor:#E2EFDA]

2345​

[/td][td=bgcolor:#E2EFDA]2[/td][td=bgcolor:#E2EFDA]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A.N.Other03[/td][td]

5​

[/td][td]3[/td][td]

0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A.N.Other04[/td][td=bgcolor:#E2EFDA]

35​

[/td][td=bgcolor:#E2EFDA]4[/td][td=bgcolor:#E2EFDA]

0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A.N.Other05[/td][td]

67​

[/td][td]5[/td][td]

0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A.N.Other06[/td][td=bgcolor:#E2EFDA]

23​

[/td][td=bgcolor:#E2EFDA]6[/td][td=bgcolor:#E2EFDA]

0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A.N.Other06[/td][td]

23​

[/td][td]6[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A.N.Other06[/td][td=bgcolor:#E2EFDA]

32​

[/td][td=bgcolor:#E2EFDA]6[/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A.N.Other06[/td][td]

58​

[/td][td]6[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A.N.Other06[/td][td=bgcolor:#E2EFDA]

67​

[/td][td=bgcolor:#E2EFDA]6[/td][td=bgcolor:#E2EFDA]

4​

[/td][/tr]
[/table]

function: fnRunningTotCountOver

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

[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]

 

[jonh]

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]

refresh thread, it was changed

 

[jonh]

Hi Sandy,

Thank you very much.

Regards,
Jon

 

[sandy666]

You are welcome

Have a nice day

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