Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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 |
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)
Proud to be a 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.
Proud to be a Super User!
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") )
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!
Proud to be a 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?