Automating data input from Microsoft Forms to PowerBI for school surveys

In a previous post I wrote about the value of utilising pupil voice surveys to help identify areas that could be improved within a school. Integrating this data into PowerBI improves the quality of analysis, makes it easily accessible for colleagues and can minimise the maintenance that’s required through automating much of the processing.

In this post, I’ll go through how to get the data into PowerBI Desktop. Examples of how to create the report pages and setting up Row Level Security will follow later. The steps required include:

  1. Create the survey form
  2. Add the survey form to a SharePoint site
  3. Use Power Automate to copy the Excel results file (this solved a problem in the PowerBI where data from the survey didn’t show on occasion, possibly when a new survey response was added)
  4. Connect a dataflow (with scheduled refresh) for the Excel file in SharePoint
  5. Use Power Query to transform the data
  6. Add the transformed survey results to the PowerBI model and create relationships between the tables
  7. Create relevant measures
  8. Build the report views
  9. Set up Row Level Security (RLS)

Create the survey form

First up, go to Microsoft Forms and build your survey. I have used sections and branching to improve the user experience and keep it more organised. For example, the basic info such as subject and teacher is within one section, whilst questions are grouped into other sections based on what they are asking i.e. questions about feedback, learning environment and so on. When completing the form, each section appears on a new page helping to keep it tidy.

Using sections in Microsoft Forms to keep it organised

Question branching allows you to determine the pathway a user takes through the survey. For example, if a pupil chooses ‘geography’ for the subject, they will then be taken to the question that asks them to select their geography teacher. They won’t see teacher names for any other subject thus reducing the chance of errors and making it quicker and easier to complete.

Setting up question branching guides the user to the relevant parts of the form based upon their responses to each question

The majority of the questions pupils respond to are the likert style. This allows you to have multiple statements and response values (e.g. always, often and so on).

The Likert question style is easy to set up to get responses based on a range of statements

In the form settings, I only allow people from within my organisation to complete the survey and I record the names. In doing so, each pupils’ name and email address is recorded alongside their responses allowing more detailed analysis to take place. If you want a survey to be anonymous, then deselect this option.

Use the settings to determine who can respond and whether it is anonymous or not

Adding the form to a SharePoint site

This is the critical step required to allow the process to be entirely automated. By adding the form to a SharePoint site, the Excel results file is saved into the document library of that SharePoint site and is updated as new survey responses are added. We can connect to this file via a data flow to bring the responses into PowerBI automatically using a scheduled refresh.

To add the form to a SharePoint site you need to do the following:

  1. Go to Forms
  2. Select ‘All my forms’ towards the bottom right of the screen
  3. Click the ‘three dots’ on the survey form
  4. Select ‘Move’
  5. Choose the SharePoint site you want to add the form too (on the right of the screen)
  6. When you view your survey responses in the form, you’ll see the results file has a cloud icon indicating that it is online and you can view the results in your browser.

Please note, that unless you set up specific permissions for the results file, all members of the SharePoint site will have access to it enabling them to see the raw data. To keep all of our PowerBI data that is in Excel/CSV files safe, I have created a separate SharePoint site that only the relevant ‘data people’ have access to.

Use Power Automate to copy the results file

This is a workaround that I created to solve a problem when viewing the survey results in our PowerBI data app. Sometimes the data would appear, other times it wouldn’t. For some reason, the dataflow would be pulling in no data at times, whilst on other occasions it worked fine. My best guess is that if new responses are added to the survey at the same time as a dataflow refresh is happening then no data is taken from the results file.

To get around this, I used a simple Power Automate flow to copy the results file to the same location when any new data was added. The dataflow in PowerBI service connects to the copy of the file rather than the original. After this, it all worked fine.

Using Power Automate to create a workaround for a possible issue when the survey data is refreshed

Connect a dataflow to the Excel file in SharePoint

I’ve written about how to do this before at this page so I won’t go into detail here. To reiterate, there are two significant things about connecting to the Excel file in Sharepoint via a dataflow:

  1. Automatic updates can happen. There is no need to download any Excel files to your harddrive, add new survey results to a master file and upload it each day.
  2. It is possible to set a scheduled refresh for a dataflow so that our data is only ever a few hours old at any point during the school day.

Use PowerQuery to transform the data

Now we’re getting a bit more technical. The format that the survey results are in is not useful for our data analysis. It needs some cleaning up which can be done in Power Query. Initially, every question has a separate column producing a very wide table. We need to make it taller rather than wide by doing a merge for teacher names and unpivoting the statements and responses.

If you want to have a go at following the steps with the same data, you can download the Excel file here:

The annotated Excel sheet below has sample data for only a small selection of questions that pupil would answer in the format that it comes from Forms. The annotations show the transformations that need to take place.

Sample data in the format that it comes from Microsoft Forms with annotations to illustrate the transformations that need to take place using Power Query to get it into shape for analysis in PowerBI.

In more detail, here are the steps I have used to transform this data:

  1. Remove Start time column
  2. Change Completion time column data type to date
  3. Change Completion time column name to Date
  4. Merge Select your teacher columns and name the new column Teacher
  5. Use the merge queries function (the same as a lookup in Power Query language) to add the StaffID from a Staff details table using the Teacher column
  6. Remove Teacher column
  7. Use the merge queries function (the same as a lookup in Power Query language) to add the StudentID from a Student details table using the Email column
  8. Remove the email and pupil name columns
  9. Duplicate the table and rename to Statement-Responses
  10. In the new table (Statement-Responses), remove all columns except the ID and statement columns (shown in yellow)
  11. Select all the statement columns and unpivot them (alternatively, select the ID column and use the unpivot other columns function)
  12. Rename the Attribute column Survey statement and the Values column as Response
  13. Remove any blank rows in the Values column. If you have modified the survey form at all, old questions remain in the Excel sheet even though they don’t appear on the form. Removing blank rows will clear these from the table.
  14. Use the conditional column function to create a new column called Response value where a response of Always = 5, Mostly = 4, Sometimes = 3, Rarely = 2, and Never = 1
  15. Check the data type for each column is correct; text for all, except the Response value column which should be whole number
  16. Return to the original table, select the statement columns and remove them
  17. Change column names as required e.g. Subject, Year group (at time of survey)
  18. Check the data types for each column are all correct
  19. Save and load the tables into PowerBI

When it’s all done, you should have a couple of tables that look like this:

Please note, that there are a few other transformations I do here to link it to the rest of my data model (including a lookup of subject names to ensure consistency across all tables in the model and creating a compound key). How you connect this data to your data model will depend upon how your model is organised.

Once the data is imported to PowerBI Desktop, you need to establish the correct relationships between the relevant tables. In this example, it should look like this:

Relationships between the dimension tables (PupilIDs and StaffIDs) and the fact tables (Pupil survey data and Statement-Responses)

You’re now ready to create some measures and start making some visuals!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s