Forum Discussion

sbadiger3's avatar
sbadiger3
Helper I
2 years ago
Solved

How to rank a row based on a column

Hi everyone, I need help in creating a column in a table in power query. In my table I have a column 'order_line_id'. This column has duplicates. Now, I need to create a column 'rank' which gives rank based on the column 'order_line_id'. I am attaching below a demo table below. 

order_line_idrank
1234561
1234571
1234521
1575931
1234562

 

Here in this case, I'm creating a rank for all the values of order_line_id. In the example table above 123456 is a duplicate order_line_id and the whichever comes first in the table is ranked 1 and later 2. 

It would be great if someone can help me to achieve this in power query. 

Thanks in advance!
 

Need Help
Show and Tell
Tutorial Requests
  • mussaenda's avatar
    mussaenda
    2 years ago

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U4rVgTLNEUwjCNPU3NTSGCEKVBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [order_line_id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"order_line_id", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"order_line_id"}, {{"Count", each _, type table [order_line_id=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Rank", 1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Rank"}, {"Rank"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Rank", Order.Ascending}})
in
    #"Sorted Rows"

3 Replies

  • mussaenda's avatar
    mussaenda
    Community Champion
    2 years ago

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U4rVgTLNEUwjCNPU3NTSGCEKVBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [order_line_id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"order_line_id", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"order_line_id"}, {{"Count", each _, type table [order_line_id=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Rank", 1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Rank"}, {"Rank"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Rank", Order.Ascending}})
in
    #"Sorted Rows"
  • mussaenda's avatar
    mussaenda
    Community Champion
    2 years ago

    We grouped them according to order line id 

    then created an index column according to it 

    then sorted the rank according to your requirement