SORTING MONTHS CHRONOLOGICALLY IN POWERBI DESKTOP
4 min readDec 22, 2022
- Early last week, I was faced with a challenge while using Powerbi. The data I was asked to work on had a column that listed the names of months in the year. While I was done creating dashboards for the dataset, the dashboard didn’t make sense because the months could not be sorted chronologically. I tried sorting the axis by the month axis, but it didn’t work.
- One of the major challenge I had was the fact that the column that had the months were not in date format. They were in text format and they couldn’t be converted which made it a little bit difficult.
- After research, I found a solution which I’ll be writing about in today’s blog.
- The first thing to do is to go to your loaded dataset under the fields tab in Powerbi. Click on the three dots near the dataset name and click on Edit query. Once this is done, the Power Query Editor will be opened.
- In the Power Query Editor, click on the Add Column tab and click on Custom Column. A dialog box will be opened to add the name of the new column and the column formula. The whole essence of this step is to convert the original month column datatype from text to date.
- In my case, I named the new column Dummy Date. The custom column formula will then be written as: “1 ” & [Month] & ” 2022” as shown below.
N.B: The Month in the formula above should be the name of the column with the month attributes in your dataset. I used Month because the name of the column is Month.
- Click on OK after this. A new column showing date will be created as shown below.
- The next step is to convert the new column data type to date format. After this is done, follow the step above to create another custom column. Once the dialog box opens, add the new column name and formula which will be different from the first one.
- In my case, I named the new column Month Number. The custom column formula will then be written as: Date.Month([Dummy Date]) as shown below.
- Click on OK after this. A new column showing the calculated month number will be created. After this, change the data type of the Month Number column to Whole Number as shown below.
N.B: The Dummy Date in the formula above should be the name of the first custom column that was created which was Dummy Date in my case.
- You can then delete the first custom column created as it won’t be needed anymore. Right click on the column and click on Remove to delete it. After this, go to the Home tab and click on Close & Apply.
- Once it’s loaded with the changes applied, click on the chart you want to sort by months chronologically, go to the data pane at left of the visual.
N.B: Once this is done, make sure to confirm that you click the dataset that is associated with the chart you want to sort by the right side of the opened tab. This is important most especially when you have many datasets with similar columns in the same workbook. For example, if you click another dataset not associated with the chart, it won’t be sorted even though the whole process above has also been done on that dataset.
- After the above has been confirmed, click the month column header, under the column tools tab, click on the sort dropdown and click on Month Number as seen below and go back to the Report pane at the left of the tab (where your visual is). Automatically, the months will be sorted.
