Original subtitles

Instructor: Let's talk about two more types of columns

that you can add from within the Query Editor,

index columns and conditional columns.

So, starting with index columns,

you'll find the option right here in the Add Column menu.

And basically, what an index column is,

is it's a list of sequential values

that uniquely identify each individual row of a table.

Typically, these start from zero or one

and they're most frequently used

to create unique keys or IDs

that can be used to form relationships with other tables.

And I know we've been kind of

hinting at this idea for quite a bit,

and it's something that's gonna be covered

in much more depth in the intro

to data modeling section of this course.

So, index columns, they're super simple.

Moving on to conditional columns,

these are a bit more interesting,

and you can find 'em right there

above the Index Column option in the Add Column tab.

And basically, these allow you to define new fields

based on logical rules or IF/THEN conditions.

So the example we're going to be walking through

is to create a new conditional column

that we're naming Quantity Type.

And the way that we're gonna define Quantity Type

is a function of the Order Quantity column.

So, we can tell Power BI for any row in our table

where the Order Quantity equals one,

I wanna set my Quantity Type equal to single item.

If my Order Quantity is greater than one,

I wanna set my Quantity Type to multiple items.

And then you always have this catchall value if false.

So, that's basically, the value

that you want your column to take

in the case that all of the rows

or line items above are false.

So, Power BI is gonna go through and cycle through

each of these rows from top to bottom.

And if none of the conditions are met,

then that otherwise statement is the value that's returned.

All right, so let's open up our Adventure Works report

and we'll see what this looks like in the Query Editor.

All right, so at this point in the course

we're getting pretty close

to having all of our tables loaded.

And in this lecture,

we're gonna start working with our sales data.

So, first thing that I want to do here is

this query view here is getting

a little bit cluttered for me.

So, I actually wanna group together

a bunch of these queries that we have disabled load for.

All right, so I'm gonna select all of these queries

by right click, Move To Group,

and then create a new group here.

I'm just gonna call these Demo Queries.

All right, we'll click OK.

And basically, what this does

is it creates a folder structure

within your query pane here.

And again, it just really cleans this up.

So, now we can see exactly

the live queries here that we're working with.

All of these other ones are still accessible

and we could manage these

and mess with them at a later date.

But again, this kind of cleans things up.

All right, so we're gonna make sure

that we have our Other Queries folders selected here

and then we're gonna connect to our sales data.

And for this example, let's use the 2022 sales data.

Our data preview looks great.

It's pretty simple here.

We've got order and stock dates,

order number, some key columns,

and then Order and Order Quantity.

So, we'll add this into our Query Editor.

The table is added in our Other Queries folder here.

And again, let's follow those kind of first two steps.

We'll check out these column headers,

Orderdate, Stockdate, Ordernumber.

We've got date, date, a text field.

Our key columns are set up as whole numbers,

which look great.

And then same thing with

our Order and Order Quantity values.

The next thing that I wanna do

is we will update our name to Sales Data 2022.

And one thing to mention here is I'm specifically

not adding in that lookup identifier here,

because this is a different type of table.

This is something called a fact table, not a lookup table.

And we're gonna explain exactly what that means

in the data modeling section of this course.

But to give you a quick teaser,

essentially, we're looking at order quantities

and order line items here

with keys that allow us to map those orders

to things like our territories, to customers,

to products, to our calendar table.

And see here, if I scroll over to dates,

we've got some different date fields

that we can use to tie to that calendar lookup table.

So, no need to really dive any deeper than that quite yet.

Just put this in the back of your head,

because it'll come into play

in the next section of the course.

So, at this point, why don't we go ahead

and practice adding an index column.

So we're gonna come up to our Add Column menu.

Index Column, and you can see

from here we have some options.

We can start from zero or one.

Maybe you've got some sort of crazy business use case

where you need to start from a different Custom Number.

Again, you can specify that,

and with a specific increment as well.

Generally, I like to start from one, and perfect.

You see here, we've got a new applied step

for Add Index column.

So, let's say that we wanna see this

at the start of our dataset.

You can click a column header here, and you can drag it.

The other thing that you could do

to be a little bit more efficient

is you could right click, come down to Move,

and then move this column

to the beginning of the table.

So, now we've got our index column here,

we've reordered the column.

So we've got another applied step there as well.

All right, so far so good.

And maybe this column is something like an order ID

that we wanna use to kind of track each individual order.

In which case we could rename the column,

but to be honest, we don't need this ID or key column here.

In fact, it actually might just complicate

and confuse things down the road.

So, what I'm gonna do is

I'm gonna right click and delete this.

Now, what's interesting here is

I've basically, landed exactly where I started.

This is the table in its original kind of untouched form.

All the same columns that we've started with

and that we imported.

But one of the things that you'll notice here is that

I've actually got six different applied steps now

and what's happened is when we've removed the column

it didn't delete these other applied steps.

So, if I click back to the Add Index column applied step,

you can see that it's actually still here.

So, the implications of this are that

each time we refresh this dataset,

or we add more data to it, or like something changes,

Power BI is gonna run through

all of these different applied steps.

And that means adding the index column,

reordering the column, and then deleting it.

And that's just, honestly, a little bit silly and redundant,

and it isn't efficient at all.

So, what I'm gonna do here is I'm gonna

delete these last three applied steps

to remove that Add Index column work.

And now we're back to our original dataset

with our original three applied steps.

So again, this is just kind of

an important nuance to keep in mind,

and it's just a good demonstration

of how these applied steps are actually working.

Now, our last little demo here

I wanna create a conditional column

and it's right there above that index column step

and we get our dialogue box that opens here.

And so, first things first,

let's call this Quantity Type.

And then we have our different inputs here

for the conditional statement.

And like we saw in our slides,

we're gonna base this on our Order Quantity

and we wanna set this when it equals to one.

We want that output to equal single item.

Click Add Clause to add an additional case here.

So, if our Order Quantity equals one then single item,

otherwise or Else if, Order Quantity is greater than one,

we wanna return multiple items.

So, at this point, we're gonna be all set

for our conditional column,

but in cases where you have more complex logic,

you can continue to add new clauses here

to create additional Else if statements

for part of that conditional logic.

In this case, what we have here is a fully comprehensive

and mutually exclusive list of conditions.

We don't really have any other rows in our table here

with an Order Quantity of zero.

And I sure hope we don't have any rows

with a quantity of a negative value.

So that really leaves us with just two possible options.

You've either ordered one thing

or you've ordered more than one thing.

So, that said, as a best practice,

I always like to have an Else clause.

So, if either of these two conditions aren't met,

we're gonna say Else Other.

And again, this is just a great kind of catchall

in case something really strange or weird

or unanticipated happens within the dataset, we're covered.

We'll be able to flag that.

So, click OK.

And you can see here in the Quantity Type column

that we're getting our values

of multiple and single returned.

We can click the column header here

and we can see multiple item and single item.

One thing to notice here is that

you've got this kind of warning flag here

where it says, "List may be incomplete."

What's happening here is that when you see this flag

it's just Power BI telling you that,

hey, I only looked at a sample of rows within the column

in order to produce this list above.

And this is really just Power BI's way

of saving some memory and processing power.

But if you want Power BI to check

the entire contents of the column, click Load More.

And that kind of forces Power BI

to scan all of the values within the column

instead of just a preview.

All right, so this confirms

that we have just two Quantity Types, multiple and single.

So from here, we'll click OK.

And that's just about all we need to do here.

So, we'll head back to our Home tab.

We'll click Close & Apply,

and then we'll make sure to save this workbook.

So, we've got our sales data 2022

loaded into our model, and we'll keep moving on.