cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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?

2 ACCEPTED SOLUTIONS

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

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

13 REPLIES 13
Solution Sage
Solution Sage

@Anonymous

 

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Anonymous
Not applicable

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

 

duration.PNG

Community Champion
Community Champion

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!

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

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. 

Community Champion
Community Champion

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!

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

Anonymous
Not applicable

@LivioLanzo

 

thank you for your help. It worked Smiley Very Happy

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 Smiley Frustrated

Solution Sage
Solution Sage

HI @Anonymous

 

try to convert it to decimal type

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Anonymous
Not applicable

@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

Helper II
Helper II

Hi @Anonymous,

 

you can add a calculated column using the following DAX:

 

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

 

Hope it helps

 

Regards

 

BjoernSchaefer

Anonymous
Not applicable

@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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors