Forum Discussion
Round up date time column to remove miliseconds
I have date time column in Power BI that stores values as 01-Jan-24 1:33:06.7200000 AM I want to store value as 01-Jan-24 1:33:07 AM Delimiting the column on "." wont work as it wi...
This query will round secodrs for each column with type datetime. Is this what you want?
(You can remove my ChangedType step if you have already correct types defined)Before
After
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdE9CsMwDAXgqxTPyUN/thytWUqvEDLkArn/WHfoVA8uFtoefIin40jE6+u6VyGxB1G05QwnT0snUoU7faL9SW00WzqXX4RDDMo0hWhwQa46iWjBJnOXWHAFU+8SC6lwG0KMIdordhzJwY5a5pDSHgTqvvgfJG/f9saQ8w0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date / heure réception information" = _t, #"Date / Heure Premier Accès Acquittement" = _t, Site.Code = _t]), ChangedType = Table.TransformColumns(Source, {{"Date / heure réception information", DateTime.From}, {"Date / Heure Premier Accès Acquittement", DateTime.From}}), // Rouded seconds - only for columns with type datetime Transformed = Table.TransformColumns(ChangedType, List.Transform(Table.SelectRows(Table.Schema(ChangedType), each [Kind] = "datetime")[Name], (x)=> {x, each [ time = Time.From(_), hours = Time.Hour(time), minutes = Time.Minute(time), seconds = Number.Round(Time.Second(time)), result = Date.From(_) & Time.From((hours * 3600 + minutes * 60 + seconds) / 86400) ][result], type datetime})) in Transformed
Ahmedx
Super User
Super Userpls try this code
= Time.From(Time.ToText(DateTime.Time([Date]), "hh:mm:ss"))This will display time as 1:33:06 AM and I want rounded up as 1:33:07
- Ahmedx
pls try this
let f = (x)=>[ H = Time.Hour( x), M = Time.Minute( x), S = Int64.From( Time.Second( x ) ), TV = #time( H, M, S), D = Date.From(x)& TV ][D], Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1MASiEANDK2NjKwMzPXMjAxBQUIqNBQA=", 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}}), Custom1 = Table.TransformColumns( #"Changed Type",{"Column1",f}) in Custom1