Create table with 1/2 hour time slots

[chrisvdwal]

Hi,

I have a requirement to create a table with a new records for each ½ time slot from a record in another table which has a date/time range

Source table

StartTime                            FinishTime

1/1/2019 11:00 am         1/1/2019 13:00 pm

Should return 4 records in temp table

StartTime

1/1/2019 11:00am

1/1/2019 11:30am

1/1/2019 12:00pm

1/1/2019 12:30pm

Ranges can go over days    1/1/2019 23:00pm -> 4/1/2019 02:am  would create lots of records (48 per day)

Is this possiable?

[Anonymous]

Hi @chrisvdwal 

Look at GENERATESERIES function.

Thanks!

[parry2k · Accepted solution]

@chrisvdwal here are query steps on sample data which you can use as per your need

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMLRUMDS0MjBQSMw9tAAElXSUIAwQVEAosjI2UCjIVYrVIVknWGsiUGssAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [StartTime = _t, EndTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartTime", type datetime}, {"EndTime", type datetime}}),
    #"Inserted Age" = Table.AddColumn(#"Changed Type", "Time", each List.DateTimes([StartTime],(Duration.TotalMinutes([EndTime]-[StartTime])/30)+1,#duration(0,0,30,0))),
    #"Expanded Time" = Table.ExpandListColumn(#"Inserted Age", "Time"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Time",{{"Time", type datetime}})
in
    #"Changed Type1"