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

DoubleJ's avatar
DoubleJ
Solution Supplier
9 years ago

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

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.

 

 

  • DoubleJ's avatar
    DoubleJ
    Solution Supplier
    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

      This is perfect, thanks!!  I realized that somewhere along the line I can converted the column to something else along the way and that screwed me up.  This solution has me up and running - thanks!

      • Sean's avatar
        Sean
        Community Champion
        9 years ago

        Alternatively :smileyhappy:

        Add Column tab => select column => Time button => Hour

        repeat 2 more times  => select column => Time button => Minute and then again for a Second column

         

         

  • 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