Leading Zeros in time Field

[kattlees]

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.

[ChrisMendoza]

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)

[Phil_Seamark · Accepted solution]

HI @kattlees

Is this what you are after?  

New Time = FORMAT('Time'[Field],"00:00")

[ChrisMendoza]

@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.

[Phil_Seamark]

Hi @ChrisMendoza

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")
        )

[ChrisMendoza]

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! 

[Greg_Deckler]

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?