Duration converted to a Date Time Format

[Anonymous]

I have downloaded data from Garmin. The Elapsed time field is a General datatype and looks like this:

It lands in Power BI and ends up like this:

I've searched through these forums and my Excel data is already in General format. The thing is that I don't need the milliseconds and happy to remove it.

I've tried changing the Elapsed time to duration in PBI and that doesn't work. I tried also to change the format to hhmmss and it doesn't work.

There seems to be conflicting ways to get this to work. I'm stuck. Can someone provide assistance? 

Thank you

[Anonymous · Accepted solution]

I think I figured it out. I have the field in Excel set to General

I import the data

leave it as a Date/Time

I don't transform anything but go to the Data tab in the builder and change the format to be hh:nn:ss

Ignore the 2nd 2 columns I was testing.

It would be nice though if PBI did not convert these types of data. Leave it as is. I can't figure out if that's an option or not...

[James-Harpin]

Hi @Anonymous 

I did a test of this and I got it to work by when importing the excel file selecting transform, then changing the data type to 'time'

[Anonymous]

Thank you for the response. I just made a copy of the column and imported the file back into my model.

I loaded it and clicked transform.

I then changed it to Time.

This is what I see

[James-Harpin]

@Anonymous is that the desired outcome you want?

[Anonymous]

In my output? No. All it's doing is extracting the Time out of the date.

The purpose of pulling in this data is to answer the question: How many hours\minutes have I spent on each activity.

Therefore:

XC sii   | 32hrs 3minutes

Run     | 14hrs 5 mintutes

How can you tell PBI desktop to not change the format from the Excel file? If it stayed the way it is, I could summarize the hours