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"
runski's avatar
runski
Frequent Visitor
9 years ago

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's avatar
ImkeF
Community Champion
9 years ago

 

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's avatar
    runski
    Frequent Visitor
    9 years ago

    Hi, Imke.

     

    Perfect! 

    Thank you!

     

    Runar Wigestrand