Infinity Group recently designed a Power BI report to showcase how you can setup Power Bi to provide actionable, real time insight from any survey’s you undertake within your business. Here is a step by step guide showing you how this can be achieved.
If you have not already seen our Power BI Introduction Survey and the Public Power BI Report that shows the results you can go off and have a play and then come back to discover how we put it together.
The technologies used in this project are:
- Microsoft Forms Pro – 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
This is also a demonstration of how well these technologies work together.
Creating the survey
Step 1 – Create the survey using Microsoft Forms Pro – a survey tool with AI-assisted insights
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 Pro does also support branching rules 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.
Step 3 Under sharing get the link and QR code for later use in the Power BI report.
Forms Pro 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 1 – Prepare a central location to store the survey results.
We needed a location that was going to be accessible from Power BI. A SharePoint list was chosen as this is 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 1 – 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.
The flow is triggered on survey response submitted
Step 2 Get response details
Step 3 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. This is called for each of the free text fields.
Step 4 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. Monitor your business and get answers quickly with rich dashboards available on every device.
Step 1 – Create the Power BI report using Power BI Desktop Application
Key points 1 – Word Clouds
For maximum visual impact I wanted to display the results of the free text fields in word clouds
Word Clouds are a free custom visual from AppSource.
Step 2 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 3 – 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 but is just a different format that gives a different perspective and allows a greater understanding and comparison of the responses.
Step 4 The ratings are another free custom visual from AppSource.
Step 5 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. I am aware that many people do like to see a list of figures so I included an example of this.
Step 6 The measures are calculated as follows. This was the only DAX needed for this report.
Getting data into Power BI
Step 1 Data was retrieved 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.
A small amount of data transformation was needed to get the data into a good shape for the report.
For example, if a question in the survey is not filled in then 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 2 Adding custom sort order columns
So that the Bar Charts can be sorted in a custom sequence Yes, A little, No, as follows;
Step 3 The data for the word clouds needed to be split 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 4 In Power Query we used the transform ‘split by delimiter’ to put each item into its own column.
Step 5 Then finally transform the multiple columns into separate rows of data with the ‘unpivot’ transform.
Step 6 This data is now in a fit state to be placed 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.
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 really could not be simpler and is very intuitive.
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 post showed one technique that 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 getting survey response into Power BI (via live streaming data or Forms from Sharepoint) which we will cover in future posts.
If you want to know more about the Microsoft Power Platform and Power BI for surveys please get in touch.
