Convert a number column into a time format in Power BI

Question

I'm looking for a way to convert a decimal number into a valid HH:mm:ss format.

I'm importing data from an SQL database.

One of the columns in my database is labelled Actual Start Time.

The values in my database are stored in the following decimal format:

73758    // which translates to 07:27:58
114436   // which translates to 11:44:36

I cannot simply convert this Actual Start Time column into a Time format in my Power BI import as it returns errors for some values, saying it doesn't recognise 73758 as a valid 'time'. It needs to have a leading zero for cases such as 73758.

To combat this, I created a new Text column with the following code to append a leading zero:

Column = FORMAT([Actual Start Time], "000000")

This returns the following results:

073758
114436

-- which is perfect. Exactly what I needed.

I now want to convert these values into a Time.

Simply changing the data type field to Time doesn't do anything, returning:

Cannot convert value '073758' of type Text to type Date.

So I created another column with the following code:

Column 2 = FORMAT(TIME(LEFT([Column], 2), MID([Column], 3, 2), RIGHT([Column], 2)), "HH:mm:ss")

To pass the values 07, 37 and 58 into a TIME format.

This returns the following:

 _______________________________________
| Actual Start Date | Column | Column 2 |
|_______________________________________|
|       73758       | 073758 | 07:37:58 |
|       114436      | 114436 | 11:44:36 |

Which is what I wanted but is there any other way of doing this? I want to ideally do it in one step without creating additional columns.

Answer 1

You could use a variable as suggested by Aldert or you can replace Column by the format function:

Time Format = FORMAT(
                    TIME(
                        LEFT(FORMAT([Actual Start Time],"000000"),2),
                        MID(FORMAT([Actual Start Time],"000000"),3,2),
                        RIGHT([Actual Start Time],2)),
                "hh:mm:ss")

Edit:

If you want to do this in Power query, you can create a customer column with the following calculation:

Time.FromText(
     if Text.Length([Actual Start Time])=5 then Text.PadStart( [Actual Start Time],6,"0") 
     else [Actual Start Time])

Once this column is created you can drop the old column, so that you only have one time column in the data. Hope this helps.