EPL 2017_18

I took the English Premier League fixture list for 2017 – 2018 from an online newspaper, did some reformatting, created an Excel Table, Used Power Query, put it all into a Pivot Table together with slicers and now I can create the fixture lists for home and away for any and all EPL clubs.

Raw Data

epl_2017_2018_a

What I did: in brief

Copied and pasted the table from the newspaper

Added a column to the left

Moved the date of the fixtures from, eg, Row 5 to the new column row 6

Copied the dates from the first row to the final row of the fixtures to which it relates

Delete the surplus data: for example, you can see that the home and away team names appear twice … delete the surplus columns. Delete the v column and the blank columns either side of that as well.

Create headers for the list: you should have just four columns

  • Date
  • Time
  • Home [Team]
  • Away [Team]

Convert the entire list, including the empty rows, to an Excel Table: Ctrl+T to do that. At this stage you could filter out the blank rows and then create the pivot table but I used Power Query for that.

Change the name of the table … I chose epl_2017_2018

Power Query: Data … From Table/Range

Change the name of the Query if you want. I was happy with epl_2017_2018

Date Filter … either Remove Empty or Deselect null

Change the Data Type for the Time column to Time … for me it came across as Whole Number. Now it will show, eg, 3:00:00 PM for a 3 pm fixture

I tried to use Transform … Time … Hour … Hour to turn 3:00:00 PM in 15:00 hours but it only showed me two digits which meant that a 19:45 fixture was shown as 19 only. I undid that!

Close and Load

Create a Pivot Table from the Query

I created the Home Fixtures Table first:

  • Filters: Home
  • Rows: Date Away … Field Settings … Number Format … Custom … ddd dd mmm yy to give me, eg Sat 19 Aug 17 (the screenshot below is not quite complete but the file you can download below is up to date)
  • Values: Time … I put time here so that I could make the time show in the way I wanted … Value Field Settings with Number Format as Time 15:00 and Summarise value field by Maximum

The Away fixtures Table

  • Copy the Home Pivot Table when you are happy with it and paste it where you like; make the changes necessary to make it show the away fixtures and then create your Slicers:
  • Slicers: create one for the Home Pivot Table and a separate one for the Away Pivot Table

My Pivot Table with Slicers

epl_2017_2018_b

What happens when they change the date/time of a fixure? Just change the basic data  for that and the Query and Pivot Table will update automatically.

I have created a single Pivot Table for Home and Away … it’s on the tab pivots (2) … maybe you prefer that one. You need to learn how to use the Slicers to make that table work.

Magic!

Download the file from here: epl_2017_2018

 

Duncan Williamson

15th June 2017