Forum Discussion
Anonymous
5 years agoNot applicable
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 ...
- 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"
CNENFRNL
5 years agoCommunity Champion
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
5 years agoNot applicable
Thanks alot..