Aggregate 5 minute time stamps to half hour values in datetime format

[Structuralguy]

I currently have data separated into 5 minute time stamps across two years worth of recordings (see below).

I need to adjust this data to display half hour time stamps (every 30 minutes and on the hour) and replace values from 5 minutes to 55 minutes past the hour. The first 6 recording intervals (:05-:30) need to be rounded up to the half hour value, whilst the second 6 recording intervals (:035-:00) need to be rounded up to the hour value. An example of what the data should look like is given below. 

Is it possible to do this using power query whilst keeping the DateTime format?

[Vera_33 · Accepted solution]

Hi @Structuralguy 

Yes, there should be different ways, here is one way to extract date, hour, minute, paste all in the Advanced Editor via blank query, you will see the result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc+xCcAwDETRVYLrQHSSBcGrGO+/Rux0iT+oOl6h33sxXfPcdB9mzayM8zN6tJr/cUptco0knaSTDJJBspLEP5Nk7lLQvkaS0C5qF7WL2vW2jwc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SETTLEMENTDATA = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SETTLEMENTDATA", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [min=Time.Minute([SETTLEMENTDATA]),
hr = Time.Hour([SETTLEMENTDATA]),
a= if min > 0 and min <= 30 then 30 else 0,
b=if min > 0 and min <= 30 then Date.From([SETTLEMENTDATA]) & #time(hr,a,0) else Date.From([SETTLEMENTDATA]) & #time(hr+1,a,0) ][b])
in
    #"Added Custom"

[Structuralguy]

Hi @Vera_33 

Thanks very much for your help! I've tried the code and it works almost perfectly, only issue is with the timestamps that already correlate to the exact hour as they are currently being adjusted an extra hour forward (see screenshot below). I've managed to fix this however by just adding an extra 'else if' statement as follows:

else if min = 0 then Date.From([SETTLEMENTDATA]) & #time(hr,a,0)

Thanks once again for your help!

[Greg_Deckler]

@Structuralguy Probably better ways to do this but I did it like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMLRQMLAyMFCK1UEVMUUXMcRQY4QhYowhYoJhjimaGkMM2w0xbDcEmxMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each DateTime.Date([Column1])),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Date"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Hours", each Time.Hour([Column1])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Minutes", each if Time.Minute([Column1]) <= 30 then 30 else 0),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Final", each DateTime.From(Date.ToText([Date]) & " " & Text.From([Hours]) & ":" & Text.From([Minutes])))
in
    #"Added Custom3"

[Structuralguy]

Hi @Greg_Deckler 

Thanks very much for your reply. 

I gave your code a shot but still a couple of bugs regarding rounding up to the next hour value once the timestamp was over 35minutes past the hour and rounding up to half past the hour for exact hour timestamps (see screenshot below).

Thanks for your time and help with this though!