Duration wrongly converted to Date-Time type

[Anonymous]

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?

[LivioLanzo · Accepted solution]

you can convert them within excel to general format and then copy paste them into Power BI

[LivioLanzo]

@Anonymous

how are you getting to these duration values? are you subtracting 2 columns_? what do they look like?

[Anonymous]

@LivioLanzo they are inserted by hand. No subtraction. They look like the picture below, which is a part of the excel sheet:

[Stachu]

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?

[Anonymous · Accepted solution]

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. 

[Anonymous]

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

[Stachu]

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)

[LivioLanzo · Accepted solution]

you can convert them within excel to general format and then copy paste them into Power BI

[Anonymous]

@LivioLanzo

thank you for your help. It worked 

I converted the source column to general, imported it in the PBI model and then set the data type as Duration. 

The only problem remained, is that it is corrected the PowerQuery mode yet in the PBI desktop data section, it is not showed correctly 

[LivioLanzo]

HI @Anonymous

try to convert it to decimal type

[Anonymous]

@LivioLanzo

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

[BjoernSchaefer]

Hi @Anonymous,

you can add a calculated column using the following DAX:

Duration = FORMAT([Column];"hh:mm:ss")

Hope it helps

Regards

BjoernSchaefer

[Anonymous]

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

[BjoernSchaefer]

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