Forum Discussion
Time Span between 2 rows with conditions
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 |
| Ready | Up | |
| 8:49 | Shut Down | down |
| Restart | down | |
| 8:53 | Active | Up |
| Running | Up | |
| 9:00 | Not Ready | Down |
| Shut Down | Down | |
| Restart | down | |
| 9:10 | On | Up |
| 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 | Up | 1 | ||
| 8:49 | down | 2 | ||
| 8:53 | Up | 4 | ||
| 9:00 | Down | 1 | ||
| 9:10 | 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
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
2 Replies
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
Thanks, works great
