Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Distribute rows hour wise

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 ...
  • CNENFRNL's avatar
    5 years ago
    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"