Why would you need to rank in Power Query?
A client of mine has demographic data for their customers that may change between transactions and the most recent demographic data is what needs to be displayed in the data model so we don’t end up with a many to many relationship between the transactions table (fact table) and the demographic table (dimension table). Filtering the data out before it is compressed and sent to Power BI Desktop decreases file size, increases performance, and is necessary to have an optimized data model.
One problem. Power Query doesn’t have a built in ranking function.
Solution. I will tell you how to do it yourself.
This is all documented, with the M code, on our GitHub.
Step 1: Add a rank function
First we need to add a function in Power Query. Functions are like queries that take an input and provide an output. In this case we are going to take an input as a table and add an index as the output.
Here is the code:
//Source: https://blog.crossjoin.co.uk/2015/05/11/nested-calculations-in-power-query/
//This code is for the function that ranks rows in Power Query grouping by one column.
(tabletorank as table) as table =>
let
SortRows = Table.Sort(tabletorank,{{"Sales", Order.Descending}}),
AddIndex = Table.AddIndexColumn(SortRows, "Rank", 1, 1)
in
AddIndex
//tabletorank is the previous step in your M code.
//The "Sales" is the column you want to rank. You would change this to the name of the column //you want to rank.
Feel free to copy and paste this into your advanced editor.
Step 2: Add the rank column
Once you have created your rank function you are ready to add the rank column to your query. First do a group by on all rows by the column you wish to rank within. For example, if I want to rank the transactions by customer, I would group all rows by customer. The following code is an example of how to add the rank:
#"Grouped Rows" = Table.Group(#"Previous Step Name", {"Column to Group By"}, {{"AllRows", each _, type table [This will have all table columns and data types (autofilled)]}}),
AddedRank = Table.TransformColumns(#"Grouped Rows", {"AllRows", each #"Rank Function Desc"(_)})
The next step in your power query would be to expand the column and you have your original table with a new rank column.
Additional Notes
You can easily change the function to rank in ascending order by including Order.Ascending in the function instead of Order.Descending.
I usually have one rank function per rank column I need to add. This is because it is likely you are not ranking every query by the same column name. For example, one query you may want to rank by Sales while another you would want to rank by Customers. Create a separate rank function for each.
Good luck and enjoy this knowledge of how to rank in power query! Please contact JourneyTEAM if you have any questions.