cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft
Microsoft

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
Super User
Super User

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
Microsoft
Microsoft

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

@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!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.