cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Duration wrongly converted to Date-Time type

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?

Re: Duration wrongly converted to Date-Time type

@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. 

Super User
Super User

Re: Duration wrongly converted to Date-Time type

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)

Re: Duration wrongly converted to Date-Time type

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.