Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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   But when I import the data to Power BI Desktop with Power Quey, it converts this column to date/...
Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

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

  • Anonymous's avatar
    Anonymous
    7 years ago

    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. 

BjoernSchaefer's avatar
BjoernSchaefer
Helper II
7 years ago

Hi Anonymous,

 

you can add a calculated column using the following DAX:

 

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

 

Hope it helps

 

Regards

 

BjoernSchaefer

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.