Welcome to Excel Magic Trick number 1434.
Hey, if you want to download this Excel work
profile either the start file or finish file
so you could follow along, click on the link below the video.
In this video, we got to see the sample
DAX function to take a sample from a data set.
Now I'm not too sure how useful this function will
be to most people, but it is interesting
that they have it in DAX.
I certainly wish I had this type of function in Excel before.
Now in order to use the sample function and the DAX table
function to extract a sample from this,
we're going to have to put this into the data model.
Now I'm going to show you two examples, one, two.
The first one I'll show you how to get this table into the data
model and extract a sample if you
have any version of Excel 2016.
Then I'll show you the normal way if you have PowerPivot.
Now this sample function works over in Power BI desktop also.
Now I'm going to click on the top cell control down.
So I have over 700 rows of names here,
and I want to extract a sample.
Now I actually have a sample size
for each one of these sample extracts
so our sample size will be 50.
And it's in an Excel table, so later we'll change it
and our extract will update to that new sample size.
I've already converted this to an Excel table
by going up to Insert and clicking on table
or using control T. Now I'm going
to get this into the data model without PowerPivot.
I have Excel 2016, so all I have to do
is click the Pivot table button.
And then right here, add this to the data model.
This is a great cheat when you don't have the right version,
but you want to take advantage of some of the features
that the data model offers to you.
So I'm going to click OK.
It's immediately going to dump the sheet there.
And I'm going to leave it there just for the time being.
Come back over here, and now we're
going to learn a trick we learned back in our last video.
We need to call from the data model, the table
and put it right here, and then use our sample function.
Now, last video, we saw how easy this is to do.
Even though we don't have PowerPivot,
we dump that in the data model.
It's sitting in the data model behind the scenes.
We go up to Data, Existing Connections.
And then over here on the Tables tab, we see a bunch of tables
here.
The one with the dark line is the only one
that's in the data model.
But I'm going to select that.
When I click Open or double-click this,
it opens up Import Data.
I click OK, and this will actually
dump the same exact table there.
But now we can right-click anywhere in this data model
DAX table and point to Table.
And look at that, Edit DAX.
Just like we saw in the last video,
if you have a normal Excel table and you right-click Table,
that option isn't there.
It's only because we used external connections
to get this from the data model that when
we point to Table, Edit DAX--
this is our dialog box for building an expression using
the DAX formula language to alter the table we dumped
into our Excel spreadsheet.
I'm going to point to Command Type.
We need DAX.
Now, in order to get our DAX formula to work and return
as a table, we have to use the command,
EVALUATE, and then Enter.
Now we enter the SAMPLE function, open parentheses.
Now the very first argument in sample is the sample size.
Most of the time, you just put a 50 here or a 20 or 30
or whatever it is.
Later, we'll come back and change this so it's dynamic
and it's connected to our Excel table with our sample size.
But I'm just going to put first argument that's
called n, the sample size, comma,
the table, the name of our table both in our Excel spreadsheet
and over in the data model is fNames.
The third argument, comma, we can either
put an expression or a column.
That's the column we want to extract from.
So for us, it's the table name, fName, and then
in square brackets, the field.
Now, we only have one column in our table.
Our next example will have multiple columns.
But that tells the SAMPLE function to take this column,
and the algorithm will sort it, put it into groups,
take the first and the last one, and try and match
the distribution and extract values from that column.
And we'll look at an example how to see if it really
is getting the same proportion of values from each group
value.
And then our fourth argument is whether we want
it ascending or descending.
Now, the default is descending.
But I'm going to use ascending here.
Close parentheses.
Now we're going to go to the fNames table in the data model,
try to extract the sample size 50,
and we'll use the algorithm to sort first and last
and try and get all the items in between and deliver ascending.
So when I click OK--
[BEEP]
Oh, look at that.
That's the problem.
I am a bad typer.
I'm going to click OK, right-click,
down to a Table, Edit DAX.
I got fNames correct there, but not here.
So I'm going to type in "s," click OK.
Oh, I did it twice.
Click OK, right-click.
Thank you for sticking with me through my often bad spelling.
Let's see if I can get this right.
I think it's fNames, Names.
I got my fingers crossed, click OK.
And there is our sample.
Look at that.
Now, here's what I've done.
I have a unique list here.
I already went through and created a formula
to calculate the proportion for each one of these items.
This is from the original table.
Now let's come over here equals COUNTIFS.
Criteria range-- this is an Excel table.
So I point to the top.
And when I see my downward pointing black arrow, I click.
It puts the whole column name in.
Comma, the criteria is I'm going to try and count
all the Bellens in this column.
Close parentheses, and then I want to divide it
by the total count.
I'm going to use COUNTA.
That'll count not-empty cells.
Same thing, I'm going to count this column.
Close parentheses, Control-Enter.
And there it goes.
So you can see the proportion seems to be pretty close,
0.096%, 10%, 13.2%, 12%, 11%, 10%, 31.9%, 32%,
11's and we got some 12's.
So that's a pretty good sample there.
Now I want to connect this to this 50
over here so then I can just change this to 20 or 10 or 100
or whatever it is.
Now, before I can right-click Edit,
I need to get this one into the data model also.
So I'm going to do the same trick, Insert, PivotTable.
Add this to data model--
I'm going to say existing sheet just to consolidate all of them
over here.
So this is just a cheat, right, to get it into the data model.
Now I have two PivotTables there.
And now I can come over, right-click,
down to Table, Edit DAX.
And instead of using a typed-in value,
I can access a column by using the VALUES function.
Now, VALUES will extract a unique list.
Since we only have one item in that column,
it'll deliver just that one item.
Now we need to put the table name, fSampleSize, no spaces,
then square brackets, and then "Sample Size"
with a space in between, close square bracket,
close parentheses on VALUES.
Now, I probably spelled it wrong, but let's see.
Click OK.
There it is.
By the way, we should have just looked over here.
There is the field name.
And go up to Design.
There's the table name.
Now let's try this.
I'm going to come over here and type 20,
smaller the sample size.
These proportions may be off a little bit, of course.
Now I need to either right-click, Refresh,
or I could use the keyboard to refresh everything,
Control-Alt-F5.
And there we go.
There is our sample size of 20.
We could see the proportions are not as good
when we go from 700-plus to 20.
If I say-- let's 30.
I'll change that to 30, Control-Alt-F5.
There is our table to 30.
And then they're looking much better.
Now, let's go over into our second example.
Notice here, we did it on a column that was text
and had many, many duplicates.
Now over here, here's our table.
We have Date, Transaction Number, Product, and Sales.
If I can Control-Down arrow--
so there's, like, 12,000 records here.
I want to do a sample.
And we're going to base it on the same sample size here.
And we're going to tell the third argument in SAMPLE
to look at this column.
Now remember, the algorithm is-- it will sort it.
And so the biggest and the smallest
will be the first and last values.
So actually, the biggest and smallest value
will always be extracted.
And then it will try to extract as best it can between the max
and min value.
All right, now this time, I'm just
going to enter this in the data model, have it in PowerPivot.
I go to the PowerPivot ribbon tab,
add this to the data model.
If we go over to Diagram View, we
can see we have all three tables-- no relationship
between any of these tables.
Alt-Tab.
Now right here, I'm going to go up to Data, Existing
Connections, over to Tables.
This one is fSales.
Click Open or double-click.
We click OK, right-click, Table, Edit DAX.
Select DAX.
Highlight, type EVALUATE, Enter, SAMPLE, open parentheses,
VALUES, open parentheses, fSampleSize, square bracket,
"Sample Size" with a space, close square bracket,
close parentheses.
So that just gives us whatever number
for our sample size we type into the Excel spreadsheet.
Comma, the name of the table as fSales.
The third argument is the column.
So, fSales, square bracket.
And this column is called Sales.
Close square bracket, comma, ascending, close parentheses.
Now when I click OK, we should have--
hey, look, there's the in.
There's the max.
You know, you could test that.
I already did over here.
And then there are the values extracted in between.
Notice there's probably not going
to be too many duplicates in this column.
If there were lots of duplicates and it
tried to do what it did over in our first example--
there you go.
We have a sample size of 30.
If I come over here, and we're going to change this to 100,
Enter, Control-Alt-F5.
Just like that, we get 100 here and 100 over here.
Control-Home.
All right, that was a little bit about how to just right-click,
Edit DAX once we've used our existing connections to get
a table from the data model.
And then we saw how to use the EVALUATE command and SAMPLE
and VALUES to extract a sample.
All right, we'll see you next video.