Forum Discussion
How to add serial number based on another column values?
Hello, I'm just starting with Power BI and facing a problem. I have table which contains Person_Id and Order_Id. I want to create column which shows if it's first order of user or it's second order of user and so on. How can I achieve it? Is it possible in Query Editor?
Thanks in advance for answers.
According to your description, you actually need to add a rank column based on Order_Id group on Person_Id with Power Query.
For your requirement, you need to group all Order_Id entries into a Table object on Person_Id column. Then custom a Rank Function, invoke that function to add custom rank column in each grouped table object. After that, expand those tables.
For more details, you can refer to this article: Power Query function for dense ranking
Regards,
5 Replies
v-sihou-msftMicrosoft Employee
According to your description, you actually need to add a rank column based on Order_Id group on Person_Id with Power Query.
For your requirement, you need to group all Order_Id entries into a Table object on Person_Id column. Then custom a Rank Function, invoke that function to add custom rank column in each grouped table object. After that, expand those tables.
For more details, you can refer to this article: Power Query function for dense ranking
Regards,
- TomMartens
Super User
Hey,
here
https://docs.com/minceddata/7251/subset-and-apply-indexing-rows?c=2asAm5
you will find a little example how to create an index column based on a column that gets ordered. This index is created in each group. This example assumes that are able to create this index column using Power Query
Hope this helps
Hi Tom - I run into a similar problem and the solution you mentioned could be exactly what I'm looking for. However the link is no longer valid. Is there any chance you can update it?
Basically I need to rank / index a column but it should restart from 1 again on each subset. Thanks a lot.
