Forum Discussion
How to add serial number based on another column values?
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,
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,
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.
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
