Expand Date and Time with diferent dates

[Ryukatan10]

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

[jbwtp · Accepted solution]

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

[Ryukatan10]

Thx Man, excellent