Timeframe Question

[Anonymous]

I am hoping you can point me to where I can find information concerning timestamps. I am able to split the  timestamp into time and date columns. In my reports I am able to query by day but this information is from 00:00 to 23:50. I need to query and break down the data by shift.  I need to create a new column with  the shift information so I can create a new query by shift. The shifts for this data is Day Shift (06:30 – 18:29) and  Second Shift (18:30-06:29). I have taken  courses and looked online but cannot find the information. Can you help?

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

I create a sample in Desktop. You could reference it to have a try and then publish the report to Service.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdE7a8MwEADgv2I0B3w6PWJrC4FsgULTLsaDcFQwODYoGvrzK0tWiNRAFvuG7166riNHPeurJjvyoe9m8X8KsH6xprJGoG2FoKgk/a4jX/PozLX6dNqZe7X8VIebseOQZWOZTRUTIftsfsdhebb8v+VNsI+pvi+X1cgk91FSprgspJlC0SbQmjIvEdaa0ObyqK01ztg1Rth4G7lQAl/WRcyhnwB4kCer58E8TbCVRNjkXgn+7v22PUWe6oenomgSYBobaYRcMZm7w03bcZrCjiLHjWLFjudldj72EUuFMT50q/jb2dM9WerDtiO1Cvnr0/PURkTaKCgO/5CpqEySAen7Pw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Country = _t, #" Product " = _t, #"  Sales " = _t, Datetime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {" Product ", type text}, {"  Sales ", Int64.Type}, {"Datetime", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Datetime])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Time", each DateTime.Time([Datetime])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Time", type time}, {"Date", type date}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Custom", each if 
 [Time] >= Time.From(#time(06,30,0)) 
 and [Time] <= Time.From(#time(18,29,0))
then 
  "Day Shift (06:30 – 18:29) " 
 else 
   " Second Shift (18:30-06:29)")
in
    #"Added Custom2"

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GilbertQ]

Hi there

What you can do is to create a Custom column in Power Query Editor , which will look at the Time column and if it is between your time ranges then make it Day Shift, else make it Second Shift.