Forum Discussion

chrisvdwal's avatar
chrisvdwal
New Member
7 years ago
Solved

Create table with 1/2 hour time slots

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                          ...
Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    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"