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
Anonymous
2 years agoNot applicable
Hi,
Thanks for the solution Ahmedx and dufoq3 offered, and i want to offer some more information for user to refer to.
hello Anonymous , you can also refer to the following code in advanced editor in power query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDU9UrM0zUyUTC0Mja2MjDTMzcyAAEFR1+l2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each let a=Text.Replace(Text.AfterDelimiter([Column1],":",1),"AM",""),
b=Text.BeforeDelimiter([Column1],":",1),
c=Number.Round(Decimal.From(a))
in DateTime.FromText(b&":"&Text.From(c)))
in
#"Added Custom"
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.