Power BI | How to Better Rank in Power Query (M)
Search

Power BI | How to Better Rank in Power Query (M)

Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs. Features in Power Query are available in Excel and the Power BI Desktop.



You ask, why would you need to rank in Power Query? Here is a story and the reasons why.

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. Do it yourself and I will tell you how.

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 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!


About JourneyTEAM

If you have any additional questions about Microsoft Power Platform, Power BI or PowerQuery (M),— we can help you out. Our consultants can help you dive deep into your data to get better results for your teams and organization. Contact JourneyTEAM today for more information.

0 views

833-438-2312
info@journeyteam.com

CORPORATE OFFICE UTAH

121 W Election Rd #300

Draper, UT 84020

--------

Nashville, TN
Salinas, CA

Houston, TX

Pittsburgh, PA

Concord, NH

High Point, NC

Baltimore, MD

Louisville, KY

St. Louis, MO

Waukesha, WI

VAR_Star_logo White.png

VAR STARS
2020 WINNER

  • White LinkedIn Icon
  • White Instagram Icon
  • White Facebook Icon
  • White YouTube Icon
  • White Twitter Icon

Microsoft Dynamics 365, SharePoint, Cloud, ERP, Field Service, Finance and Operations, CRM and Marketing Consultant in Utah and Tennessee. Microsoft Gold Partner and Okta top 20 World-Wide Partner. © 2020 JourneyTEAM. All Rights Reserved. Privacy Policy Terms and Conditions