Forum Discussion

hansbogaert's avatar
hansbogaert
New Member
5 years ago
Solved

Text to time

Looking for the best way to convert a text column to a time column, allowing me to calculate the sum, average, ...   Data comes in looking like time, but is formatted as text. For most only the tim...
Need Help
  • aj1973's avatar
    aj1973
    5 years ago

    Hey hansbogaert 

    this was something new for me and I thank you for making me discover it.

    Here are the steps:

    let's say your column looks like this in your DB

    You need to add a custom column and convert that column to Sum of seconds

    here is the formula Duration.TotalSeconds([Duration] - #datetime(1899,12,31,0,0,0))

    Change the Column Type to number

    Then add a Dax formula like this:

     

    And here is the DAX

    Total Duration =
    var vSeconds=SUM(Sheet1[Dur])
    var vMinutes=int( vSeconds/60)
    var vRemainingSeconds=MOD(vSeconds, 60)
    var vHours=INT(vMinutes/60)
    var vRemainingMinutes=MOD(vMinutes,60)
    var vDays=INT(vHours/24)
    var vRemainingHours=MOD(vHours,24)
    return
    vDays&" D : "&
    vRemainingHours&" H : "&
    vRemainingMinutes&" M : "&
    ROUND(vRemainingSeconds,0)& " S "

     

    Hope it works for you.

    Attached the Sample Pbix

    https://drive.google.com/file/d/1wU_VFfS69XSIPeYkT2v12-r1s84QQb3G/view?usp=sharing

     

     

     

aj1973's avatar
aj1973
Community Champion
5 years ago

Hi hansbogaert 

change the type of the column to Date/Time, then change it into Time...In Power query

 

  • hansbogaert's avatar
    hansbogaert
    New Member
    5 years ago

    Hi aj1973 ,

    Thanks for your fast reaction.

    When I try to do this, I get the message that I need to convert my data to Import Mode instead of DirectQuery. 

     

    • aj1973's avatar
      aj1973
      Community Champion
      5 years ago

      Oh yes, you can't do it in DQ mode connection. you need to do the cleaning directly to the source. Is the source an excel file?

       

      • hansbogaert's avatar
        hansbogaert
        New Member
        5 years ago

        It's linked to a huge SQL database.

        I imported it, and indeed the steps work, but with an error. For those who go over 24 hours. They are indicated with the date:

        One row looks like this: 2/01/1900 8:15:00

        After converting it to Date/Time it stays the same

        And when I move to Time again, it is 8:15:00, but it should be 56:15:00