Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a problem dealing with shift days. My sample table looks like this:
DatetimeStart | DatetimeFinish | Shift | Weekday | Mastercalendar | ORDERID |
13.06.2021 22:55:01 | 13.06.2021 23:45:01 | Nightshift | Sunday | 13.06.2021 | 12345 |
13.06.2021 23:00:00 | 13.06.2021 23:45:01 | Nightshift | Sunday | 13.06.2021 | 12346 |
14.06.2021 01:00:00 | 14.06.2021 02:00:00 | Nightshift | Monday | 14.06.2021 | 12347 |
14.06.2021 04:00:00 | 14.06.2021 05:00:00 | Nightshift | Monday | 14.06.2021 | 12348 |
14.06.2021 06:00:00 | 14.06.2021 11:00:00 | Dayshift | Monday | 14.06.2021 | 12349 |
14.06.2021 11:00:00 | 18.06.2021 13:00:00 | Dayshift | Monday | 14.06.2021 | 12350 |
15.06.2021 14:30:00 | 15.06.2021 15:00:00 | Lateshift | Tuesday | 15.06.2021 | 12351 |
16.06.2021 15:00:00 | 16.06.2021 17:00:00 | Lateshift | Wednesday | 16.06.2021 | 12352 |
17.06.2021 11:00:00 | 17.06.2021 14:00:00 | Dayshift | Thursday | 17.06.2021 | 12353 |
The shift column is build very typically with if statment:
When hour is from 6 to 14, "Dayshift", 14 to 22 "Lateshift", 22 to 06, "Nightshift".
My problem is... when I want to know the counts of all orders or duration etc. of Monday nightshift (which starts on a Sunday), semantically the time actually starts from the same night one day before from 22:00:00 on. And the counts of orders or the duration of start and finishtimes, also belongs to the nightshift which last until next day morning 06:00 o'clock.
That means whatever nightshift I want to looka at , or counting of orders etc. it should start from one day before at 22:00:00.
What is best practice here?
Thank you very much in advance.
Best.
Probably there is a neater solution.
Until someone provides it, you can try with steps below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdA9C4MwEIDhv1Iyi1y+beaObZcKHcRB0FYXC1UH/30tMSbGQJFClnsJD9xlGcI0BhETIPhAiOJcAUbRqlLF5nptnnXf1c2jn4bb0JbFuPr7HQhlHOVR5hEA0/sXFhpmCwHYwk4lS13Bl5eBmQfLDcyCMN8LJxtYhGBs9zgV42/26LMOgBNb6S6Wg2a5BZiihnWqPcO56CvjpkPVzTD3YKxhESLcKoPwvSrbhRYeTTQtg6eQ7iaBU6T18Daw9GCK8vwD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DatetimeStart = _t, DatetimeFinish = _t, #"Shift " = _t, Weekday = _t, Mastercalendar = _t, ORDERID = _t]),
Removed = Table.RemoveColumns(Source,{"Shift ", "Weekday", "Mastercalendar"}),
Locale = Table.TransformColumnTypes(Removed, {{"DatetimeStart", type datetime},{"DatetimeFinish", type datetime}}, "hr-HR"),
AddedHrs = Table.TransformColumns(Locale,{{"DatetimeStart", each _ + #duration(0,2,0,0), type datetime}, {"DatetimeFinish", each _ + #duration(0,2,0,0), type datetime}}),
ShiftCol = Table.AddColumn(AddedHrs, "Shift", each if [DatetimeStart]>= DateTime.From(Date.From([DatetimeStart])) and [DatetimeStart]< DateTime.From(Date.From([DatetimeStart])) + #duration(0,8,0,0)
then "Nifghtshift"
else if [DatetimeStart]>= DateTime.From(Date.From([DatetimeStart])+ #duration(0,8,0,0)) and [DatetimeStart]< DateTime.From(Date.From([DatetimeStart])) + #duration(0,16,0,0)
then "Dayshift"
else "Latehift"),
WeekdayCol = Table.AddColumn(ShiftCol, "Weekday", each Date.DayOfWeekName([DatetimeStart])),
#"Added Custom" = Table.AddColumn(WeekdayCol, "Mastercalendar", each Date.From([DatetimeStart]), type date),
Reset = Table.TransformColumns(#"Added Custom",{{"DatetimeStart", each _ - #duration(0,2,0,0), type datetime}, {"DatetimeFinish", each _ - #duration(0,2,0,0), type datetime}}),
FINAL = Table.ReorderColumns(Reset,{"DatetimeStart", "DatetimeFinish", "Shift", "Weekday", "Mastercalendar", "ORDERID"})
in
FINAL
Hello @Jakinta , thank you for the quick reply. The first part with "binary.FromText..." and then the long code...where does it come from. And did you had to write the code manually or is it possible to get there with clicking through PowerQuery Editor?
Best.