Introduction

By now you have likely heard a little about Power Query (formally known as Data Explorer). While you may have heard of the tool many still have not gotten their hands on it and started experiencing the potential it has. In case you’re new to Power Query here’s a couple quick points before continuing on:

• It is a self-service ETL (Extract Transform Load) Tool built on top of Excel
• It is an add-in available for Excel 2010 or 2013
• Download it here.
• Getting Started with Microsoft Data Explorer

My goal with this post is to show you how simple yet powerful Power Query really is. I’ll do this by showing you an example of solving a problem that would be fairly complex using traditional ETL tools like SSIS (SQL Server Integration Services) but made simple with Power Query.

Problem

Power Query has the ability to do some basic “screen scraping” of data from web pages and add this as a new data source to your Self-Service BI solution. The problem that often occurs when doing this is that there are often dozens of pages or filters that need to be modified to get a full historical view of the data on the web page. To solve this problem we can leverage parameters in the Power Query Formula Language to navigate through this data. Even though Power Query is new there are a couple very good resources for learning it that can be found here. To make this example fun we will be pulling our data from data from the National Football League website (www.nfl.com). When pulling historical data about teams from the league the website only permits users to view one year at a time. However, our goal is to view how teams have performed across all time.

To solve this we will create a parameter using the Power Query Formula Language to dynamically pass in the years that are needed to extract data across all time. Next we’ll walk through a beginning to end example on solving this problem.

Hypothesis

Once this data is collected I would like to prove or disprove a hypothesis of mine. I believe that the fewer penalty yards are accumulated by a team’s offensive will result in better performance. To determine if I’m right we will apply a visualization to the data once we’ve completed importing it.

Step by Step

• Launch Excel 2010 or higher. My screenshots are all done using Excel 2013 but everything should look the same until the data visualization section.
• Select the Power Query tab.
• To complete this example we’ll need to enable advanced query editing. Under the Machine Settings section select Options.

• Check the option called Enable Advanced Query Editing then click OK. This setting is turned off by default.

• Click From Web under the Get External Data part of the Office Ribbon.
• Use the URL http://www.nfl.com/stats/categorystats?tabSeq=2&statisticCategory=GAME_STATS&conference=ALL&role=TM&season=2012&seasonType=REG then click OK.

• In the Navigator pane select Table 0, which has the content we need for this demonstration.

• Rename the query by double-clicking on the query name in the top left of the Query Editor. Change the name from Query1 to Team Stats.
• Now click the Edit Query button in the top right of the Query Edit.

• Add the following parameter definition above the existing query:

(getYear) =>

• Look at the URL string that is part of the query and replace the hardcoded year 2012 with the following expression:

" & Number.ToText(getYear) & " Click Done. The full query after these two changes should look like this: (getYear) => let Source = Web.Page(Web.Contents("http://www.nfl.com/stats/categorystats?tabSeq=2&statisticCategory=GAME_STATS&conference=ALL&role=TM&season=" & Number.ToText(getYear) & "&seasonType=REG")), Data0 = Source{0}[Data], ChangedType = Table.TransformColumnTypes(Data0,{{"Rk", type number}, {"Team", type text}, {"G", type number}, {"Pts/G", type number}, {"TotPts", type number}, {"Scrm Plys", type number}, {"Yds/G", type number}, {"Yds/P", type number}, {"1st/G", type number}, {"3rd Md", type number}, {"3rd Att", type number}, {"3rd Pct", type number}, {"4th Md", type number}, {"4th Att", type number}, {"4th Pct", type number}, {"Pen", type number}, {"Pen Yds", type number}, {"ToP/G", type text}, {"FUM", type number}, {"Lost", type number}, {"TO", type number}}) in ChangedType

• You can now click the Invoke button to pass in any year value you want and get different results based on your selection. Click Invoke and type in the year 2009. Click OK.

• This returns all data for the year 2009 for all teams. Our next step is to return data for every year for all teams. Expand the Steps pane on the right side of the Query Editor and click the X next to InvokedTeam Stats. This removes the last action taken, which was passing in the value of 2009 into our parameter. Click Done.

