Forum Discussion
Helper IOrdering 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.
Instead of looking like this:
It looks like this when I do the sort by:
Hi ale_BI ,
To get a custom sort order, you need to define it in a new column in your table.
For your scenario, you would add another column like this:
// 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
1 Reply
- BA_Pete
Super User
Hi ale_BI ,
To get a custom sort order, you need to define it in a new column in your table.
For your scenario, you would add another column like this:
// 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