How to rank a row based on a column

[sbadiger3]

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_id	rank
123456	1
123457	1
123452	1
157593	1
123456	2

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!
 

[mussaenda]

We grouped them according to order line id 

then created an index column according to it 

then sorted the rank according to your requirement

[mussaenda · Accepted solution]

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"

[sbadiger3]

@mussaenda , can you please explain what all steps you performed?