Forum Discussion

acrscotland's avatar
acrscotland
New Member
9 years ago
Solved

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...
Need Help
  • DoubleJ's avatar
    DoubleJ
    9 years ago

    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

acrscotland's avatar
acrscotland
New Member
9 years ago

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.

 

 

MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

When I have a csv fle containing:
Input
02:00:00
03:30:20
40:00:00

 

Then the following code works with me:

 

let
    Source = Csv.Document(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Durations from csv.csv"),[Delimiter=",", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
// Split text at ":" (list of 3 items) and convert to number:
    Split = Table.AddColumn(#"Promoted Headers", "d", each List.Transform(Text.Split([Input],":"), Number.From)),

// Get duration from the set 3 numbers
    Duration = Table.AddColumn(Split, "Duration", each #duration(0,[d]{0},[d]{1},[d]{2}), type duration)
in
    Duration