Practice Exams:

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

1. 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.

1. 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.

1. 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.