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

1. Creating a list of dates

Hello. In this section we’re going to have a look at dates and times. And we’re going to start by getting data, but not from our friend at the Power Bi data spreadsheet. Instead, we’re going to start with a blank query. So there’s some interesting things we can do with a blank query. Now, I’m just making sure the windows just a bit larger than normal, so that when we get to date, time, duration, it looks like that. So a blank query, absolutely nothing in it. So let’s add some data. Suppose I wanted to add the numbers from one to nine. I could do that equal sign and in curly brackets. Notice I’ve just entered the first curly bracket, the curly bracket donate a list. So I want numbers from one to nine. So I’m just going to type one and two dots and nine and enter. And that gives me a list all the way from one to if I was to scroll down far enough, nine. Now, this is a list. It’s not a table, but we can transform the list to become a table.

You can see we’ve got the various keep items, like keep the top three items or remove the top three items, remove, duplicates, reverse the items, salt and statistics that we’ve had a look at in the previous section. But we can convert this to a table, if you so wish. And to do that, click to Table and enter a delimiter, where there’s no need for a delimiter in a list of one column. But I’ll just have a space and click OK. And obviously a space won’t work with a number. So let’s just go back to that dialog box. I want none. And now we have a table with one to nine. And from there we can do things like adding additional table columns. So maybe I wanted to add one to this number there. So let’s go back to where you were one to nine.

Now, if I wanted a list of letters, not that likely, but if I did, then I could put them in quotation marks. And here we have a list from A to Z. And again, you could convert that to a table. But it’s a bit more difficult to do this with dates. But let’s figure out how to do it. So, first of all, a date can be expressed in a formula with a hash or pound sign at the beginning, and then the word date and then open bracket. And we’re putting the year, the month and the day. So we do that and there is our answer. However, if I was to do exactly what I’ve just been doing, so date from the 1 January 2022 to the 31 January 2022, we get an error. We can’t apply this operator to type date and date.

So instead we need to use a different function. Now, if I was using numbers I could use list numbers, so I could say where it starts. So we start at number one, and where it ends tends at nine. And we get the same sort of list that we had before. But it’s noticeable that there is two ways of doing this. So if you have a look at one comma, nine nine. There are two versions of this formula. One just starting with a number and then the count, and the second, which is free arguments with the increment. So if I was to put in two, then it would start at one and go up in twos all the way to nine. Or just like we’ve got this in numbers, there is also a list function for dates. So list dot dates, and again you put in the start, you put in the count how many you want and you put in the step.

So we want to start in this case at the date of 2022, January the first. We want a full year, so this would be 365 days. It’s not a leap year. If, however, we put in say, 200 364, it would stop one short. So it’s not start with this date and generate another 364 more. It’s how many do you want in total? And then the step as duration. So what is the interval between each of these days? And here we’re going to use hash or pound duration. Now, duration is measured in things like days, hours, minutes and seconds. So we’re putting one day, 0 hour zero minutes 0 second. So now you can see the list goes from the 1 January 2022 all the way to the 31 January 2022. Now, this is called a date table. Date tables can be incredibly useful in power bi because we know that this list contains every single date. We’re not missing one. And so if we’ve got data which is being mapped to dates, then we’re not going on to this list of data and going, okay, what’s two days before the 22 December? Because if we had a list of incomplete dates, it might go well two days before 22 December. The day before that is or the item we’ve got is 21 December. Item before that is the 18th.

Because we’re missing two dates here, however, we are guaranteed not to have any missing dates. And you can see very swift to do so hash date and hash duration. If I wanted to do this for times, and we will be doing this for times later on, then we have a list times and here we’ve put in the time. So hash a pound time and then let’s start at midnight, 0 hour zero minutes, 0 second. We will have 96 of these and we will be going up in zero days, 0 hour 15 minutes, 0 second. So that gives us every quarter of an hour from 12:00 A. m. All the way to 11:45 P. m. .

So let’s go back to our list of dates that we have generated. In fact, I’m going to double this. Why not? 730 days. So that generates it all the way going to the end of 2023. And now I will convert this to a table. So no delimiter. There we go. And let’s rename this as date. So this is the first step to having a date table. Other next steps could be to have the year in a column, the month in a column, the quarter in a column, the name of the day in a column, the day of the month in a column. It just can be incredibly useful to not have to calculate them, but just look them up from a table. And we’ll be doing that in the next video.

2. Transform/Add Column – Date

