Process mining gaps and islands

[ToreVingare]

Hello! 

I'm working on a process mining dashboard at my hospital. I have created all the timestamps that happens to our patient in the patientgroup but I can't succeed with the creation of a ID for every connective period in the date per patient. All rows in the data have a activity, start time, end time, location and a person identificationsnumber. 

Short example:

Person*******	Activite********	Start********************	End*******************	Location**************
Person 1	Surgery	2019-06-05 17:29	2019-06-05 18:02	Surgery 1
Person 1	Ward	2019-06-05 18:03	2019-06-06 22:44	Ward 135
Person 1	Ward	2019-06-06 22:45	2019-06-14 10:00	Ward 354
Person 1	Ward	2020-03-26 10:54	2020-03-26 16:48	Ward 237
Person 1	Ward	2020-03-26 16:49	2020-03-27 15:45	Ward 236
Person 1	Ward	2020-03-27 15:46	2020-04-03 12:29	Ward 303
Person 1	Ward	2020-04-04 04:51	2020-04-04 15:40	Ward 303

What I'm trying to achieve is this:

Person*******	Activite********	Start*******************	End********************	Location************	Group*****
Person 1	Surgery	2019-06-05 17:29	2019-06-05 18:02	Surgery 1	1
Person 1	Ward	2019-06-05 18:03	2019-06-06 22:44	Ward 135	1
Person 1	Ward	2019-06-06 22:45	2019-06-14 10:00	Ward 354	1
Person 1	Ward	2020-03-26 10:54	2020-03-26 16:48	Ward 237	2
Person 1	Ward	2020-03-26 16:49	2020-03-27 15:45	Ward 236	2
Person 1	Ward	2020-03-27 15:46	2020-04-03 12:29	Ward 303	2
Person 1	Ward	2020-04-04 04:51	2020-04-04 15:40	Ward 303	2 (3)

The two rows marked with bold is the end and start of a new group. The first three rows is in a consecutive order. End time is just before the next rows start time. I want the first three rows to have the same identifier (1) and the next three rows a new identifier (2). 

The last row is just a bonus but if I could specifie the "threshold" for what I think is consecutive that would be a plus but absoluty not necessay. Lets say that if a new row is less the 12 hours later then accept is as the same group. Something like that. But just a bonus!

Each person can have n-groups. 

Here is some more example data for a better understanding:

Person*******	Activite********	Start******************	End******************	Location***********	Group*****
Person 2	Ward	2019-06-24 14:57	2019-06-27 20:50	Ward 137	1
Person 2	Ward	2019-06-27 20:51	2019-06-28 20:32	Ward 137	1
Person 2	Ward	2019-06-28 20:33	2019-07-06 09:05	Ward 67	1
Person 2	Ward	2019-07-06 09:06	2019-07-09 14:19	Ward 136	1
Person 2	Ward	2019-07-09 14:20	2019-07-16 13:07	Ward 603	1
Person 2	Ward	2019-11-01 13:05	2019-11-10 14:06	Ward 357	2
Person 2	Ward	2019-11-10 14:07	2019-11-10 15:10	Ward 25	2
Person 2	Ward	2019-11-10 15:11	2019-11-12 16:33	Ward 25	2
Person 2	Ward	2019-11-12 16:34	2019-11-20 16:01	Ward 357	2
Person 2	Ward	2019-11-20 16:02	2019-11-21 12:14	Ward 25	2
Person 2	IVA	2019-11-21 12:15	2019-11-21 21:00	IVA	2
Person 2	Ward	2019-11-21 21:01	2019-11-22 13:14	Ward 25	2
Person 2	Ward	2019-11-22 13:15	2019-11-27 13:53	Ward 25	2
Person 2	Ward	2019-12-03 11:24	2019-12-03 16:44	Ward 25	3
Person 2	IVA	2019-12-03 16:45	2019-12-03 22:36	IVA	3
Person 2	Surgery	2019-12-03 22:37	2019-12-03 23:26	Surgery 1	3
Person 2	Ward	2019-12-03 23:27	2019-12-03 23:29	Ward 135	3
Person 2	IVA	2019-12-03 23:30	2019-12-04 13:10	IVA 2	3
Person 2	Ward	2019-12-04 13:11	2019-12-12 14:22	Ward 135	3
Person 2	Ward	2019-12-12 14:23	2019-12-16 17:41	Ward 135	3
Person 2	Ward	2020-08-12 18:34	2020-08-13 01:08	Ward 91	4
Person 2	Ward	2020-08-13 01:09	2020-08-13 12:30	Ward 137	4
Person 2	Ward	2020-08-13 12:31	2020-08-20 12:40	Ward 10	4
Person 3	Ward	2019-05-23 19:57	2019-05-24 11:07	Ward 135	1
Person 3	Surgery	2019-05-24 11:08	2019-05-24 13:39	Surgery 1	1
Person 3	Post-op	2019-05-24 13:40	2019-05-24 19:49	Post-op 96	1
Person 3	Ward	2019-05-24 19:50	2019-05-26 19:20	Ward 135	1
Person 4	Surgery	2019-12-19 13:43	2019-12-19 14:04	Surgery 1	1
Person 4	Ward	2019-12-19 14:05	2019-12-19 15:19	Ward 135	1
Person 4	Post-op	2019-12-19 15:20	2019-12-19 20:03	Post-op 95	1
Person 4	Ward	2019-12-19 20:04	2019-12-21 15:47	Ward 135	1

Thank you so much in advanced a have a great weekend!

[Greg_Deckler · Accepted solution]

@ToreVingare - OK, this was far easier than I thought it would be. But, it is a 2 step process:

Consequective = 
    VAR __Previous = MAXX(FILTER('Table (3)',[Person]=EARLIER([Person]) && [End]<EARLIER([Start])),[End])
RETURN
    IF(ISBLANK(__Previous) || ([Start] - __Previous)*1. < .000695,1,0)


Group = COUNTROWS(FILTER('Table (3)',[Person]=EARLIER([Person]) && [Start] <= EARLIER([Start]) && [Consequective]=0))+1

Those are columns. Attached a PBIX file below sig. Table (3). 

[Anonymous]

Hi @Greg_Deckler,

Is there a way we achieve same in PowerQuery? If yes, can you update the same PBIX. I'm struggling to achieve it.

Thank you so much in advance.

[ToreVingare]

Wow! It works great! You saved my weekend! 🙏

[Greg_Deckler]

@ToreVingare - This is the second one of these I have seen now so I guess I am going to have to sit down and give this some serious thought.