Data Training Courses

Home | Power BI | Excel | Python | SQL | Generative AI | Visualising Data | Analysing Data

Football Match Dataset

This data lists the football matches played in the current season of the English Premier League as of 8th January 2025.

Data Description and Links

Football Data

An Excel spreadsheet contains several useful tables and is here.

This includes:an Excel file with data in an Excel table named Team with a row for each of the teams in the Premier League. The columns are:

• a three letter abbreviation e.g. CHE,
• the short name of the team used in the match record,
• the long name of the team, used in the Premier League website,
• the name of the team used in the BBC sport website, and
• some other columns such as city

This also includes some fictional data on the ticket sales for each match, used in an advanced Power BI exercise

Logos

The logos of each team are in a public web folder at https://zomalextrainingstorage.blob.core.windows.net/datasets/epl-svg/. Each logo is a .svg filetype. The filename is the three letter abbreviation. For example, The Chelsea logo file is at https://zomalextrainingstorage.blob.core.windows.net/datasets/epl-svg/CHE.svg

Match Record

This data is in an CSV file in a public web location here.

The column (variable) names and explanations are listed here for convenience.

Key to results data:

Title	Description
Div	League Division
Date	Match Date (dd/mm/yy)
Time	Time of match kick off
HomeTeam	Home Team
AwayTeam	Away Team
FTHG	Full Time Home Team Goals
FTAG	Full Time Away Team Goals
FTR	Full Time Result (H=Home Win, D=Draw, A=Away Win)
HTHG	Half Time Home Team Goals
HTAG	Half Time Away Team Goals
HTR	Half Time Result (H=Home Win, D=Draw, A=Away Win)

Match Statistics (where available)

Title	Description
Referee	Match Referee
HS	Home Team Shots
AS	Away Team Shots
HST	Home Team Shots on Target
AST	Away Team Shots on Target
HC	Home Team Corners
AC	Away Team Corners
HF	Home Team Fouls Committed
AF	Away Team Fouls Committed
HY	Home Team Yellow Cards
AY	Away Team Yellow Cards
HR	Home Team Red Cards
AR	Away Team Red Cards

The original data is from the football data website.

Power BI Lesson Plan

The objective is to build a league table from the match record. This will involve several challenges.

Firstly we need to understand both the football match dataset and the league table.

The league table columns are:

• Position -the ranking of the team by Points, then if any ties, by GD
• Team, the name of the team. Note that some names differ slightly between the source match record data and the BBC and Premier League tables - the supplied Team table translates between these
• Played - the number of matches played by each team
• Won - the number of matches won
• Drawn - the number of matches drawn
• Lost - the number of matches lost
• GF - a common abbreviation of Goals For, the number of goals scored by each team
• GA - a common abbreviation of Goals Against, the number of goals conceded by each team
• GD - the Goal Difference, equal to GF - GA
• Points - teams get 3 points for a win, 1 for a draw, and 0 points for a loss

Data preparation

We need to restate the match record as a pair of results both for the home and away teams. This will involve several operations in the query editor including

• import queries
• select columns
• copy (reference) datasets
• rename columns
• replace values
• append queries

Data Modeling

We will start by using the auto-date time feature, but the later import and configure a custom Dates table.

We will create a relationship between our results data and the Dates and Team tables to form a start schema pattern

DAX Calculations

We will build several measures ot use on the league table.

• GF and GA will be explicit measures (and we will discuss the difference between implicit and explicit measures and that best practice suggests that explicit measure are the better option)
• Played
• Won, Drawn and Lost -these introduce the DAX CALCULATE function
• Position. This is a challenging measure to build and we will do this in 2 ways:
  1. ask an AI tool to provide a quick answer
  2. build the measure from first principles step-by-step to gain a deeper understanding

Visual Exercises

The league table show the logo of each team. These logos are available in a public web location as image files. We will want to show these also on our Power BI report

Advanced Exercise

There is an advanced exercise to calculate ticket sales by month. This is based on some fictional data that lists the total proceeds of ticket sales and the number of days on sale. In this data tickets go on sale a few months before the match date.

For this exercise, we assume that the sales for each are evenly distributed over the sale period for that match.