Forum Discussion

shahinnakhai's avatar
shahinnakhai
Frequent Visitor
6 years ago

formatting and customizing Matrix rows and Column

I created a matrix but I don't want my matrix sort by decsending or ascending how can I customiz that in my order. Also same thing for the rows.   for example I want to have UCS, CSE, Net Rev...
Tips and Tricks
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
6 years ago

you could try a schema like the following wich use a record as dict where you can associate tha words as fields to the rank as values and then use the funcyion table to records and viceversa to bring the features of list.sort to  table context:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxU0lEyVIrVATKTgEwjMDM5LQXINgazHYNdXIEcE4gaIMsUwjICMs3AzJTitPR0IM8czKuqKE4pSgVyLZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [idx = _t, val = _t]),
    t = Table.TransformColumnTypes(Source,{{"idx", type text}, {"val", Int64.Type}}),
    ttr=Table.ToRecords(t)
    
in
   Table.FromRecords(List.Sort(ttr, (x,y)=>Value.Compare(Record.FieldOrDefault(custOrd,x[idx],100),Record.FieldOrDefault(custOrd,y[idx],100))))

 

 

 

PS

is also possible to get analogous results using List.Zip and table.tolist table.fromlist.

For example to sort the table rows respect to the length of value of a given column, one can use this:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxU0lEyVIrVATKTgEwjMDM5LQXINgazHYNdXIEcE4gaIMsUwjICMs3AzJTitPR0IM8czKuqKE4pSgVyLZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [idx = _t, val = _t]),
    t = Table.TransformColumnTypes(Source,{{"idx", type text}, {"val", Int64.Type}}),
    ttr=Table.ToRows(t),
    ak=Table.FromRows(List.Zip(List.Sort(List.Zip({t[idx],ttr}),(x)=>Text.Length(x{0}))){1})
in
    ak