How to setup Power BI for survey data
When creating surveys, you want to be able to show the results easily and meaningfully. This makes it easier to gain actionable insights and key takeaways.
Fortunately, using Power BI for survey data offers real-time, clear insight, using any questionnaires or surveys your business undertakes.
This step-by-step guide will show you how to achieve this.
The technologies used in this project are:
- Microsoft Forms – to create the survey
- Microsoft Power Automate – to process the survey responses
- Microsoft SharePoint – to store the survey responses
- Microsoft Power BI – to publicly display the responses
Creating the survey
Step 1: Create the survey using Microsoft Forms.
Alternatively, you can use Dynamics 365 Customer Voice, which is an enterprise-level feedback management system. While it’s not free, unlike Forms, it offers advanced features and can also integrate directly with Power BI.
There are many options available when configuring survey questions. We chose a few of the question types that were appropriate for our needs.
It is worth noting that Forms does support branching logic so you can construct a more interactive survey displaying only questions that are relevant to the responders.
Step 2: We needed the survey to be publicly accessible so we adjusted the settings so anyone can submit a response. To do this, go into ‘Collect responses’ and select ‘Anyone can respond’.
Step 3: Under sharing, get the link and QR code for later use in the Power BI report.
Forms does have response analysis built-in as you can see from the following screenshots.
However, the response charts above are only available internally and we wanted to surface the results on a public facing webpage.
This is where Power Automate, SharePoint and Power BI come in.
Data store
Step 4: Prepare a central location to store the survey results.
We needed a location that was going to be accessible from Power BI. We chose a SharePoint list as it’s accessible from Power Automate and can be used as a data source directly from Power BI.
We created all the necessary fields in a SharePoint List. For maximum flexibility we made all the columns text fields. We can transform them as necessary using Power Query in Power BI later.
Here is a screenshot of part of the SharePoint List containing some results.
Automation
Step 5: Create a Power Automate workflow to push the results into SharePoint.
Power Automate was the obvious choice for processing the results of the survey.
There is a template flow that is triggered when a survey is submitted. This makes automating processes even faster.
A survey response will trigger the flow.
Step 6: Get response details.
Step 7: On the survey, there are text fields that are open for anyone to enter any data. Since we are going to process and redisplay that data in the resulting report, it makes sense to sanitise the data.
We chose to call a free web API from PurgoMalum to remove banned words. We applied this to each of the free text fields.
Step 8: The final step in the flow is to push the data into the SharePoint List, using the SharePoint Create Item action.
Power BI Report
What is Microsoft Power BI?
Power BI is a suite of business analytics tools to analyse data and share insights. It allows you to monitor your business and get answers quickly with rich dashboards available on every device. Due to this, Power BI is ideal for survey data.
Step 9: Create the Power BI report using Power BI Desktop Application
Key points 1 – Word Clouds
For maximum visual impact, we wanted to display the results of the free text fields in word clouds.
Word Clouds are a free custom visual from AppSource.
Step 10: We imported custom visuals from AppSource, a marketplace with hundreds of free and paid for visuals which you can easily import to use in reports in no time.
Step 11: Toggle visuals. The red button on this report uses ‘bookmarks’ to toggle the Word Cloud and Bar Chart visibility.
This report view is based on exactly the same data as the word clouds, just in a different format that gives a different perspective and allows a greater understanding and comparison of the responses.
Step 12: The ratings are another free custom visual from AppSource.
Step 13: Apply your company branding.
You will have noticed by now that all visuals in Power BI have the ability to be customised to match your corporate image. All backgrounds were created by exporting PowerPoint slides to PNG files. All images were downloaded from Pexels.
The statistics page is just a matrix showing each of the measures. However, many people do like to see a list of figures so we’ve included an example of this below.
Step 14: The measures are calculated as follows. This was the only DAX needed for this report.
Getting data into Power BI
Step 15: Next, we retrieved data from SharePoint using the SharePoint connector. Connectors make it very easy to consume data from various data sources. There are many connectors to all sorts of data.
You will need to transform the data to get it into a good shape for the report.
For example, if a question in the survey isn’t filled in, the SharePoint List field will contain the value ‘no input’. It is very easy in Power Query to replace those values with something that makes more sense to your data model.
Step 16: Add custom sort order columns.
You can now sort bar charts in a custom sequence, as follows:
Step 16: Next, split the data for the word clouds into their own tables using the following transformations:
- Split column by delimiter
- Unpivot
The raw data from the survey for a multi-select option question looks like this comma separated list.
Step 17: In Power Query, we used the transform ‘split by delimiter’ to put each item into its own column.
Step 18: Then, finally transform the multiple columns into separate rows of data with the ‘unpivot’ transform.
Step 19: This data is now in a fit state. You can place in the word cloud visual.
Power BI Mobile
Another key feature of Power BI is the ability to show reports on mobile devices. It’s very easy to create a mobile friendly view. Here’s a short step-by-step guide on how to do it.
Step 1: Click on Phone layout view.
Step 2: Drag the previously created visuals onto the phone canvas.
Step 3: Adjust the size of the visuals. This is very intuitive, so should be simple to do.
Step 4: Publish report to Power BI Service.
Step 5: Configure data refresh schedule.
Step 6: Publish the report to the web.
Step 7: Close the feedback loop from survey to the report results
Step 8: Update the survey thank you message with the link to the live report
Conclusion
Microsoft has made it really simple for these technologies to work together. This is one technique to allowed some advanced sting manipulation to be included in Power Automate.
If profanity filtering is not important to you, because you are working on a purely internal system, then there are other even simpler techniques for using Power BI for survey data (via live streaming data or Forms from SharePoint) which we will cover in future posts.
Find out more about Microsoft Power Platform
Power BI sits in the wider Microsoft Power Platform, offering various solutions to drive innovation and agility across your organisation. Core tools include:
- Power BI – enabling you to end data siloes and share meaningful insights across teams, with visually-engaging representations of your data
- Power Apps – quickly create expert apps for customised solutions for your business problems, without the need for external development resource
- Power Pages – build customer and stakeholder-facing websites to share data and allow people to self-serve, without expensive website build costs
- Power Automate – automate manual workflows in your operations to save teams valuable time
- Copilot Studio – create your own AI bots to automate tasks and drive efficiency across processes
To find out more about Power Platform and how Infinity Group can help you realise its rewards in your organisation, visit our dedicated Power Platform page.
