Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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"
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"
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!
@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"
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.