Many schools are using Microsoft Forms to gather the results of questionnaires sent out to parents, staff and pupils. Schools can also create forms that allow surveys, quizzes, and polls and even formative assessment, with different levels of difficulty that are dynamically assigned based on the answers pupil give to certain questions. The forms that it creates are easy to share by URL or email, and look just as good on a mobile phone as they do on a PC desktop. Microsoft Forms is also part of the MS365 licenses already owned by many schools, so there is usually no extra cost associated with using it.

Here a live link to an example form: https://forms.office.com/r/SLMjqVy5AA

Microsoft Forms uses Excel to gather the results and provides some good analysis from within the MS Foms app itself. However, there are some advantages to analysing the data in Power BI, namely:

• Results can be integrated with other data. So you can cross reference staff opinion on the effectiveness of your target setting with an analysis of the actual targets set for pupils
• You can use the full range of Power BI visualizations – bar charts, pie charts, tree maps, decomposition trees etc
• You can create a ‘livefeed’ link directly between your Forms and Power BI, so that changes are automatically and regularly fed through to your Power BI analysis.

Integrating Power BI and Microsoft Forms – the easy way

The easiest way to link your MS Forms responses with a Power BI report is to create your Form entirely in SharePoint from the get-go, then get the link the spreadsheet’s URL to Power BI’s web connector. Here’s how:

Go to Office.com (1) and click the Sharepoint icon (2)

Open your school site, navigate to Documents (1). I recommend you create a new folder by clicking the New button (2). I’ve called my folder ‘MS Forms’ (3).

In your new folder, click the New button (1) and choose Forms for Excel (2)

Now create your form. This article won’t go into details about creating your form, that’s out of scope, but as you add questions, here are a few tips:

• Power BI gives you more options when the responses are numbers rather than text values.
• If the responses are text values all Power BI can offer is to count up the number of responses of each type.
• Choose numeric responses where possible.

• Ratings give your users the opportunity to awards a star rating to your question, which is recorded as a number 1 to 5.
• Likert questions are extremely good for schools as they allow for full sentence statements which users can rate across a range (for example ‘Strongly Agree’ to ‘Strongly Disagree’). Tip: You can use the numbers between 1 and 5 as column headings instead of Agree/Disagree.
• Net Promoter Scores are also useful, as they allow answers between 1 and 10

Create your form, and test it using the Preview function. Enter several demo responses – or release your Form into the wild and wait for the users to repond!

How to connect the responses to power BI – the easy way

If you created the form directly in SharePoint as per the above method, the next bit is easy. Click Preview (1) then click Open in Excel (2).

This will open the responses in the web (online) version of Excel. We need to open the file in the app version (the old-fashioned version of excel that you run from your local PC) so click Editing (1) and then Open in Desktop App (2)

Excel Desktop will load, with the data in the ‘Form’ tab at the bottom (you may need to select this tab to see your data). From the main menu ribbon at the top of Excel, choose File and Info (1). Then click Copy Path (2)

This will copy a special file path to your clipboard. You might want to paste the path into a simple text editor like notepad. It should looks something like this:

(I’ve blurred out part of the address above to protect the innocent)

Remove the ?web=1 bit from the end, like this:

Now copy the URL back into the clipboard and load Power BI. Click on Get Data (1) and Web (2)

Paste in the URL and click OK

At this point you may need to re-enter your microsoft credentials, but after you have done this you should get this screen, from which you can navigate to Form (1) where you will see your data. and then load it into Power BI (2)

And that’s it! I’ll leave you to analyse the data in whatever way you choose. This is a live connection, so every time you press the refresh button in Power BI desktop the data will be updated directly from the spreadsheet. If you publish your data to the Power BI cloud, it doesn’t need a Power BI gateway and you can set an auto refresh frequency (up to 8 times a day for Power BI Pro licenses).