Hello everyone, Pitan here! I’ve finally had the chance to get my head around PowerPivot, the new Excel 2010 add-in for grabbing, pivoting, and displaying data. The chronicles of my journey to set up my own PowerPivot report will be revealed in a series of blog posts over the coming weeks. Tune in as I give you some HOW-TOs with a healthy dose of side commentary!
PowerPivot allows you to pull data from a wide variety of sources that you can combine into a single report. For instance, you can query a SQL Server 2008 database, extract data from an external text file, or link to tables of data within your Excel workbook, etc.
While having all the data in the world at the ready may be intoxicating for some, in this example, I’ll just stick with using data from the 2008 R2 AdventureWorks database:
1. Go to the PowerPivot tab in Excel 2010 in the ribbon
2. Click PowerPivot Window to launch PowerPivot.
3. Go to From Database and select From SQL Server
4. The connection wizard will walk you through connecting to the desired database and optional default table.
5. You will be given the option to select tables from the database or use a query. For this example, I used a pre-defined query.
Note: I found the Validate function useful. PowerPivot doesn’t allow duplicate column names, which Validate kindly pointed out to me.
6. Once the data was imported, I noticed that the default table name was Query. I changed it to be Sales Data.
There you have it: a simple data import into PowerPivot.
But what if you want to import the newest data? One does not simply refresh PowerPivot data…
Actually, it turns out that you do. PowerPivot has a one-click button to refresh data from external sources.
Want to read more about PowerPivot?
Check the rest of the series!
- Part 2: Copying PivotTable Functionality
- Part 3: Slicers
- Side Quest: How to Format a Slicer
- Part 4: Sharing PowerPivot Workbooks
Follow