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.