Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi people,
Im havin the following issue:
Im trying to rank securities by their time to zero the position (liquidity mesure) but i want to rank it by "PRAZO DE ZERAGEM" and by "CLASSE DE ATIVO".
What I mean is, i need a single column with rank starting in 1 to N-stocks, another rank starting in 1 to N-options, another starting in 1 to N-bonds etc...
I've tried using rankx but I'm not familiar with it and it has not worked.
Here's the M script:
let // Source is your source table; here it's a manually created one // for demo purposes. You should replace this with your table. Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZExCoNAEEWvEra28K+uumcRi0hSCKLBmCK3j0SIzN8ZSCDle82+P9u2Di5z820d5sl12Yb5b1wSB+KKuCZuiGPyHoukyLMoWHBkKjgT3AkOBZd6LvVJ6Yb9PF2OQ37ICyoElYKCoOprirl8XaKsgcyB7IEMgiyCfHY/5IGNxEhXkEhVG6zL9Xx/LM9TP4zjn+x7TqqDritd17pudB11vf+K4mF4b/jC8MZUGFthjIWxFsZcGHu9sddre7sX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Time to Zero" = _t, #"Asset Class" = _t]), // Then I change the type of the two columns #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time to Zero", Int64.Type}, {"Asset Class", type text}}), // Need to sort the rows in order to get the ranks right. #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Asset Class", Order.Ascending}, {"Time to Zero", Order.Ascending}}), // This is needed to calculate the rank in the line below. #"Buffered Table" = Table.Buffer( #"Sorted Rows" ), // The calculation of rank. #"Rank" = Table.AddColumn( #"Sorted Rows", "Rank", (_) => let TimeToZeroTable = Table.SelectColumns( Table.SelectRows( #"Buffered Table", (r) => r[Asset Class] = _[Asset Class] and r[Time to Zero] <= _[Time to Zero] ), {"Time to Zero"} ), // If you want to have ranks with gaps, remove the List.Distinct function. rank = List.Count( List.Distinct( TimeToZeroTable[Time to Zero] ) ) in rank ) in #"Rank"
And here's the result:
Just make the obvious changes to the script and you're done.
Truth be told, if you remove the line that starts with #"Sorted Rows" =, you'll also get what you need. You don't have to sort the table but if the table is really big, then it might be better to sort it.
Best
Darek
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |