Power Pivot as the Core to your Power BI Solution

I’ve noticed a recent trend with many presentations and articles on Power BI and Excel functionality. Many of these presentations show how quickly you can take tools like Power Query to find and discover data and then quickly present it through tools like Power View and Power Map. These are really impressive demonstrations of what you can do in Excel in a matter of a few minutes to start with no data to quickly making business decisions through some form of a reporting layer.

While I really enjoy these demonstrations my one gripe is it doesn’t tell the full story. Take the example I just gave of discovering data with Power Query then presenting in in Power View.  In the excitement of taking the results of Power Query into Power View we often forget there is another important tool involved. I’m certainly guilty of this as well but I thought it was worth some explanation of what’s really going on behind the scenes.

By now you have probably learned that Power Query provides the option to put your data into Power Pivot immediately after you have finish transforming the data by selecting Load to Data Model.


But what if you don’t choose this option? You go with the default Load to worksheet setting and go about your business. Your data gets imported into an Excel worksheet and then you have to make a decision on how to visualize it.

This is what is often skipped in presentations. As soon as the decision is made to present your data through either Power View or Power Map the data in this Excel worksheet is automatically pushed into Power Pivot.

Let’s get a bit of an expanded view of how all these parts work together:


Another way of looking at this is based on the tool:

Power View

Power View reports can be developed from either Excel 2013 or SharePoint (On Premises) 2010 SP1 with SQL Server 2012.

If using Excel 2013 at some point the data must be placed in Power Pivot to visualize it in Power View.

If designing reports through SharePoint you can design reports on top of a Power Pivot workbook deployed to SharePoint, a connection to Analysis Services Tabular deployed to SharePoint or a connection to Analysis Services Multidimensional deployed to SharePoint.

Power Map

Power Map can currently only be done within Excel 2013 and Office 365 (Power Map preview is still available for On Premises Office). The data in Power Map must be based on top of a Power Pivot workbook.

Power BI Q&A

Power BI Q&A can only be done through a Power BI site and Office 365 subscription. The Q&A interaction is all based on top of a Power Pivot workbook that has been deployed to a Power BI site. You can optimize the Q&A interaction by adding things like synonyms to your Power Pivot workbook.


What you can get from this diagram is that Power Pivot is central to creating a complete Power BI solution. A well designed data model can only increase the chances of your reporting layer being successful. A poor design data model can make it difficult to make appropriate visualizations. So as you start exploring the Power BI tools make it a point to have a well designed Power Pivot data model.

Final Day for Power BI Voting, Can I Have Your?

Today is the final day for the Power BI Contest voting and I could use your help to move on in the competition!  Please take a moment and vote for my video here  http://tinyurl.com/PowerBIDevin

Thanks for voting!

Power BI Contest Entry

Today I submitted my Power BI contest entry and I’d love for you to take a look!  Here’s a short description of entry and the video itself:

In this submission I have used Power BI to help me plan a vacation with my wife. We both love thrill rides so I wanted to make the best decision on planning our next trip. Using Power Query I imported data from the web to find all possible roller coasters and amusement parks. I then took that raw data and made relationships and calculations using Power Pivot. Next, I visualized the results through Power View to help guide me through the data easier. Finally, I uploaded my workbook to a Power BI site and enabled Q&A to allow my wife to ask the workbook questions about our trip.


Find out more about this contest here and put in your own contest entry.

Understanding Microsoft Self-Service BI Recording and Q&A

I hope you were able to attend my free webinar on Understanding Microsoft Self-Service BI on September 26, 2013.  If you weren’t you can now download the recording here.

Because I covered new material all the way to the end of the webinar i thought I’d also answer some of the top questions I didn’t have time to answer here.

Q: Can an Excel Power Pivot 2013 model be read by Excel 2010 or Excel 2007? What about Excel versions and their compatibility with SharePoint Versions?  I ask because we are upgrading our SharePoint environment to 2013 but most of our users have Excel 2010 (a few have 2007).

You can open an Excel 2013 workbook that has a Power Pivot model in older versions but you cannot modify them.  You can however deploy Excel 2010 and Excel 2013 workbooks to you SharePoint 2013 environment and it they interact the same.

Q: Is there a way for me in us my own map?  ex. wards/districts in my county

I assume this one is referring to Power Map and the ability to map geopolitical regions.  It doesn’t currently have the ability to your own regions yet.

Q: Where do we download power map?


Q: Can you change to use google maps instead of bing maps?

No, Microsoft tool = Microsoft maps!🙂

Q: Could I use relationships using more than one field?

No, Power Pivot only has the ability to create relationships on one field at a time.  If you need to join on more than one field, which in the real world is often, then you have to get creative.  In the quick example I showed we merged two columns together to make a single join column that could be used for our relationship.

Q: Is Power Query available for Excel 2010?

It sure is!  This is one of the great things about Power Query is that it is compatible with both Excel 2010 and 2013.

Q: How can you share Power View reports? Do you have to share the excel file or can you display in SharePoint?

You have a couple options.  You can share the excel file like you’ve suggested or you can deploy it to SharePoint.  If you develop the Power View reports in SharePoint you can also export via PowerPoint.

Q: Will these slides be available

Sure thing!  Download them here.

Q: The maps features is very interesting.  Can it do counties within a state .. and neighboring states?

Yes, Power Map has the ability to regionally map counties and zip codes as well.

Whitepaper: Building Self-Service BI Solutions with Power Query

Self-Service BI with Power Query

Through Self-Service BI, business units can personalize Business Intelligence to their needs and solve problems at a much faster rate than any traditional BI solution. This is why businesses are looking to Self-Service BI to solve the smaller, but no less significant, problems that individual departments need addressed. The goal of this white paper is to focus on using and understanding one of Microsoft’s latest Self-Service BI tools called Power Query.

Building Self-Service BI Solutions with Power Query

Download the Whitepaper now!

Using Parameters in Power Query Extracts


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.


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.


Once this data is collected I would like to prove or disprove a hypothesis of mine. I believe that the fewer penalty yards 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

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


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


  1. Click From Web under the Get External Data part of the Office Ribbon.
  2. 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.


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


  1. 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.
  2. Now click the Edit Query button in the top right of the Query Edit.


  1. Add the following parameter definition above the existing query:

(getYear) =>

  1. 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) =>


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}})



  1. 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.


  1. 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.


  1. Select the Power Query tab.
  2. Select From Other Source > Blank Query


  1. 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

  1. 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.


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

#”Team Stats”([Column1])

clip_image021 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.

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


  1. 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.
  2. 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.


  1. 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.
  2. 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.


  1. Finally, click Done to actually import the full dataset into Excel. Depending on the web site you’re extraction could take several minutes.
  2. 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.

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


  1. Expand the scatter chart so it takes up the entire design surface except for the title.
  2. 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.

  1. Now, if we hit the Play button in the bottom left of the chart we can see across the last 10 years.
  2. 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.


  1. Click somewhere in the background of the chart to remove the filter on the Patriots.
  2. 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.

Power View Reporting on Multidimensional Cubes Released

I’m a little late to the celebration but on Friday some there was some exciting news regarding Power View was announced. You can read about the details of it at the Analysis Services and PowerPivot team blog for Microsoft here.


This is part of a SQL Server Cumulative update pack that you can download here.


One thing to probably make mention of that you can ready on the Microsoft team blog is that this is only for SQL Server and SharePoint Power View.  If you’re using Power View with Excel 2013 only then you’ll have to wait a little longer for an update.

Either way this is great news and for those that were anticipating projects to convert Multidimensional cubes to Tabular or PowerPivot so they could use Power View.