Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kgriendling
Advocate I
Advocate I

How Do I Avoid Alphabetical Sorting?

Hi All,

 

Total BI Newbie here. I have a list of costs being fed into my report, and it auto-sorted to alphabetical. I want it to show as its native sorting. How do I control this? The arrow on the column just swaps the alphabetical from ascending to descending...

1 ACCEPTED SOLUTION

You need a numeric or alphabetic column that corresponds to that original sort order, then use that to sort.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

16 REPLIES 16
RBear
Advocate I
Advocate I

I find it hard to belive that PBI will have such a counterintuitive (and, under some circumstances, outright stupid) "feature" as a non-preventable sorting. So if I have a table where the first column is presented in some logical order (say, animals by ascending size - dog, horse, elephant), there is no way to preserve that order without resorting to all sorts of unnecessary gymnastics?

 

Is there really no way to prevent it?

KHorseman
Community Champion
Community Champion

@RBear I'm not sure what the difficulty is. If you want a non-alphanumeric sort order, provide another column that has that order. Then under Modeling, select Sort By Another Column and choose the sort order column. So in your example, you would create two columns:

 

Animal     SortOrder

Dog          1

Elephant   3

Horse       2

 

 

I don't know if that's what you're refering to as unnecessary gymnastics but I can't think of any simpler alternative.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






If you want a non-alphanumeric sort order, provide another column that has that order. Then under Modeling, select Sort By Another Column and choose the sort order column. So in your example, you would create two columns:

 

Animal     SortOrder

Dog          1

Elephant   3

Horse       2



Yes, this works, but it entails more than you let on - in a model that may be complicated enough already, you have to add a column, add it to the table in the report, make sure you click "Don't Summarize" and then go through the the fun process of hiding it. Of course, you should remember to document the whole process for the benefit of those who are going to maintain the model after you.

I don't know if that's what you're refering to as unnecessary gymnastics but I can't think of any simpler alternative.


A simpler alternative would be to just make sorting optional - as in adding "Don't Sort" just under "Don't Summarize"...

kgriendling
Advocate I
Advocate I

Hi, 

 

I am a newbie to Power BI. How do I sort my list of data in the TABLE visualization by the source spreadsheet, and not alphabetically?

 

Thanks,

 

Kevin

@kgriendling,

 

You coud create a sort columd based on your requirement, and then change the order in your table visual.

large.png

 

Regards,

Charlie Liao

Thanks Charlie.

 

This would work if I want it to sort by a certain column. But how do I get it to NOT sort. We have a specific order the original file is in, and we want it to show up that way, which is non-alphabetical nor numerical.

You need a numeric or alphabetic column that corresponds to that original sort order, then use that to sort.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I am no database expert but that seems like a major oversight to me... Wouldn't all programs start withOUT modification to the list, then allow users to modify the list with features? Odd. But I can do that... Thanks!

No, I wouldn't expect that. The only natural sort orders are alphanumeric or date. Any other order would need to be specified by some other piece of data, such as a row index number. A row index number would be a column in a database. If you don't have a column that corresponds to that piece of data, you don't have that sort order.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I have the same problem... I am trying to display text in a multi-row card or a table or a matrix or anything.  Power BI sorts my data alphabetically.  I do not want it sorted.  So I added a sortby column.  However, I cannot hide this sort column from the user.  

 

I sorted the data in the data view. But when I add it to a multi-row card or table PowerBi sorts it for me.

You can add a sort order column based on one or more column s that you want to sort on.  Add that column to the table in the report and sort by that column.  You can shrink the size of that column so it is not showing.  You cannot hide it but you can shrink it to nothing so it doesnt show anyway.

I agree, it would be nice to just display the data in the order in which Power BI received it.

"The order in which Power BI receives it" would be an arbitrary order unique to any given query or refresh. It would essentially be completely random. So that wouldn't help you.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




The order of the data is not always arbitrary in common, real-life settings. E.g. load a csv file, or make an SQL query with an "order by" clause. If the order is fixed in the source it is preserved in Power BI and sometimes you might want to trust that source. It's not a textbook un-ordered set of relations. So I don't see what is wrong with allowing the table's original order as an option.

 

The present solution here is: add an order column, go to the table view, sort you labels column by the order column -- but be careful, that is different from sorting the table itself by the order column even though the resulting table looks exactly the same... so make sure you click on your label column then go up to the columns tab and click the "sort by column" button there, not any of the other sorting options... This seems surpising to me for such a simple feature.

Hi @KHorseman,

 

That would greatly depend on the data source that you use. If you are using JSON for an examle, you might want to display the data in the exact order you received it as the data source might have already sorted it in a specific order that is not necessary chronologically or alpha-numerically sorted or sortable.

I have the data sorted in the query behind the visual - on multiple columns - so that's the order I want to see in the table as well.  Not the default behaviour which is to have the Table visual automatically sort the data for me automatically in ascending order on the first column.  Please add the "don't sort" option to the Table visual.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.