How to add serial number based on another column values?

[Nikita]

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.

[v-sihou-msft · Accepted solution]

@Nikita

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]

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

[raynory]

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.

[TomMartens]

Hey @raynory

the link is still working, you only get a hint from Microsoft that docs.com will be retired.

But nevertheless here you will find the pbix file, that creates a grouped index.

Be aware that this is the "tweaked" GroupingFormula of the step Grouped Rows that you will see in the Query rowindexsubset in the Query Editor. Once you  "tweaked" the base formula you are just able to use the formular bar, but you will not get the dialog for  the "Group by" transformation.

= Table.Group(Source, {"customerid"}, {{"AllRows", each 
Table.AddIndexColumn( Table.Sort(_, {{"orderdate", 0}, {"orderid" , 0}} ) , "rowindexinsubset" ,1,1 ), type table}})

Hopefully this is what you are looking for

Regards

Tom

[raynory]

Thanks very much for your help Tom! I will try it:)