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 Revenue, DME, COGS and so.

 

thanks

Tips and Tricks
Tutorial Requests

6 Replies

    • shahinnakhai's avatar
      shahinnakhai
      Frequent Visitor
      6 years ago

      those values are changing and I am using direct query. I just want to customize in my order

      UCS
      CSE
      Net revenue
      Gross Margine
      DME
      COGS
      OPEX

      Non_op

       

      and if values are changed they stay the same.

       

      thanks

      • edhans's avatar
        edhans
        Community Champion
        6 years ago

        shahinnakhai ,

         

        You have to use the Sort By Columns feature as amitchandak has linked to. What you will need to do though is enter your data into a table somewhere for sorting. You can use the Enter Data feature of Power BI to do this and create a table like this:

        Then you merge this with your main table and expand the Sort Order column.

         

        However, this will not work with Direct Query. So you will have to create this table on your source server (SQL, AzureSQL, whatever) and then merge.

         

        As a last resort, you can try to do this with a conditional column. Power Query is pretty good about semi-complex if/then/else statements and folding them back, but I've never tried one with 7 steps. Below will get you started:

        You won't know until you try it to see if it folds properly and allows Direct Query to continue working. You would add this column to your table where those fields exist.

         

         

  • 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