Power BI in schools - prototype dashboards with MS Excel

16/09/18

A quick way to get started with Microsoft Power BI is to connect to data in MS Excel spreadsheets. Spreadsheet data is easy to import and it means you can focus your efforts on dashboard design - prototyping ideas before sharing them with colleagues.

Here are five things to think about when using Microsoft Power BI with data stored in MS Excel spreadsheets:

  1. Put data into ‘Tables’ in MS Excel - do this before you try to connect from Power BI. Most people never use tables in Excel and miss this step when they use Power BI for the first time.

    Highlight your data in Excel, click Insert on the ribbon menu at the top and then click ‘Table’.


    Tables in Excel

    Give the table a meaningful name - something that will make sense when you see it in Power BI.

  2. Check column formats in Excel. I’ve noticed that data in Excel that’s come from somewhere else - maybe an export from a student record system - is often formatted as text. Things work better in Power BI if you format dates as dates, numbers as numbers. Do that in Excel before you do the import into Power BI.

  3. Create simple flat tables - column headings only, not row headings. This step is second nature to a software developer but I think it’s less obvious to someone who doesn’t fiddle with data for a living. The tables below illustrate this. You want tables that look like Option A - you get lots more flexibility and you can still ‘pivot’ to Option B in Power BI.


    excel tables for Power BI


  4. Put different tables on different sheets in the Excel workbook. Just housekeeping but it’s makes it a bit easier to stay organised, particularly if you start adding more rows to your Excel tables before doing a data refresh.

  5. Use the Relationships tab in Power BI to link tables. Power BI does this automatically when it finds matching columns. It does a good job but you should check the relationships and add any that Power BI has missed.

Get the data into Power BI, build some dashboards and work out exactly what it is that you want to see and what it is you want to share with colleagues. The next step is then to decide a between long term solution built on spreadsheets or a Power BI solution that connects to a bespoke reporting database.