How to sort months chronologically in Power BI
Learn how to sort month fields in chronological order within Power BI
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.
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 appears.
Browse for the file that you just created in the previous step and click on Open.
In the next dialog that appears, verify the data and click on Load.
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 alphabetically.
In the Power BI Desktop, select Transform Data and then click on 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 exists.
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 Date.
You can see that the data type and values for this column have changed.
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 OK.
You’ll now see one more column added in the dataset.
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 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 Remove.
Finally, you can close the Power Query Editor window by navigating to the Home and select Close and Apply.
Selecting Column to Sort Months Chronologically in Power BI
Now that we have made the necessary changes in our data model to sort months chronologically in Power BI, the final step is to set the sorting order on the Month in ascending order of MonthNumber. Please follow the steps below to sort the months.
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 tab.
You can now see, that the months are being sorted chronologically as opposed to alphabetically.
Additionally, if you want to hide the MonthNumber field, you can just right-click on it and select Hide.
You’ll have your dataset as was in the original CSV file but the months are sorted chronologically.
Conclusion
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 step-by-step guidance on how to resolve the issue and finally get the desired results.
Originally published at https://www.sqlshack.com on March 16, 2020.
