cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joshcomputer1
Helper V
Helper V

Expand hours between a start and end time

I have two columns with times and I would like to expand them so that I can get a row for each hour in between.  The times are rounded to the nearest 30 minutes.

record  start         end

     1     6:30am      930am

      2     2:00pm     4:30pm

 

 

 

result    record

6:30am      1

7:00am      1

8:00am      1

9:00am      1

9:30am      1

2:00pm      2

3:00pm      2

etc...

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

@joshcomputer1 

Here's some sample M code.

 

The code constructs a list of times "on the hour" between start (shifted to next hour) and end (shifted to previous hour), then adds start & end to that list. It then expands that list to rows.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTKzMjZIzAUyLCGMWJ1oJSMg18jKwKAAJG4CFAcyYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [record = _t, start = _t, end = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"record", Int64.Type}, {"start", type time}, {"end", type time}}),
    #"Added start2" = Table.AddColumn(#"Changed Type", "start2", each Time.EndOfHour([start]), type time),
    #"Added end2" = Table.AddColumn(#"Added start2", "end2", each Time.StartOfHour([end]), type time),
    #"Added timelist" =
        Table.AddColumn(
            #"Added end2",
            "result", each let numtimes = Number.Round(([end2]-[start2])/#duration(0,1,0,0))+1,
            timelist_middle = if numtimes > 0 then List.Times([start2], numtimes, #duration(0,1,0,0)) else {},
            timelist_final = {[start]} & timelist_middle & {[end]}
            in timelist_final
        ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added timelist",{"record", "result"}),
    #"Expanded timelist" = Table.ExpandListColumn(#"Removed Other Columns", "result"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded timelist",{{"result", type time}})
in
    #"Changed Type1"

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

@joshcomputer1 

Here's some sample M code.

 

The code constructs a list of times "on the hour" between start (shifted to next hour) and end (shifted to previous hour), then adds start & end to that list. It then expands that list to rows.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTKzMjZIzAUyLCGMWJ1oJSMg18jKwKAAJG4CFAcyYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [record = _t, start = _t, end = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"record", Int64.Type}, {"start", type time}, {"end", type time}}),
    #"Added start2" = Table.AddColumn(#"Changed Type", "start2", each Time.EndOfHour([start]), type time),
    #"Added end2" = Table.AddColumn(#"Added start2", "end2", each Time.StartOfHour([end]), type time),
    #"Added timelist" =
        Table.AddColumn(
            #"Added end2",
            "result", each let numtimes = Number.Round(([end2]-[start2])/#duration(0,1,0,0))+1,
            timelist_middle = if numtimes > 0 then List.Times([start2], numtimes, #duration(0,1,0,0)) else {},
            timelist_final = {[start]} & timelist_middle & {[end]}
            in timelist_final
        ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added timelist",{"record", "result"}),
    #"Expanded timelist" = Table.ExpandListColumn(#"Removed Other Columns", "result"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded timelist",{{"result", type time}})
in
    #"Changed Type1"

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

Hi,

 

Can you please elaborate your question, what do you mean by "expand them so that I can get a row for each hour in between".

 

Any example is much appreciate. 

 

Regards,

Pavan Vanguri.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors