DA-100 Microsoft Power BI – Part 2 Level 5 – Transform – Dates and Time Part 2

4. Transform/Add Column – Time

In this video we’re going to have a look at the time functions that we have got. So we’re going to create again a new query, starting from a blank query. And we’re going to have a list of dates. But instead of using dates or times, I’m going to use date times, which combines dates and times. So instead of starting with a date, I’m going to start with a date time. So the difference between hash or pound date and date time is that date requires three functions free arguments, year, month, day, whereas date time requires six. So we have the year, the month, the day, the hour, the minute, the second. So I’m going to be starting at midnight on January the first, 2022. Now, with list date times, you then say what the count is, how many individual times do you want or what? 9000.

And the step, this is as a duration. So again, pound of hash duration. Now, duration has four arguments days, hours, minutes, seconds. If you’re wondering, by the way, why say months isn’t part of the duration, then it would be impossible for the computer to do it because how do you tell the computer what a month is? It 28, 29, 30 or 31 days. There are ways that we will get into to advance dates and times and we’ll have a look at that in the next video, I think. So if you want to go a day later, you can do that or months later. So here we now have a list of 9000 rows which have got various days and times just 15 seconds away from each other. So 15 minutes away. I’m also just going to put in a little randomness just for the purposes of this video. I wouldn’t do this otherwise just to change the seconds. And I’m going to convert that into a table.

 So this is my date time and I’m going to just put the word column just in case it gets confusing. And I’m going to call this a date time data type. So let’s have a look at the transform and the add for times. Well, first of all, you can add the time only, so this will separate out the date from the time. So similarly with the date, you could have the date only, so that gives me just the date. Then we can have the number of hours. So here is the hour of that particular time and then the minutes and the seconds. Now you can also get what the start of the hour and the end of the hour is.

So if I just simplify this by just removing a few columns, I can look at what the start of the hour is. So instead of it being one and 4 seconds, it’s 01:00, 115 and 5 seconds, it’s 01:00. And similarly the end of the hour, it looks like it’s the next hour. But if you click onto it, you can see it’s the next hour minus a fraction of a second note no, not one of a second. Now, what’s the difference between these two? Well, we can highlight that and go to subtract. So you can see there’s 15 minutes and 1 second. Notice that a difference between two times becomes a duration.

And in the next video we will look at all of the duration functions. Now, just to let you know that, again, you can combine date and times. So if I extract an additional column at the date only and then the time only, we can highlight these two columns and combine date and time. So you could have that into a new column or you could transform your existing columns and have it in a new single existing column. And likewise, as per date, you can have the earliest and the latest and you can pass dates as well.

Again, there may be a cultural impact on this and there is not as much cultural impact as you might imagine for dates. For dates you could have January, Janvere, Niro or lots of different languages. It’s a bit harder to put times into various cultures, but there are still some cultural differences. For example, the time 525 in Japanese might be expressed like this with a little character in between for the hours and the minutes. And so that could need some representation. Equally, if I was to go into, say, Spanish and say what is 05:25 and start talking about Am or PM, then Spanish speakers don’t use Am or PM. So there may be some cultural requirements with passing there, but probably a lot less than you’ve got with dates.

However, just a quick demonstration of how to pass. So I’m just going to get the hours, the minutes, the seconds, going to combine them all with a call on in between. So this could be the source data that you’ve got and then you could pass it like that. So this is how we work through the time functions, very similar to the date functions. In the next video we’ll have a look at the difference between times and dates being durations. So like this being a duration and the functions that we have got for them.

5. Transform/Add column – Duration

Now, in this video, we’re going to look at duration, and it’s the same setup as we’ve had previously. I’ve just removed a few columns. So duration is the gap between two date times, or two dates or two times. So it is measured with four attributes day, hour, minute, second, and you can extract extract those at the top. So you can have days, hours, minutes or seconds. So if I was to extract seconds, for instance, you’ll see that it has extracted the thing right at the right hand side. So 15 minutes and 1 second, it’s extracted the one. So this isn’t extracting how many seconds, 15 minutes and 1 second is it’s just extracting 1 second.

Now, you can do that by using total years, total days, total hours, total minutes, total seconds. So if I click on total seconds, you can see that 15 minutes and 1 second is 901 total seconds. So you may remember in a few videos previous I said you could have the difference between two dates and then we can change this. So we can see the difference in hours or the difference in minutes or the difference in seconds, or we can also adapt that to say, the difference in total seconds, which gives us an identical formula to what we’ve got here, is just another way of getting to it. A lot of these dropdowns give very similar results.

So I’ve changed subtract days to subtract total seconds very easily. Now, if I was to get a second duration, I can subtract the two away from each other. So obviously these two have been created using exactly the same process and so the difference is zero. But you can see that duration is able to subtract one value from another. We can also multiply and divide durations. For example, we have this duration. What happens if it was doubled?

