Search code examples
powerbivisualizationpowerquerypowerbi-desktopdata-transform

How to convert decimal date type column to time data type in power query/power BI?

enter image description here

The column consist of minutes that is in the decimal format. This is to be converted to Time format.

Example: The 5th record is 61 minutes and 6 seconds.

This is to be displayed as 1 hour, 1 minute and 6 seconds - (01:01:06).

How to solve this problem in power query editor/ power BI?

Solution

  • Plenty of options. You can do it in Power Query, in DAX as a measure or as a calculated column.

    Here is a simple calculated column:

    Formatted = 
VAR _hrs = QUOTIENT ( [minutes_watched] , 60 )
VAR _mins = INT ( [minutes_watched] - _hrs * 60 )
VAR _sec = MOD ( [minutes_watched] , 1.0 ) * 60
RETURN
FORMAT(_hrs,"00")&":"&FORMAT(_mins,"00")&":"&FORMAT(_sec,"00")

    This also handles weirdos watching for more than 24 hrs, at which a TIME data type would overflow:

    enter image description here

    It is also easily written as a measure where the minutes watched can be an aggregation instead:

    Formatted Minutes := 
VAR _agg_time = [Sum minutes watched] // Aggregation measure of choice goes here
VAR _hrs = QUOTIENT ( _agg_time , 60 )
VAR _mins = INT ( _agg_time - _hrs * 60 )
VAR _sec = MOD ( _agg_time , 1.0 ) * 60
RETURN
FORMAT(_hrs,"00")&":"&FORMAT(_mins,"00")&":"&FORMAT(_sec,"00")

    Which gives you this result:

    enter image description here