cancel
Showing results for
Did you mean:  Post Patron

## Leading Zeros in time Field

I am uploading an excel document with time fields that are entered in excel as numbers (don't have the colon in them).

Example:

1245
0700
0637
0027

I have this formula to convert the field in power bi
New Time = ifERROR(TIME(LEFT('Time'[Field],LEN('Time'[Field])-2),RIGHT('Time'[Field]2),0),BLANK())

This works amazingly well except on any time that starts with 00 as in the 0027 above.

Is there a better way to do this? My goal is to not have to put the formulas in excel and let Power Bi do the conversion.

1 ACCEPTED SOLUTION  Microsoft

Is this what you are after?

```New Time = FORMAT('Time'[Field],"00:00")
``` Proud to be a Datanaut!

6 REPLIES 6  Super User

If it was just Excel and the cells formatted as TEXT I would do the following:

 Time Hour Minute CONCATENATE TIMEVALUE 1245 12 45 12:45 0.53125 0700 07 00 07:00 0.291666667 0637 06 37 06:37 0.275694444 0027 00 27 00:27 0.01875
1. [Time] values as TEXT
2. [Hour] = LEFT(A2,2)
3. [Minute] = RIGHT(A2,2)
4. [CONCATENATE]= B2&":"&C2
5. [TIMEVALUE] = TIMEVALUE(D2)

Get Data actually converted to Whole Numbers automatically so I had to remove the step to get the leading zeros back by editing the query. I applied the same logic in PowerBI and got the [time in decimal] which I then formatted see below. One formula:

```One Formula =
VAR hours = LEFT(Table2[Time],2)
VAR minutes = RIGHT(Table2[Time],2)
VAR concat = hours&":"&minutes
RETURN
TIMEVALUE(concat)```

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!  Microsoft

Is this what you are after?

```New Time = FORMAT('Time'[Field],"00:00")
``` Proud to be a Datanaut!  Super User

@Phil_Seamark Is this not TEXT now? Can you still do time math on this column? Of course I'm assuming doing math on the time is what is desired.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!  Microsoft

Just wrap the field in TIMEVALUE in that case.  You can format the final result using the Formatting features.

```New Time =
TIMEVALUE(
FORMAT('Time'[Field],"00:00")
)``` Proud to be a Datanaut!  Super User

Oh, my @Phil_Seamark, you just made my head explode on that one. Where was that bit of information the first time I had a text time problem. Awesome!

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!  Super User

Typically to preserve leading zeros in numeric fields you convert them to Text in the query. You can also use the DAX FORMAT command but I believe that will also make them text?

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!     