cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

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

 

duration.PNG

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:

 

durationBPI.PNG

 

I don't know how can I solve this issue.

 

Any idea?

13 REPLIES 13
Highlighted

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?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Highlighted
Anonymous
Not applicable

@Stachu only in the power query you can change the data type to the duration, but in the desktop view, you can't. 

According to this link:

"The Data Type drop down in Query Editor has two data types not currently present in Data or Report View: Date/Time/Timezone and Duration. When a column with these data types is loaded into the model and viewed in Data or Report view, a column with a Date/Time/Timezone data type will be converted into a Date/Time, and a column with a Duration data type is converted into a Decimal Number."

 

 

and I don't know how we can convert the decimal number back to the duration, outside the power Query. 

Highlighted

for the purpose of the calculation you can keep it as a decimal number, and use e.g. FORMAT in DAX to present it in more user friendly manner

the disadvantage is you will need 2 sets of measures (FORMAT turns values to text)



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Highlighted
Anonymous
Not applicable

PowerBI does not currently support milliseconds in date time datatype. I solved the problems like follow:

 

convert the excel doc to general, load it into PowerBI, and multiply the value by 86400 to get the total number of seconds. 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors