If you have been using Power BI to create data visualisations for primary schools, you’ve probably run in to this problem: you want the x axis of your graph to run from nursery through reception to year 1 and then to year 6, but Power BI insists on alphabetical order and the whole thing looks like this:

Power BI uses the default sort order for the column of data we are graphing, and in this case it uses the default sort order for the Year taught in code column. Since it knows that the entries in this column are alphanumeric, the default sort order is also alphanumeric.

There are a few ways to fix this problem. Here’s the method I use (and a tip to avoid the problem at the bottom of the article)

• From the Report screen (the screen that display the graph), click on Edit Queries to open the Power Query Editor.
• Click on Enter Data and start to create a new table.
• Create two new columns, and give the column heading the names Year taught in code and Year index
• Fill out the Year taught in code exactly as it is in your PupilData table.
• The Year index column needs to contain integer number in the order in which you want the years to appear on year graphs.
• In my example, I started numbering N1 and N2 at -2 and -1 just so that Y1 to Y6 are numbered more intuitively.
• Here’s what it should look like:

• Click Close & Apply to return to your graph
• Click the Relationships tab on the left hand side of the screen

Once the two tables are linked, we can add the Year Index column to our main PupilData table. We can do this by adding a new column to PupilData and using a simple DAX formula, thus:

• Click on the Data tab and display the PupilData table.
• Scroll to the last column
• Click the Modelling tab at the top of the screen
• Click New Column to open the DAX formula row
• Overwrite the default formula with the following DAX formula

 YearIndex = RELATED(YearIndex[Year Index])

Click the tick next to the code box and your screen should look like this:

Finally highlight your original Year taught in code column and click the Sort by Column button.

• Sort by column allows you to specify how data in that column will be sorted.
• As you would expect, the default sort column for any given column is the column itself.
• But we can also specify a different column, so that’s what we’ll do here.
• Simply choose the new YearIndex column and all the visualisations that use Year taught in code on their axes will now be sorted by our YearIndex column instead.

Also works for reg groups, aspects and resultsets

You can use the same technique to order registration groups and resultsets. Although it can be fiddly to set up, once done it doesn’t need to be repeated.

But think about making changes to SIMS first

The format of your year groups and registration groups is difficult to change in SIMS. But aspect names and results set names can be easily changed in SIMS. So consider giving them names that are naturally in the right order and you will avoid having to make any changes in Power BI