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

Yes, this looks very similar, but we need to create an additional lookup/merge-step here:

 

Partition = Table.Group(Source, {"Group", "Date"}, {{"Partition", each Table.AddIndexColumn(Table.Sort(_,{{"Date", Order.Ascending}}), "Index",1,1), type table}}),

Lookup = Table.NestedJoin(Source,{"Group", "Date"},Partition,{"Group", "Date"},"NewColumn",JoinKind.LeftOuter)

 

You can then expand the "Index" column from the newly created column holding the merged content.

 

Anthony007's avatar
Anthony007
Helper I
9 years ago

Hi ImkeF! Can u help please with this example. We have a table with three columns. Need to build a rating (or index) given the week and the value

 

Need:

  • ImkeF's avatar
    ImkeF
    Community Champion
    9 years ago

    Hi Anthony007,

    the formula should work for you as well. Have you tried it?

  • 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