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.
Hi All,
I have this data of Transaction Time in my table and it shows the time as a whole number. E.g. 23:00 is shown as 2300. How can I convert it into time format? I tried transforming it but it returned all the values as 12:00. Your inputs will be highly appreciated. Thank you so much!
Solved! Go to Solution.
Hi @Anonymous,
Based on my test, you should be able to use the formula(DAX) below to create a new calculate column to convert your original column to time format.
Column = IF ( LEN ( Table2[TR_TIME] ) = 4, LEFT ( Table2[TR_TIME], 2 ) & ":" & RIGHT ( Table2[TR_TIME], 2 ), LEFT ( Table2[TR_TIME], 1 ) & ":" & RIGHT ( Table2[TR_TIME], 2 ) )
Regards
Hi @Anonymous,
Based on my test, you should be able to use the formula(DAX) below to create a new calculate column to convert your original column to time format.
Column = IF ( LEN ( Table2[TR_TIME] ) = 4, LEFT ( Table2[TR_TIME], 2 ) & ":" & RIGHT ( Table2[TR_TIME], 2 ), LEFT ( Table2[TR_TIME], 1 ) & ":" & RIGHT ( Table2[TR_TIME], 2 ) )
Regards
hi,
i have a lot of stats on times that are in csv format that i have imported to power bi. the formula above works to a certain extent. besides the 3- or 4-digit times (example 115 representing 1:15 or 1615 representing 16:15 that seem seem to work using above formula, there are times that are just 1 or 2 digits (example 7 which represents 00:07 or 22 that represents 00:22) that may require modification to the formula. kindly advise on how to handle by dax formula for those timing numbers ranging from 1-4 digits.
tks, -nik
i found the solution in creating a calculated column (PLN_DEP_TIME) for the column (CRS_DEP_TIME) from the '2017'-table contains those 1-4 digit times as follows. the resultant column wil subsequently b set to hh.nn time-format:
Generally Time is held as a decimal number between 0 and 1. You'll firstly need to convert your time to ensure that its not HHMM (i.e. if 13:50 is stored as 1350, you'll need to change it to be stored as 1383). Once you have done that, you can divide the number by 2400
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |