Forum Discussion

Nikita's avatar
Nikita
New Member
9 years ago
Solved

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 o...
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    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's avatar
TomMartens
Icon for Super User rankSuper User
8 years ago

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's avatar
raynory
Regular Visitor
8 years ago

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