Sort by is not working

[Anonymous]

I have a matrix and I want the Row item (called Product) to be sorted in a specific order so I created a column in Query Editor called 'Order' and i've used an IF statement to define the order as 01, 02, 03 etc. 

I've then sorted Product by the Order column in the Modelling tab, and it's made no difference to the order whatsoever!

I tried changing the column type of 'Order' from decimal to text, to whole number and none of these are working. 

What's the secret???

[makcim392]

Hi,

I came across the same issue. I went to edit query, I have a simple column called code_person. Selected said column as 'whole number'.

It works fine when I sort them in power query. When I go back to the Data View, they are showing up like this:

any ideas why?

thanks!

[makcim392]

ok, so I just discovered that you have to change the Data Type to whole number, in order for it to get ordered as a number. 

So I wonder, what's the point in selecting the type of data in editing query, if I have to do it again in Data View?

[themistoklis]

@Anonymous

Make sure that the format of the field is of data type: Whole Number and not Text or anything else.

When you say you cant see the ordering... do you mean that you created a table object or chart and the ordering is not working?

Or you just want to see the ordering on the background data?

Also have you used the sort by column on Modelling tab?

[Anonymous]

@themistoklis

Yep i've used whole number, and I created a matrix and the ordering is not working. 

Yes I used the sort by column on the modelling tab. 

When I applied the sort by, it did not affect the order in the matrix. It's like it's not recognising the Order column, but it's not erroring. 

[themistoklis]

@Anonymous

Can you share the workbook with us so as to have a look at it?

[Anonymous]

@themistoklis

I've attached some screenshots of the relevant areas, I can't attach the worksheet due to the data it contains. 

In Query Editor, the Order column is 'whole number'The M Query formula used to create the 'Order' columnThe Row item is sorted by 'Order'In the table, the Order column still shows as Whole Number and contains values 1 to 26

[v-danhe-msft]

Hi @Anonymous,

Based on my research, you could refer to below link to custom sort your column:

https://community.powerbi.com/t5/Desktop/Custom-Sorting-in-PowerBI/td-p/126534

Regards,

Daniel He