Original subtitles

Instructor: Up next, we have our number specific tools.

So let's get back to that transform tab in the query editor.

And assuming we have a numerical column selected,

you'll see a group of number specific tools that are active

that'll look something like this.

And the first option within those number tools

are the statistics functions.

And these are aggregators

like some, min, max, median, average, standard deviation,

count, and count distinct.

Now, you may be wondering,

if you have a whole column selected

and you apply an aggregation function

that's designed to return a single value,

how does that work?

What happens?

And I'll show you exactly how this works

as soon as we jump into Power BI, but the answer is that

these all return one value, meaning a single value.

So the entire table gets replaced

with that one single value.

And as a result,

you don't really use these statistics functions

within the query editor as a means of transforming

or preparing a table, but rather as a way to explore it

and maybe get some information about it.

So things like the count of products,

the maximum costs, the medium age, questions like that.

Now at this point, you also might be thinking,

hey, can't we use the column profile tools for this?

And the answer is, absolutely.

The column statistics

and the column profile view are the exact same

as what will derive using these statistics functions.

And this lecture is really all about exploring the tools

and options that are available to you

within the query editor.

Plus, you'll never know when these statistics tools

may come in handy.

Now the next set of tools within this group,

the standard scientific and trigonometry tools,

these actually allow you to apply road level operations.

So you can apply the same standard operation like

addition, multiplication, division

or some more advanced calculations like log,

sign, tangent, et cetera.

And these are actually applied

to each value within the column.

So unlike the statistics options, these tools are applied

to each individual row within the table.

In addition to those standard scientific and trig functions,

you also have info functions.

And these basically allow you to identify binary flags,

either true or false or one or zero,

to mark each row in a column,

whether it's odd, even, positive or negative.

So with that, let's open up Power BI and we'll practice

some of these number tools.

All right, so similar to the tech specific tools lecture,

we're not gonna connect to new data for this lecture,

but instead use the product lookup table.

So we're gonna head back into the query editor,

through that transform button,

and we're gonna practice the numerical tools here.

Once we're in the query editor, let's make sure we've got

the product lookup table selected.

And if we jump up to the add column menu,

here are my number specific tools.

And you'll notice that the statistics options

are grayed out, right?

Because they're aggregators,

they're gonna return a single value.

So it doesn't really make sense to add a new column

that's derived by one of these stats functions.

But when we move to the transform tab,

you can see that we do have these options

available to us, right?

And these statistics options are active here.

And you'll see all of these different aggregation functions

like sum, min, max, median, average, and so on.

Let's say for example, we wanted to understand

the number of unique products captured in this lookup table.

So if we click on product name

and then head up to our statistics options,

wait, we actually see here that a lot of these options

are grayed out, right?

And that kind of makes sense

because we're asking about a text-based column, right?

And not a numeric column.

So you can't aggregate or sum texts

or find the minimum or maximum values

of a text value here, right?

All we can do is count or count distinct.

And in fact, all of my other number column tools

are actually grayed out here as well.

So let's head back here to statistics

and we're gonna count the distinct values here

of product name within the table.

And we have 293.

So what this tells me is that there are 293

unique product names in my product lookup table.

And like I had mentioned, as you can see it's pretty obvious

that this isn't a means of transforming or preparing data

to then load it into Power BI,

it's really better suited for exploratory analysis.

So almost every time you use these stats functions,

what you're gonna want to do

is delete this last applied step that's created

to return that full table.

All right, so let's look at another couple quick examples.

I'm gonna scroll all the way over to the right

and check out our product price column.

And now in this case,

I'm curious like, what's our average product price

in the Adventure Works data set?

All right, so I can click on product price here,

we'll go back to transform, and I can come back

to my statistics tools and click on average, right?

And we see here we get $714 and 43 cents

and then a big remainder here.

This is kind of alarming, right?

It feels pretty high.

So if we close back out,

you can actually see here for our product names, right?

We've got these different road frames

you know, medium, large, all this stuff,

so remember this is a bike company,

and so we probably sell a lot of higher end bike equipment.

And you can see here in the product price column,

you've got some values here around 12, $13,000.

Keep scrolling, we go up a little bit higher

to, you know, 3,500.

So now I'm curious,

can we use another statistics function

to answer this question a little bit more precisely, right?

What is our highest priced item within the dataset here?

So what we can do is, again, we'll go back

to this product price column, transform, statistics,

and let's find the maximum value, right?

And it's exactly what we just saw there.

So a little over $3,500 here,

pretty expensive item here within the dataset,

and then we can click back on our X to clear this out.

All right, so it's pretty easy to use these tools

to explore some of the columns within your dataset.

So let's test out a couple more of these tools here.

And we're gonna scroll back over

to the product price and cost columns here.

And because we've already updated these data types

to a fixed decimal number, let's do a little test here.

Let's say we had left this as a decimal number, right?

We hadn't updated this change to currency step here.

All right, one of the things that we can do here

is we can use

the rounding tools from the transform menu

and we can either round up, down,

or we can specify the number of digits to round to

using that third option.

So let's say we wanna round this

to two decimal places, right?

And we generate this round off function.

And you can see here that we've rounded these off

to two significant decimal places,

but maybe that doesn't make sense for our use case,

maybe we want to keep the data type set to currency.

So let's delete that last applied step.

We'll delete the change type step there as well.

And now we're back here to our currency data type.

One of the last things that I want to show you here

is I want to do a demonstration of the standard operations.

So what I would like to do is not transform the column,

but we're gonna keep product price selected

and we want to add a new column

that's based off of product price, right?

And we're gonna do that

using one of these standard operators.

And what we're gonna do in this example

is we're gonna multiply our product price

by 0.09, all right?

I basically want to return 90% of the product price column

for each row within the table, right?

So we're gonna multiply this by 0.09.

And the way that you can think about this is,

let's say Adventure Works as a company ran a 10% off deal.

You know, this might be something like that

discounted product price column.

So we'll lock that in.

All right, so I can see here

that we have created the new column,

a new applied step here for inserted multiplication.

And we've got this, you know, pretty poor column name here.

So let's update this to discount price.

And the other cool thing to notice here is that because

this column is based off of the product price column,

it inherits the data type,

and when we multiply the product price by 0.9

our discounted price also is set as a currency

or a fixed decimal number data type.

All right, so I think that's just about everything

that we need to do as far as modifications

to the product lookup table.

So let's head back to our home tab

and we're gonna click close and apply.

And again, like we've seen,

this is basically just updating these queries

and it'll apply these transformations,

as you can see by what's happening on the screen.

And it's great.

It basically loads those tables, refreshes our data model.

So if I zoom in here

and I look at our product lookup table,

you can see at the top here,

we have our new discount price column

that we had created, right?

So that has been added into our data model.

All right, so there you have it.

That's your crash course in query editing number tools.