Forum Discussion
Solved
Expand Date and Time with diferent dates
Hello Everyone,
I have this data here:
DayInbound Dept TimeGround Time
| 01Oct2022 | 01:55 | 4:20:00 |
| 01Oct2022 | 01:35 | 1:45:00 |
| 01Oct2022 | 01:45 | 0:55:00 |
| 01Oct2022 | 02:00 | 1:00:00 |
| 01Oct2022 | 02:10 | 0:50:00 |
| 01Oct2022 | 02:25 | 1:05:00 |
| 01Oct2022 | 02:55 | 4:05:00 |
| 01Oct2022 | 02:40 | 2:55:00 |
| 01Oct2022 | 02:10 | 0:50:00 |
| 01Oct2022 | 02:35 | 2:40:00 |
| 01Oct2022 | 02:55 | 3:40:00 |
| 01Oct2022 | 02:50 | 0:55:00 |
| 01Oct2022 | 00:50 | 10:05:00 |
| 01Oct2022 | 20:40 | 12:30:00 |
What I need is expand this table to one tha have all the hours between dep time summed with ground time,
The last line will be an interval between 01oct2022 20:40 and 02oct2022 09:10
And the last line should return something like this
DayInbound HourGround Time
| 01Oct2022 | 20 |
| 01Oct2022 | 21 |
| 01Oct2022 | 22 |
| 01Oct2022 | 23 |
| 02Oct2022 | 00 |
| 02Oct2022 | 01 |
| 02Oct2022 | 02 |
| 02Oct2022 | 03 |
| 02Oct2022 | 04 |
| 02Oct2022 | 05 |
| 02Oct2022 | 06 |
| 02Oct2022 | 07 |
| 02Oct2022 | 08 |
| 02Oct2022 | 09 |
Thanks
Hi Ryukatan10,
Interesting problem :). Could you please check if this works for you?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZCxDcAgDAR3oQbpbaDxEhkAUWWF7K9gTBXZTSpLnLl/GCNRue6nMKecSHpfowlDgDTzh9a+R+subUqxFA5lPdMleGYWgt31KVsufPPpHNCGs/Qjd79XFXFuDSni34BRQlAa1ppWA5PPFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Day Inbound" = _t, #"Dept Time" = _t, #"Ground Time" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day Inbound", type date}, {"Dept Time", type time}, {"Ground Time", type time}}), AddRange = Table.AddColumn(#"Changed Type", "Range", each List.DateTimes(DateTime.From([Day Inbound]) + #duration(0, Time.Hour([Dept Time]), 0, 0), Time.Hour([Ground Time] + #duration(0, 0, Time.Minute([Dept Time]), 0))+1, #duration(0, 1, 0, 0))), #"Expanded Land" = Table.ExpandListColumn(AddRange, "Range"), #"Added Custom1" = Table.AddColumn(#"Expanded Land", "TimeStamp", each DateTime.ToText([Range], "yyyyMMddhh")) in #"Added Custom1"Kind regards,
John
2 Replies
Hi Ryukatan10,
Interesting problem :). Could you please check if this works for you?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZCxDcAgDAR3oQbpbaDxEhkAUWWF7K9gTBXZTSpLnLl/GCNRue6nMKecSHpfowlDgDTzh9a+R+subUqxFA5lPdMleGYWgt31KVsufPPpHNCGs/Qjd79XFXFuDSni34BRQlAa1ppWA5PPFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Day Inbound" = _t, #"Dept Time" = _t, #"Ground Time" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day Inbound", type date}, {"Dept Time", type time}, {"Ground Time", type time}}), AddRange = Table.AddColumn(#"Changed Type", "Range", each List.DateTimes(DateTime.From([Day Inbound]) + #duration(0, Time.Hour([Dept Time]), 0, 0), Time.Hour([Ground Time] + #duration(0, 0, Time.Minute([Dept Time]), 0))+1, #duration(0, 1, 0, 0))), #"Expanded Land" = Table.ExpandListColumn(AddRange, "Range"), #"Added Custom1" = Table.AddColumn(#"Expanded Land", "TimeStamp", each DateTime.ToText([Range], "yyyyMMddhh")) in #"Added Custom1"Kind regards,
John
Thx Man, excellent