How to Build a World Cup Tournament Tracker with Microsoft Power Platform

Microsoft Power Platform is a line of business intelligence, app development, and app connectivity for software applications. It is a whole ecosystem that depends on its constituent applications to build solutions. Power Platform is a so-called low code platform, which means that the apps can be used by business users without extensive knowledge of code.

 

Power Platform is a system that allows users to perform three key actions on data: Build, Automate, and Analyze. This is done with Power BI, Power Apps, and Power Automate. The three applications all work on top of your data and help all employees, from the CEO to the ordinary employee, to drive and develop the business using data. This makes it an ecosystem where different applications have codependency to achieve exceptional results in the technological sphere.

 

The Challenge  

Microsoft Power Platform is a low-code ecosystem that enables users to build, automate, and analyze data. It comprises Power BI, Power Apps, and Power Automate, and each of these applications has codependency that makes it an exceptional solution for business intelligence, app development, and app connectivity. In this instance, we will create a World Cup Tournament solution using Microsoft Forms, Power Automate, Power Apps, and Power BI. Participants will sign up using Microsoft Forms, and the data will be stored in SharePoint using Power Automate. The points and scores will also be updated in a SharePoint List using Power Apps. The data will then be displayed using Power BI visuals.

 

The solution

For this instance, we will have an end-to-end solution on how one can build a solution using different applications. Every four years, The World Cup takes place. If you have no idea what it is, this is a football tournament with 32 teams from across the world. The teams are in eight groups (A-H). To have a little fun with this at our place of work, we decided to have a tournament for football fans where we choose 4 teams and rank their outcomes in the tournament and whoever of the participants has the highest points according to their team’s selection is the winner.

The Implementation 

We will leverage Microsoft Forms, Power Automate, Power Apps, and Power BI for this task. The Solution will allow participants to sign up using MS Forms, and Power Automate will then store that data in SharePoint. We will use Power Apps to update scores and points that will also be stored in a different SharePoint list. The data will then be displayed on Power BI using visuals.

 

Architecture 

 

Here is how we will go about the 5 steps. First, we need to have the teams.

The teams are subdivided into two groups. Red Teams (the two relatively stronger teams) and Green Teams (The two relatively weaker teams) in each group. The participants should only pick 2 red teams and 2 green teams. 

 

Microsoft Forms

The Questionnaire is to allow the collection of the teams from different participants. The questions will be as follows.

 

 a). What is your name? (Short Answer, Required)

 

b). Among the red teams, which two teams do you select? (Choice Answer, Multiple answers, Maximum of 2, Required)

 

c). Among the green teams, which two teams do you select? (Choice Answer, Multiple answers, Maximum of 2, Required)

 

d). Please input your email address. (Short Answer, Required)

 

For number four, if the participants are in the same organization there is no need to collect the emails. The emails will be available as a variable on Power Automate.

 

 

SharePoint (Participants)

We will require a SharePoint site where we can create a SharePoint List. The List stores the participants and their respective four teams of choice. This will be an easy List that will have the following columns and their data types.

 

The Columns and their data types will be:

a. Email – text

b. Fullname – Nam

c. Red_Teams – Text
d. Green_Teams – Text

 

Below is an image of how the data would look on SharePoint

Power Automate

Once we get the responses, we now will use Power Automate to transfer the data from the forms to SharePoint. Go to the link and Search for 'Record Form Responses in SharePoint' among the templates.

All you need to customize the template flow is by filling in the Form ID by choosing the name of the form in the first two steps.

You will also need to specify the site address and the List name of the SharePoint Site where you created the columns above.

The Teams SharePoint List

SharePoint (Teams Points)

Having already got all the participants and their respective teams, the next thing we need is to update the teams’ points.  This can be done in two ways.

1. Directly on SharePoint by creating another SharePoint List. 
2. Using Power Apps to write to SharePoint. 

For either method, we will require to have the following columns and respective datatypes.

1. Team – Text
2. Points_Accrued – Number
3. Goal_difference – Number
4. Matches Played – Number
5. Disqualified – Choice [FALSE, TRUE], FALSE is the default.

The points accrued are awarded after a game is played where a win is 3 points, a draw is one point, and a loss is zero Points.

The goal Difference is the subtraction between the goals scored and the goals conceded per match.

