formatting and customizing Matrix rows and Column

[shahinnakhai]

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

[Anonymous]

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

[amitchandak]

@shahinnakhai , if these are values of columns. Create a sort column and mark it as sort column

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

if these are measure or row, change the order

[shahinnakhai]

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]

@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.

[shahinnakhai]

@edhans 

Super User II

I have created the new column in my SQL table based on what I want to be sort by, but still doesn't give me an option based on that.

[edhans]

You are going to have to be more specific @shahinnakhai.
I just did a direct query connection to my SQL server and sorted based on another column. The granularity of the sort by column has to be 1:1 with the column sorted. So, if you want to sort by a month name by month, the sort by column must be 1-12 (or have 12 unique values). It cannot be a date column.

If your column you want to sort has 15 unique values, your sort by column must have 15 unique values to sort on.