Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
Ryukatan10
Regular Visitor

Expand Date and Time with diferent dates

Hello Everyone,

 

I have this data here:

 

DayInbound Dept TimeGround Time

 

01Oct202201:554:20:00
01Oct202201:351:45:00
01Oct202201:450:55:00
01Oct202202:001:00:00
01Oct202202:100:50:00
01Oct202202:251:05:00
01Oct202202:554:05:00
01Oct202202:402:55:00
01Oct202202:100:50:00
01Oct202202:352:40:00
01Oct202202:553:40:00
01Oct202202:500:55:00
01Oct202200:5010:05:00
01Oct202220:4012: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

 

01Oct202220
01Oct202221
01Oct202222
01Oct202223
02Oct202200
02Oct202201
02Oct202202
02Oct202203
02Oct202204
02Oct202205
02Oct202206
02Oct202207
02Oct202208
02Oct202209

 

Thanks

1 ACCEPTED SOLUTION
jbwtp
Memorable Member
Memorable Member

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

View solution in original post

2 REPLIES 2
jbwtp
Memorable Member
Memorable Member

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors