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
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
2 years ago

Hi, it looks like the exact solution that I am looking for but I not so well-versed in Power Query and unable to follow how to implement it in my work. Can someone show it in steps? For a start I have a table that I want to index based upon Week-ending date.

Thank you so much.