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

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

Accepted Solutions
Super User
Super User

Re: Duration wrongly converted to Date-Time type

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!  

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. 

13 REPLIES 13
BjoernSchaefer Regular Visitor
Regular Visitor

Re: Duration wrongly converted to Date-Time type

Hi @elahemeydani,

 

you can add a calculated column using the following DAX:

 

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

 

Hope it helps

 

Regards

 

BjoernSchaefer

Super User
Super User

Re: Duration wrongly converted to Date-Time type

HI @elahemeydani

 

try to convert it to decimal type

 


 


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


Proud to be a Datanaut!  

Re: Duration wrongly converted to Date-Time type

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

Re: Duration wrongly converted to Date-Time type

@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 Regular Visitor
Regular Visitor

Re: Duration wrongly converted to Date-Time type

Hi @elahemeydani,

 

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

Super User
Super User

Re: Duration wrongly converted to Date-Time type

@elahemeydani

 

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!  

Re: Duration wrongly converted to Date-Time type

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

 

duration.PNG

Super User
Super User

Re: Duration wrongly converted to Date-Time type

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!  

Re: Duration wrongly converted to Date-Time type

@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