Original subtitles

All right moving right along.

Next up we have our number specific tools.

So back to that transform tab in our query editor.

Assuming we have a numerical column selected you'll see a group of numbers specific tools active that

look like this.

And the first option within those number tools are the statistics functions easier aggregators like

some men Max median average standard deviation and count 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.

And I'll show you exactly how that works.

As soon as we jump into power be-I.

But the answer is that these all return one value.

So the entire table gets replaced with one single value.

And as a result you really don't use these statistics functions within the query editor as a means of

transforming or preparing a table for loading but rather as a way to explore it and maybe get some information

about it.

So the count of products the maximum costs the median age.

Questions like that.

Now the next set of tools within this group the standard scientific and trigonometry tools these actually

allow you to apply row level operations so you can apply some standard operations like addition multiplication

division or some more advanced calculations like log's sine tangent et cetera.

And these are actually applied to each value in a column.

So unlike the statistics options these tools are applied to each individual row in the table.

So we're going to be using a ton of our coastline and arctangent throughout the course.

Just kidding we're not going to actually touch the trigonometry stuff.

If you're looking to go deep into the trigonometry world unfortunately I'm not your guy but that's neither

here nor there.

In addition to those standard scientific and trig functions you also have your info functions and these

basically just allow you to define binary flags either true false or 1 0 to mark each row in a column

whether it's even odd positive or negative.

So let's open up power be-I and practice some of these number tools.

All right so here we are in the Adventure Works report this time instead of grabbing new data actually

want to edit the product data that we've already connected to.

So in that case I'm going to go to my edit queries button that just jumps me straight to the query editor

where else the two connections that I had in place product look up and customer look up.

You'll notice that all of the applied steps are still there preserved.

And in this case it jumped into product look up and play with some of these numbers specific functions.

So if I jumped to the add column to your my number specific tools you'll notice that these statistics

function is greyed out.

And I remember because the statistics functions are aggregators they're going to return a single value

so it doesn't make sense to add a new column that's derived by one of these stats functions.

But when we move to the transform tab now you'll see the same set number tools and statistics option

will be active here you'll see all those aggregation functions.

So let's say we want to know the number of unique products captured in this lookup table I could select

the product name column for instance go into transform statistics and you can see that all of the options

that require a numerical input like some men Max are grayed out since I'm selecting a text column.

The only thing I can do here is count the values or count the distinct values.

And in fact all of my other numbers specific tools are greyed out as well.

So in this case I want a distinct count of products I can select that last option and my whole table

disappears and is replaced with that one aggregated value to ninety three.

So what that tells me is that there are 293 unique product names in my product lookup table.

And like I mentioned you know as you can see it's pretty obvious this isn't a means of transforming

or preparing data for loading it into power be-I.

It's more of an exploratory analysis.

So almost every time you use these stats functions you're going to go ahead and remove that last step

to return back to where you started.

So let's go through a couple other quick examples.

Scroll all the way to the right to my product price column.

And now in this case I'm curious what's the average product price in this adventure works dataset.

So back to my transform and you the only place I can access my statistics tools and I can drill an and

calculate the average retail price which is seven hundred and fourteen dollars and 44 cents.

Now that feels pretty high.

But if we close back out you'll see that we're dealing with cycling products which includes some pretty

high ticket items like these road frames.

And if we scroll over and see that some of these are 12 $1300 some are even higher.

In fact we can go ahead and use another similar statistics function to answer that question precisely.

Transform statistics maximum this time and we see that the highest priced item the maximum product price

in the lookup table is three thousand five hundred seventy eight dollars and twenty seven cents which

must be an extremely expensive bicycle.

There we go we can X out back to where we started and let's test out just a couple of more of these

tools scrolled back to our cost and price columns.

It's time I want to round these off to just two decimal places.

So let's do it both at once and click product cost.

Hold shift quick price to grab both and then I want to transform these columns.

I want to add anything new.

And when you use the rounding tool this time and what I can do is choose the third option round and

say let's write it to two decimal places.

Press OK.

Both those columns are transformed.

Now both reflect just two decimal places and we're good to go there.

Now one last thing I want to show you want to do a demonstration of the standard operations here.

And so what I'm going to do is not transform this column but I'm going to select the product price and

add a new column using a standard operator.

So what we're going to do in this example is we're going to add a multiplication step here and what

we'll do is multiply each value in our product price column by point nine.

In other words I don't return 90 percent of the product price for each row.

And this might make sense if Adventure Works as a company you know often ran 10 percent of life deals.

You know this might be something like a discount product price.

So I can press OK.

It's going to create a new column by default that gives the column header and name called multiplication.

So it's double click and let's call that discount price and press enter.

And just like we modified the product price and product cost columns it's going to transform rounding

we're going to round this to two decimal places and press OK and that looks good.

So I think that's just about everything that we need to do as far as modifications to the product lookup

table are concerned.

So let's head home.

And now when we pressed close and apply it basically just updates the query loads the refresh table

into our model.

And there you have it.

So nothing else has changed.

We still see our tables here this product look up now reflects those new fields that we added including

discount price at the bottom.

So there you have it.

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