Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to Solution.
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
Thanks very much for your help Tom! I will try it:)
User | Count |
---|---|
86 | |
82 | |
68 | |
67 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |