Rank by date for each client Power Query

[bml123]

Hi,

I have data like this and need to add a rank column to order by date in descending order for each client.

How do I achieve this?

Client	Date	Rank
A	01/02/2021	1
A	09/09/2021	3
A	15/05/2021	2
B	20/01/2022	2
B	07/02/2022	3
B	19/11/2021	1

[v-henryk-mstf · Accepted solution]

Hi @bml123 ,

Try formula like below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vcy7CQAhEIThXjYW5gFyGHptiP23cRssJ8JEH/yzVsxoQYGGacVuRQO5i9TBfuhNMpFtkn/iU1+HNCBVuD8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"Date", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Client"}, {{"AllRowsGroups", each Table.AddIndexColumn(Table.Sort(_,{{"Date", Order.Ascending}}), "Row Rank",1,1), type table [Client=nullable text, 
                        Date=nullable text,
                        Row Rank=nullable number]}}),
    #"Expanded AllRowsGroups" = Table.ExpandTableColumn(#"Grouped Rows", "AllRowsGroups", {"Date", "Row Rank"}, {"Date", "Row Rank"})
in
    #"Expanded AllRowsGroups"

For more, you can read below blog:

Adding a row rank based on a different column with Power Query – data witches (data-witches.com)

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Avantika-Thakur]

Hi @bml123 ,

You can try applying the steps mentioned in this link - https://blog.crossjoin.co.uk/2015/05/11/nested-calculations-in-power-query/