Create a column in Power Query that cycle counts based on the event state of another column

[schilderic]

Event State            Cycle Count

Start                               1

Charge                           1

Discharge                       1

Charge                            2

Discharge                        2

End                                 2

[schilderic]

Thanks Vijay,  You've definitely moved the ball.  The only thing is that were counting the number of charge and discharge (or discharge and charge) cycles.  The first combination is cycle 1 the next is 2, the next is 3 and so forth.  We assign the start and end state to 1 and the last cycle counted respectively.  If there are other states beyond charge or discharge they would be assigned to the cycle previously counted.  So in your case the second discharge would still be 2 and the Dummy State would also be 2.  The End would also be 2.  In this example since charge is the first after start that would be the trigger state to increment the cycle count.  If discharge was first after start it would be the trigger state.

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

I don't know the logic for value 2 for End. I assumed that it is the maximum in that column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5JLCpRitWJVnLOSCxKTwUzXTKLkxE8nBJovNLc3EqF4pLEEgjfNS9FKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Event State" = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    BuffList = List.Buffer(#"Added Index"[Event State]),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Count", each List.Count(List.Select(List.FirstN(BuffList,[Index]), (x)=> x=[Event State]))),
    Maximum = List.Max(#"Added Custom"[Count]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Cycle Count", each if [Event State]="End" then Maximum else [Count]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Count"})
in
    #"Removed Columns"