Reset counter and ignore previous index after event

[PomJack]

I am trying to create a report that allows me to monitor the performance of different dies based on their usage and the number of events that they have.

When one of the dies wears out, it is repaired and the hit count resets. I only care about the performance of the die on it's most recent repair. I would like my output of my power query to look something like the table below with the index column being the additional column. The index should reset after the die hits value drops below the most recent die hits value for that die. All dies need to have their own index. 

Event		Die Hits		Die		Index
X		40000		A		-
X		40520		A		-
X		500		B		1
Y		600		B		2
X		689		B		3
X		41560		A		-
Y		86		A		1
Y		260		A		2
Y		823		B		4

Thanks for your help.

[Omid_Motamedise]

Hi, 
Use this solution (I split it in 3 steps to be more clear):
note: source is your table.

let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Added_Index = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(Added_Index, "Step1", each Table.SelectRows(Added_Index, (x)=> x[Index]<[Index] and x[Die]=[Die])[Die Hits]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [Step1]={} then 1 else List.Count(List.LastN([Step1],(x)=>x<[Die Hits]))+1)
in
#"Added Custom1"

I hope you find it helpful and if you have any other question, do not hesitate and ask.

[ronrsnfld]

In your example, the first series of "A" does not increment the index, but it does for the first series of "B". What is the logic?

[PomJack]

I apologize that the example is not clear. 

The index will increase when ever the the same die reoccurs with a larger die hits value. The index resets for a die when the die hits is less than the most recent die hits value for that die as shown in the table. 

Event		Die Hits		Die		Index
X		40000		A		1
X		40520		A		2
X		500		B		1
Y		600		B		2
X		689		B		3
X		41560		A		3
Y		86		A		1
Y		260		A		2
Y		823		B		4

I only care about the die hits and index for each die after the most recent reset, which is why I left those cells blank, creating this confusion. Ideally, those cells would be left blank, so I can easily filter them out after the column is created. 

[ronrsnfld · Accepted solution]

Now that I understand better what you are doing, I deleted my last response as this code will reproduce what you showed in your First example, with blanks for all except the last sequence of Die Hits:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilDSUTIxAAIg7agUqwMTMTVCFTEFq3AC8yOBLDMkPkjezMIShW9iaGqGMAGkw8IMhWuELm1kDDEgFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Event = _t, #"Die Hits" = _t, Die = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event", type text}, {"Die Hits", Int64.Type}, {"Die", type text}}),

//Index to retain original order
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "IndexX", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Die"}, {

//Use List.Generate to implement the logic for the resetting count
        {"Index", (t)=> 
            let 
                #"Add Reset Index"=
                    Table.FromColumns(
                        Table.ToColumns(t)
                        & {List.Generate(
                                ()=>[i=1, idx=0],
                                each [idx] < Table.RowCount(t),
                                each[i=if t[Die Hits]{[idx]+1} > t[Die Hits]{[idx]} then [i]+1 else 1, idx=[idx]+1],
                                each [i])},
                    {"Event","Die Hits", "Die","IndexX","Index"}),
                #"lastOne" = List.PositionOf(#"Add Reset Index"[Index],1,Occurrence.Last),
                #"Blank Except Last" =
                    Table.ReplaceValue(
                        #"Add Reset Index",
                        each[Index],
                        each [IndexX] ,
                        (x,y,z)=> if z < #"Add Reset Index"{#"lastOne"}[IndexX] then null else y,
                        {"Index"})
            in 
                #"Blank Except Last",
                type table[Event=text, Die Hits=Int64.Type,Die=text,IndexX=Int64.Type, Index=Int64.Type]}
                }),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Die"}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Removed Columns", "Index", {"Event", "Die Hits", "Die", "IndexX", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Index",{{"IndexX", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"IndexX"})
in
    #"Removed Columns1"

[Omid_Motamedise]

The question was not completely clear to me, if you mean there is a specific value like 1000 as hit limitation, use the next formula, otherwise please let me know why the Index for first B is 1