cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Ordering table not by alphabetical sort, or index

I have this table and I want to order it so that the order is Men, Women, Other, Total. How can I do that with Power Query? Also, when I change the order in DAX, and do a sort column on [Gender] by [Cod. Gender], it removes the Total from the graphs.

It looks like this when I do the sort by:

1 ACCEPTED SOLUTION
Super User

Hi @ale_BI ,

To get a custom sort order, you need to define it in a new column in your table.

``````// In Power Query M
if [Cod. Gender] = "M" then 1
else if [Cod. Gender] = "F" then 2
else if [Cod. Gender] = "O" then 3
else 99

// OR

// In DAX
SWITCH(
[Cod. Gender],
"M", 1,
"F", 2,
"O", 3,
99
)``````

Once you have your new column, go the Data View

Select your [Gender] column, then go to the Column Tools tab > Sort by Column

Select your new sort key column from the list an the text values will now be sorted in order of your sort column instead.

Pete

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Super User

Hi @ale_BI ,

To get a custom sort order, you need to define it in a new column in your table.

``````// In Power Query M
if [Cod. Gender] = "M" then 1
else if [Cod. Gender] = "F" then 2
else if [Cod. Gender] = "O" then 3
else 99

// OR

// In DAX
SWITCH(
[Cod. Gender],
"M", 1,
"F", 2,
"O", 3,
99
)``````

Once you have your new column, go the Data View

Select your [Gender] column, then go to the Column Tools tab > Sort by Column

Select your new sort key column from the list an the text values will now be sorted in order of your sort column instead.

Pete

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.