How to find Rank on 2 Columns with duplicate values

Hi Anonymous 

try calculated column

Column = RANKX(FILTER('Table1';'Table1'[Document ID]=EARLIER(Table1[Document ID]));'Table1'[InvoiceNumber];;ASC;Skip)

do not hesitate to give a kudo to useful posts and mark solutions as solution

HI Anonymous ,

I'd like to suggest you enter to query editor to add an index column to achieve your requirement.

Steps:

1. Group table records by Document id, aggerate mode to 'all rows'.

2. Modify group steps to add index into grouped tables.

3. Click expand icon to expand table column, then modify the formula to add display index column.

Full query sample data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXI0VIrVwcV0AjKdMJjOQKYzTmYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Invoice = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Invoice", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Contents", each Table.AddIndexColumn(_,"Index",1,1), type table [ID=text, Invoice=text]}}),
    #"Expanded Contents" = Table.ExpandTableColumn(#"Grouped Rows", "Contents", {"Invoice","Index"}, {"Invoice","Index"})
in
    #"Expanded Contents"

Regards,

Xiaoxin Sheng

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQ0MlbSUYqINFSK1SHEdYJwI6OMsHGdoYqjjAlxYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Document ID" = _t, InvoiceNumber = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Document ID", type text}, {"InvoiceNumber", type text}}),
    Partition = Table.Group(#"Changed Type", {"Document ID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"InvoiceNumber", "Index"}, {"InvoiceNumber", "Index"})
in
    #"Expanded Partition"

Hope this helps.