In excel, when I have a column with only time, I can not use the formulas in the article Power BI: time management because when I load it into Power BI, the data is showing with an incorrect date:

Excel	Power BI

The “outage” column has a mix of data, values with:

• Date and time (for instance INC001)
• Decimal number (for instance INC003)

Also an outage can last more than 24 hours, for instance, 37 hours so I can not use the time format function. IMPORTANT: I make sure that my “outage” column is formatted as text in Power Query Editor (data type: text)

I will create a new column to convert them into decimal numbers:

var rspace = TRIM('table'[argument])
var extime = RIGHT(rspace,8)
var rhour = VALUE(LEFT(extime,2))
var rmin = VALUE(MID(extime,4,2))
var rsec = VALUE(RIGHT(extime,2))
RETURN
IF(NOT(ISBLANK(rspace)),IF(CONTAINSSTRING(rspace,"/"),DIVIDE(rhour*3600+rmin*60+rsec,86400),
VALUE(SUBSTITUTE(rspace,",","."))))

With the decimal number, I will convert them into seconds:

ROUND('table'[argument]*86400,0)

NOTE: for minutes, replace 86400 by 1440

Once I have them in seconds, I will format them into hh:mm:ss:

var TotalSeconds = INT(VALUE('table'[argument]))
var Hours = FORMAT(INT(DIVIDE(TotalSeconds,3600,0)),"00")
var Minutes = FORMAT(INT(DIVIDE(MOD(TotalSeconds,3600),60,0)),"00")
var Seconds = FORMAT(MOD(TotalSeconds,60),"00")
RETURN
IF(NOT(ISBLANK('table'[argument])),Hours&":"&Minutes&":"&Seconds)

NOTE:

• If the “column 2” was in minutes, replace VALUE('table'[argument]) by VALUE('table'[argument])*60
  
• Doing it from the decimal number, replace INT(VALUE('table'[argument])) by ROUND('table'[argument]*86400,0) and 'table'[argument]
  

To end, I will create a measure so I can see the average duration:

var TotalSeconds=AVERAGE('table'[argument])
var Hours=FORMAT(INT(INT(TotalSeconds/60)/60),"00")
var Minutes=FORMAT(MOD(INT(TotalSeconds/60),60),"00")
var Seconds=FORMAT(MOD(TotalSeconds,60),"00")
return
IF(NOT(ISBLANK(TotalSeconds)),Hours&":"&Minutes&":"&Seconds)

In the other hand, if I want my measure to show values rounded down, I will use this formula:

var TotalSeconds=AVERAGE('table'[argument])
var TotalSecondsRD=INT(TotalSeconds)
var Hours=FORMAT(INT(INT(TotalSecondsRD/60)/60),"00")
var Minutes=FORMAT(MOD(INT(TotalSecondsRD/60),60),"00")
var Seconds=FORMAT(MOD(TotalSecondsRD,60),"00")
Return
IF(NOT(ISBLANK(TotalSeconds)),Hours&":"&Minutes&":"&Seconds)