Forum Discussion
Applicable88
5 years agoImpactful Individual
Same 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
5 years agoSolution Sage
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- Applicable885 years agoImpactful 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.