Sort Months Chronologically in Power BI

Aveek Das
Aveek Dashttps://datacloudmag.com
Data Engineer, Cloud Data Architect, Thinker, Traveler, Amateur Photographer. Enjoys short walks for hot chocolates. Blogs @ https://datacloudmag.com

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.

Sample Dataset for Sort Months Chronologically in Power BI

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:

  1. Select on Get Data and select Text/CSV from the menu that appearsGet Data in Power BIFigure 2 – Get Data in Power BI
  2. Browse for the file that you just created in the previous step and click on OpenBrowse Sort Months Chronologically in Power BI DatasetFigure 3 – Browse for Dataset
  3. In the next dialog that appears, verify the data and click on LoadLoad data into Power BI modelFigure 4 – Load data into Power BI Model
  4. You will see the data has been loaded into Power BI
  5. Click on Table from the Visualizations Pane and drag and drop the fields into a table as shown in the figure
  6. In the new table that is created, you can see that the months are now sorted alphabeticallySort Months Chronologically in Power BI tableFigure 5 – Creating the table
  7. In the Power BI Desktop, select Transform Data and then click on Transform DataFigure 6 – Transform Data
  8. In the Power Query Editor that opens, navigate to the Add Column tab that opens
  9. Select Custom Column and enter the formula as shown in the figure below
  10. For the name of the custom column, I’m using “Date” since this column is going to store dummy date values
  11. In the formula for the custom column, use the following: = “1 ” & [Month] & ” 2020″Figure 7 – Adding Dummy Date Column
  12. 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
  13. 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
  14. You can see that the data type and values for this column have changedFigure 10 – Changed the Data Type
  15. 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
  16. Provide the name of the field as “MonthNumber” and formula as “= Date.Month([Date])” and click OKFigure 11 – Adding new MonthNumber Column
  17. You’ll now see one more column added in the datasetFigure 12 – Added new MonthNumber Column
  18. 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
  19. 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
  20. Finally, you can close the Power Query Editor window by navigating to the Home and select Close and ApplyClosing the Power Query EditorFigure 15 – Closing the Power Query Editor
  21. Click on the Data tab on the left-hand pane
  22. Select the Month column and then select Sort by Column in the Sort pane in the toolbar above
  23. In the drop-down that appears, select MonthNumber and navigate to the Reports tabFigure 16 – Sort Month By Month Number
  24. You can now see, that the months are being sorted chronologically as opposed to alphabeticallyMonths sorted chronologically in Power BIFigure 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

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 a step-by-step guidance how to resolve the issue and finally get the desired results.

Recommended

Popular