Forum Discussion
Applicable88
Impactful Individual
5 years agoSame shift time but different day
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...
Jakinta
Solution Sage
5 years agoProbably 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- Applicable885 years ago
Impactful Individual
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.