http://www.sqlservercentral.com/blogs/dknight/2013/08/07/using-parameters-in-power-query-extracts/

Printed 2014/11/23 08:07AM

Using Parameters in Power Query Extracts

By Devin Knight, 2013/08/07

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:

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.

clip_image002

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

clip_image004

clip_image006

clip_image007

clip_image009

clip_image011

(getYear) =>

" & 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

clip_image013

clip_image015

clip_image017

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

clip_image019

#"Team Stats"([Column1])

clip_image021 NOTE: The Power Query Formula Language is case sensitive

clip_image023

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

clip_image025

clip_image027

clip_image029

clip_image031

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.

clip_image033

clip_image035

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.

clip_image037

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.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.