Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
98 | |
75 | |
63 | |
62 |
User | Count |
---|---|
139 | |
104 | |
104 | |
80 | |
66 |