Be a Power Platform Jedi – How to run a book raffle

Gil Raviv2

This is the first blog post in a series of posts that I plan to publish and share some of my demos that I presented during the Power Platform World Tour in Chicago last week (April 9-10, 2019).

In today’s post, I will share a technique to run digital raffle during a presentation. Let’s say you are a speaker in an event and would like to run a raffle at the end of your session. Wouldn’t it be cool if the entire process will be automated?

In the last Power Platform World Tour I raffled off three digital copies of my book. The digital raffle consisted of four steps:

Four-Step Power Platform Automation

Step 1: A Microsoft Form is presented to the audience, asking them to provide their email address and a public unique phrase. The email address will not be shared in class, but the unique phrase will be displayed and be used to announce the winners (Announcing the winners live is crucial. To win the price the winner needs to be present).

Step 2: Once the form is answered, an automatic email is sent to the audience, confirming that they are participating in the raffle. The email will be used to confirm their participation.

Step 3: At the end of the session, a Power BI dataset is refreshed on PowerBI.com. Once refreshed, three unique phrases are drawn from the list and displayed to the audience. To ensure that all the winners are present, I announce the winning unique phrases and ask the winners to raise their hand.

Step 4: In that point, I click on each of the public phrases in the Winners table, if the participants are present in class. The public phrase is auto-populated in a PowerApps form which is embedded in the report. I click the submit button and an automatic email is sent to the winners with the code to download the free book.

And here a sample email for the winners with the instructions to download the book:

How did I build it?

Now when you understand the sequence of events, let’s drill down and explain each of the steps.

The Survey

The survey was implemented using Microsoft Forms. A simple form with two questions to get the raffle participant’s email and unique phrase. Once the survey is ready, you can generated a QR code (read how here).

A SharePoint list was also created with the fields: Email, Title (for the unique phrase), Code (to later store the download code that is assigned to the winner) and IsWinner, a Yes/No status that is set to 0 by default.

To create the Microsoft Flow, I selected the highlighted template:

The flow connects to SharePoint, mail and Microsoft Forms. In the next screen you can connect to these data sources.

From here, you can select the relevant form in the Trigger step.

  1. Next, select the Form Id again in Get response details step.
  2. Under Send an email, select the email response from the Form.
  3. Enter an email subject.
  4. Enter email body
  5. Include the unique phrase from the response, as shown in the screenshot below.
  1. Under SharePoint Create item, select the relevant SharePoint site.
  2. Select the SharePoint list name in List Name.
  3. Select the unique phrase from the form as Title.
  4. Select the email address from the form as Email.
  5. SelectNo for IsWinner.

Now, you can test the form and flow. Answering the form should trigger the email with the confirmation and the unique phrase.

The Random Operation

This step is the core element of the raffle. Since I am raffling off a Power Query book, I decided to use Power Query to load the raffle participants from the SharePoint list, add a random column, sort the table by the random column and keep the top three rows.

The base query uses the Online SharePoint List connector to load the list and keep only the phrases. Then, I created a reference from the base query and followed these steps on a new Winners query:

In Add Column tab, I selected the Custom Column and entered the new column Random with the following formula:

Number.Random()

In the formula bar of Power Query Editor, you can see the following code:

Table.AddColumn(Source, "Random", each Number.Random())

While the Random column contains different random numbers in the preview pane as expected, if you load the query at this stage, you will be surprised to find out that a single random number is used in all the rows. To resolve this issue, you can use the Table.Buffer function. This function loads the entire table into memory and enforces the engine to run the random function in each row. Without it, the M engine in Power Query will run a lazy evaluation and will only compute the random number once. Not sure you understand it, no worries. If it works, we are good.

Here is the modified formula:

= Table.Buffer(Table.AddColumn(Source, "Random", each Number.Random()))

Now, you can sort the table by Random, and keep the first 3 rows. Here is the full query:

let
    Source = #"Book Raffle",
    #"Added Custom" = Table.Buffer(Table.AddColumn(Source, "Random", each Number.Random())),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Random", Order.Descending}}),
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",3)
in
    #"Kept First Rows"

At this stage I loaded the query and built a table of the Winners’ phrases. To test the random operation, I refreshed the report multiple times. Each time I got 3 different winners, which proved that Table.Buffer works.

Next, I merged the 3 book codes into the Winner table. These codes were used to allow the winners to download a digital copy of the book for free.

The PowerApp and Flow to trigger email to winners

After I published the report to the service, I added a PowerApp custom visual in the report page (You can follow this tutorial to learn how to embed PowerApps in Power BI). Then, I dragged and dropped Code and Title fields into the PowerApps Data pane.

I started a new PowerApps form that includes two simple UI controls: A label whose Text attribute is fed from Power BI Title field, and a button that triggers a new flow. Here is the screenshot for the Text attribute of the label control. The label will include the unique phrase which is stored in the Title field.

Here is the function used to load the Title field:

First([@PowerBIIntegration].Data).Title

The button triggers a new flow which was created in advance. It gets the title and the code from Power BI and generates the email to the selected winner.

Here is the code to trigger the flow:

SendBookCode.Run(First([@PowerBIIntegration].Data).Title, First([@PowerBIIntegration].Data).Code)

The final flow

The final flow which is triggered above performs the following steps as shown in the screenshot below:

  1. The flow is triggered by the PowerApps form.
  2. It loads all the SharePoint list items and checks if each one of the Title values in the list equals the Title value that is selected in the PowerApps form.
  3. If the Title values match, and IsWinner is No. the flow proceeds to the next step (IsWinner ensures that the same winner will not receive multiple emails).
  4. The flow updates the specific SharePoint list and set IsWinner to Yes and updates the code in the SharePoint list.
  5. An email is sent to the winner, with the Email that was stored on the SharePoint list and the code input value from the PowerApps form.

That’s it. Hope you find it useful to run raffles for your books 🙂 or to take elements from this demo and use them in your own business context. You are welcome to share ideas on how you can use these techniques in the comments below.

I plan to use this raffle again in Power BI Dashboard in a Day workshops and in the upcoming conferences and webinars.

2 comments

Leave a Reply