Forum Discussion
Duration conversion issue (CVS)
Hi all,
I'm struggling with what I feel should be a simple issue! I have a CSV file coming from some time tracking software I use. The format is HH: MM: SS and represents the duration of a task. When imported into Power BI, it looks like this (format of the column is 'Time'). I then parse this and convert myself into a decimal for the purposes of charting:
As an aside, converting to Duration doesn't work and triggers the following error:
Anyway, all is well until I have a duration >24 hours. Then it get's upset:
I've tried converting to Text but then it tries to convert it to an actual Time of Day and that's no good. Any idea how I can handle this situation?
Thanks in advance!!
I just made a small working sample.
First I duplicated the Duration column. Then I used the "Split Column" function. That created 3 columns containg hours, minutes and seconds.
Hope this works with your data, too!
JJ
9 Replies
How about you
- convert the values to a string
- split the string value in 3 columns (hours, minutes, seconds) (split by the ":" character)
- add a calculated column that computes the duration with these 3 values?
JJ
Thanks for such a quick suggestion. This is actually what I tried first but unfortunately, when converting the column to 'Text' (I'm doing this within the Query Editor, it converts to the following, which I can't parse since it's no longer a duration (these are the same values as I pasted above so as an example, 00:30:00 (30 mins) is converted to 12:30 AM:
Is there a different way to convert that you had in mind? It doesn't work once the import has completed since any hours >24 error out during import.
I just made a small working sample.
First I duplicated the Duration column. Then I used the "Split Column" function. That created 3 columns containg hours, minutes and seconds.
Hope this works with your data, too!
JJ
