Get Start & End Times for each state change

[Dan_SP]

Hi there,

I want to help to extract the start and end times for each state change within a day and put it in a table to visualize the same. We have record for every 2 mins of the status of the agent. Note an agent can have same status multiple times in a day so everytime he changes state I want to know the start time and end time of the same.

My data looks something like this.

Index	Username	Status	Timestamp
1	AB	Available	4/22/2022 13:50
2	AB	Available	4/22/2022 13:52
3	AB	Available	4/22/2022 13:54
4	AB	Break	4/22/2022 13:56
5	AB	Break	4/22/2022 13:58
6	AB	Break	4/22/2022 14:00
7	AB	Lunch	4/22/2022 14:02
8	AB	Lunch	4/22/2022 14:04
9	AB	Available	4/22/2022 14:06
10	CD	Available	4/22/2022 13:30
11	CD	Available	4/22/2022 13:32
12	CD	Break	4/22/2022 13:34
13	CD	Break	4/22/2022 13:36
14	CD	Lunch	4/22/2022 13:38
15	CD	Lunch	4/22/2022 13:40
16	CD	Available	4/22/2022 13:42
17	CD	Available	4/22/2022 13:44
18	CD	Break	4/22/2022 13:46
19	CD	Break	4/22/2022 13:48
20	AB	Available	4/23/2022 13:00
21	AB	Available	4/23/2022 13:02

What I want to achieve is 

Username	Status	StartTime	EndTime
AB	Available	4/22/2022 13:50	4/22/2022 13:54
AB	Break	4/22/2022 13:56	4/22/2022 14:00
AB	Lunch	4/22/2022 14:02	4/22/2022 14:04
AB	Available	4/22/2022 14:06	4/22/2022 14:06
CD	Available	4/22/2022 13:30	4/22/2022 13:32
CD	Break	4/22/2022 13:34	4/22/2022 13:36
CD	Lunch	4/22/2022 13:38	4/22/2022 13:40
CD	Available	4/22/2022 13:42	4/22/2022 13:44
CD	Break	4/22/2022 13:46	4/22/2022 13:48
AB	Available	4/23/2022 13:00	4/23/2022 13:02

I tried getting previous state using this link and try something but I am getting a memory error (right now this table has 2 lakh records). I'd appreciate any help

[Vijay_A_Verma · Accepted solution]

This can be very easily done through PQ using GroupKind.Local property of Table.Group. See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdPPCoJAEMfxV5E9C+7Mjv+6ZR17A/FgIRRJh6Cev21nNwJ15iKCn8Pvu2rfGzC52Xffy3u8zeN5nvw9FYgFWsQM3K60Zsh7gzrEAJ0OKUBKsHtO432BqoBKGTUBVQKineWAOqHT63G5ZgvF6xtF8fRWafSQ54P1Dw5H6TQczwPQJU8ETHL1TBxPBCeruI+SWuv1jA8YSplRbKjUBooNtS5jRyN2UOxoZcUZaDfem/vJ+LHg1n/xJ33J8AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Username = _t, Status = _t, Timestamp = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Username", type text}, {"Status", type text}, {"Timestamp", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Username", "Status"}, {{"StartTime", each List.Min([Timestamp]), type nullable datetime}, {"EndTime", each List.Max([Timestamp]), type nullable datetime}}, GroupKind.Local)
in
    #"Grouped Rows"

[Dan_SP]

@Vijay_A_Verma , this is what I was looking for. Thank you so much for your help.

[amitchandak]

@Dan_SP , Create a new table like

summarize(Table,[Username],[Status], "Start Time", Min(Table[Timestamp]) , "End Time", Min(Table[Timestamp]))

[Dan_SP]

@amitchandak, thanks for your response but this does not give me the desired result as it summarizes the status and gives the overall start and end time of a particular status. But what I want is every time the status changes the start time of it and the end time when the status changes to something different.

So if an agent went for a Break twice in a day I would want the start and end time of 1st break (same day) and start and end time of 2nd break (same day). But your solution gives Start time of 1st break and End Time of 2nd break. Hope I could put my point across correctly.