Representing time as a duration in Microsoft Power BI

0

I have an Excel spreadsheet containing video statistics. One of those statistics is the length of the video as a duration. When I import this data into Microsoft Power BI, it can be represented as either a time of day (i.e. in 24-hr format or 12-hr with AM or PM) or as a floating point number.

Is there a way to represent this information, either in the Excel file or using some feature of Microsoft Power BI that will show me this value as a duration rather than as a time of day or floating point number?

Adam J Limbert

Posted 2018-10-15T12:32:31.593

Reputation: 228

Answers

1

I would import it as a time of day and then add a calculated column using datediff eg 

Video Duration (seconds) = DATEDIFF( DATE( 1899 , 12, 30 ) , [duration] , SECOND )

Power BI uses that wacky date when you load Excel duration data.

Mike Honey

Posted 2018-10-15T12:32:31.593

Reputation: 2 119

1Thanks! I had to slightly alter my Excel data to represent the duration in hh:mm:ss, eg 00:03:10 to represent 3 minutes and 10 seconds (Power BI didn't like the omission of the "hh:"), use "Time" as the field type in Power BI, then add the column as you described. I also added another column where I divided that result by 60, which made it a little more palatable for my needs. – Adam J Limbert – 2018-10-25T07:19:11.130

Asked: 2018-10-15T12:32:31.593

Viewed: 4 281 times

Active: 2018-10-25T06:56:59.350