Forum Discussion

Frequent Visitor

2 years ago

Solved

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

I have data that needs to be split on a daily basis. Item Name Start Date Time End Date Time A 11/19/2023 11:15:00 PM 11/20/2023 1:15:00 AM B 11/20/2023 02:15:00 PM ...

Need Help

AlienSx

2 years ago

gabereal

    f = (row as record) as list =>
        if Date.From(row[End Date Time]) = Date.From(row[Start Date Time]) then row else
        [max_dt = row[End Date Time],
        gen = List.Generate(
            () => Record.TransformFields(row, {"End Date Time", (w) => Date.StartOfDay(Date.AddDays(row[Start Date Time], 1))}),
            (x) => x[Start Date Time] < max_dt,
            (x) => 
                [Item Name = x[Item Name], 
                Start Date Time = Date.StartOfDay(Date.AddDays(x[Start Date Time], 1)), 
                End Date Time = List.Min({max_dt, Date.AddDays(x[End Date Time], 1)})]
        )][gen],
    tbl = Table.TransformRows(your_table, f),
    z = Table.FromRecords(List.Combine(tbl))

dufoq3

Community Champion

2 years ago

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

geiratatea

Frequent Visitor

2 years ago

Hi again

And thanks for your genious solution on my problem. Here is a list of norwegian 2024 holiday days. I guess this had to be related to an ordinary date table, but here it is. If you could reuse this in the solution it would be great. I will study your code to learn all the process. Thank you again

DateDay NameHoliday NameType

1/1/2024	Monday	Første nyttårsdag	Helligdag
3/28/2024	Thursday	Skjærtorsdag	Helligdag
3/29/2024	Friday	Langfredag	Helligdag
3/31/2024	Sunday	Første påskedag	Helligdag
4/1/2024	Monday	Andre påskedag	Helligdag
5/9/2024	Thursday	Kristi himmelfartsdag	Helligdag
5/19/2024	Sunday	Første pinsedag	Helligdag
5/20/2024	Monday	Andre pinsedag	Helligdag
12/25/2024	Wednesday	Første juledag	Helligdag
12/26/2024	Thursday	Andre juledag	Helligdag

dufoq3

Community Champion

2 years ago

Hi geiratatea, done.

You can edit holidays via gear icon (see red rectangle) or replace whole selected code with your holiday table reference (but don't forget that you holiday table must contains column called [Date])

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZHdagIxEIVfZdirlkrVVdvqXbGCFGyl9U68GJNxd3A3WfJT8e07uyu1lgqBSZjkfGdO1utkRT7AzdwWrPF4m3SSJQVyUvvdfjftpUMYT3o9WfC8gLtmJ83Bg5RNZ51MczQZgWZfFXgEu4MMi4LcEdiANZllk4HCskLOjJeXC3SMrXx6XX80POl/2BjYEOysA5WT2td6s+ULaAzkAY2GafTBluTAxHIrBb23iqWt4cAh/6FfwkctfDQZNPDlL/hjejlcyAleP9/fwIuDEuvJdoU9AFbV/ZwF7lhNTxAIFpQ1AeWSj1vPmqkee+YKMrpFP7XowT/ov7lyoNLXwDZV0aoPKjpHJjTTV46+2EZ/Drm2EHAvmXEhX3m2IU+lhUrZaMJlGuPrltJ+42nzDQ==", 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"),
    Holidays = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc8xCoMwFAbgq5TMQkzUUscuUmg7WeggDoFEjcZYkjh4IHuG7l6silhoSbo9Hu/j/X+WAQQRxD4OgQeunaRkmIdkeilt2E4Oxkyj0pSU8/bEhODlMudeBgKIDxu8Vf1ytNC0qaenMp0TxRtKFF/JhciyUMx+H3zSpf1Pusc06sbKQkuno6Tqn4lgbKlzVlwbvqt42zJREGWstSKIYndMLrXjI/ZdMV0GYYijDd0ZlUx/f6t74YR7S8H1n1Xlbw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Day Name" = _t, #"Holiday Name" = _t, Type = _t]),
    HolidaysFormatted = Table.TransformColumnTypes(Holidays,{{"Date", type date}}, "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_DateHelper = Table.DuplicateColumn(StepBack, "Timestamp", "Date Helper"),
    ChangedType2 = Table.TransformColumnTypes(Ad_DateHelper,{{"Date Helper", type date}}),
    FilteredOutHolidays = Table.SelectRows(ChangedType2, each not List.Contains(HolidaysFormatted[Date], [Date Helper])),
    RemovedDateHelper = Table.RemoveColumns(FilteredOutHolidays,{"Date Helper"}),
    Ad_StartTime = Table.DuplicateColumn(RemovedDateHelper, "Timestamp", "Start Time"),
    Ad_EndTime = Table.AddColumn(Ad_StartTime, "End Time", each [Start Time] + #duration(0,0,0,[PeriodLength]), DateTimeZone.Type),
    Ad_WorkingMinutes = 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),
    TransformWorkingMinutes = Table.TransformColumns(Ad_WorkingMinutes, {{"Working Minutes", each Table.SelectRows(Record.ToTable(_), (x)=> x[Value] > 0), type table}}),
    ExpandedWorkingMinutesTable = Table.ExpandTableColumn(TransformWorkingMinutes, "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

Forum Discussion

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

Related content

Splitting values equally into Months based on the Start and End Date

EBIT Financial Bridge - Dynamic Start and End

Building Deadlines based off Start, End Dates and Frequency

Need to Show Active count based on Start & End Date

I need a month column based on a start and end date

Forum Discussion

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

Splitting values equally into Months based on the Start and End Date

EBIT Financial Bridge - Dynamic Start and End

Building Deadlines based off Start, End Dates and Frequency

Need to Show Active count based on Start &amp; End Date

I need a month column based on a start and end date

Need to Show Active count based on Start & End Date