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"
davidestg's avatar
davidestg
Helper I
8 years ago

Hi. I'm trying to do something different about the cases exposed. 

 

I have a table that need an increase index when I have some change between three columns. I mean: 

 

WorkerFechaValorIndex
A16/04/2018TRUE1
A16/04/2018TRUE1
A16/04/2018FALSE2
A16/04/2018TRUE3
B15/04/2018TRUE4
B16/04/2018FALSE5
B16/04/2018TRUE6
B16/04/2018TRUE6

 

Starting by 1 in each change of first 3 columns I need to increase the index. I'm trying with all the answer from this post but I can't find the solution. 

 

Other possibility is to save the last combination and the last index in a parameter or another table, but I can't find how to save it in a parameter or table.