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

ImkeF, I tried all the examples and nothing happened.For each week it is necessary to make a rating of values.I did not receive such a result

willfray's avatar
willfray
Regular Visitor
8 years ago

Many thanks ImkeF this is fantastic. However I am trying to use this in DirectQuery mode and it is stumbling at the last step: 'Table.ExpandTableColumn' - I get an error message saying 'This step results in a query that is not supported in DirectQuery mode'.

 

Might you be able to help?

 

Kind regards,

 

Will