Split Rows Based On Start/End Time. New Row per Day

Hi geiratatea, try this.

Holidays are not included fow now. You should have such holidays table - if you have it, provide it here and I can implement holidays too.

You can edit times at Times step (but please preserve the logic)

• R = Regular
• O1 = Overtime 50%
• O2 = Overtime 100%

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBbS8NAEIX/ypBXi82lUds3iQURqkUfQx+mm00yNNkNe7H03zu5UA1UWJgdZne+c06eB1mNqpJQkO0avIAuocKmkeYCpECrSpOqQGDbIVXKBotgh4aQa7SM4mUcxitYb8KQDzzv4G648TRdcTks8uBTe0dKQqkNiFqKU79vu3+BAp20gKqAzFunW2lA+fbIBa3VgnhcwJlcfaXP4ekITzfJAN//gT/GE3wy52oJb18f72BZQYu9s7LRZ8Cuu38lhhsS2QQBp0Fo5ZAfWX+0VJDsbW9NI1Uxop9GdHIDnTzM0eRka3vgmCrv6hvhjZHKDe47I79Je/sbci/B4Ykzo8ZxHlcZ/JVHKIT2ys3TWP8vKY4GTYcf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Work Item" = _t, #"User Name" = _t, Timestamp = _t, PeriodLength = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Timestamp", type datetimezone}, {"PeriodLength", Int64.Type}}, "en-US"),
    Times = [ R_Start = #time(8, 0, 0),
    R_End = #time(16, 0, 0),
    O1_Start = R_End,
    O1_End = #time(19, 0, 0),
    O2_Start = O1_End,
    O2_End = #time(8, 0, 0) ],
    MinutesList = [ R_Minutes = List.Buffer(List.Times(Times[R_Start],
                    if Times[R_End] < Times[R_Start] then Duration.TotalMinutes(#time(23, 59, 0) - Times[R_Start] + Times[R_End] - #time(0, 0, 0)) +2 else Duration.TotalMinutes(Times[R_End] - Times[R_Start]) +1, #duration(0,0,1,0))),
    O1_Minutes = List.Buffer(List.Times(Times[O1_Start],
                    if Times[O1_End] < Times[O1_Start] then Duration.TotalMinutes(#time(23, 59, 0) - Times[O1_Start] + Times[O1_End] - #time(0, 0, 0)) +2 else Duration.TotalMinutes(Times[O1_End] - Times[O1_Start]) +1, #duration(0,0,1,0))),
    O2_Minutes = List.Buffer(List.Times(Times[O2_Start],
                    if Times[O2_End] < Times[O2_Start] then Duration.TotalMinutes(#time(23, 59, 0) - Times[O2_Start] + Times[O2_End] - #time(0, 0, 0)) +2 else Duration.TotalMinutes(Times[O2_End] - Times[O2_Start]) +1, #duration(0,0,1,0)))
  ],
    StepBack = ChangedType,
    Ad_StartTime = Table.DuplicateColumn(StepBack, "Timestamp", "Start Time"),
    Ad_EndTime = Table.AddColumn(Ad_StartTime, "End Time", each [Start Time] + #duration(0,0,0,[PeriodLength]), DateTimeZone.Type),
    Ad_WorkingMinutesRecord = Table.AddColumn(Ad_EndTime, "Working Minutes", each 
     [ TimeStart = DateTime.Time([Start Time]),
       TimeEnd = DateTime.Time([End Time]),
       WokrkingMinutes =  List.Times(TimeStart, if TimeEnd < TimeStart then Duration.TotalMinutes(#time(23, 59, 0) - TimeStart + TimeEnd - #time(0, 0, 0)) +2 else Duration.TotalMinutes(TimeEnd - TimeStart) +1, #duration(0,0,1,0)),
       R_WorkingMinutes = List.Count(List.Intersect({MinutesList[R_Minutes], WokrkingMinutes})) -1,
       O1_WorkingMinutes = List.Count(List.Intersect({MinutesList[O1_Minutes], WokrkingMinutes})) -1,
       O2_WorkingMinutes = List.Count(List.Intersect({MinutesList[O2_Minutes], WokrkingMinutes})) -1
     ][[R_WorkingMinutes], [O1_WorkingMinutes], [O2_WorkingMinutes]], type record),
    TransformWorkingMinutesRecord = Table.TransformColumns(Ad_WorkingMinutesRecord, {{"Working Minutes", each Table.SelectRows(Record.ToTable(_), (x)=> x[Value] > 0), type table}}),
    ExpandedWorkingMinutesTable = Table.ExpandTableColumn(TransformWorkingMinutesRecord, "Working Minutes", {"Name", "Value"}, {"Time Type", "Hours"}),
    TransformColumns = Table.TransformColumns(ExpandedWorkingMinutesTable,
     { { "Time Type", each if Text.StartsWith(_, "R_") then "Regular" else if Text.StartsWith(_, "O1_") then "Overtime 50%" else "Overtime 100%", type text },
       { "Hours", each _ / 60, type number } } ),
    Ad_Coefficient = Table.AddColumn(TransformColumns, "Coefficient", each if [Time Type] = "Regular" then 1 else if [Time Type] = "Overtime 50%" then 1.5 else 2, type number)
in
    Ad_Coefficient