Forum Discussion
Duration wrongly converted to Date-Time type
I am getting data from an excel file which include a column which shows duration as mm:ss:ms
But when I import the data to Power BI Desktop with Power Quey, it converts this column to date/time format, like the picture below:
I don't know how can I solve this issue.
Any idea?
you can convert them within excel to general format and then copy paste them into Power BI
13 Replies
Hi Anonymous,
you can add a calculated column using the following DAX:
Duration = FORMAT([Column];"hh:mm:ss")
Hope it helps
Regards
BjoernSchaefer
I believe it is not going to work because if you look at the original column form the excel, there are milliseconds in the end, but after importing to power BI, it rounds up the milliseconds, and that doesn't make sense because it a competition these milliseconds actually matter.
Hi Anonymous,
sorry, i missunterstood you. DAX cannot handle milliseconds. But i found something that'll may help you with that.
https://stackoverflow.com/questions/46833631/how-to-deal-with-milliseconds-in-powerbi
Regards
BjoernSchaefer
HI Anonymous
try to convert it to decimal type
As I explained to BjoernSchaefer, before doing anything power query convert this column to the date/time format, and this rounds up the numbers, therefore turning it to a decimal type will not solve the problem
Anonymous
how are you getting to these duration values? are you subtracting 2 columns_? what do they look like?
LivioLanzo they are inserted by hand. No subtraction. They look like the picture below, which is a part of the excel sheet:
in the Query Editor - if I use the format
hh:mm:ss.000
in Enter Data I can change the data type to duration and only AFTER that to number
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjCwMjC0MjTUMzQxUIrVgQkYmOpZWiALGBrrGZqhqDDRMzRAETDTMwEJxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type duration}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Column1", type number}})
in
#"Changed Type1"but if I import the same from Excel I cannot convert to duration, only to number but it adds the 1 to the value for some reason
does it work the same for you?
