Classification based on time stamp

Power Query
1

Hello. First question I post here. Not sure if the query editor or M code topic is the right one but here it goes:

I have a column with time stamps. Ex:

Time stamp
08:34
17:02
21:50
13:21
03:15

And I’d like to create another column with shifts based on time stamps:

Day shift (from 06:00 to 18:00)
Night shift (from 18:00 to 06:00)

Final result desired 2 columns (time stamp column) (shift column):

08:34 Day shift
17:02 Day shift
21:50 Night shift
13:21 Day shift
03:15 Night shift

How can I achieve this in the query editor?

2

Hi @Josimar_Biosse,

Copy this into a new blank query and see if that works for you

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrCwMjZRitWJVjI0tzIwArOMDK1MDSBixlZGhmCWgbGVoalSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Time stamp" = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Time stamp", type time}}),
    AddShift = Table.AddColumn(ChType, "Custom", each if List.Contains( List.Times( #time(6,0,0), 12*60*60, #duration(0,0,0,1)), [Time stamp] ) then "Day shift" else "Night shift", type text)
in
    AddShift

.
With this result

image
image1313×186 10.4 KB

.

I hope this is helpful

2 Likes
3

Awesome, I was just working on this but did it in a much more roundabout way…all the time I was thinking…“how would/will @Melissa do this”…I don’t need to wonder anymore :rofl: :partying_face:

2 Likes
4

Wow! That was fast! Thanx @Melissa

I’ll give it a try and then get back to you.

Best regard

2 Likes
5

So…your use of List.Times…you create a starting point of 6am by using (6, 0, 0), create a list/range of values by multiplying the 12 hours & 60 mins & 60 secs…incrementing by 1 second at a time and if the time falls in this range then it is “Day Shift”?

3 Likes
6

@DavieJoe exactly, this creates a list with a starting value 06:00:00 and an ending value of 17:59:59
BTW you can copy that bit into a new blank query to see the list it generates, if that’s helpful

2 Likes
7

Cool, thanks as always :+1:t2::tada:

8

You’re more than welcome David!
…apologies for ruining your mojo earlier :upside_down_face:

1 Like
9

Haha, not at all, I solved it in a roundabout way……far happier knowing how you would do it.

10

Hi @Melissa thanx once again. It really worked for the values I posted above, as an example. But my true goal is to apply to a table which has a Time stamp column. Therefore, how can I do it? Changing the source at the M code perhaps? But how?

11

Query like this:

time stamp values
time stamp values576×520 61.8 KB

12

I’m not sure I understand what you’re asking here @Josimar_Biosse

1 Like
13

Hi @Josimar_Biosse,

Don’t follow either… please provide a sample that clarifies why the provided solution doesn’t work for you. Looking at the [Time stamp] column in your screenshot that appears to be of the correct datatype so wouldn’t expect any issue there

2 Likes
14

Hi once again @Melissa and @DavieJoe . Thank you for your patience :ghost:. Sorry if my explanation wasn’t clear enough. I’ll explain further.

The small sample of the dataset I’ve provided on first hand was just an example. And indeed, I copied @Melissa 's solution and it worked perfectly, for that small example of dataset.

What I really want is: how can I apply the solution provided (the steps) to a larger dataset? That’s why I provided an example of the dataset I have on the picture above. It’s not just 5 rows.

And by the way (it’s not a secret) this is dataset from the current EDNA challenge 19. Are you following me now?

1 Like
15

Right, well in that case just add a custom colum and enter this in the window that pops up:

if List.Contains( List.Times( #time(6,0,0), 12*60*60, #duration(0,0,0,1)), [Time stamp] ) then "Day shift" else "Night shift"

.
and/or you can also examine a Time table

2 Likes
16

Thanx @Melissa . I’ll give it a try right away.

1 Like
17

Great to hear you’re getting stuck into Challenge 19

1 Like
18

Worked beautifully! Thank you @Melissa .

1 Like
19

Thanx for the encouragement @DavieJoe . It’s just a first shot. :wink:

1 Like
20

Everyone has to have a 1st time, I’m sure you’ll find it valuable! Look forward to your entry @Josimar_Biosse :+1:t2:

2 Likes