PL-300 – Section 17: Part 2 Level 5 – Transform – Dates and Time

135. 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 out friend the PowerBIData 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 window’s 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 999. I could do that. Equals sign, and in curly brackets, notice I’ve just entered the first curly bracket, the curly brackets donate a list. So, I want numbers from one to 999. So, just going to type one, and two dots, and 999, and enter. And that gives me a list all the way from one to, if I was to scroll down far enough, 999.

Now, this is a list. It’s not a table, but we can transform the list to become a table. You can see we got the various keep items, like keep the top three items or move the top three items, remove duplicate, reverse the items, sort, and statistics, that we’ve had a look at in the previous sections.

But we can convert this to a table if you so wish. And to do that, click To Table, and enter a delimiter, well, there’s no need for a delimiter in a list with one column, but I’ll just have a space. And click okay, and obviously a space won’t work with a number, so let’s just go back to that dialogue box. I want none. And now, we have a table with one to 999, and from there, we can do things like adding additional columns. So, maybe, I wanted to add one to this number, there. So, let’s go back to where we were, one to 999.

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 Zed. 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 put in 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 1st of January, 2022 to the 31st of January, 2022, we get an error. We can’t apply this operator, dot-dot, to types 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. It ends at 999. 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 we have a look at one-comma-999, there are two versions of this formula, one just starting with a number and then the count, and the second, which is three 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 999. Or just like we’ve got this in numbers, there is also a list function for dates. So List.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 1st. We want a full year, so this will be 365 days. It’s not a leap year. If, however, we put in say 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, zero hours, zero minutes, zero seconds. So now, you can see, the list goes from the 1st of January, 2022, all the way to the 31st of 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 matched to dates, then, we’re not going onto this list of data and going, “Okay, what’s two days before the 22nd of December?” Because if we had a list with incomplete dates, it might go, “Well, two days before 22nd of December, “the item we’ve got is 21st of 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 #date and #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 put in the time. So, hash or pound time, and then let’s start at midnight, zero hours, zero minutes, zero seconds. We will have 96 of these, and we will be going up in zero days, zero hours, 15 minutes, zero seconds. So, that gives us every quarter of an hour from 12 am, all the way to 11:45 pm.

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 name of the month in a column. It’s 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.

136. Transform/Add Column – Date

So, we’ve got a list of dates here using the proper capitalization. So, capital L for List, capital D for Dates, because they’re also M formulas but lower case D for date and duration when they are preceded with a Hash or Pound Sign.

So, let’s convert that into a table. And we’ve renamed the table. So, for a date table, 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 the 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 DateTime.LocalNow, So, let’s just break this formula now, down. So, DateTime.Local.

Now, that is the current date and time where I am right now, my local timezone. Or technically, on the system. There is another one called DateTime.FixedLocalNow, which will give you the same answer when you repeatedly call it. But most the time you just call LocalNow,. Date.From, converts the Date Time LocalNow, to a date. So, what’s the point in that? Because it drops the time. So, if right now it was, let’s say, the 31st December at 5:28 PM, then converting it to a date will make it just the 31st December. We’d then subtract in the column called Date. So, we’re taking the LocalNow, and subtracting the column called Date. So, if you wanted it the other way around then you could say, Each Date minus Date.FromLocalNow. 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.00.00. It’s a Type Duration. Again, you’ll notice that Duration has lower case d. Also notice all of this in enclosed within a Table.AddColumuns 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 few times. So that is how many days we’re in currently, our Age. And you can see the little duration there, except I’ve reversed it. You can convert Date Time 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.EndOfYear. 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 using 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 or the end of the month, the number of days in a month so you could do calculations as to how many 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 local, don’t worry, we will be looking at other locals 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.QuarterOfYear, Date.StartOfQuarter, Date.EndOfQuarter, fairly predictable results. We then have the week, so the week of the year. 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 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 day, we have got the day itself, we’ve got the day of the week, day of the year, start of day, end of day, and name of day.

So again, all of those formulas, very predictable. Date dot 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.9999999 seconds. But it’s not doing that because the date that I’ve got here, the date column, it’s actually not a date, it’s text. So, I’m going to change that to Date.Time and notice it says the 1st 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 2nd January, right at the top. But when I click on it, you’ll see at the bottom, it saying the 1st 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, or Date.Time columns. So either 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 actually answer is zero because there is no difference between the number of days from the 1st January at midnight and the 1st January at 23:59:99999 seconds. So, if there was an additional minute added to that then this subtraction would say one. You notice, the formula there is Duration with a capital D for days. And 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’ve 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 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.

• Category: Uncategorized