Separate day shift from night shift by splitting into two rows

[powerinM]

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.

[mahoneypat · Accepted solution]

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

[powerinM]

Thanks for solution. It works.

[powerinM]

I am unable to accept as solution. I am getting the following error

[lbendlin]

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.