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"
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Awesome. Great video. The video definitely helped explain things. Thanks

qsong's avatar
qsong
Helper II
6 years ago

Hi ImkeF 

I experimented your method to rank my data with two criterion. However, when I took a look at the data carefully, I found some data which is not ranked properly.

 

 WEIGHTHEIGHTRANKCORRECT RANK
A651.723
B651.832
C701.7511

A,B,and C three people are ranked firstly by weight, if some of them have the same weight then we will rank them by height. I got the rank column which is not correct. Here I also show you the correct ranking.

 

So I tried to the buffer table query to conserve the sort, unfortunately, the issue has not gone so far. 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTK00DMw0TMyMDRXitXBKWRkjCFkYKhnYIoQcgKpMkRRhUPI2ACvUCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Group = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    RemoveDups = Table.Distinct(#"Changed Type", {"Group", "Date"}),
    Partition = Table.Group(RemoveDups, {"Group"}, {{"Partition", each Table.AddIndexColumn(Table.Sort(_,{{"Date", Order.Ascending}}), "Index",1,1), type table}}),
    LookupTable = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Index"}, {"Date", "Index"}),
    Lookup = Table.NestedJoin(#"Changed Type",{"Group", "Date"},LookupTable,{"Group", "Date"},"NewColumn",JoinKind.LeftOuter),
    ExpandedIndex = Table.ExpandTableColumn(Lookup, "NewColumn", {"Index"}, {"Index"})
in
    ExpandedIndex

This is your query. The difference is that I rank based on two values - weight and height. Also, I consider merge later, as I have data which has the same weight and height, so I want them to have the same ranking. I used buffer table after Partition to fix the issue, the issue is still there.

 

I suspect that in the Partition, they sort weight and height as any type of numbers, because later when I expand the table (in the LookupTable step), the column weight and height come with any data type.  

 

How do you view this case?

 

I appreciate any help.

 

Thank you.

 

Best, Qianru