Original subtitles

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 when an index column is it's a list of sequential values that uniquely identify each individual

row of a table.

Typically these start from 0 or 1 and they're most often 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 quite a bit.

That's going to be covered in depth in the data modeling 101 section of the course.

So in next call is super simple.

Moving on to conditional columns these are a bit more interesting.

You can find them right there above the index column option in the ad column tab.

And basically these allow you to define new fields based on logical rules or if then conditions so example

that we're going to be walking through is creating a new conditional column that we're naming quantity

type and the way that we want to define quantity type is a function of the order quantity column.

So we can tell power be-I for any row in our table where the order quantity equals 1.

I want to set my quantity type equal to single item if my order quantity is greater than 1.

I want to set my quantity type to multiple items and then you always have this kind of catch all value

is false otherwise statement at the end.

That's basically the value that you want your column to take in the case that all of the rows are line

items above are false.

So power is going to cycle through each row from top to bottom and if none of those conditions are met

that otherwise statement is the value that's returned.

So let's open up our Adventure Works report and see what this looks like in power be-I.

All right.

So I'm looking at the relationships you see the three tables three connections that we already have

in place.

We're going to go ahead and Attie forth now.

So follow along we're going to get more data from the cxxviii and this time I want my adventure work

sales 20:17 file and preview looks good.

Let's go ahead and edit it to jump into the query editor and right off the bat.

Let's simplify this table name a little bit.

I'm just going to change this to A.W. sales 2017.

Now you'll notice that I'm not adding the word look up to this table name just like I did with product

customer and calendar and that's intentional because we're not dealing with a lookup table here we're

dealing with something called a data or a fact table.

And we're going to explain what that means in the data modeling section of course but to give you a

quick teaser.

Essentially we're looking at order quantities and order line items here with keys that will allow us

to map those orders territories to customers to products.

And if I scroll all the way over to dates.

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

Just put that in your back pocket because that will come into play in the next section of course.

So why don't we go ahead and practice adding an index column.

So we'll add column index and if I drop down that menu you can select to start from 0 or 1 or if you're

feeling crazy.

Can Start with any customer number you choose.

In this case my keys or my ID columns typically start from 1 so we can choose that option creates a

new column here at the end.

Kind of interesting it formatted it as a decimal number which doesn't really matter but let's go ahead

and make it a whole number just like the other columns here and a little shortcut.

Instead of dragging it to the beginning of the table you can right click and use this move option and

move it to the beginning just in one click.

So there we go.

Maybe this is something like an order ID that we want to 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 may just complicate and confuse things down the road.

So I'm actually going to write click and remove this column.

Now what's interesting here is that I've landed basically exactly where it started.

This is the table in its current untouched form.

But you'll notice that there are now seven steps in my applied steps here because even though I removed

my index column that step was recorded and that step still exists here in the history of my applied

steps.

So what this means is that whenever this sales 20:17 connection is refreshed power be-I is going to

run through all of these applied steps.

It's going to add an index column.

Change the type.

Move it to the beginning of the table and then delete it which is really inefficient and downright silly

when you think about it.

So rather than just calling that day because I'm back at the correct table format the proper approach

here would be to actually remove all of those steps back to before I even added the next column in the

first place.

And now nothing's changed with my table preview.

This is it's original raw form but now I only have three applied steps instead of seven.

So kind of an important nuance to keep in mind there and a good demonstration of how these applied steps

are actually working.

Now last a little demo.

Let's go ahead and add a conditional column right here above index.

This opens up my dialog box and we're going to name this column quantity type.

And just like we talked about we're going to say OK for every row in this table if my order quantity

equals the value of 1 then the output or the value that my quantity type column should take is a single

item just like that.

Now I can press this button to add new rule and it gives me an LS If line.

So if this first row condition is not met then what's the next thing you check for.

Well you check that order quantity is greater than 1 in which case my output or my quantity type column

should equal multiple items and you could keep adding conditions here if you had more complex scenarios

in this case what we have here is a fully comprehensive and mutually exclusive list of conditions.

You don't have any rows with an order quantity of zero.

And I sure hope we don't have any rows with a quantity of negative values.

So that just leaves us with two possible options.

You've ordered one thing or you ordered more than one thing.

But that said as a best practice kind of rule of thumb I always put an otherwise value in here.

So let's just say you know if something's crazy we've got some weird data that we weren't anticipating

will just set quantity type to other and press OK.

And there you go you've got our new column quantity type and see that it's taking values of either multiple

or single item based on this order quantity column.

And if we look at our drop down one thing call out here it does show that the only two items are the

only two options in this column are multiple items and single item.

You'll often see this little flag that says list may be incomplete.

That's just telling you that power be I looked at a small sample of rows within that column in order

to produce the list above.

And that's just to save memory and processing power.

But if you want power be out to check the entire column to make sure that this list is comprehensive.

Just press the load more button and that flag goes away.

And this confirms that this quantity type column that we just created contains two values multiple items

and single item.

So press OK.

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

So let's go ahead and enter our home tab and press clothes and apply.

All right.

Let's save our workbook and keep moving on.