Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to 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
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