So we got a list of dates here using the proper capitalization. So capital L for list, capital D for dates, because those are M formulas, but lowercase D for date and duration when they are proceeded with the hash or pound sign. So let’s convert that into a table and we’ve renamed the table. So for a date, what we want is to extract things from this. We want to extract the year, we want to extract the month, we want to extract the day, the name of the month, the name of the day and so forth. And we can do that quite simply in the date part of transform or what we’re going to use. Add column. So we’ve got a list of items here, some more useful than others. For example, age. So that tells you how many days into the past this particular date is from now.

And you can see now is date time. Local now. So let’s just break this formula now down. So Date Time Local Now, that is the current date and time in where I am right now, my local time zone. Technically on the system there is another one called Date Time fixed Local Now which will give you the same answer when you repeatedly call it. But most of the time you just call Local Now. Date from converts. The date time local now to a date. So what’s the point of that? Because it drops the time. So if right now it was, let’s say the 31 December at 05:28 P. m. , then converting it to a date will make it just the 31 December. We’re then subtracting the column called Date. So taking the Local Now and subtracting the column called Date. So if you want it to be the other way around, then you could say each date minus Date from Local Now. It then gets converted into the type called duration. So duration is what is a type which has days, hours, minutes, seconds. So that’s why we’ve got 780. 00. It’s a type duration.

Again, you’ll notice that duration has a lowercase D. Also notice all of this is enclosed within a table. Add columns which has the previous step. So take the table in the previous step, add a new column and this is what you add with it. So we’ll be seeing that quite a lot, few times. So that is how many days wind currently are aged. And you can see the little duration there, except I’ve reversed it. You can convert date times to date only. So you can see it converts it into a type date. For this new column we can extract the year. So either the year itself using Date Year and convert it into an integer, or we could have a date at the beginning of the year.

So start of year or a date at the end of the year, which would be Date End of year. So usually out of those year is probably the most useful. And then secondly, probably the start of the year so you can see how many days you are from or used in calculations. Now we’ve also got month, so we’ve got month, that is the month number. Or we’ve got the start of the month for the end of the month, the number of days in the month. So you could do calculations as to how many days you’ve got to go and that sort of thing. And we’ve also got the name of the month, so the name of the month in this case being January and then going down to February. If you’re not using an English locale, don’t worry, we will be looking at other locales in the next video. So I’m just going to keep in here the month and the year. So month, month, year. Additionally, you could have quarter, quarter of year, date dot, quarter of year, date dot, start of quarter, date, end of quarter, fairly predictable results. We then have the week, so the week of the year. So starting at one and going all the way up potentially to 53.

There we go. Or we have week of month. So that’s a number between one and five, whoops like one and six and then the start of the week and the end of the week. So of those I’m just going to keep start of week and I’ll be keeping all of these because of the next video, I want to actually have a play with these formulas looking at the date of the day. We have got the day itself, we’ve got the day of the week, day of the year, start of day, end of year, day and name of day. So again, all of those formulas, very predictable, date dot, day, day of week, day of year, start of day, end of day. So from those, again, I’m building up to the next video. I’m going to keep name of day, but just to show you what the start of the day would look like, it just gives you the date and end of the day. If our data was date time, it would also give us the date time 23, 59 and 59. 9 seconds. But it’s not doing that because the date that I’ve got here, the date column, is actually not a date, it’s text.

So I’m going to change that to date time. And notice it says the 1 January right at the top. I’m going to now insert the end of day and you’ll notice it looks like it says the 2 January right at the top. But when I click on it, you’ll see at the bottom it’s saying the 1 January with a fraction of a second before midnight, which is being rounded up in the display. The next thing, subtract days. That requires you to highlight two day columns, so or date time columns, so I have a date or date time. So if I bring these two over just so that it’s easier to look at. We now see subtract days is now allowed. So I click on that and you can see the number of days between them. And the actual answer is in zero because there is no difference between number of days from the 1 January at midnight and the 1 January at 23, 59, 59. 9 seconds. So if there was an additional minute added to that, then this subtraction would say one. You notice the formula there is duration of a capital D days.

Andrew we’ll be using this formula later when we start looking at times. We’ve also got combine Date and time. So if we have a Date and time column, we can combine them into one big time. And then we got Earliest and Latest, which gives you essentially the Min and the Max. So that is a quick look through all of these date functions. All bar one pass. And we’re going to have a look at pass in the next next video. I’m going to remove a few of these columns so that we can have a look at what happens if you have data that comes in date data which is not in the language or the format that you expect.

3. Transform/Add Column – Dates in other cultures/languages

