Expand hours between a start and end time

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"