Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
LivioLanzo
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

Stachu
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 🙂

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. 

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. 

Stachu
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 🙂

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!  

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

LivioLanzo
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

BjoernSchaefer
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.