How can I create an Index column based on 2 columns (time and the process level)

I am trying to obtain the Index column (highlighted in yellow) that can count the number of times the product ID has iterated through 1 – 6 denoted by the Status Key and the tible is sorted in chronological order.

For detail: The Product ID follows a chronological order denoted by the timestamp and the Status Key can fluctuate back and forth (this a type of approval or rejecting process and the keys indicate the approval stage). I want the index column to count +1 every time the Status key starts to loop 1-6. The starting number will not always start at 1.

Answer

You do not state a clear rule for how to detect when the Status Key starts to increment. But this approach using List.Generate should be adaptable in case the method I intuit from your data is not correct.

I increment the count by one starting after Status Key = 6

Source

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Products"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Product ID", Int64.Type}, {"Date", type datetime}, {"Status Key", Int64.Type}}),

//Generate List for Index column
// incrementing after SK = 6
    sk = #"Changed Type"[Status Key],
    IndexList = List.Generate(
        ()=>[Index = 1, idx = 0],
        each [idx] < List.Count(sk),
        each [Index = if sk{[idx]} = 6 then [Index]+1 else [Index],
                idx=[idx]+1],
        each [Index]
                            ),
//Add Index column to table
    integer = Int64.Type,
    newTble = Table.FromColumns(
                Table.ToColumns(#"Changed Type") & {IndexList},
                type table[Product ID = integer, Date = datetime, Status Key = integer, Index = integer]
    )
in
    newTble

Results