Expand hours between a start and end time

[joshcomputer1]

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

[OwenAuger · Accepted solution]

@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"

[Anonymous]

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.