can't sort a non-unique column based on another one

[eshetb]

hey,

i'm trying to sort one column which has duplicate values by another one.

assume this example, where i want to have "status_category" sorted by "rank":

status	status category	rank
new	in progress	10
first review	in progress	20
second review	in progress	30
rejected	closed	40
on hold	hold	50
closed	closed	60
canceled	closed	70

the problem is that one category value cannot have more than one rank value. (getting a can't sort error)
so i created another column, taking the min rank value for each category:

status	status_category	rank	min_rank
new	in progress	10	10
first review	in progress	20	10
second review	in progress	30	10
rejected	closed	40	40
on hold	hold	50	50
closed	closed	60	60
canceled	closed	70	60

using this:

min_rank = 
VAR currentCategory = table[status_category]
RETURN
    CALCULATE (
        MIN ( table[rank] ),
        FILTER ( ALL ( table), table[status_category] = currentCategory)
    )

so now there's only one possible rank for each category value.
however, wheb ttying the sort by option i get a new error: "This column can't be sorted by a column that is already sorted, directly or indirectly, by this column".
am i to understand that it's not possible to sort a column by another one that's calcaulated by it?

can anyone find a solution to either of the sort errors?

[Stachu · Accepted solution]

you can create the rank in query editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykstV9JRysxTKCjKTy9KLS4G8gwNlGJ1opXSMouKSxSKUssysagxgqgpTk3Oz0vBpcgYoqgoNSs1uSQ1BSiSnJNfDGaYQKTy8xQy8nNAAlDKFCIOVwdnmEElEvOSU3NQpcyBUrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [status = _t, #"status category" = _t, rank = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"status", type text}, {"status category", type text}, {"rank", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"status category", "rank"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"status category"}, {{"min_rank", each List.Min([rank]), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"status category"},#"Grouped Rows",{"status category"},"Custom1",JoinKind.LeftOuter),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Custom1", {"min_rank"}, {"min_rank"})
in
    #"Expanded Custom1"

[eshetb]

Thanks, that's perfect!