PowerBI connectors for DfE data

[elJukes]

Hi

 

Not sure which forum to post this in, but seeing as PowerBI is mentioned here a lot I went with this one. I put together a couple of Power Query scripts that can be used in either PowerBI or Excel, to connect to the Get Information About Schools and Compare School Performance websites and download their latest national datasets.

 

The scripts are here and a freely available to anyone that wants to use them. There's a brief description in the ReadMe about how to change the PerformanceTables Data script to pull different datasets (ks2, ks4, ks5, census, spend per pupil, destinations etc)

 

Both of these work at the time of posting. All I ask is if you find any innovative uses for these or adapt them, then share what you've done. It's great seeing what others come up with!

[elJukes]

I've found a bug with the Performance Tables script when trying to download certain data sources. Need to check something with the DfE website, but will post here when it's all fixed.

[elJukes]

Some of the datasets published via the Compare School Performance website are only available as Excel files. To accomodate that the Performance Tables script has been updated to support sources in Excel format.

[elJukes]

I've adapted a SPARQL query I found on StackOverflow that looks up either the Decile, Rank or Score for any of the 10 IMD domains including IDACI for a postcode, and turned it into a PowerBI function.

 

Using the Invoke Custom Function option to add a column into a dataset such as student address details you can use the function to quickly get the IDACI ranking for each student in your school. More details can be found here

As before, if anyone does anything innovative or interesting with this please share it with the community. I'm going to have a play with maps and disadavantaged funding adjustments!

[Jonlloyd85]

I am fairly new to Powerbi, but I'm sure that I managed to get this working before the end of last term and now I can't remember how do it. Please can you post some instructions on how I can import this data please.

[elJukes]

Hi Jon

 

When you're in PowerBI click on the Edit Query button to bring up the query editor. If you right click on the left hand pane, select New Query and then Blank Query to create a new query with nothing in it. Once you've done that find the Advanced Editor button along the top bar to open a code editor for the blank query. Just copy and paste the code from GitHub into the query editor to get things rolling.

@Marci has spotted a potential issue with automatic refreshes that we're going to look at once the start of term madness has simmered down a bit.

 

Hope they help!

[Jonlloyd85]

Its all coming back to me now thank you very much