Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

duration format

Hello, I have a duration column in Power query, but it transforms in decimals when I go to the data panel. When I do an average, it is all in decimals. I want to be able to show the average in a time format: 00:00:43 (43 secondes). I searched everywhere on the web and there is no solution. Thank you

 

 

 

Need Help
Show and Tell
  • v-yingjl's avatar
    v-yingjl
    4 years ago

    Hi Anonymous ,

    You can follow these steps to calcualte:

    1. Change the data type of duration column to HH:MM:SS:

    2. Create a calculate column to get the seconds:

    Second = SECOND('Table'[Duration])

    3. Create a measure to calculate the average seconds:

    Average =
VAR _avg =
    AVERAGE ( 'Table'[Second] )
VAR HoursPart =
    MOD ( INT ( _avg / 3600 ), 24 )
VAR MinPart =
    MOD ( INT ( _avg / 60 ), 60 )
VAR SecPart =
    MOD ( _avg, 60 )
RETURN
    FORMAT (
        CONVERT ( HoursPart & ":" & MinPart & ":" & SecPart, DATETIME ),
        "hh:mm:ss"
    )

     

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies