Welcome to Excel Magic Trick number 1,429.
Hey, if you want to download this Excel workbook,
either the Start file or Finish file
so you can follow along, click on the link below the video.
Hey, back in Excel Magic Trick 1,420,
we saw how to use Power Query to rank.
Back in Excel Magic Trick 1,419, we
saw how to use the DAX function rank x to rank.
And below 1,420, awesome online teammate, Bemint at YouTube,
posted this amazing video right here.
Now back over in 1,420, we ranked,
but we used sorting grouping indexes and custom columns.
Bemint said hey, why not just use yes, the index
and grouping, but use the Min function for shorter
code and fewer steps?
So we want to see how to do that.
Now we're going to dump our report right here.
And we're going to use the same data set
that we did back in 1,420.
Here are the credentials if you need to log in.
The data is already loaded into this workbook,
but you may need to log in when you refresh.
Now we're going to go up to Data.
And I've already started the query.
So in the Queries and Connections,
I'm going to click Queries and Connection button.
If you have an earlier version of Power Query,
you'll have to click the Show Queries button.
And of course, I'm using Excel 2016,
so the Data Ribbon tab getting transform, Queries
and Connections, that's all Power Query.
Earlier versions like Excel 2010 and '13,
you have to download Power Query.
All right.
So I'm going to click this button.
Now Rank Units, Index Group, and Min,
that has the table we can use to start our ranking.
So I'm going to double click to open up the Power Query editor.
Here's are two columns, Product and Quantity.
Now, before we get to ranking, we actually need to group
by the product column.
So we can get a unique list of products
and add all the quantities.
So in Home, we can go to Group By, Transform, we
can go to Group By, or we can Right click Group By.
We want to group by product.
The column title will be Total Units, Tab.
I'm going to Down arrow and then Up arrow.
We want to add using the sum function,
Tab, Tab, Down arrow, we want to add
using the Quantity column, Tab.
Click OK.
There's our grouping.
Now our first step is to sort.
So I'm going to come up to Total Units dropdown, Sort
Descending.
The biggest ones are on top.
Now we add an index, one, two, three, four, five, six.
So we go to Add Column.
Index column, and I'm going to say from one.
There is our rank.
Now back in 1,420, I noticed that there
was a group of three records with Tide Total units.
The index gives us seven, eight, nine.
And I thought to add an index for this group zero, one, two,
and then subtract the two columns, seven minus zero,
eight minus one.
But Bemint said no.
How about if we group by this column,
we already have these three records together.
So then use the Min function on the grouped index as seven,
eight, nine, and it will get seven
as the min for all three records.
So with this column, select and Right click Group By.
Now because we're grouping by total units,
it will remove the product column, but no problem.
For our first grouping, we're just
going to group all the records together.
So we'll call that All Records, Tab.
And we'll Down arrow to all rows.
Now we don't have a column to act on, because we're
grouping all rows.
Now we click Advance.
And we're going to add an aggregation.
We're going to call this Rank.
Dropdown, there's Min.
And the column we're going to get the men from is Index.
Now when we click OK, there will be a table for each record.
If we click on the first one, we can see our table down here
with the original two indexes.
But notice our new Rank column has the min right there.
And when I expand this column, that one
will be repeated for both of the total units.
Now all we need to do here is expand, unselect everything,
uncheck that.
And the only column we want is we want Product.
So now we click OK.
That is amazing.
There's our one, one and three sevens.
much more efficient than the multiple step grouping
and extra column back in 1,420.
Now we could move this if we wanted by simply dragging it
to the left.
Come up to Home, close and load.
It will close as a connection.
Now we can Right click, load to.
I want to load this as a table, existing sheet.
That is correct.
Click OK.
And there we go.
We get the same rank using min as we did back
in 1,419 and 1,420.
It's awesome hanging out on our online team.
Thanks to Bemint at YouTube.
There is his YouTube channel.
He has some great videos.
All right.
We'll see you next video.