Distribute rows hour wise

[Anonymous]

Hi Everyone, 

I am having a table where I get timestamps for the tasks

Task	fm_dttm		to_dttm
A	12/31/2021 21:50		12/31/2021 22:10
B	10/21/2021 17:55		10/21/2021 18:05
E	6/15/2021 14:48		6/15/2021 15:02

I trying to get the following outputs in power query:

Thanks 

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc2xDYAwDETRVZBrJJ9NDJY7iJgiyv5rEEIa0r5/0pVCJ60kypuwQmVRCcNEGgKqa6HrDWAdQY4wm8gD1rd3CzuLDU+R/C8W0L7M+Qs+AgI+S/up9QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, fm_dttm = _t, to_dttm = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task", type text}, {"fm_dttm", type datetime}, {"to_dttm", type datetime}}),
    #"Added Custom" =
        let 
            EoH = (dttm as datetime) => DateTime.Date(DateTime.Date(dttm)) & #time(Time.Hour(DateTime.Time(dttm)),59,0)
        in
            Table.AddColumn(#"Changed Type", 
                "Custom", 
                each
                let _to = EoH([fm_dttm]) 
                in Table.FromRecords(
                    if _to>=[to_dttm] then {[fm=[fm_dttm], to=[to_dttm]]}
                        else List.Generate(
                            () => [fm=[fm_dttm], to=_to],
                            (x) => x[to]<[to_dttm],
                            (y) => [fm=y[to]+#duration(0,0,1,0), to=EoH(fm)]
                        ) & {[fm=to-#duration(0,0,Time.Minute(DateTime.Time([to_dttm])),0), to=[to_dttm]]}
                )
            ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"fm_dttm", "to_dttm"}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"fm", "to"}, {"fm", "to"})
in
    #"Expanded Table"

[Anonymous]

Thanks alot..