Custom column Index or Ranking by other column

[fedpar]

I'm looking to add an index column, but have it increase according to a certain column value. Let me give an example; let's say my data is:

Group	Date
A	18-Apr
A	19-Apr
A	23-Apr
A	1-May
B	21-Apr
B	21-Apr
B	30-Apr
B	4-May

And I would like to have the indices show like this:

Group	Date	Index
A	18-Apr	1
A	19-Apr	2
A	23-Apr	3
A	1-May	4
B	21-Apr	1
B	21-Apr	2
B	30-Apr	3
B	4-May	4

How can I perform this dynamically?

[dong]

Hi ImkeF,

Your solution works great! You are so smart! Thank you!

[runski]

Excellent solution - and thanks for uploading the video.

Just one question, how can I choose which column to rank on?

Runar Wigestrand

[ImkeF]

Not sure if I understood you correctly, but I think you mean the field/column to group on? In this example it was also called Group:

Partition = Table.Group(Source, {"Group"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),

[nsrshkh]

You are a genious!!!

[runski]

Hi, Imke.

Thanks for the quick reply. I was thinking more about if you have duplicates and want to keep only the newest record based on date. Then I want to group on one column and rank on another column ( for example the date column). 

Something like this:

Group on SerialNum, rank on date column

Thanks.

Runar

[ImkeF]

Oh, I see: You need to fold in a Table.Sort-operation that will be executed before the Index is added. Sth like this:

Partition = Table.Group(Source, {"SerialNum"}, {{"Partition", each Table.AddIndexColumn(Table.Sort(_,{{"Date_Updated", Order.Ascending}}), "Index",1,1), type table}}),

Does that work for you? 

[runski]

Hi, Imke.

Perfect! 

Thank you!

Runar Wigestrand

[sebbyp]

YES!!! Imke you are an absolute star 🙂

I have been stuck on this for weeks and weeks.  The solution you have provided works perfectly.  Thank you so much!  What a great way to start the day...