Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table with a couple of text fields that employees put a time in such as 0800 or 1625. The system we input into doesn't allow it to be formatted as a time.
I'm wondering the easiest way to convert it to a time value. I can't just add a column and concatenate with left two characters and right two characters with a colon in the middle as inevitably someone types in a value incorrectly such as 8025 or 0861. Currently I am make three extra columns. One with the left two characters, converting it to a number and one with the right two characters and converting it to a number. Then a third column that checks to make sure the original field is 4 characters, the hour column is less than 24 and the minute column is less than 60 and doing the concatenate that way.
Is there an easier way?
Solved! Go to Solution.
invalid times are a little more tricky because the left/right still reads them.
but this will fix the length issue:
there could be a couple of options.
Are you able to attach an excel document with some samples - just so I can be sure to get all scenarios.
@Anonymous I just posted an example for Taylor. Does that help?
@kattlees You can navigate to the "Modeling" tab on the top ribbon, click on "Data Type" and change to "Time." Hope this helps!
Thanks taylor_c, I do that after I do the final concatenate but I was hoping to avoid having to create 2 new columns. Maybe I didn't explain well enough
Data comes in as text field
StartTime 0825
I then create
StartTImeHour = left(StartTime,2) to get 08 and convert to a number to get 8
StartTImeMinute = right(StartTime,2) to get 25 and convert to a number to get 25
Then I create
StartTImeFinal = if(Len(StartTIme)=4 && StartTimeHour < 24 && StartTimeMinute < 60, concatenate(Left StartTime,2) & ":",Right(StartTime,2) and convert this to a time datatype
I've managed to do this and it works OK. Crucially you need to import this data as text (so it keeps the original '0' whre necessary)
Then change the format of Time in the modelling tab.
Thanks @Anonymous This does work nice. What happens if someone has a typo and enters 1262 or only puts in 3 characters like 800? I get an error and the visual won't display because 12:62 and 80:00 aren't valid times.
invalid times are a little more tricky because the left/right still reads them.
but this will fix the length issue:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |