Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:00,610 --> 00:00:02,070
All right moving right along.
2
00:00:02,090 --> 00:00:05,180
Next up we have our number specific tools.
3
00:00:05,180 --> 00:00:08,560
So back to that transform tab in our query editor.
4
00:00:08,720 --> 00:00:15,380
Assuming we have a numerical column selected you'll see a group of numbers specific tools active that
5
00:00:15,380 --> 00:00:16,710
look like this.
6
00:00:16,760 --> 00:00:23,450
And the first option within those number tools are the statistics functions easier aggregators like
7
00:00:23,450 --> 00:00:30,710
some men Max median average standard deviation and count count distinct.
8
00:00:30,710 --> 00:00:37,490
Now you may be wondering if you have a whole column selected and you apply an aggregation function that's
9
00:00:37,490 --> 00:00:40,060
designed to return a single value.
10
00:00:40,070 --> 00:00:41,420
How does that work.
11
00:00:41,720 --> 00:00:43,960
And I'll show you exactly how that works.
12
00:00:44,080 --> 00:00:45,970
As soon as we jump into power be-I.
13
00:00:46,160 --> 00:00:49,680
But the answer is that these all return one value.
14
00:00:49,850 --> 00:00:53,830
So the entire table gets replaced with one single value.
15
00:00:53,990 --> 00:01:00,680
And as a result you really don't use these statistics functions within the query editor as a means of
16
00:01:00,740 --> 00:01:07,940
transforming or preparing a table for loading but rather as a way to explore it and maybe get some information
17
00:01:07,940 --> 00:01:08,480
about it.
18
00:01:08,480 --> 00:01:13,580
So the count of products the maximum costs the median age.
19
00:01:13,580 --> 00:01:15,100
Questions like that.
20
00:01:15,150 --> 00:01:22,040
Now the next set of tools within this group the standard scientific and trigonometry tools these actually
21
00:01:22,100 --> 00:01:29,450
allow you to apply row level operations so you can apply some standard operations like addition multiplication
22
00:01:29,450 --> 00:01:35,440
division or some more advanced calculations like log's sine tangent et cetera.
23
00:01:35,660 --> 00:01:39,420
And these are actually applied to each value in a column.
24
00:01:39,470 --> 00:01:45,950
So unlike the statistics options these tools are applied to each individual row in the table.
25
00:01:46,130 --> 00:01:51,640
So we're going to be using a ton of our coastline and arctangent throughout the course.
26
00:01:51,740 --> 00:01:54,850
Just kidding we're not going to actually touch the trigonometry stuff.
27
00:01:55,520 --> 00:02:02,270
If you're looking to go deep into the trigonometry world unfortunately I'm not your guy but that's neither
28
00:02:02,270 --> 00:02:03,370
here nor there.
29
00:02:03,500 --> 00:02:09,950
In addition to those standard scientific and trig functions you also have your info functions and these
30
00:02:09,950 --> 00:02:17,720
basically just allow you to define binary flags either true false or 1 0 to mark each row in a column
31
00:02:18,290 --> 00:02:21,640
whether it's even odd positive or negative.
32
00:02:21,650 --> 00:02:26,100
So let's open up power be-I and practice some of these number tools.
33
00:02:26,180 --> 00:02:32,510
All right so here we are in the Adventure Works report this time instead of grabbing new data actually
34
00:02:32,510 --> 00:02:36,110
want to edit the product data that we've already connected to.
35
00:02:36,110 --> 00:02:41,750
So in that case I'm going to go to my edit queries button that just jumps me straight to the query editor
36
00:02:42,210 --> 00:02:47,410
where else the two connections that I had in place product look up and customer look up.
37
00:02:47,570 --> 00:02:51,430
You'll notice that all of the applied steps are still there preserved.
38
00:02:51,650 --> 00:02:57,700
And in this case it jumped into product look up and play with some of these numbers specific functions.
39
00:02:57,710 --> 00:03:04,730
So if I jumped to the add column to your my number specific tools you'll notice that these statistics
40
00:03:04,730 --> 00:03:06,880
function is greyed out.
41
00:03:07,050 --> 00:03:12,400
And I remember because the statistics functions are aggregators they're going to return a single value
42
00:03:12,410 --> 00:03:18,300
so it doesn't make sense to add a new column that's derived by one of these stats functions.
43
00:03:18,380 --> 00:03:25,820
But when we move to the transform tab now you'll see the same set number tools and statistics option
44
00:03:26,160 --> 00:03:30,080
will be active here you'll see all those aggregation functions.
45
00:03:30,080 --> 00:03:37,880
So let's say we want to know the number of unique products captured in this lookup table I could select
46
00:03:37,880 --> 00:03:46,250
the product name column for instance go into transform statistics and you can see that all of the options
47
00:03:46,370 --> 00:03:54,240
that require a numerical input like some men Max are grayed out since I'm selecting a text column.
48
00:03:54,260 --> 00:03:58,910
The only thing I can do here is count the values or count the distinct values.
49
00:03:59,000 --> 00:04:03,740
And in fact all of my other numbers specific tools are greyed out as well.
50
00:04:03,740 --> 00:04:11,180
So in this case I want a distinct count of products I can select that last option and my whole table
51
00:04:11,180 --> 00:04:17,170
disappears and is replaced with that one aggregated value to ninety three.
52
00:04:17,570 --> 00:04:24,640
So what that tells me is that there are 293 unique product names in my product lookup table.
53
00:04:24,860 --> 00:04:30,170
And like I mentioned you know as you can see it's pretty obvious this isn't a means of transforming
54
00:04:30,170 --> 00:04:33,470
or preparing data for loading it into power be-I.
55
00:04:33,680 --> 00:04:36,460
It's more of an exploratory analysis.
56
00:04:36,470 --> 00:04:42,020
So almost every time you use these stats functions you're going to go ahead and remove that last step
57
00:04:42,470 --> 00:04:44,750
to return back to where you started.
58
00:04:44,750 --> 00:04:47,500
So let's go through a couple other quick examples.
59
00:04:47,530 --> 00:04:52,330
Scroll all the way to the right to my product price column.
60
00:04:52,590 --> 00:04:59,460
And now in this case I'm curious what's the average product price in this adventure works dataset.
61
00:04:59,460 --> 00:05:07,760
So back to my transform and you the only place I can access my statistics tools and I can drill an and
62
00:05:07,760 --> 00:05:15,170
calculate the average retail price which is seven hundred and fourteen dollars and 44 cents.
63
00:05:15,170 --> 00:05:16,840
Now that feels pretty high.
64
00:05:16,970 --> 00:05:22,790
But if we close back out you'll see that we're dealing with cycling products which includes some pretty
65
00:05:22,790 --> 00:05:26,050
high ticket items like these road frames.
66
00:05:26,330 --> 00:05:33,250
And if we scroll over and see that some of these are 12 $1300 some are even higher.
67
00:05:33,250 --> 00:05:39,850
In fact we can go ahead and use another similar statistics function to answer that question precisely.
68
00:05:39,840 --> 00:05:48,920
Transform statistics maximum this time and we see that the highest priced item the maximum product price
69
00:05:49,010 --> 00:05:56,060
in the lookup table is three thousand five hundred seventy eight dollars and twenty seven cents which
70
00:05:56,060 --> 00:05:59,470
must be an extremely expensive bicycle.
71
00:05:59,480 --> 00:06:04,280
There we go we can X out back to where we started and let's test out just a couple of more of these
72
00:06:04,280 --> 00:06:08,830
tools scrolled back to our cost and price columns.
73
00:06:08,930 --> 00:06:12,860
It's time I want to round these off to just two decimal places.
74
00:06:12,860 --> 00:06:16,670
So let's do it both at once and click product cost.
75
00:06:16,670 --> 00:06:22,120
Hold shift quick price to grab both and then I want to transform these columns.
76
00:06:22,190 --> 00:06:23,710
I want to add anything new.
77
00:06:24,080 --> 00:06:30,850
And when you use the rounding tool this time and what I can do is choose the third option round and
78
00:06:30,850 --> 00:06:34,030
say let's write it to two decimal places.
79
00:06:34,130 --> 00:06:35,100
Press OK.
80
00:06:35,320 --> 00:06:37,480
Both those columns are transformed.
81
00:06:37,480 --> 00:06:42,160
Now both reflect just two decimal places and we're good to go there.
82
00:06:42,310 --> 00:06:48,380
Now one last thing I want to show you want to do a demonstration of the standard operations here.
83
00:06:48,490 --> 00:06:54,730
And so what I'm going to do is not transform this column but I'm going to select the product price and
84
00:06:54,940 --> 00:06:58,950
add a new column using a standard operator.
85
00:06:58,960 --> 00:07:05,260
So what we're going to do in this example is we're going to add a multiplication step here and what
86
00:07:05,260 --> 00:07:11,800
we'll do is multiply each value in our product price column by point nine.
87
00:07:11,800 --> 00:07:17,290
In other words I don't return 90 percent of the product price for each row.
88
00:07:17,480 --> 00:07:24,270
And this might make sense if Adventure Works as a company you know often ran 10 percent of life deals.
89
00:07:24,430 --> 00:07:28,120
You know this might be something like a discount product price.
90
00:07:28,120 --> 00:07:29,720
So I can press OK.
91
00:07:30,010 --> 00:07:36,610
It's going to create a new column by default that gives the column header and name called multiplication.
92
00:07:36,610 --> 00:07:44,760
So it's double click and let's call that discount price and press enter.
93
00:07:44,760 --> 00:07:52,320
And just like we modified the product price and product cost columns it's going to transform rounding
94
00:07:52,800 --> 00:08:00,480
we're going to round this to two decimal places and press OK and that looks good.
95
00:08:00,770 --> 00:08:06,840
So I think that's just about everything that we need to do as far as modifications to the product lookup
96
00:08:06,860 --> 00:08:08,350
table are concerned.
97
00:08:08,360 --> 00:08:09,740
So let's head home.
98
00:08:10,050 --> 00:08:16,610
And now when we pressed close and apply it basically just updates the query loads the refresh table
99
00:08:16,790 --> 00:08:19,330
into our model.
100
00:08:19,560 --> 00:08:20,820
And there you have it.
101
00:08:20,820 --> 00:08:22,840
So nothing else has changed.
102
00:08:22,860 --> 00:08:29,130
We still see our tables here this product look up now reflects those new fields that we added including
103
00:08:29,130 --> 00:08:31,630
discount price at the bottom.
104
00:08:31,980 --> 00:08:32,960
So there you have it.
105
00:08:33,000 --> 00:08:36,630
That's your crash course in query editing number tools.
11398
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.