This article was originally published on SQL Shack.
In this article, I’m going to demonstrate how to sort months chronologically in Power BI. Visualizing sales data in a time period is one of the most influential ways of reporting. Often, at times it is essential that you design charts that show the trend or growth of a metric over time. The time period can be anything for example days, weeks, months, or years. It basically gives you the idea of how the metric has increased or decreased over the specific period.
In Power BI, you can also visualize your metrics by a specific selected period. This is usually implemented by drawing line charts that show the trend over time, or by using vertical bar charts that show specific metrics in the time period, or even simply using a tabular matrix to display the results textually. For the sake of this article, we will consider only the third scenario i.e. tabular matrix for simplicity. However, the same solution can be made to work for any type of chart which contains a time period in it.
Sometimes when you import data into Power BI, and specifically if you have months or quarters available as a textual data in the original data source (from a flat-file), then Power BI cannot understand whether the imported field is an actual date field (days/month/year, etc.) or just simple text data. In such a case, after the import, the months or quarters are sorted alphabetically, rather than chronologically which is an error depending on the requirements and something not so relevant in developing trend reports. In this solution, we will first learn how to reproduce the error, and then see how to make the necessary changes in the data model to resolve this error and sort months chronologically in Power BI.
Reproducing the Error
Let us first try to reproduce the error and then I shall explain how to sort the months chronologically in Power BI. In order to replicate the error, we need to create a simple CSV file as shown in the figure below.
Figure 1 – CSV Dataset
As you can see in the figure above, we just have two simple columns – Month and Sales. The Month lists all the values starting from “January” to “December” and corresponding Sales values along with it. Once you have created the CSV file, the next step is to open Power BI and connect it to this dataset.
Open Power BI Desktop and follow the steps below to fetch this data into the Power BI data model:
- Select on Get Data and select Text/CSV from the menu that appearsFigure 2 – Get Data in Power BI
- Browse for the file that you just created in the previous step and click on OpenFigure 3 – Browse for Dataset
- In the next dialog that appears, verify the data and click on LoadFigure 4 – Load data into Power BI Model
- You will see the data has been loaded into Power BI
- Click on Table from the Visualizations Pane and drag and drop the fields into a table as shown in the figure
- In the new table that is created, you can see that the months are now sorted alphabeticallyFigure 5 – Creating the table
- In the Power BI Desktop, select Transform Data and then click on Transform DataFigure 6 – Transform Data
- In the Power Query Editor that opens, navigate to the Add Column tab that opens
- Select Custom Column and enter the formula as shown in the figure below
- For the name of the custom column, I’m using “Date” since this column is going to store dummy date values
- In the formula for the custom column, use the following: = “1 ” & [Month] & ” 2020″Figure 7 – Adding Dummy Date Column
- So basically, what we are trying to achieve is just create a dummy date value by adding “1” as the date and “2020” as the year value to the month that already existsFigure 8 – New Date Column Added
- The next step is to convert this new field, Date to a date datatype. Right-click on the column and select Change Type and then select DateFigure 9 – Changing the Data Type
- You can see that the data type and values for this column have changedFigure 10 – Changed the Data Type
- We will now add one more custom column that will extract the month number from this field. Click on Add Columns and then on Custom Columns
- Provide the name of the field as “MonthNumber” and formula as “= Date.Month([Date])” and click OKFigure 11 – Adding new MonthNumber Column
- You’ll now see one more column added in the datasetFigure 12 – Added new MonthNumber Column
- We will change the data type of this field to the Whole Number. Right-click on the MonthNumber, select Change Type and then select the Whole NumberFigure 13 – Change the Data Type to Whole Number
- Since we have the desired field MonthNumber in our data model, we can remove the dummy date field that we created in our previous steps. Right-click on the Date column and select RemoveFigure 14 – Removing the dummy Date column
- Finally, you can close the Power Query Editor window by navigating to the Home and select Close and ApplyFigure 15 – Closing the Power Query Editor
- Click on the Data tab on the left-hand pane
- Select the Month column and then select Sort by Column in the Sort pane in the toolbar above
- In the drop-down that appears, select MonthNumber and navigate to the Reports tabFigure 16 – Sort Month By Month Number
- You can now see, that the months are being sorted chronologically as opposed to alphabeticallyFigure 17 – Months sorted chronologically in Power BI
Additionally, if you want to hide the MonthNumber field, you can just right click on it and select Hide.
Figure 18 – Hide MonthNumber field
You’ll have your dataset as was in the original CSV file but the months are sorted chronologically.
Figure 19 – Months Sorted Chronologically in Power BI
In this article, I have explained how to sort months chronologically in Power BI. I have also mentioned the steps to replicate the error and then provided a step-by-step guidance how to resolve the issue and finally get the desired results.