The Birth of the PPPQ: how the global Power Platform Quiz was born!
In this blog, guest contributor Amey Holden shares the story of the start of the PPPQ (our Power Platform Pub Quiz) event which was born in May 2020 out of Sydney/Melbourne. During what were challenging times with the world in lock-down due to COVID, four of us QuizMeisters decided to try and keep the community spirit alive (through virtual means of course), and developed a global Power Platform quiz.
Amey writes about the story behind the idea, the tech used for automating elements of the workflow, and the goals for what we all set out to achieve. Enjoy the read and thank you Amey!!
Our Approach: You can build “data-driven business solutions” to solve practical problems with the Microsoft Power Platform, and you can also build fun solutions to ease the side effects of a global pandemic too.
Where this idea came from…..
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 brought a bright yellow raincoat, 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; and were overwhelmed with the community response as individuals across the world contributed questions to fuel the quiz.
Click through for the PPPQ Meetup page!!
What exactly is the 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 future PPPQ events are here.
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 parts:
Calculating scores; and
Teams collaborating to agree on an answer
There are many ways to overcome this but the focus was to keep it as low cost, simple and reliable as possible, focusing on how the four of us can use what we know to present the ideal solution.
The Power Platform solution for the original PPPQ!
The Team Collaboration Saga
In a traditional Pub Quiz you are huddled with your team around a table with your 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 located across the globe 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 part of a team.
So the “Team Collaboration Solution” involved Forms Quiz and Power BI, and this is what we did…..
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.
Team Set-up: 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 was extracted into Power BI where the Power BI Unicorn Greg Nash worked his magic. Answers and Points are separated into two fact tables, with the Round, Question, and Team tables included as supporting dimension tables. This ensured we could select any combination of Round /Question /Team and it will return the corresponding Answers and Points.
We initially wrote two simple Power BI measures:
Total Points - SUM(Points[Points])Submissions - DISTINCTCOUNT(Points[AnswerID])
Which were then used to calculate the “Team Average Points”, by dividing the sum of the total points for the round by the number of submissions for the round (i.e. an individual team could have multiple submissions):
Team Average Points = AVERAGEX('Round',DIVIDE([Total Points],[Submissions]))
The AVERAGEX() iterator function meant that we could show the “overall average” (if we ever aggregate it) and therefore could also compare the current team score to the overall average.
The Scoring Saga
The approach in a Traditional Pub Quiz is that after each round you pass around the answer sheets and mark for another team, the quiz master then collects the results and you are onto the next round.
This approach had a few limitations when considering a Virtual Quiz, mainly - no paper, no pens, no ‘passing it on’ to your neighbour. Sure its possible but it became a potential logistical nightmare plus at the risk of dodgy internet connections we may have been faced with spending more time reading answers and collecting scores then what we would have liked. Alternatively we could mark them ourselves but again that’s a timely boring job.
We needed a system for live automated scoring at the end of each round to make sure time was spent actually having some quiz-related fun
So the “Scoring Solution” involved Forms Quiz, Microsoft Flow for Excel & the Common Data Service (CDS), and this is what we did…..
Forms Quiz 📄: Quiz has an automated points scoring process. 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, including:
‘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, so we explored two options:
Option 1: Automated Flow triggered when a form is submitted
In what should had been a ‘simple‘ solution, there were two limitations to this Flow 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. 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.
And now to the next question in the chain:
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? I’m glad you asked: CDS of course!
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.
Round data in CDS
Quiz Response in CDS created by Power Automate
Power Automate Flow ➡️: You can build a 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 use Power Automate to get rows into CDS as Quiz results:
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.
Initialise Variable ‘Team Name’: Passed from Forms Quiz
Initialise 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 (Step 1)
cr2fa_roundnumber e.g. '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 (Step 5) we get the actual record here
List Records - Team: Same as (Step 4) but for ‘Teams’ (i.e. accounts) name e.g. 'TEAM NAME'
