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
Spoiler
let
Source = Excel.Workbook(File.Contents("C:\Users\kanishka.taneja\OneDrive - Securitas\Data Analytics Hub\Projects\Richemont KPI Dashboard\Files\Extra\Test\Analyse Alarmes.xlsx"), null, true),
#"Security Alarms_Sheet" = Source{[Item="Security Alarms",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Security Alarms_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date / heure réception information", type text}, {"Date / Heure Premier Accès Acquittement", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Date / heure réception information", "Information"}, {"Date / Heure Premier Accès Acquittement", "Action"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Information 2", each [ a = Text.BetweenDelimiters([Information], ":", " ", {0, RelativePosition.FromEnd}),
b = Text.PadStart(Text.From(Number.Round(Number.From(a, "en-US"))), 2, "0"),
c = Text.BeforeDelimiter([Information], ":", {0, RelativePosition.FromEnd}),
d = Text.AfterDelimiter([Information], " ", {0, RelativePosition.FromEnd}),
e = c & ":" & b & " " & d
][e]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Information 2", type datetime}})
in
#"Changed Type1"
Source = Excel.Workbook(File.Contents("C:\Users\kanishka.taneja\OneDrive - Securitas\Data Analytics Hub\Projects\Richemont KPI Dashboard\Files\Extra\Test\Analyse Alarmes.xlsx"), null, true),
#"Security Alarms_Sheet" = Source{[Item="Security Alarms",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Security Alarms_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date / heure réception information", type text}, {"Date / Heure Premier Accès Acquittement", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Date / heure réception information", "Information"}, {"Date / Heure Premier Accès Acquittement", "Action"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Information 2", each [ a = Text.BetweenDelimiters([Information], ":", " ", {0, RelativePosition.FromEnd}),
b = Text.PadStart(Text.From(Number.Round(Number.From(a, "en-US"))), 2, "0"),
c = Text.BeforeDelimiter([Information], ":", {0, RelativePosition.FromEnd}),
d = Text.AfterDelimiter([Information], " ", {0, RelativePosition.FromEnd}),
e = c & ":" & b & " " & d
][e]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Information 2", type datetime}})
in
#"Changed Type1"
Hi dufoq3 , Thanks for sharing your approach.
I tried it but when I convert to text it loses all the decimal values and also when rounding off the number, will it not cause exception to handle lets say 01:01:59.760 AM?
I have shared my advance editor query with table "security alarms" where this approach does not seem to be working. Is there any way I can share some sample data with you?
Sample data
| Date / heure réception information | Date / Heure Premier Accès Acquittement | Site.Code |
| 01-Jan-2024 00:00:15.707 | 01-Jan-2024 00:00:33.770 | 0CH0000354 |
| 01-Jan-2024 00:01:24.310 | 01-Jan-2024 00:00:33.770 | 0CH0000354 |
| 01-Jan-2024 00:03:16.583 | 01-Jan-2024 00:00:33.770 | 0CH0000354 |
| 01-Jan-2024 00:03:36.920 | 01-Jan-2024 00:00:33.770 | 0CH0000354 |
| 01-Jan-2024 00:04:18.103 | 01-Jan-2024 00:04:28.740 | 0CH0000354 |
| 01-Jan-2024 00:04:41.237 | 01-Jan-2024 00:04:28.740 | 0CH0000354 |
| 01-Jan-2024 00:05:17.867 | 01-Jan-2024 00:04:28.740 | 0CH0000354 |
| 01-Jan-2024 00:06:01.007 | 01-Jan-2024 00:04:28.740 | 0CH0000354 |
| 01-Jan-2024 00:06:59.770 | 01-Jan-2024 00:04:28.740 | 0CH0000354 |
Also please that the date time format in excel is dd-mmm-yyyy hh:mm:ss I have updated it to dd-mmm-yyyy hh:mm:ss.000 just to share the data with you.
dufoq3
Community Champion
Community ChampionThis 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
This works!! Thank you very much 🙂
- dufoq3
You're welcome 😉