The Matches played is the number of each team has played in the tournament and the last item is

Disqualified – This is a YES/NO choice, where when a team is disqualified, the default FALSE value should be updated to TRUE.

 

Below is an image of how the data would look on SharePoint

  

Power Apps

However, we can Update SharePoint with a Power Apps Application. We will use a Gallery to display the teams’ points and an Edit form to input and update the team’s points.

After connecting the SharePoint Data Source (Team_Points) SharePoint List, we insert the two main controls.

The Form is called FormPoints.

a).  App  Onstart Property, we need to set the following:

• NewForm(FormPoints); - To have a new form each time we start the application
• Set(VarGalDefault,{}); - Setting the Gallery default.
• Set(backgroundcolour,RGBA(220,222,228,1)); - Setting background colors.
• Set(greencolor,RGBA(112,194,69,1)); - Setting the primary colors.

b). The Gallery properties that we will change;

• Template Fill - If(ThisItem.IsSelected, ColorFade(RGBA(141, 196, 63, 1), 80%),RGBA(0,0,0,0))
• OnSelect - ResetForm(FormPoints);ViewForm(FormPoints)
• Items - Search(Points_1, TextInput1.Text,"Team"

c). The template has 4 items. The team name, the played games, the goal difference, and the points accrued. 
In the text property of the labels, they will be represented as follows. 

• "Goal Difference - " & ThisItem.GoalDifference
• "Played Games- "& ThisItem.MatchesPlayed"
• Points Accrued - "& ThisItem.PointsAccrued

 

Below is an image of the gallery on Power apps

     

 

d). The Form properties that we will alter are as follows;

1. OnSuccess - ResetForm(FormPoints); ViewForm(FormPoints); Set(VarGalDefault,FormPoints.LastSubmit);
2. Items - Gallerypoints.Selected
3. DefaultMode - FormMode.Edit
   

    

   Below is an image of the update and entry form on Powe apps

   
   

e). New Team Button –

1. On Select Property - ResetForm(FormPoints);NewForm(FormPoints);Set(VarGalDefault, Blank()); Set(VarGalDefault, {})

 

 

f). Edit Points Button –

1. On Select Property - ResetForm(FormPoints);EditForm(FormPoints)

 

g). Label - Allows one to switch between the new team and the edit team.
If(FormPoints.Mode =FormMode.New, "Please input New Team","Updating points for- " &Gallerypoints.Selected.Team)

 

in points edit mode 

 

in new team mode

 

h. Submit button.
When in view mode, we do not want to see the button hence on the Visible Property of the button: FormPoints.Mode <> FormMode.View

OnSelect property - SubmitForm(FormPoints)

 

Overall, our page will look like this. 

 

 

In the beginning, one should update all the teams. Once a match is played, you check the result and then update the team's points as per the match result.

Power BI

First, we need the data from our data source SharePoint. Once in the home tab of Power BI, Go to the data section and select  Get data.
For SharePoint, we can connect to the data using the Web or using SharePoint Online List in the online services Tab of Get Data.
Once you load the data(the Participant's Entries and the Team Points), our main task is to display the team ranking per participant. 

 

All we need is to perform a Pivot of the teams and do a Merge of the two tables on Power Query. Once connected. We need the rank the players using the Points accrued and the goal difference.

We first create an entries pivot with the participants' names and Their teams.

Below is an image of the Pivot result

 

We then create a new table WC Merge which is a combination of the entries Pivot and the Points. We merge on the team's column In Power Query, it is as shown below.

 

Below is an image of the Merge of the two tables

 

Create a Visual Table with the participants, the points, and the goal difference.

To rank the participants, create a DAX measure as follows.

RANK Table (Points and GD) =

 

RANKX(

    ALL ('WC Merge'[Full Name]),

    CALCULATE (SUM('WC Merge'[PointsAccrued]) + SUM('WC Merge'[GoalDifference])/100))

Add the measure in the Table visual and Order by it.

This will enable one to have all the participants ranked by their teams’ performances.

Conclusion

This implementation allows us to utilize several Power Platform applications, demonstrating how Power Platform is an ecosystem. This solution was rather hard, but the different applications eased the task. But, with Power Apps and Power Automate, one can develop more sophisticated applications. There are countless usage scenarios.

Enjoy using the Power Platform going forward!