Forum Discussion
Anonymous
2 years agoNot applicable
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...
- 2 years ago
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
dufoq3
Community Champion
2 years agoThis 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
Anonymous
2 years agoNot applicable
This works!! Thank you very much 🙂
- dufoq32 years ago
Community Champion
You're welcome 😉