Forum Discussion

fedpar's avatar
fedpar
Microsoft Employee
10 years ago
Solved

Custom column Index or Ranking by other column

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...
  • ImkeF's avatar
    ImkeF
    10 years ago

    Thats like an index on a table partition. You can create that by using grouping on the column and returning "_" - which means that all column of the table (but only for the specific value in the column) will be return. You then nest your Index-command in:

     

    let
    Source = Table1,
    Partition = Table.Group(Source, {"Group"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Index"}, {"Date", "Index"})
in
    #"Expanded Partition"
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Someone might be able to make the code a bit tidier

qsong's avatar
qsong
Helper II
6 years ago

Hi Anonymous 

 

Thank you for your answer, but I think I am not clear about my question. I want to evaluate both criterion and create one ranking. 

 

In the example, it should be like that:

 

Does it make sense now? ImkeF provided us with a solution for it in the blog, my issue is that the ranking is not proper for some observations, so my result may look like that:

 

Thank you, I appreciate any ideas to fix it.

 

Best, Qianru