• Select the Power Query tab.
• Select From Other Source > Blank Query

• In the Query Editor formula bar type the following expression:

= {2002..2012} This will automatically create a list of the last 10 years from 2002 to 2012

• Right-click on the column header and select To Table to convert this list into a table. You will be prompted with some settings for changing this list to a table. Just accept the defaults and click OK.

• Right-click on the column header again and select Insert Column > Custom
• Use the following formula to pull in data from the parameter driven function we created earlier.

#"Team Stats"([Column1])

NOTE: The Power Query Formula Language is case sensitive

Double quotes are only needed here because there is a space in the name of the other query. Click OK.

• Click the Expand button next to the new custom column then click OK.

• This should give you a preview of all team stats across the last 10 years. Rename the query in the top left of the Query Editor from Query1 to Full Team Stats.
• Multi-select the columns Column1, Custom.Team, Custom.Pts/G, Custom.TotPts, Custom.Yds/G, Custom.Pen, Custom.Pen Yds then right-click and select Remove Other Columns.

• Rename Column1 to Year and rename all the other columns to remove the prefix of Custom. from the name. Columns can be renamed by right-clicking on them and selecting Rename.
• Multi-Select the columns that have our aggregate data in it then right-click and select Change Type > Number as shown in the screen shot.

• Finally, click Done to actually import the full dataset into Excel. Depending on the web site you’re extraction could take several minutes.
• With the data now in Excel click the Load to data model button inside the Query Settings pane on the right of your screen. This sends to data directly into Power Pivot.

This is a feature only available in Excel 2013. If you’re using Excel 2010 then you must launch Power Pivot and go to the Design tab and chose from Existing Connections to do the same behavior.

Visualizing Data (Cherry on top)

Now that we have the data in Power Pivot it should be fairly simple to visualize it. We could go with a straight forward approach and use PivotTables, but since this demonstration was done using Excel 2013 let’s use Power View. If you’d like to replicate this demonstration but are using Excel 2010 you can do so by deploying your workbook to a Power Pivot gallery in SharePoint 2010 SP1 that uses the SQL Server 2012 Reporting Services add-in. Remember this is the section where I should be able to prove or disprove my hypothesis about the team’s offensive performance being impacted by penalties.

• Go to the Insert tab in Excel 2013 and select Power View.
• Delete any visualizations that Power View may have automatically tried to create for you.
• Close the Filters section by hitting the next in the top right next to the Filters pane.
• In the Power View Fields list expand Full Team Stats table and select Pen Yds, Pts/G, Yds/G, and Team.
• With these fields selected change the visualization to a scatter chart by select Other Chart > Scatter in the Design tab.

• Expand the scatter chart so it takes up the entire design surface except for the title.
• From the Power View Fields list bring the Year column from the Full Team Stats table into the Play Axis of the Chart properties.

This chart is starting to tell us some interesting things. It looks like the least penalized team, the Atlanta Falcons, is on the upper half of offensive performance but not the best. The best offense is the New England Patriots and they’re about middle of the pack when it comes to penalties. Probably the most interesting thing I find here is that the Super Bowl winner from 2012 was the most penalized team and about middle of the pack when it comes to offensive output. That just goes to tell you that in American Football there’s another half of the game we’re not analyzing here. Baltimore is well known for have a great defense, which would explain the discrepancy. Overall it looks like my hypothesis cannot be proven right.

• Now, if we hit the Play button in the bottom left of the chart we can see across the last 10 years.
• It looks like the most consistent offensive team has been the New England Patriots. We can focus on the New England Patriots by clicking on their bubble in the scatter chart and it shows over the last 10 years Patriots have been an outstanding offensive team.

• Click somewhere in the background of the chart to remove the filter on the Patriots.
• Finally, give the report a title of NFL Offensive Performance to complete this example.

This is the kind of amazing analysis you can do with Power BI. I hope you’ve found this useful and can apply it to your own work! You can download the sample workbook for this example here.