In the previous article, we understood how to format numbers in power bi. We learned about predefined formatting and custom formatting. If you haven’t already read the previous article, I would highly recommend you to go read that one first.
In this article, we are going to understand formatting time in Power BI.
Formatting Time
I have already loaded a time table in power bi since we’re going to format it.
Extracting Hours from Time
Now I’m going to add a new column and enter the following:
What I entered here is the “FORMAT” code and entered the location of my table and the column which I desire to format along with “h”. “h” here stands for hours. This will allow me to see ONLY hours from the column I’m formatting.
Now that I have formatted the column. A new column has been created where it will show me only the hours from the time table. For example, for the time of “09:56:56”, only “9” was considered in the next column. However, do you notice how “04” is entered as just “4” and “06” as “6”, and so on? Good news for you, we can also change this!
Instead of entering “h”, type “hh” while formatting:
This will allow you to see the zeroes before the number too.
Now you can see how “04” is formatted as “04” itself and “06”, “09”, as “6” and “9”.
Extracting Hours and Minutes from Time
Now, if you feel that you also want to see the minutes along with the hours from the “time” table, you can do that too.
Enter the following in order to format in that specific way:
This will allow you to see minutes along with the hours from the time table.
If you decide that you don’t want zeroes before a number, for example, you want “09:56” to be shown as “9:56”, you can just change the formula to “Time 3 = Format( ‘Table’ [Time], “h :nn”)“. What I did in this formula is basically eliminate the “h” from “hh”. This is basically the first formula we just learned a while ago except it also shows you the minutes.
The above image is the result from entering that formula.
Extracting Everything From Time
Now, if you decide that you also want to see the seconds. you can do so too by entering the following formula:
Time 3 = Format( 'Table' [Time], "h:nn:ss")
The “ss” here stands for seconds. This will allow you to see the seconds along with the hours and minutes from the time table.
Some More Formulas
I believe by now you must have already understood how much more flexible power bi is when it comes to formatting. So I’m now going to ask you to look at the following image and tell me what you think I did here. If you believe that you have understood everything from this article so far, it must be really easy for you to answer this.
Firstly, have a look at the formula. Does the formula appear different?
The previous formula was : Time 3 = Format( ‘Table’ [Time], “h:nn:ss”)
The current formula is : Time 3 = Format( ‘Table’ [Time], “h:n:s”)
What I did here is remove one “n” from “nn” and “s” from “ss”. The same thing we did with “hh”. So now if you happen to have any single digits in the minutes and seconds section such as “6” or “7”, it will appear like that itself instead of appearing as “06” and “07”.
Replacing Colon with Hyphen
Also, we know how the hours, minutes and seconds in “4:34:45” is seperated by a colon (:). If we wanted to change it from a colon to a hyphen too.
Just replace the colon with a hyphen when entering the formula.
Time 3 = Format( 'Table' [Time], "h-n-s")
Once you enter that formula, the following will be the result.
You can also format it from a hyphen to a slash by replacing the hyphen with it.
Time 3 = Format( 'Table' [Time], "h/n/s")
I should also inform you that you are not just limited to a bunch of hyphens and colons but you can enter literally anything! it can be a “*” or a “$” or a “#” or “@”, literally anything as long as you don’t forget to enter it in the formula.
Adding “AM/PM” After Time
You can see how in the time table I have entered the time in a 24 hour format. We can also change this and add an “AM” or “PM” next to it.
We can do so by entering the following formula: Time 3 = Format( 'Table' [Time], "h/n/s AM/PM")
Once you have entered the formula, the time table will be formatted with an AM/PM next to it.
Now this next thing is very random and not really needed. However, since it is ‘possible’ to do so, I will tell you anyway. It is possible to add a text after every single time from the time table. For example, let’s add “Right Now” after “AM/PM” in the formula. You must think that by doing so we will get the desired result. The following is what the formula would look like:
Time 3 = Format( 'Table' [Time], "h/n/s AM/PM Right Now")
Looks pretty neat, doesn’t it? Let us have a look at the result.
The reason why it looks so awkward is because certain alphabets have certain values. In order to avoid this, we will need to separate every alphabet using a backslash. We will use 2 backslashes in order to make a gap between 2 words.
Time 3 = Format( 'Table' [Time], "h/n/s AM/PM R\i\g\h\t\ \N\o\w")
This formula will do it for us.
There are a few more ways you can format the time from the time table.
For example, you suddenly decide that you want to see the entire time including hours, minutes, seconds, AM/PM. To do so, you will enter “Long time” in the formula just like the image below.
If you decide that you don’t think the “seconds” in the time are as important, you can eliminate it. You can do so by using “Medium Time” in the formula.
If you believe that you don’t think the “AM/PM” next to time is important, you can eliminate that too. You can do so by using “Short Time” in the formula.
I hope this article helped you understand time formatting in depth.