Power Query - Creating a Custom Column to show Text based on Times

[HCW1966]

Hi There,

Please i want to create a custom column based on the "Stop Time" column below. This column has approx 1,000 rows of information.

What i want to show on the Custom Column is

"Early Shift" if the stop time is between 07:00:00 and 14:59:59
"Back Shift" if the stop time is between 15:00:00 and 22:29:59
"Night Shift" if the stop time is between 22:30:00 and 06:59:59

The above is my primary question, however i have a secondary question, is it possible to create another column showing the Day of the Week (Sun, Mon, Tue..........) that corresponds to the StopTime column date.

Kind Regards Harry

 

[HCW1966]

Hi Sandy666,
Its almost perfect, the only issue that i have is that the last Else If Condition, never gets picked up, i have changed the order, and each time its always the last Else If Condition that never picks up. Its as if the code only looks at the first two Else If Conditions? Other than that its perfect.

 

[sandy666]

try this

if [StopTime] >= #time(7, 0, 0) and [StopTime] < #time(15, 0, 0) then "Early Shift" else if [StopTime] >= #time(22, 30, 0) or [StopTime] < #time(7, 0, 0) then "Night Shift" else "Back Shift"

check if each shift time exist
eg.

13:13:23	Sunday	Early Shift
16:06:25	Sunday	Back Shift
22:44:49	Sunday	Night Shift

 

[HCW1966]

Hi Sandy666,

Yes each shift time exists, when i put in the new code i receive this error

try this

if [StopTime] >= #time(7, 0, 0) and [StopTime] < #time(15, 0, 0) then "Early Shift" else if [StopTime] >= #time(22, 30, 0) or [StopTime] < #time(7, 0, 0) then "Night Shift" else "Back Shift"

check if each shift time exist
eg.

13:13:23	Sunday	Early Shift
16:06:25	Sunday	Back Shift
22:44:49	Sunday	Night Shift

Hi Sandy666,

I finally got it, i changed the first condition to or and it seems to work. I really appreciate your help, and you have shown me so much with this code, so thank you.

Conditions = Table.AddColumn(Time, "Shift",
each if [StopTime] >= #time(22, 30, 0) or [StopTime] <= #time(6, 59, 59) then "Night Shift"
else if [StopTime] >= #time(15, 0, 0) and [StopTime] <= #time(22, 29, 59) then "Back Shift"
else if [StopTime] >= #time(7, 0, 0) and [StopTime] <= #time(14, 59, 59) then "Early Shift" else "Help")

 

[sandy666]

sure, if it works for you that's good
have a nice day