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

Preparing for a certification exam? Ask exam experts all your questions on May 15th. Register now.

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
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
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
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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