Original subtitles

Instructor: Alright, so we're doing well here

and have already looked

at some tools that allow us

to perform data exploration in QA,

conduct some basic table transformations,

and manipulate tables with text and numerical tools,

and in this lecture,

I wanna talk about arguably one of the most important tables

in the data model, the date table,

and we're gonna check

out some date-specific tools within the Query Editor.

So, looking at the "Add Column" menu in our Query Editor,

you'll see those date

and time tools grouped here on the right,

and these are pretty straightforward.

You've got a few different options here,

and one of the more interesting ones right

at the top of the list here is the Age option,

and that actually automatically calculates the difference

between the current time based on today, or a Now function,

and the actual date in each row.

You have the "Date Only" tool,

which basically looks at any field that might contain

both a date and a timestamp

and it strips out the time component

and leaves you with just the date only.

You also have your pretty basic set

of date component functions like year,

month, quarter, week, and day,

and that just basically extracts individual components,

or individual attributes, from the date field,

and if you're selecting a field

that has a timestamp as well,

you'll also have similar time-specific options

like hour, minute, and second.

Two other ones that I wanna call

out here are Earliest and Latest,

and you'll see that those two are at the bottom

of the list and they're actually both grayed out,

and that's because we're in an "Add Column" menu,

and both of these functions act just like statistics tools

and they return a single value.

So, to access those two options,

you actually need to grab them from the Transform option,

and as you might have guessed,

the "Earliest" option is going to return

the earliest date in the column,

"Latest" is gonna return the last date.

So, a quick note here,

you'll almost always wanna perform these date operations

from the "Add Column" menu as opposed to the Transform menu

since most of the time,

the goal is to actually build

up new fields using these date components

as opposed to overriding

or transforming an individual date or time column.

So, pro tip here,

what you can actually do

in Power BI quite easily is load

up a table that contains a single set of dates,

or you could create a date table from scratch,

and then use these date tools to actually build

out an entire robust calendar.

So, that's exactly what I'm gonna show you how

to do in a second, but first,

let me give you a little sneak peek

of what it's gonna look like.

So, we're gonna start out

with a single column containing dates,

and then we're gonna apply some

of those predefined date options using

the "Add Column" tools

in the Query Editor,

and then we're gonna transform that individual column

into an entire calendar containing additional fields

like day of week, day of name,

start of week, month, quarter, year, et cetera.

Alright, so let's open

up Power BI and we'll give this a shot.

Alright, so if you're continuing along,

we had left off in the Query Editor here,

and we're gonna go

and open up our sixth CSV,

and we're gonna connect

to our AdventureWorks Calendar Table.

Alright, and the preview looks very straightforward.

We have just a single column containing dates.

So, let's click OK

and we'll add this into the Query Editor, and again,

let's keep our process consistent.

So, the first two steps that we're gonna take,

we're gonna update our table name to "Calendar Lookup,"

and then we're gonna confirm

that our column headers are updated

and that the data type is set.

So, we can see that we have date here

and that our data type is set as a date also.

Alright, so check and check. Everything looks great.

Now, let's explore some of these different date tools,

and I actually wanna start

with the "Earliest" and "Latest" options.

So, I'm gonna head up to the Transform menu,

and then I'm gonna come over to my date tools here,

and when I click "Earliest,"

it behaves just like those stats functions, right?

We transform the entire table

into a single aggregated value,

and we can see here that our calendar,

or our "Earliest" date starts on January 1st, 2020.

If I clear this out,

head back to my Transform tab,

and then we'll click "Latest,"

this is going to give us the latest date

in the calendar, right?

So, this is June 30th, 2022, alright?

So, we've got about two

and a half years worth of data within our calendar table.

So, let's close these steps out

and we'll get back to where we started.

Now, let's shift gears into actually adding columns, right?

Because that's what the real value is here, and this time,

I wanna create some new fields based

on this date column.

So, if I come up here to "Add Column,"

and let's say we wanna add in the day name first, right?

And again, you can see that these values are great out here

because we're adding columns.

So, if I click on "Name of Day,"

we get a brand new column that's added.

We have an applied step here, insert a day of name,

and we see our column here, right?

"Day name" for the column header name

and then the data type is a text,

and we could update this if we wanted to,

but "Day Name" is actually pretty good,

so we're gonna leave this as is.

Another interesting piece here that I wanna show you is