Now, let’s say that you have got year, month and day in separate columns and you want to combine them together and then make a date. How can you do that? Well, you could use the Merge columns feature, highlight the columns and then merge columns. Now, I’m just going to put a space separator just for the moment, and you can see the result 2022. One, two not really the order that we want. Now, we can change it in the formulas. Obviously, we can change where they are in this table, but we can change it in the formula. Suppose we wanted the columns to stay in the same place. So here we have the year text from converts into string. So converting the year, converting the month, converting the day. So what I want is an American format.

So American format is month, day, year. So we’ll put the year right at the end and we’re going to separate them, not by the space that we’ve done previously, but by slash. And now we have got something that looks like a date, albeit is in text format. We can convert it into a date like that, or we can go to pass. So I will transform this date using pass, and so it reads the date and so it tries to interpret it as a date. Now, let’s just have a look at that formula again, because there’s something interesting going on here. En. Hyphen us English United States. That is called the culture, and that is going to be useful for this particular video. So it’s a net culture specifically. And if you look up, as I’ve done, for instance, net culture, fr hyphen fr, which is the French of France. One of the hyperlinks you get is this, which gives you a full list of various cultures.

So en hyphen us, you can see, is United States. So how can this help with interpreting dates from a different culture? Well, let’s remove this past date and let’s instead of having this being in the US culture, month, day, year, we’re going to change the order so that the dates that we’ve got are in the British order. So here we have the British order, which is day, month, year. And now we’re going to pass this. And you can see we’ve got problems. The computer is passing it thinking this is American, because this computer has been set up in an American locale. So for the first row, the computer thinks that’s going to be the first month, first day, 2022, that’s February, that’s March, but it’s not. It’s in the British format. And when it gets to the 12th, okay, that’s December the first, it thinks, not the 12 January.

And then when it gets the 13th, it hasn’t a clue until it just gives an error. So how can we use this pass to say, okay, these dates are from Britain? Well, what we can do is use the culture that we were just talking about. So the culture name for Great Britain is en hyphen, and I think it’s GB. So, look, it could be UK, not David. So it’s English. United Kingdom en GB. But the question is, where are you able to put it into this formula? There isn’t a en hyphen us already. These dot from functions have got two arguments, and the second one is culture as nullable text. So as nullable text means it doesn’t have to be there.

So what we’re going to do, let’s work this out. The underscore is what’s already there. So give me that format cell, convert it into a time date time zone. We’ll be getting on to date time zone later, but just for now, know it’s a date time, which also has a time zone, and then it converts it into a date, so it gets rid of any time. So it’s this first one that’s going wrong, it’s getting your text, it’s converting it into date time zone wrongly. So this has a culture as a null able text. So right here, that is the value that we’ve got at the moment. The user value is already there. So I’m going to put a comma and then in quotation marks en hyphen GB and press Enter. And now you can see it works. The computer has interpreted it correctly, the errors have disappeared. And because this is a date, and because my computer is set up in American in the United States, then it has been displayed in the American format. So that’s how you can get from a British date into an American date. And similarly, that can also happen the other way around. So I’m going to have a small modification of what we’ve already got. Suppose we had this date, but we wanted the month name, not in English but in French.

Okay, so let’s go back to where we’re inserting the date, the month name, and you can see the formula is date month name. But when I hover over it, you can see again there is a culture nullable text. So I can change or add in a second argument. So I’m going to put in the French of France, fraudcase, mine fr, uppercase. And now, instead of January with a capital J, we’ve now got Yanver. Similarly, I can put in Spanish en so, es, so inhero Febrero and so forth.

So I can do the same with the date name. It’s like the day name. So again after day, because if I hover over a day of week, you can see culture. I can change that again, es hyphen, yes. So now I’ve got Sabado, Domingo, Lunas, mates, mikolas, and that sort of days. So suppose instead of having one, one 2002, so month day, 2002 of the year. Instead of that, I did have the month, I had the month name, and I’m going to use spaces to separate them.

So I’ve got one inro 2002, so I can try passing that. And you can see I can also convert it to date time, but that will just get me an instant error. So if I pass that, I still get the same error. But now I can modify the culture so that I can say to the computer, it’s actually Spanish of Spain, press Enter. And there we get the interpretation of one Inero two Inero one fibero maso and all the rest into American style dates or however the computer is set up on your computer. However, the format is so culture very important if you have got data that is coming from a different culture to the one that your computer is set up. Similarly, you can export into a different culture.

So I could have a column for English name, French name, Spanish Name, and so forth, if I so wished. And in this video, we’ve had a look also at the formulas that have been used and seeing that there is a hidden second argument that we can put whatever culture you are currently using or wish to use to interpret the data that you got.