cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
harshnathani
Super User
Super User

Create a Rank Index Column in Power Query

Hi Community Members,

 

I have a column ID with values  50, 70, 10, 5, 95, 100, 100, 225

 

I want to create an index column (1 - 8 )such that the lowest value in the ID column gets the value 1 and the highest in the id column gets value 8.

 

Here 5 will get index value 1, 10 will get index value 2 ........ 225 will get index value 8.

 

I can do it using rankx but want to try this in Power Query.

 

Regards,

Harsh Nathani

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

Hi @harshnathani 

 

Sure, just add 2 steps 

1. sort you values column in Ascending order.

image.png

2. Add an Index column as below, click on the small down arrow next to index column and select "from 1" or adjust the code to start from 1 after.

image.png

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

View solution in original post

1 REPLY 1
Mariusz
Community Champion
Community Champion

Hi @harshnathani 

 

Sure, just add 2 steps 

1. sort you values column in Ascending order.

image.png

2. Add an Index column as below, click on the small down arrow next to index column and select "from 1" or adjust the code to start from 1 after.

image.png

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!