Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
kattlees
Post Patron
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
Phil_Seamark
Employee
Employee

HI @kattlees

 

Is this what you are after?  

 

 

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

 

image.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

6 REPLIES 6
ChrisMendoza
Resident Rockstar
Resident Rockstar

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

 

TimeHourMinuteCONCATENATETIMEVALUE
1245124512:450.53125
0700070007:000.291666667
0637063706:370.275694444
0027002700:270.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.

 

 8.PNG

 

One formula:

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





Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Phil_Seamark
Employee
Employee

HI @kattlees

 

Is this what you are after?  

 

 

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

 

image.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@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 I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



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

 

 

image.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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 I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Greg_Deckler
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?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.