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:

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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:
Upvote 0
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
 
Upvote 0
You are welcome

Have a nice day

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

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top