that the Query Editor actually has

some very interesting week-related calculations, alright?

So, if we head up here to Week, we can see week of year,

week of month, start of week, end of week,

and I wanna insert a "start of week" calculation,

and as we can see here,

as we look down when the start

of the week is actually occurring,

we can see that it happens on a Sunday, right?

January 5th is the start of the week, on this Sunday.

We skip down here,

we see the 12th, it's a Sunday,

so on 19th, the Sunday, so on and so forth, right?

But maybe that's fine, but maybe you wanna update it,

and to update it or customize the start of the week,

it's actually not super intuitive

and there are a couple of different ways that we

could actually accomplish this, right?

The first way is we could come up here to the Formula bar

and we could type in and edit the M code here,

or we can click this gear icon

and edit the applied step directly

from a window that pops up.

Alright, so now that we're in

this custom column window here, right,

this just makes it a little bit more manageable

to understand what exactly we're dealing with

instead of trying to edit the M code directly

within the Formula bar,

and this date function, "Date.StartOfWeek,"

has an optional second parameter, right?

The first parameter is the date column that you wanna use.

The second is the start,

or the first day of the week, right?

You can see it brought

up here through the Intellisense option,

and I know that if I type in "one,"

that returns Monday as the start of the week.

So, we'll click OK,

and you can see

that our "Start of Week" column has shifted, alright?

So, 1/6, it's a Monday, 1/13, it's a Monday, 1/20,

it's a Monday, again,

so on and so forth.

You can also see up here

in the Formula bar that the one has been added to this code.

Say we wanted to change this to starting on a Tuesday,

we could update that to two,

and then we see the column shift again,

1/7 on a Tuesday, 1/14 on Tuesday, 1/21 on Tuesday, right?

So, it's super easy to kind of update the start of the week.

It's just not really intuitive at first.

So, let's actually jump back in to the supplied step here,

and while this is really straightforward,

it's tough for people to kind of read this, right?

Two or one isn't a very intuitive or readable format.

So, our other option here is to come in

and update this two to something a little bit more readable,

like "Day.Monday, right?

And that is going to show somebody,

when they're looking at this code that, hey,

the day of the week is starting on a Monday.

We'll click OK, and again,

we see that update back

to Monday as the start date on the sixth,

Monday on the 13th,

so this is great.

I want my week to start on a Monday,

so I'm gonna leave this alone.

Alright, so let's keep things rolling along here,

and I wanna add in some additional date columns, like,

let's add in start of month.

So, if I come back up to my date tools, click on Month,

and then click on "Start of Month,"

I can see here I've got this "Start of Month" column,

and on close inspection here, something seems off,

and what we need to be really careful

of is the column that we've selected

to base our calculated columns off of, right?

So, see how we were on the "Start of Week" column?

When we look at the "Start of Month,"

we're seeing the start of month is in December 1st,

December 1st, December 1st, December 1st, December 1st,

but our dates are actually in January, right?

It takes us until the 6th

of January to actually catch up and say, hey,

the start of our month is actually in January.

So, what we need to do is make sure we have

the Date column selected,

and then we can come over, go to Month,

"Start of Month," and here,

you can see the difference between the two, right?

Now, we're correctly assigning the appropriate start

of month based on the Date column instead

of based on the start of the week.

So again, this is something to really just keep an eye

on as you're working through adding columns.

If you have any sort of issues

or errors within these calculated columns,

make sure that you're basing everything

off of the Date column.

So again, we cleared those applied steps out.

I'm gonna go back to my "Start of Month," and now,

we've got this appropriately set up, right?

All these starter months in January, we get into February,

and it's starting appropriately there as well.

Alright, so everything looks good now.

Let's add in one more column here

for our start of quarter,

and again, we had that Date column selected,

so our "Start of Quarter" values

are good to go there as well.

Alright, so this is a great start so far,

and I think you're getting the idea

of how quickly a calendar table can be built

from a single column of dates,

and what this calendar table will actually allow you to do

is to filter and segment the values

in a data set by any of these fields that we've created,

and this is gonna be really helpful when we start using more

of the analysis and visualization tools within Power BI.

Alright, so for now, I think our calendar is good to go.

We'll head back home.

We'll click "Close and Apply" to save these changes.

There we go.

We've got our calendar lookup table

that we just imported today.

Alright, so there you have it.

Those are our query editing date tools.