refresh thread, it was changed
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 by jonh; Apr 15th, 2019 at 10:00 AM.
something like this?
(with your example)
Name Values CntGrp CountOver A.N.Other01 131 0A.N.Other01 231 1A.N.Other01 1001 2A.N.Other01 1221 3A.N.Other01 1561 4A.N.Other01 3471 5A.N.Other01 21351 6A.N.Other02 452 0A.N.Other02 1242 1A.N.Other02 7832 2A.N.Other02 23452 3A.N.Other03 53 0A.N.Other04 354 0A.N.Other05 675 0A.N.Other06 236 0A.N.Other06 236 1A.N.Other06 326 2A.N.Other06 586 3A.N.Other06 676 4
function: fnRunningTotCountOver
table: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})), AddedRunningTotCountOver = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType) in AddedRunningTotCountOver
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
Last edited by sandy666; Apr 15th, 2019 at 11:17 AM.
I know you know but I forgot my Crystal Ball and don't know what you know
Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result. impossible things we do on the spot. for miracles you need to wait for a while
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
refresh thread, it was changed
I know you know but I forgot my Crystal Ball and don't know what you know
Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result. impossible things we do on the spot. for miracles you need to wait for a while
Hi Sandy,
Thank you very much.
Regards,
Jon
You are welcome
Have a nice day
if it works for you don't forget to use Thanks/Like buttons
Last edited by sandy666; Apr 15th, 2019 at 11:31 AM.
I know you know but I forgot my Crystal Ball and don't know what you know
Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result. impossible things we do on the spot. for miracles you need to wait for a while
Like this thread? Share it with others