Why your school NEEDS to try PowerBI (even if it’s just a little bit)

This week I’ve been experimenting with data sets in PowerBI to try and identify a range of schools in New Zealand. In former roles I’ve managed a BI team that has done considerable work with PowerBI but I have not been the primary constructor of dashboards and manipulating data sets. I say this because I want to emphasize how relatively easy it is to quickly build some useful visualizations for interacting.

In my case, I grabbed some publicly available data on NZ schools and downloaded it as a CSV file (Excel would be fine as well) and then used one of the many  data import options into PowerBI Desktop to “Get Data”:

The nice part about the Get Data process is that, if the import file is formatted or separated in anyway, then the data is automatically placed into columns during the preview stage of the import:

What PowerBI can’t do, of course, is easily identify or label the columns based on the data within them. This is important as otherwise when you start creating the visualisations you won’t easily know what the data is, they will simply be labeled “Column 1”, “Column 2” etc. Fortunately, it’s very easy to rename these:

With the data now in PowerBI, the fun part begins – visualizing it and creating slicers for easy selection and drill down into the data you’re wanting to find. Here’s a dashboard I created with the above data, showing Christchurch secondary schools:

There are a few things to note about the above:

• Schools are represented geographically and in different colours and sizes on the map.
  • Helpfully, the data from the website included the exact latitude and longitude of the schools, which PowerBI will easily use to accurately map. Without this, you can use suburb, postal code or street address (maps are powered by MS Bing Maps, which can struggle with smaller countries like NZ for accuracy)
  • The bubbles for each school is proportionate to the school roll size. Again, this data came with the CSV so I was able to use a visual filter to represent the number of students in each school
• I’ve created slicers on the right hand side based off the various data in the columns I imported from the CSV. In this case it allows the user to filter by:
  • School Name – if they’re looking for a specific school it will locate it by searching or scrolling through the list (I set the filter to allow multi-select so schools can be “compared” if required
  • Region – if a user wanted to see all schools in a certain region they could select this. The above screenshot is set to Christchurch
  • Decile – if a user wants to filter/locate schools based off a single Decile or a range, they can multi-select and only matching Decile schools will be displayed
  • School Type – again, a user may want to be looking at only Primary or Secondary schools, possibly Independent or Integrated – the School Type filter allows for this.
• I’ve used a matrix visualization to create a table in the bottom left that shows the schools matching the filtered criteria and includes useful information like the School Name, Principal’s Name, Region etc. This could be extended to include any field from the imported columns such as school website, phone number etc.

It took only about 30 minutes to create the above and then I decided to throw some Tertiary information in as well. The available list was a bit less detailed, but nevertheless I was able to create a quick selector for the various tertiary institutions:

The last thing to do is then publish it to PowerBI.com. With the built in PowerBI Connector Gateway this is as easy as pushing a button:

This is a high level overview of a pretty basic data set, but it shows that schools can grab data they already have (academic results, student home addresses etc) and start interacting with it. I’m planning to do more with this report above by getting internal Office365 usage data and start analyzing which schools are using / not using O365.

There are a few “gotchas” when creating reports in PowerBI that I’ll write about later, but the aim of this one is to inspire schools to give it a go.