Forum Discussion

Vatz8's avatar
Vatz8
Helper I
3 years ago

Split datetime columns into multiple rows for each day.

Hi,

I want to split the datetime columns range into multiple rows as shown below.

 

ID|     punch_Start                     | punch_End
--------------------------------------------
A | 2019-03-04 23:18:00| 2019-03-04 23:21:00
--------------------------------------------
A | 2019-03-04 23:45:00| 2019-03-05 00:15:00
--------------------------------------------

 

Required Output-

ID|       punch_Start                   | punch_End
--------------------------------------------
A | 2019-03-04 23:18:00| 2019-03-04 23:21:00
--------------------------------------------
A | 2019-03-04 23:45:00| 2019-03-04 23:59:00
--------------------------------------------
A | 2019-03-04 23:59:00| 2019-03-05 00:00:00
--------------------------------------------
A | 2019-03-05 00:00:00| 2019-03-05 00:15:14

 

 

Please let me know how can I achieve this.

Need Help
Show and Tell

2 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    Hi, Vatz8 

    let
    Source = your_last_step,
    types = Table.TransformColumnTypes(Source,{{"punch_Start", type datetime}, {"punch_End", type datetime}}),
    f = (lst as list) =>
        [id = lst{0},
        end = lst{2},
        gen = 
            List.Buffer(
                List.Generate(
                    () => lst{1},
                    (x) => x < end,
                    (x) => 
                        if DateTime.Time(x) = #time(23, 59, 00) 
                        then x + #duration(0, 0, 1, 0)
                        else DateTime.From(DateTime.Date(x)) + #duration(0, 23, 59, 0)
                )
            ),
        dts = List.Zip({List.Repeat({id}, List.Count(gen)), gen, List.RemoveFirstN(gen, 1) & {end}})][dts],
    rows = List.Buffer(Table.ToRows(types)),
    txform_rows = List.Combine(List.Transform(rows, f)),
    z = Table.FromRows(txform_rows, Table.ColumnNames(Source))
in
    z