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

 

[sandy666]

use XL2BB to post your example

 

[HCW1966]

StartTime

sandy66 did it work?

 

[sandy666]

you cannot use xl2bb in Power Query editor
post link to the shared excel file with your example
or
load example to the sheet, select whole range then use xl2bb and post here

 

[HCW1966]

you cannot use xl2bb in Power Query editor
post link to the shared excel file with your example
or
load example to the sheet, select whole range then use xl2bb and post here

PQ Report - WK42.xlsx
	B
2	StartTime
Sheet1

I tried it again, basically its the same data as my original post, only its in a table? I cannot link any file, as my employer doesnt allow

 

[sandy666]

you need to select range of your data first then use xl2bb

 

[HCW1966]

you need to select range of your data first then use xl2bb

Fingers Crossed

PQ Report - WK42.xlsx
	B	C
2	StartTime	StopTime
3	11/10/2020 06:01	11/10/2020 07:31
4	11/10/2020 06:02	11/10/2020 09:09
5	11/10/2020 06:03	11/10/2020 08:01
6	11/10/2020 06:06	11/10/2020 08:04
7	11/10/2020 06:06	11/10/2020 07:26
8	11/10/2020 07:26	11/10/2020 09:29
9	11/10/2020 07:31	11/10/2020 08:56
10	11/10/2020 08:02	11/10/2020 09:39
11	11/10/2020 08:07	11/10/2020 08:38
12	11/10/2020 08:38	11/10/2020 11:01
13	11/10/2020 08:56	11/10/2020 10:32
14	11/10/2020 09:10	11/10/2020 12:41
15	11/10/2020 09:30	11/10/2020 11:37
16	11/10/2020 09:39	11/10/2020 10:56
17	11/10/2020 10:44	11/10/2020 12:18
18	11/10/2020 10:56	11/10/2020 12:54
19	11/10/2020 11:37	11/10/2020 12:55
20	11/10/2020 12:19	11/10/2020 13:13
21	11/10/2020 12:41	11/10/2020 12:57
22	11/10/2020 21:31	12/10/2020 00:05
23	11/10/2020 21:31	11/10/2020 23:06
24	11/10/2020 21:31	12/10/2020 01:09
25	11/10/2020 21:32	11/10/2020 23:09
26	11/10/2020 21:33	12/10/2020 01:48
27	11/10/2020 21:34	11/10/2020 22:44
28	11/10/2020 21:41	11/10/2020 22:52
29	11/10/2020 21:41	12/10/2020 01:30
30	11/10/2020 21:48	11/10/2020 23:10
31	11/10/2020 22:44	12/10/2020 03:12
32	11/10/2020 22:52	12/10/2020 00:36
33	11/10/2020 23:06	11/10/2020 23:50
34	11/10/2020 23:10	12/10/2020 01:48
35	11/10/2020 23:11	12/10/2020 00:59
36	11/10/2020 23:50	12/10/2020 01:19
37	12/10/2020 00:05	12/10/2020 00:30
38	12/10/2020 00:31	12/10/2020 02:30
39	12/10/2020 00:41	12/10/2020 01:20
40	12/10/2020 00:59	12/10/2020 06:49
41	12/10/2020 01:13	12/10/2020 01:13
42	12/10/2020 01:13	12/10/2020 02:23
43	12/10/2020 01:21	12/10/2020 01:42
44	12/10/2020 01:29	12/10/2020 01:58
45	12/10/2020 01:43	12/10/2020 02:46
46	12/10/2020 01:48	12/10/2020 03:04
47	12/10/2020 01:51	12/10/2020 03:24
48	12/10/2020 01:59	12/10/2020 06:51
49	12/10/2020 03:02	12/10/2020 03:46
50	12/10/2020 03:02	12/10/2020 03:50
Sheet1

 

[sandy666]

LOL, ,it works just refresh thread and you'll see

 

[sandy666]

try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DT = Table.TransformColumnTypes(Source,{{"StartTime", type datetime}, {"StopTime", type datetime}}),
    DayName = Table.AddColumn(DT, "Day Name", each Date.DayOfWeekName([StopTime]), type text),
    Time = Table.TransformColumnTypes(DayName,{{"StopTime", type time}}),
    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) or [StopTime] <= #time(22, 29, 59) then "Back Shift" else if [StopTime] >= #time(7, 0, 0) or [StopTime] <= #time(14, 59, 59) then "Early Shift"  else null)
in
    Conditions

 

[sandy666]

or this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DT = Table.TransformColumnTypes(Source,{{"StartTime", type datetime}, {"StopTime", type datetime}}),
    DayName = Table.AddColumn(DT, "Day Name", each Date.DayOfWeekName([StopTime]), type text),
    Time = Table.TransformColumnTypes(DayName,{{"StopTime", type time}}),
    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(7, 0, 0) or [StopTime] <= #time(14, 59, 59) then "Early Shift"  else if [StopTime] >= #time(15, 0, 0) or [StopTime] <= #time(22, 29, 59) then "Back Shift" else null)
in
    Conditions

if something goes wrong change ifs order in Conditions step