Welcome to Excel Magic Trick number 1,420.
Hey, if you want to download this Excel workbook, the start
file or the finished file so you can follow along,
click on the link below the video.
Ranking in Power Query?
Is it possible?
Well, I actually searched all over
to try to find a ranking function like in Excel,
RANK.EQ, or in DAX like RANKX, but I just
couldn't find a function and Power Query to do it.
No problem.
We'll bring the data in, sort it at an index,
but then there'll be a problem when we have duplicates
and we'll have to solve it.
Now if it was me, I would use the Excel formulas and the DAX
formulas, but if I was connecting to some big data
and SQL database and all I wanted
was an end ranking report, then I'd use Power Query.
Now what we're going to do here is
we're actually going to connect using these credentials here
to an SQL database I set up, and in the database,
there are transactions for boomerang sales.
Then we have a couple extra records
that were not included that we're going
to take from an Excel table.
We'll append both of these tables,
create one big table with over 7 million rows of data,
and then we'll use Power Query to group
up according to product total units and the rank.
All right, if we're going to connect to an SQL database,
I'm going up to Power Query in the Data ribbon tab
to Get and Transform group.
Now I have Excel 2016, so that's why
I'm using the Get and Transform group.
In earlier versions you have to download Power Query
as a special add-in.
We go over to Get Data, and we want From Database,
From SQL Server Database.
The server, it is pond.highline.edu.
The database is called boomerang.
Now I'm going to click OK.
You might have to enter in the username and the password,
there they are.
Click OK.
It opens up the query editor.
Here is our name over there, that's OK.
We only want Product and Quantity,
so I'm going to click on Product,
hold Shift, click on Quantity, right-click,
Remove Other Columns.
We have Whole Number and Text Data Type, that's looking good.
I want to go up to Close and Load, Close and Load To.
This is our new import data that combines the Load To
with the old Import.
It has all the same buttons, I'm going
to select Only Create a Connection, click OK.
It's loaded, Connection Only.
Now let's go get our second table.
We're going to go over to New Records, click
in a single cell--
I've already converted this to an Excel table.
We can see, it's called New Records.
Click in a single cell, Data ribbon tab,
Get and Transform group, From Table Range.
Here's the query editor, there's our name,
there's some steps already, and this is a silly little table,
but I wanted to make sure that we
had ties to run into the problem of ties.
We have whole numbers, text for data types, looking good.
We're going to load this.
Load To, Only Create a Connection, click OK.
We have our two tables that's Connection Only.
Now we can combine them using Append.
So we go back up to Get and Transform,
Get Data, Combine Queries, Append,
meaning we're going to join the second table
to the bottom of the first.
Because they have the same columns and data type,
it'll be no problem.
I click Append, it wants to know the first table, that's
going to be fTransaction.
Second table, New Records.
Come over, click OK.
Here's our query editor.
I'm going to come over and name this Rank Report and Enter.
Now our first step is we need to use the grouping feature
to add the total units for each one of the products.
Over in Excel we'd use SUMIFS or a pivot table,
but over here we use the Group By feature.
We're going to group by product, and Group By
will give us a unique list of products
and then add all the associated quantities.
Group By is in a few different places--
Home, Group By; Transform, Group By, or right-click, Group By.
It already knows we want to group by Product
and get a unique list.
The operation is going to be SUM.
We're going to call this Total Units,
and the column we want to add is Quantity.
Click OK.
And look at that.
Lightning speed on 7 million records,
there's our unique list, there's the total.
Now our first step in ranking in Power Query
is to come up and simply sort biggest to smallest.
So I click Descending.
Now we can add an index column which simply adds a count--
1, 2, 3, 4, 5, 6, 7, 8.
If there were no ties, this would work perfectly.
I'm going to go to Add Column, Index Column,
and for our first index, we're going to do from the number 1.
So I click that, and there is our rank.
Now I'm actually going to load this at this point
to our sheet, and then we'll come back and edit it later.
Home, Close and Load, Close and Load To.
I want to load it as a table on a new worksheet, click OK.
I'm immediately going to come down and call this Rank Report
and Enter.
There's our report.
Over here we can see our third query
based on these two merged queries,
and then are grouping and index.
Now if we look over here, it looks like there's
a three-way tie here.
Maybe that's what you want.
Maybe you want to see 7, 8, 9, and then 10,
but really, these are all ranked at 7.
So we want to go back and amend our formula
to have this list 7, 7, 7, and then 10.
Now really think about this.
Why do we want?
We want 7 minus 0, 8 minus 1, 9 minus 2.
So we're going to go and fix this,
we can group by total units, it will group these
up into a little table, and then we'll add an index,
but it will start at 0.
Then we can take this original rank and subtract the new index
and it will give us the correct rank.
Now I'm going to come over here, double-click or right-click,
Edit.
Now when we use the user interface to create our report,
it creates these steps over here.
If you select a step, you can actually
look up in the function bar and see the function.
If you wanted, you could go over to View and click Advanced.
But what we want to notice is, hey look, here's
the Table.AddIndexColumn, and that's
the part of the function that gave this the name.
I want to change this here, and this
will be the first time in this video we have actually changed
what the user interface did.
I'm going to call this StartRank and Enter.
Now our next step is to group total units,
but what I want to do is group all of these together
and in a new column get a new individual table,
and then on that table we can add an index.
So I'm going to select Total Units, right-click, Group By.
Total Units is our group by, and I
want to select the operation-- please give me all the rows
or records in this grouping.
I'm going to call this Records.
There's no column because it's just
going to take all of them, that's the action
to take on this group.
Click OK.
So now we get a new column of tables.
Now I want you to notice something,
if you click off to the side, not on the table right here,
you're going to see, oh, there's one,
there's two-- one record here.
But when we come down to our tie right here,
we can see there's a table of 7, 8, 9.
Now notice for the products and total units
where there is not a tie, if we add an index here
starting at 0, it'll be 0.
But this one will have 0, 1, 2.
Now we could add an extra column here and type out our formula,
but I actually want to try and edit
this step over here, Group Rows-- you can see,
it says Table.Group.
Now Table.Group requires a table,
and that's the name of the previous step.
Then it says, which column do you want to group by?
Total Units, and then in list as a list syntax,
it lists our grouping action.
Now the underscore means please get all the records or rows
for this particular record over here.
Now using the underscore gets all the data
for each row in this table over here.
What I really want instead of each,
just give me all the records, I actually want to add an index.
So watch this, I'm going to go back to the previous step
and totally cheat.
I'm going to copy from the formula bar,
the Table.AddIndexColumn, Control-C, Escape,
come down here to Group Rows, and right where we have that
underscore-- we're going to have to use the underscore,
but for the time being I'm going to Backspace, Control-V,
and we don't want to have Table.AddIndexColumn
look at sort rows, we want it to--
and I'm deleting underscore.
We want it to do this for the actual data for each row
in this larger table down here.
Now, I don't want it to start at 1, I want it to start at 0.
So I'm going to change that to, and we're
going to call this Amount To Subtract.
Now when I hit Enter, well it doesn't
look like anything changed, but watch this.
We can select each one of the tables,
and when we come down to our duplicate right here,
yes, it did grouping by total units,
but within that, it did Table.AddIndexColumn.
And there it is down there.
So there's the original table, and we add an extra column
with an index starting at 0--
0, 1, 2.
Now we need to expand this, so we can come up
to our Records column, click the Expand button,
and we already have Total Units right here,
so I'm going to uncheck that, uncheck Use Original Column,
Name as Prefix, click OK.
There's our amount to subtract, 7 minus 0, 8 minus 1,
9 minus 2.
Now we can add another column here.
Add Column, Custom Column.
I'm going to call this one Rank.
Click down here.
Start Rank, double-click, there it
is in square brackets, minus Amount
to Subtract, double-click, there it is.
Click OK.
1, 2, 3, 4, and there's our 7's and are 10.
Now I do not need Start Rank and Amount to Subtract,
so I click on one, hold Shift, click
on the other, right-click, Remove Columns,
and there is our report.
I can simply go up to Home, Close and Load,
because we've already closed and loaded it,
and there is our new rank.
Now I actually want to test this.
It worked here, but let's go over to our table.
And we want to add this record, So I'm
going to click in the last cell on the last record,
Tab to add a new record, Quad, Tab, Control-C, right-click,
Paste Special Values.
Let's go back over here.
I'm going to come over to the Rank Report,
right-click, Refresh.
There it is refreshing, and there's our report
with the correct rank.
Tied, FunFly and Quad for first, and these three for 7.
So that's a little bit about how to rank.
We started off with appending an SQL database
table with an Excel table, we created a single table
with over 7 million records, and then created
our ranking report.
All right, we'll see you next video.