Virtual Quizzes with the Power Platform
You can build “data-driven business solutions” to solve practical problems with the Microsoft Power Platform. You can also build fun solutions to ease the side effects of a global pandemic too.
A Friday afternoon teams meeting titled “Isolation Chat” between myself, Alice Drummond & Christian Borovac. The agenda? “catching up and sharing ideas to keep our spirits up and the community connected“. Alice & Christian came well prepared with wine, I bought a bright yellow rain coat, some headphones and a large rock (now known as the great ideas rock). Less than an hour later the Power Platform Pub Quiz #PPPQ initiative was born.
Within a few weeks the date for the first #PPPQ was announced. Along the way we roped in our Power BI Unicorn friend Greg Nash; got awesome advice from NZ365 guy (aka Mark Smith) a seasoned Power Platform Pub Quiz master; bought in the support and sponsorship from the wonderful Linda Do on behalf of Microsoft; were overwhelmed with the community response as individuals across the world contributed questions to fuel the quiz.
What’s #PPPQ?
A live virtual Power Platform Pub Quiz to challenge everyone’s Power Platform knowledge including Power BI, Power Apps, Power Automate, Dynamics 365, Power Virtual Agents, Artificial Intelligence oh and some actual pub trivia along the way too #PPPQ. More details on how to join us for the next quiz event -> here
Translating a Pub Quiz into a Virtual World
The basic premise of a pub quiz is pretty simple. You have some teams, you ask some questions, you calculate their scores and you have a winner. All translates pretty easy to a virtual world except two bits:
Calculating scores
Teams collaborating to agree an answer
There are many ways to overcome this but the focus was to keep it as low cost, simple and reliable as possible.
Solution Overview
The Power Platform solution for #PPPQ
The Team Collaboration Saga
In a traditional Pub Quiz you are huddled with your team around a table with you paper & pen, conversation is easy, anyone can pick up the pen, you can debate answers and the quiz master interrupts you with the next question when its time. Having just a team captain who submits the answers is one possible way but when the team is co-located across the world and the questions are being delivered live via a video conference with time restrictions it makes the collaboration a bit harder. If the teams can make that work its great maybe a separate teams call for example. Again - logistics!
We needed to make sure there was the option for each team member to submit their own answers, and their score be counted as a team.
The Team Collaboration Solution: Forms Quiz & Power BI to the rescue!
Forms Quiz
Microsoft Forms is well known for its survey functionality but did you know it also does Quizzes too? (Credits to Dilyana Radulova for her LinkedIn post about this). You can choose from a variety of questions types such as single/multiple-choice, text, numbers, dates etc. The screenshots show a few of the personal highlights for me. The quiz can be shared in many ways but for our purposes a link via email was sufficient - now each team member can submit their answers.
Automatically shuffle answers; Allocate points per question; Mark correct answer
Single choice questions as Radio Button or Drop Down
Team sizes can vary from 1-4 people, not everyone has to submit their answers, nor will they have to submit for each round - so we need to ensure that each teams ‘overall’ score is fairly weighted based on how many members submitted their answers. So lets look at our options:
CDS roll-up/calculated fields but they only refresh every 12 hours and the averaging wasn’t easy to crack using a dynamic number such as ‘total submissions per round/team’
Power Automate definitely possible but it would involve some loops and logical thinking, plus they are not real time so I didn’t have much appetite for this
CDS real-time workflow definitely possible but would need some additional workflow plugins to total up child records, loops and logical thinking which i still didn’t fancy
Power BI calculated columns can quickly and easily calculate the average total points based on the number of submissions per round/team
Power BI
The data from CDS is extracted into Power BI where the Power BI Unicorn Greg Nash worked his magic. Answers and Points are separated into two fact (transaction) tables, with Round, Question, and Team joined in as dimensions (descriptive entities). This ensures we can select any combination of Round/Question/Team and it will return the corresponding Answers and Points.
The tables in CDS are extracted and modeled into the following data model
We created two simple measures:
Total Points - SUM(Points[Points])
Submissions - DISTINCTCOUNT(Points[AnswerID])
Which where then used to calculate the “Team Average Points” in the Points table with the following DAX formula which divides the sum of the total points for the round by the number of submissions for the round:
Team Average Points = AVERAGEX('Round',DIVIDE([Total Points],[Submissions]))
The AVERAGEX() wrapper means that we show the “overall average” (if we ever aggregate it) and therefore can also compare the current team score to the overall average.
The Scoring Saga
Traditional Pub Quiz - after each round you pass round the answer sheets and mark for another team, the quiz master collects the results and you are onto the next round.
Virtual Quiz - no paper, no pens, no ‘passing it on’ to your neighbor. Sure its possible but it became a potential logistical nightmare plus at risk of dodgy internet connections we were at risk of spending more time reading answers and collecting scores. Alternatively we could mark them ourselves but again that’s a timely boring job saved for primary school teachers.
We needed live automated scoring at the end of each round to make sure time was spent actually having some quiz related fun
The Scoring Solution: Forms Quiz, Microsoft Flow for Excel & CDS to the rescue!
Forms Quiz
Quiz has automated points scoring. Assuming you set it up correctly by marking the correct answers and allocating points - it captures the points scored per questions and also per quiz (i.e. points per round). There are some nice analytics and a few ways to access the results:
‘Review Answers’ allows you to review answer by submission or by specific answer
‘Post Scores’ an overall leader-board view of total points per submission
‘Open in Excel’ extracts each submission as a single line including the answers, points per answer and total points
However, since we are sending the quiz to individuals outside of the organisations the responses all come back as anonymous there named as ‘Respondent 1’, ‘Respondent 2’ etc. plus there are potentially multiple submission per team so none of these really allow for round by round score updates.
Power Automate is good friends with Microsoft Forms, we explored two options:
Option 1 - Automated Flow triggered when a form is submitted
I had no doubts that this would work a treat but much to my sadness there are two limitations to this connector which made it a no go - Points/Total Points are not returned as outputs and there was a significant time lag from submission to flow run.
Option 2 - Instant Flow triggered from a selected row in Excel
I had never heard of or used Excel as a trigger before so an exciting investigation was born.You need to install an Office add-in called ‘Microsoft Flow for Excel’ to trigger a flow from Excel. You can capture user inputs to pass to the flow (similar to the power Apps trigger). The flow can be run on one or many rows in the table based on which ones you have selected, each row is a separate unique flow run. This way of trigger flow is really cool and opens up some exciting new doors for Power Automate but there are a few limitations to bear in mind:
The Excel sheet must be stored in SharePoint or OneDrive for Business and accessed via Excel Online
Each flow is hard coded to a specific sheet and table (and can only be triggered from there - nowhere else)
The add-in still shows as in preview but it is unclear whether the functionality is GA ready or not
It’s pretty intuitive but more info on how to use the trigger from excel is available on Microsoft docs/blogs [1]
To add the add-in open excel online Insert-> Office Add-ins -> Store -> Search/Add Microsoft Flow for Excel. you will then see a new tab called ‘Data’.
NOTE: the add-in drops out every time you close and re-open excel. Go to Insert-> Office Add-ins -> My Add-ins -> Select Microsoft Flow for Excel.
So how do we link the quiz result back to the correct team, and capture the data in way that it can be easily accessed for a Power BI leader board? CDS of course!
A very simplified ERD of the data structure for #PPPQ
CDS
A single ‘Quiz Response’ record is created for each Forms Quiz submission which is linked to the Team whom the respondent belongs to and the Round of the quiz which the results are for.
Team leverages the out of the box entity know as ‘Account’ in CDS
Round contains a set of master data detailing the round number, the name of the round and the maximum number of points this round can accommodate - this allows the solution to flex to an infinite about or rounds without any code changes
Quiz Result the record created by Power Automate, it is linked to the team and the round. Answers and total points are stored in individuals fields and it is currently hard coded to support up to 15 answers - configuration would be required to support more answers per round. The name of the submitter is stored as free text. Admittedly its not the prettiest data structure but for the task at hand it is more than sufficient.
Quiz Response in CDS created by Power Automate
Round data in CDS
Power Automate Instant Flow
You can actually build your flow from Excel online too either from a template or blank which is pretty cool. Go to Data -> Flow -> Create New
Lets look at how we user Power Automate to get rows are respondent data excel into CDS as Quiz results. Images for each step can be found in the gallery and description follows below
Trigger flow from a selected row in Excel
Select the spreadsheet of choice from your One Drive/SharePoint storage & define the table which will be used (you will need to create a template for this before configuring in the flow). Add an input to capture which round the quiz results are for - this allows us to reuse the same flow for each round and therefore the solution to flex to an infinite about or rounds without any code changes.
Initialize Variable ‘Team Name’
Passed from Forms Quiz
Initialize Variable ‘Total Points’
Passed from Forms Quiz
List Records - Rounds
This performs a lookup on the ‘Rounds’ reference data to retrieve the ‘Round’ record from CDS which relates to the correct round number passed in from (1)
cr2fa_roundnumber eq 'ROUND NUMBER'Compose Round ID
As the list records function is designed to return one or many records it will instate ‘for each’ loops every time a variable from the action is used, which makes sense if I had many records, but in this case I just want the one. So this ‘Compose’ function captures the GUID of the individual record we want to use
first(outputs('Lookup_Round_CDS')?['body/value'])?['cr2fa_roundid']Get Records - Round
Then using the GUID from (5) we get the actual record here
List Records - Team
Same as (4) but for ‘Teams’ (i.e. accounts)
name eq 'TEAM NAME'Compose Account ID
Same as (5) but for ‘Teams’ (i.e. accounts)
first(outputs('Find_Team_(Account)_in_CDS')?['body/value'])?['accountid']
Get Records - Team
Same as (6) but for ‘Teams’ (i.e. accounts)
Create Record - Quiz Result
We have done all the hard work in steps 1-9 so using the outputs from (1) Trigger from a selected row in Excel, & (6) Get Records - Round & (8) Get Records - Team we can populate the record in CDS. Each question from the quiz returns six values in the flow, ensure you use the (formatted) value otherwise its likely to cause conflicts with CDS fields and their data types.
Update a Row in Excel a) Success & b) Error
A very low fidelity approach to error handling/feedback from the flow - without having to go to Power Automate flow run manager to interrogate any failures. This little piece writes back to the excel sheet with a success/error message for each row the flow is run on. Using the run after parallel branch for any errors/failures.
Six values per question returned from Forms Quiz via the Excel trigger
Error handling feedback into excel
Round up!
So there you have it - COVID cannot stop the Power Platform and Microsoft Community from thriving. I would love for the automation from Forms Quiz submission to CDS to be automated without the copy paste excel magic but its been built with positivity that when it becomes possible to use the Forms trigger its an easy adaptation to the existing solution. But for now I’m enjoying the Excel trigger, its really powerful and a great tool to empower business users who cant break free from their beloved spreadsheets.
More details on how to join us for the next #PPPQ event -> here
[1] https://flow.microsoft.com/en-us/blog/introducing-microsoft-flow-integration-in-excel/