Super Bowl Winners and Losers Using Power Query

Quote of the Day

In Data Science, 80% of time spent prepare data, 20% of time spent complain about need for prepare data.

—Tweet from Big Data Borat. I must admit, I am amazed at the poor format of data on the web. I work with a lot of old WW2 data that was processed by human typists, so I understand the quality issues there. By there is no excuse for the poor formatting of machine-processed data today.


Figure 1: Teams with Most Super Bowl Wins.

Figure 1: Teams with Most Super Bowl Wins.

I was reading a post on Statista showing the NFL teams with the most Super Bowl wins. Since my staff includes a number of football fans — mainly Viking and Packer supporters — I decided it would be a good training exercise to show them how to gather the football statistics and present them in the same manner as shown on Statista.  I should mention that I do not follow football at all; this is purely a data analysis exercise for me.

I used web resources and Power Query to generate an Excel workbook that I could then use to generate the charts I wanted. I duplicated the Statista graph in Figure 1. In addition to gathering the data, I also used a "bulk" substitution routine put together by Miguel Angel Escobar, also known by his Youtube handle as The Power User.  My spreadsheet is available here.

Figure 1: Teams with Most Super Bowl Wins.

Figure 2: Teams with Most Super Bowl Losses.

In addition to duplicating the Statista chart, I also want to look at the number of Super Bowl losses. I live in Minnesota and our Vikings team has never won the Super Bowl, though it has made four appearances. Figure 2 shows a list of NFL teams ranked by their number of Super Bowl losses.

The Denver Broncos have the most losses with 5, but they also have had three wins. The Vikings, Patriots, and Bills have all have had four losses. While the Patriots have had five wins, neither the Vikings nor Bills have had even a single Super Bowl win.

Figure 3: Number of Super Bowl Appearances By Teams with No Super Bowl Wins.

Figure 3: Number of Super Bowl Appearances By
Teams with No Super Bowl Wins.

In Figure 3 shows the number of Super Bowl appearances by teams with no wins. The Vikings and Bills lead this list. Things could be worse. Notice how the Jaguars, Texans, Lions, and Browns have never been to the Super Bowl. I especially feel sorry for the Lions and Browns, who have long established franchises.

I did put the data into table form, which I show in Table 1.

Table 1: NFL Super Bowl Statistics

RankTeamGamesWinsLosses
1Pittsburgh Steelers862
2San Francisco 49ers651
3Dallas Cowboys853
4New England Patriots954
5Green Bay Packers541
6New York Giants541
7Los Angeles/Oakland Raiders532
8Washington Redskins532
9Denver Broncos835
10Baltimore Ravens220
11Baltimore/Indianapolis Colts422
12Miami Dolphins523
13New Orleans Saints110
14New York Jets110
15Tampa Bay Buccaneers110
16Chicago Bears211
17Kansas City Chiefs211
18St. Louis/Los Angeles Rams312
19Seattle Seahawks312
20Buffalo Bills404
21Minnesota Vikings404
22Atlanta Falcons202
23Carolina Panthers202
24Cincinnati Bengals202
25Philadelphia Eagles202
26Arizona Cardinals101
27San Diego/Los Angeles Chargers101
28Tennessee Titans101
29Cleveland Browns000
30Houston Texans000
31Detroit Lions000
32Jacksonville Jaguars000
 
This entry was posted in Excel, Statistics. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *