Visualising Forms data with Power BI
For DATA:Scotland 2019 we wanted to make it as easy as possible for attendees to leave session feedback for this year’s speakers. It’s an important part of being a speaker and helps us improve our skills.
At the same time, I didn’t want to waste hours of the team’s time, typing out feedback from paper forms. So I set out to automate as much of the process as possible and bring tools I’m already familiar with together: Microsoft Forms, Flow, SQL and Power BI.
Gathering the data
Logically, we built out the feedback forms in Microsoft Forms, with the hardest part deciding on what we wanted to know.
We then printed the link and QR code for the form on every attendee badge, in our speaker slide decks and in every session room. With this, we had about 40% engagement on the day which is pretty good, outside of paper collection.
Transforming the data
Forms is great for collecting all the data and there’s some nice summary graphs on the results page, but the output is Excel, the bane of my life.
Here’s where we bring Microsoft Flow and an Azure SQL database into the equation.
We can set up a new Flow with a Forms trigger so that when each response is submitted, it triggers the flow to run. There’s a catch though, we can’t just use the response that triggered the flow to run, we must use the response ID from the trigger to retrieve the response details. That’s the data the attendee submitted.
It’s also worth mentioning that my form was created as a Group Form so I didn’t get the option to select it from a list. I had to manually paste the FormID into the trigger from the Form’s URL.
Microsoft Flow also connects to a SQL instance directly so there’s no significant challenge here. We just need to create the destination table first and then map the fields in an Insert Row action in Flow.
If you’re having issues with fields from the form or the table you’ve created in SQL not showing in Flow, save your work, close it and re-open. There’s no refresh button here.
There’s a copy of the Flow JSON template file in my Github.
Visualising the data
With the data in SQL, the rest is straight forward. I wanted to visualise the feedback in Power BI, being a data event and that I like working with Power BI, it seemed a no-brainer.
I’ll share my (ok?) feedback here as an example:
I built out a dashboard page with the summary info on it like number of attendees, feedback provided and how satisfied attendees were with various aspects of the session, including the (apparently cold) rooms. I then dumped all the comments on the second page.
Again, the template for this is in Github. I’m not saying it’s pretty but it works 🤣
Sharing the data
The real pain here turned out to be sharing the data. I could easily PDF the Power BI report, though it takes about 30 seconds to 1 minute each time. The issue was that I had to manually do this for each speaker, adjusting my report filter, exporting to PDF, rinse and repeat.
Regrets?
In hindsight, this may have been better suited to a paginated report in Power BI or Reporting Services, like my co-organiser, Robert (@sql_bob) created for our speaker announcements. He blogged about it too!
If you’ve got any insights, tips or thoughts, please share them in the comments.
