Time Span between 2 rows with conditions

[ABR002]

This honestly caused me a headache even summing it up to where I hope someone else can understand. 
I have a table that tracks the logs of a system, there are several tags that occur, some result in a status of down, some result in a status of up:

Time	Tag	System Status
8:45	On	Up
8:46	Ready	Up
8:49	Shut Down	down
8:51	Restart	down
8:53	Active	Up
8:57	Running	Up
9:00	Not Ready	Down
9:01	Shut Down	Down
9:02	Restart	down
9:10	On	Up
9:15	Ready	Up
9:21	Off	Down

I need to calculate the time it spends in Up and Down status. Here is the desired result:

Time	2nd Time	Status	Time in Status
8:45	8:50	Up	5
8:50	8:53	Down	3
8:53	9:00	Up	7
9:00	9:10	Down	10
9:10	9:21	Up	11

The issue I'm running into is that there are consecutive Up satuses and consecutive down statuses. I need to take the earliest time it was in an up status and compare it with the first instance of a down status, and from there the first instance of an up status, so the time it was triggerred as being down to the time it was triggerred as being up. I basically don't want the times that are crossed out, would like to just delete those rows. So for the first time it went down, 8:49, I would want to calculate the time to 8:53, the time it said it was down, I don't need 8:51, as the system was already down when that entry came up.

In searching this board I actually found a solution that pretty much got me to the five yard line:

I created 2 index columns, first one from 0, second one from 1. Then I merged the table with itself using those 2 index columns on a left outer merge. From there I made a conditional column to eliminate all rows where the 2 system status column values were the same. 

This would be a perfect solution if the logs simply went from Up, Down, Up, Down, Up, Down, Up~, but they don't, the consecutive instances cause this to happen:

Time	Added Index1.Time		System Status	Time in Status
8:45	8:46		Up	1
8:49	8:51		down	2
8:53	8:57		Up	4
9:00	9:01		Down	1
9:10	9:15		Up	5

For the time of the status change, I get the most recent entry with the up or down tag. So using the bold rows as an example, the 8:49 time is correct, but the 8:51 is not, as it's a down status that came after a down status, so I would want 8:53 to be there, which is an up status

[ABR002]

Thanks, works great

[lbendlin · Accepted solution]

Your sample data doesn't seem to match your explanation.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrAyMVXSUfLPAxKhBUqxOmAhMyAvKDUxpRJF1BLIC84oLVFwyS8HqQdTEDlTQ7CO4pLEohI0GWMg3zG5JLMsFdkwU3OQhtK8vMy8dIS4pZWBAZDnl1+iALMebhRQzhCXA4ByRtgdYGllaIDmP6CQKab/LK2MQMb7p6XBtccCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t, Tag = _t, #"System Status" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type time}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"System Status"}, {{"Time", each List.Min([Time]), type nullable time}},GroupKind.Local)
in
    #"Grouped Rows"

Use Grouping with GroupKind.Local