Calculate occupancy by half hour for hospital waiting room

[Anonymous]

I am trying to calculate the waiting room occpuancy at a hospital by half hour intervals using Power Query. I found a solution for hourly occupancy on this board but I when I try to break it down to the half hour level I am getting stuck (I am new to Power Query). I have an Excel file that does this but it's cumbersome and tedious so I am hoping there is a more automated way to do it using Power Query. 

As I mentioned, I want to count people who are in the waiting room during half hour intervals. So if someone checks in at 12:15pm and is roomed at 2:05pm they would be counted in the 12p, 12:30, 1p, 1:30p and 2p groupings. 

I created one dim table with the dates/times (as one date/time field) for each date and all the half hour increments I need. 

Below is a sample (de-identified) of my data. Any help is greatly appreciated! 

Check In	Roomed	Patient ID
11/27/20 12:17 AM	11/27/20 12:37 AM	1
11/27/20 12:42 AM	11/27/20 12:45 AM	2
11/27/20 1:14 AM	11/27/20 1:22 AM	3
11/27/20 1:28 AM	11/27/20 1:33 AM	4
11/27/20 1:37 AM	11/27/20 1:44 AM	5
11/27/20 3:44 AM	11/27/20 3:47 AM	6
11/27/20 4:57 AM	11/27/20 5:15 AM	7
11/27/20 4:58 AM	11/27/20 5:09 AM	8
11/27/20 5:05 AM	11/27/20 5:18 AM	9

[Anonymous]

another differente solution and view

[Anonymous]

This worked wonderfully until I applied it to my bigger dataset and I got an error on the fields where the patient checked in and was roomed on different dates- I'll include a few examples below. I get an error message saying "The 'increment' argument is out of range" - any ideas?

[Anonymous · Accepted solution]

try this.

Pay attention to how times like 12:05 AM are interpreted

PS

The solution works on the assumption that the wait does not last more than a day.

I really hope, for the patients, that they don't have to be so patient. 😁

PPS

version 4 aims to be much more general. test it!

[Anonymous]

Thank you so much for your response! Apologies for not being clear on the output - I just need a list of the half hour increments by date and then the count of patients in the waiting room for each increment. Hoping to eventually graph it by month with the half hour times across the x-axis, but struggling to get the counts by date correct. 

This solution seems to be the closest to what I need, but it looks like some of the patients are being counted in too many groups - for example patient 1 should only be in 11/27/2020 12:00a and 11/27/2020 12:30a (patient checked in 12:17am and roomed 12:37am) but in this solution it looks like patient 1 is being counted at 1am, 1:30am etc. 

[Anonymous]

fro this input

i get this output:

to make your observations refer to these table(*) otherwise it is difficult for me to understand what you mean

(*)I changed data from your example, to make the intervals more meaningful of different situations.

I don't know, because you didn't say anything about it, how much is the maximum waiting time.

[Anonymous]

(*) is a perfect solution, sorry I didn't realize you changed the times from my original data. There is no max wait time - it is a 24/7 emergency department so each day would have the 24 hours broken out by half hour. Thank you!

[Anonymous]

perhaps I underestimated the difficulty of the problem.

here is a corrected version and, I believe, responding to the request.

[Anonymous]

you haven't described what kind of output you want. Maybe this is close to what you are looking for