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

Hi Ashish_Mathur,

it works for me. You might not have applied the code correctly or have other queries in your current file with name conflicts that cause this message. Please have a look at this video - there I've worked with exactly the same data and used the query you've mentioned: https://youtu.be/S9xlq5KUZ60

Ashish_Mathur's avatar
Ashish_Mathur
Super User
9 years ago

Hi,

 

It is working absolutely fine now.  Thank you very much.