Forum Discussion
Consecutive Row Counter Column
Hi there! I am looking to make a counter that counts the consecutive rows and resets when there is a nonconsecutive value. Here is an example: Animal Counter Tiger 1 Tiger ...
See if this works:
Column 2 = VAR __index = CALCULATE(MAX([Index])) VAR __tmpTable1 = FILTER('Table34',[Animal]=EARLIER([Animal])&&[Index]<EARLIER([Index])) VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Table34'),[Index]<EARLIER([Index]) && [Animal]=EARLIER([Animal])),[Index])) VAR __max = MAXX(__tmpTable2,[Index]) VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[Index]) VAR __tmpTable3 = FILTER(__tmpTable2,[Index]>=__maxStart) RETURN IF(ISBLANK(__max),1,IF(__max=[Index]-1,COUNTX(__tmpTable3,[Index])+1,1))PBIX is attached.
Hi MFelix,
setting the 4th (optional) parameter in the Table.Group-function to "GroupKind.Local" will do some magic in this use case ;-)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslMTy1SitUhnuWTmZ+Hg4FNuWtOakFGYl4JAQ4eO2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Animal = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Animal", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Animal"}, {{"Partition", each Table.AddIndexColumn(_, "Counter", 1,1), type table}}, GroupKind.Local),
#"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"Counter"}, {"Counter"})
in
#"Expanded Partition"
Anonymous: May I ask what your source-data exactly looks like?
See if this works:
Column 2 = VAR __index = CALCULATE(MAX([Index])) VAR __tmpTable1 = FILTER('Table34',[Animal]=EARLIER([Animal])&&[Index]<EARLIER([Index])) VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Table34'),[Index]<EARLIER([Index]) && [Animal]=EARLIER([Animal])),[Index])) VAR __max = MAXX(__tmpTable2,[Index]) VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[Index]) VAR __tmpTable3 = FILTER(__tmpTable2,[Index]>=__maxStart) RETURN IF(ISBLANK(__max),1,IF(__max=[Index]-1,COUNTX(__tmpTable3,[Index])+1,1))PBIX is attached.
I just wanted to add this link to this topic!
Related to ImkeF's solution...
https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/
Anyway I'll "process" :smileyvery-happy: Greg_Deckler's solution later today :smileywink:
Sean - Here it is with comments if it helps! :)
Column 2 = VAR __index = CALCULATE(MAX([Index])) //What is my current row index? VAR __tmpTable1 = FILTER('Table34',[Animal]=EARLIER([Animal])&&[Index]<EARLIER([Index])) //Return all rows earlier than the current row within the same "group" VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Table34'),[Index]<EARLIER([Index]) && [Animal]=EARLIER([Animal])),[Index])) //For each returned row, calculate the difference between the index values within the same group. For rows in grouped sequence, this will be 1 but for rows within a group that are out-of-sequence this value will be greater than 1 VAR __max = MAXX(__tmpTable2,[Index]) //Figure out the max index in the current table. VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[Index]) //In order to account for "skips" in the grouping, figure out the max index value of the latest "skip" (the row right after the skip where the group starts again) VAR __tmpTable3 = FILTER(__tmpTable2,[Index]>=__maxStart) //Filter out all the other junk because we don't want to count rows before the skip RETURN IF(ISBLANK(__max),1,IF(__max=[Index]-1,COUNTX(__tmpTable3,[Index])+1,1)) //If __max is blank, we know that we are at the start of the table, so 1. If the max index of our original table is 1 less than the current index, we know that we are in sequence so we count all of our filtered rows (which don't include rows past a "skip"), otherwise return 1 because we know we are on the row immediately after a "skip.
