Split row every monday at 6am

Hi lirakoto ,

 

Paste the following code into a new blank query. I've left the steps separate so you can follow through what each stage is doing, but you could easily condense it into one or two steps at a later date if required:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY3JDYAwDARbQX5HYWPndCsRTyqA/oURh18rrWfWcxLKiroyWBaw5qEABUr5b1NT5qc99+NMliyIpdAWTO8OiuLTxUe7cnOd72sqUR67Omfqy2H4ZlXJbotlGzHb7+0C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [start = _t, finish = _t, comment = _t, durationHour = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"start", type datetime}, {"finish", type datetime}, {"comment", type text}, {"durationHour", type number}}),

    // Relevant steps from here ---->
    addHoursList =
        Table.AddColumn(
            chgTypes,
            "hoursList",
            each let
                __startHour = DateTime.From(Number.RoundDown(24 * Number.From([start]) / 1 ) / 24),
                __stopHour = DateTime.From(Number.RoundUp(24 * Number.From([finish]) / 1 ) / 24)
            in
                List.DateTimes(
                    __startHour,
                    Duration.TotalMinutes(__stopHour - __startHour) / 60,
                    #duration(0,1,0,0)
                )
        ),
    addSegmentEnd =
        Table.AddColumn(
            addHoursList,
            "segmentEnd",
            each List.Combine(
                {
                    List.Select(
                        [hoursList],
                        each Date.DayOfWeek(_, Day.Monday) = 0 and Time.Hour(_) = 6
                    ),
                    {[finish]}
                }
            )
        ),
    expandSegmentEnd = Table.ExpandListColumn(addSegmentEnd, "segmentEnd"),
    addSegmentStart =
        Table.AddColumn(
            expandSegmentEnd,
            "segmentStart",
            each if [start] > Date.AddDays([segmentEnd], -7) and [segmentEnd] <> [finish] then [start]
                else if [finish] = [segmentEnd] then List.Max({Date.StartOfWeek([segmentEnd], Day.Monday) + #duration(0,6,0,0), [start]})
                else Date.AddDays([segmentEnd], -7)
        ),
    addDurationHoursCalc = Table.AddColumn(addSegmentStart, "durationHoursCalc", each Duration.TotalMinutes([segmentEnd] - [segmentStart]) / 60),

    remOthCols = Table.SelectColumns(addDurationHoursCalc,{"start", "finish", "comment", "durationHoursCalc"})
in
    remOthCols

 

 

Example query output:

 

 

Pete