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

 
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     

    This site may help. Even though it is power query the same techniques that use M code/DAX applies to Power BI. Look around the 20min mark in the video. It shows how to rank the same if the same.

     

     

     

     

    23:03 / 30:42

    MSPTDA 08: Power Query Group By feature & Table.Group Function

     

    https://www.youtube.com/watch?v=hs21s0TWT14

     

    I also recreated your data and added a few in case the second sort, by height also had a ‘match’

     

     

    let

        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Weight", Int64.Type}, {"Height", type number}}),

        #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Weight", Order.Descending}}),

        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "IndexWeight", 1, 1),

        #"Sorted Rows1" = Table.Sort(#"Added Index",{{"Height", Order.Descending}}),

        #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows1", "IndexHeight", 1, 1),

        #"Grouped Rows" = Table.Group(#"Added Index1", {"Weight"}, {{"All Rows", each _, type table [Person=text, Weight=number, Height=number, IndexWeight=number, IndexHeight=number]}, {"Rank Weight", each List.Min([IndexWeight]), type number}}),

        #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Person", "Weight", "Height", "IndexHeight"}, {"All Rows.Person", "All Rows.Weight", "All Rows.Height", "All Rows.IndexHeight"}),

        #"Grouped Rows1" = Table.Group(#"Expanded All Rows", {"All Rows.Height"}, {{"Height", each _, type table [Weight=number, All Rows.Person=text, All Rows.Weight=number, All Rows.Height=number, All Rows.IndexHeight=number, Rank Weight=number]}, {"Rank Height", each List.Min([All Rows.IndexHeight]), type number}}),

        #"Expanded Height" = Table.ExpandTableColumn(#"Grouped Rows1", "Height", {"Weight", "All Rows.Person", "All Rows.Weight", "All Rows.Height", "All Rows.IndexHeight", "Rank Weight"}, {"Height.Weight", "Height.All Rows.Person", "Height.All Rows.Weight", "Height.All Rows.Height", "Height.All Rows.IndexHeight", "Height.Rank Weight"}),

        #"Removed Columns" = Table.RemoveColumns(#"Expanded Height",{"Height.All Rows.Weight", "Height.All Rows.IndexHeight", "Height.All Rows.Height"}),

        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Height.Rank Weight", "Rank Weight"}}),

        #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Height.All Rows.Person", "All Rows.Height", "Height.Weight", "Rank Weight", "Rank Height"}),

        #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"All Rows.Height", "Height"}, {"Height.Weight", "Weight"}}),

        #"Sorted Rows2" = Table.Sort(#"Renamed Columns1",{{"Rank Weight", Order.Ascending}, {"Rank Height",Order.Ascending}}),

        #"Reordered Columns1" = Table.ReorderColumns(#"Sorted Rows2",{"Height.All Rows.Person", "Weight", "Height", "Rank Weight", "Rank Height"})

    in

        #"Reordered Columns1"

     

    Result as below

     

     

    I hope this helps

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Someone might be able to make the code a bit tidier

  • qsong's avatar
    qsong
    Helper II
    6 years ago

    Hi Anonymous 

     

    Thank you for your answer, but I think I am not clear about my question. I want to evaluate both criterion and create one ranking. 

     

    In the example, it should be like that:

     

    Does it make sense now? ImkeF provided us with a solution for it in the blog, my issue is that the ranking is not proper for some observations, so my result may look like that:

     

    Thank you, I appreciate any ideas to fix it.

     

    Best, Qianru