Receive an answer to all your Power BI questions
You asked, we’re answering! During our “Dashboard in a Day” workshops, we receive insightful questions related to Power BI. To help you retain the knowledge and master those dashboards, we’re turning those frequently asked questions into this in-depth blog post that we will update on regular basis, and you can revisit at any time.
What if you have alphabetically sorted data, and you want to change that?
Let’s start with the default sort order of columns in Power BI and how you can work with this.
When you load your data throw it on a page, the data gets automatically sorted alphabetically. Which is fine if that is what you want it to do. But what if you don’t want to have alphabetically sorted data?
Let’s create a very interesting model, consisting of one table with the following data?
This data is already in the correct order that I want to see it in my report, so that solves it. Let’s put it in our report and done.
The data in my report however, is not in the correct order, it is sorted and no longer useful.
To solve this, we need to go back to the ‘Transform data’ screen and add a way to sort this data correctly.
Since the data is in the correct order when we pull it into power query, we can simply add an index, which will give us something to sort the values by.
A remark here is that the table you are going to sort by must have exactly one value for each of the values of the column you want to sort. In our example that is the case as each value is unique, so our index fits the requirement.
It is always easier and safer if you have a value in the source that you can use, but in lack of that, this index option is a good alternative
For our example, let’s ‘close and apply’ and go back to the report view.
Here we select the column we want to sort.
In the top bar, you can now choose ‘Sort by column’ and then select the column by which you want to sort it, ‘Index’ in our case.
Immediately the values in our table are reordered, though not each visual will reorder automatically. If it doesn’t, just remove the column and add it again.
With this trick at hand, we can enhance our reports and provide users with data in de order they expect it. And also in other ways then the “standard” alphabetically sorted data order.
Sparkle’s Data Analytics team host multiple “Dashboard in a Day” workshops (in Dutch and English) in association with Microsoft. We work according to best practises of Microsoft. And all our Power BI & Analytics experts are Microsoft certified.
