This topic contains 1 reply, has 2 voices, and was last updated by 
-
Posts
-
I’m trying to create a conditional column that groups hospital shifts into three groupings in PowerQuery:
- 8am-3pm
- 3pm-113opm
- 1130pm-8am
The issue i’m having is for the hours between 1130pm and 8am. I have a separate column for TIME displayed as hh:mm:00
Here is the formula in powerquery:
= Table.AddColumn(#”Changed Type”, “Shift Grouping”, each
if [Arrival Time] >= #time(8, 1, 0) and [Arrival Time] <= #time(15, 0, 0) then “8a-3p”
else if [Arrival Time] >= #time(15, 0, 0) and [Arrival Time] <= #time(23, 30, 0) then “3p-1130p”
else if [Arrival Time] >= #time(23, 31, 0) and [Arrival Time] <= #time(8, 0, 0) then “1130p-8a” else “None”)
OK. This is just off the top of my head, but I think I see the issue here.
The 1130p-8a am shift crosses midnight, so the test needs to be in two parts, the first would be for times between 23:30:00 and 23:59:59 and the second test would be fore times from 00:00:00 to 08:00:00. Each test would be wrapped within its own set of parentheses and between the two tests would be an “or”.
Another solution would be to first check if the time is null or empty, then check for the 8a-3p followed by the check from 3p to 11:30p, and then the default would be 11:30p-8a.
Tom
The forum ‘Power Pivot’ is closed to new topics and replies.