Hi, thanks for great tips. For rankig based on two or more columns I used your tip : - sort data by those columns - Table.Buffer , to be sure sorting will finish before next step -Group table by column using ‘All Rows’ as operation , new column is added, CustomCol Table.AddIndexColumn([CustomCol], “Rank”1,1) Can all these steps be replaced with AddRankColumn ? Thanks
@@RADACAD Great. I have one issue with steps described i prevous post. Afer AddindexColumn I delete other columns and expand column created by this last step. Problem is column types are lost. All columns are AllTypes. Is there any eay to keep types as they were before groupping ?
Thanks, Reza! Just to make sure I understood when to do the rank calculation in PQ and not in PBI, if I want to rank customers on lifetime sales then using PQ makes sense as that rank will not be affected by any subsequent PBI measures; lifetime is lifetime. However, if I need to calculate sales rank within different time periods, say rank of sales in the last year, then that would be done in a DAX measure or PBI table. Did I understand that correctly? Thanks again for another great explanation!
Thanks for the great explanation!!
Super clear and useful, thanks!
Great to hear!
This was very helpful, thank you!
very helpful, thank you!
Thanks. this helps. some people has a button called "rank column" in transform. but I do not have it. This video is good
thanks , can you help what if in ranking we need to do order by another column (other than partition by column) , can we do this ?
thank you reza. this is very usefull.
You're welcome
Hi, thanks for great tips.
For rankig based on two or more columns I used your tip :
- sort data by those columns
- Table.Buffer , to be sure sorting will finish before next step
-Group table by column using ‘All Rows’ as operation , new column is added, CustomCol
Table.AddIndexColumn([CustomCol], “Rank”1,1)
Can all these steps be replaced with AddRankColumn ?
Thanks
Yes, that is right :)
@@RADACAD Great. I have one issue with steps described i prevous post. Afer AddindexColumn I delete other columns and expand column created by this last step. Problem is column types are lost. All columns are AllTypes. Is there any eay to keep types as they were before groupping ?
Thanks, Reza! Just to make sure I understood when to do the rank calculation in PQ and not in PBI, if I want to rank customers on lifetime sales then using PQ makes sense as that rank will not be affected by any subsequent PBI measures; lifetime is lifetime. However, if I need to calculate sales rank within different time periods, say rank of sales in the last year, then that would be done in a DAX measure or PBI table. Did I understand that correctly? Thanks again for another great explanation!
Hi George
Thanks
Yes, you got it exactly right. those two scenarios are spot on.
Brilliant!
Glad to help!
Is Table.AddRankColumn a new function? I don't have it in my May version of PBI.
How would you rank the column if the Customer names are repeated multiple times?
You can first GROUP BY the customer name, and then do the ranking