Forum Discussion
Helper IISeparate day shift from night shift by splitting into two rows
Hi: Each row below represents each shift, from 6 AM to 6PM represents day shift and 6PM to 6AM represents night shift. For Index # 2713 (2nd row) 1 hour falls in day shift and the remaining 3 hours falls in night. I would like to separate the night shift from the day shift. For this type of shift, I need to create one row for the day shift 1 hour and another a row for the night shift 3 hours. How do I create an extra rows dynamically for mixed (day & night )shifts? Any assistance would be greatly appreciated. Thank you.
Please see this M code for an example of how to do this. Create a new blank query and, in the Advanced Editor, replace the text there with this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLTNzTUNzIwMlAwtzIwACKFAF8UYUNDhHisTrSSEUTWCCJriqYJKmyJpCcWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, ShiftStart = _t, ShiftEnd = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"ShiftStart", type datetime}, {"ShiftEnd", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "FirstShiftHours", each if Time.Hour([ShiftStart]) < 18 then if Time.Hour([ShiftEnd]) > 18 then Duration.TotalHours(#time(18,0,0) - DateTime.Time([ShiftStart])) else Duration.TotalHours(DateTime.Time([ShiftEnd]) - DateTime.Time([ShiftStart])) else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "SecondShiftHours", each if Time.Hour([ShiftEnd]) >= 18 then if Time.Hour([ShiftStart]) < 18 then Duration.TotalHours(DateTime.Time([ShiftEnd])-#time(18,0,0)) else Duration.TotalHours(DateTime.Time([ShiftEnd]) - DateTime.Time([ShiftStart])) else null),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom1", {"Index", "ShiftStart", "ShiftEnd"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type number}})
in
#"Changed Type1"If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
4 Replies
- mahoneypat
Microsoft Employee
Please see this M code for an example of how to do this. Create a new blank query and, in the Advanced Editor, replace the text there with this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLTNzTUNzIwMlAwtzIwACKFAF8UYUNDhHisTrSSEUTWCCJriqYJKmyJpCcWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, ShiftStart = _t, ShiftEnd = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"ShiftStart", type datetime}, {"ShiftEnd", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "FirstShiftHours", each if Time.Hour([ShiftStart]) < 18 then if Time.Hour([ShiftEnd]) > 18 then Duration.TotalHours(#time(18,0,0) - DateTime.Time([ShiftStart])) else Duration.TotalHours(DateTime.Time([ShiftEnd]) - DateTime.Time([ShiftStart])) else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "SecondShiftHours", each if Time.Hour([ShiftEnd]) >= 18 then if Time.Hour([ShiftStart]) < 18 then Duration.TotalHours(DateTime.Time([ShiftEnd])-#time(18,0,0)) else Duration.TotalHours(DateTime.Time([ShiftEnd]) - DateTime.Time([ShiftStart])) else null),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom1", {"Index", "ShiftStart", "ShiftEnd"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type number}})
in
#"Changed Type1"If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
- lbendlin
Super User
Create a custom column that concatenates the number of hours for the day shift and for the night shift.
So for index #2713 that column would read "1,3". For #2581 it would be a single value "2"
Then Split that column by delimiter (comma) and in advanced options choose "Split into Rows"
That will create separate rows if an index falls into both shifts.