So I can multiply it by two or I can divide it by two if you want to note it’s like one. And of course, you can edit the formula if you so wish to refer to another column. And then there’s all the standard things that you can do with durations, like you can do with time and date. You can have the sum, the min, the max, the median, and the average doll hidden away in the statistics, but very rare that you actually need to use them. Most of what we’ve got here for duration is duration days, I’ll minutes, seconds and total days. Total hours, total minutes and total seconds. You’ll have seen, by the way, total years. That is simply total days divided by 365. So durations one date time or date or time minus another can be manipulated.

6. Practice Activity Number 14 – The Solution

So how did you do? Well, we looked at time and dates in this recent section and durations. So this gives you a small overview of these. So we’re looking at the computer login. I’m going to transform that. Now it’s got all of this time zone data. We’ll be looking at time zones later on, but you don’t need to worry about them. Also notice that in fact there were two time zone columns and the second one has been called Timezone on score one. So first of all, we want the date only of the time of the of the login column. So without the time. And the reason for this is it probably just makes it a fair bit easier sometimes if you’re just looking at dates without times. So we’re going to add a new column date only and we’re going to call that Date of Login.

Now again, you could just double click on the header or you can use other methods to rename it. But what I’m going to do is I’m going to change the actual formula from date to date of login. So the next thing is what is the difference between Login and Log out? So I’m going to highlight them. I’ve clicked on one and clicked on the other. Now, notice the answer is actually going to be negative at the moment because we’ve got Login first and then Log Out. So I’ve clicked on Login, and then I’ve held down control and clicked on Log out. And then if I got to subtract, you can see it’s negative. So two ways of sorting that out. Firstly, you can edit the formula so it says Log out minus Login. Or secondly, you could select Log Out first and then hold that CTRL and select Log In and that will do it for you as well. So I’m going to call that duration of login. And again, I’m just going to edit the formula. It reduces the number of steps that mount up over here in the applied step section. Then we’re going to create a stacked chart. I’ve suggested a stacked column chart which shows date of login going across and the amount of time going up. So let’s get the stacked column chart and we’ll have date of Login going on the axis and on the value. I want the duration. Now, you’ll notice that it’s a hierarchy that I brought in. So I can drill down and drill down and drill down further.

But if I do that way using the 2 hours, I’ll get to the first, the second, the third, regardless of it’s, the 3 January, 3 February secondary, 3 March, that sort of thing. So instead I’ll go down to the March a different way using this expand button as opposed to going to the next level. So now we’ve got August 2022, so now we’ve got February the 7th, the first quarter 2022, which may or may not be what you want if you don’t want the date hierarchy, that’s fine. Just click on this drop down and go to date of login instead. And there you’ve got the date of the login. Disadvantage of this method. As you can see, it shows it all at once. Whereas if you have a look at the hierarchy, then you’ve got a scroll bar, so entirely dependent on what you want and you may not want the quarter to be included there.

So here we have February the 10th, 2000 and 222. Now, as it for bonus points, haven’t noticed what the duration of the login is, 2. 88, 1. 38. So let’s just go back and find out what 1. 38 could be from the actual data. So we’ll edit the query and you can see for the 1 January, and I’ll just filter temporarily. On the 1 January, we have got 3 hours, 8 hours, 7 hours, 5 hours, 6 hours and 0 hour. So let’s just work out what that is. Three plus eight is eleven, plus seven is 18, plus five is 23, plus six is 29. And then assume the ball from the half hour. So we’ll add six half hours. So that’s 32 hours. So I’ll just get a calculator out and see what 32 divided by 24 is 1. 33. We had 1. 38 marker calculation. So what we were looking at I don’t want to do that filter, so what we were looking at is number of days, not number of hours.

So having worked out what the data is actually giving us in this visualization, I want to change it, so it’s giving me the number of hours. Now, there are various methods of doing this and well done if you manage to get to any final solution. So you could highlight the duration and go to add column or transform the column either way and say hours. And that will give you roughly the right number of hours as a number. And we’ll close, it applies. And there we have the number of hours. And you can see 29 hours, but it curtails into the number of hours. It’s not really perfect. So a better way could be instead of getting the number of hours, getting the number of seconds. So let’s find the total seconds, not just seconds, the total seconds.

And you can see here 13,714. So from there we need to convert it into hours without the rounding. And we can do that because it is now a number, it is a decimal number. And if it isn’t, make sure it is by using the number column standard. And we are going to divide by 60 and then by another 60. So that’s 3600. So now we got the duration of the login in number of hours as a decimal. So now if we have a look at it, that figure which used to be, say, 29, has now gone up, I hope to 33. 17. So it’s now showing us the number of hours for the login. Well, I hope you enjoyed that practice activity. Hopefully you’re getting excited by all of these new features you’re learning.

So we’ve not just in this particular example, looked at dates, times, durations. We’ve also then brought it back into an earlier version, earlier section, and had a look at how to divide a particular column by 3600, for instance. I’m hoping that this is allowing you to connect the dots in your work as well and allowing you to think of examples where you can use what we are seeing in your data. So now let’s move on to the next.