BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
PowerPivot is a superb addition to Excel: it allows you to create pivot tables based on multiple tables taken from a variety of data sources, and its DAX language lets you report any statistic you want. This overview summarises how to get started with PowerPivot, and what it does.
- Getting started with PowerPivot for Excel 2010
- Stage 1 - Importing the tables we need (this blog)
- Stage 2 - Creating a data model
- Stage 3 - Creating a Pivot Table
- Stage 4 - creating measures using DAX
- Stage 5 - KPIs in PowerPivot for Excel
Posted by Andy Brown on 31 January 2013
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Stage 1 - Importing the tables we need
What we're trying to do is to turn two tables (one in Word, one in SQL Server) into a single pivot table field list:
What we're trying to achieve!
To do this we need to connect to the tables, create a relationship between them, rename some columns and hide others and create some calculated columns.
Connecting to our SQL Server table
The first thing we need to do is to link to our table called tblFilms, which I've assumed is in a SQL Server database called Movies. First choose to link to SQL Server:
Choose to get data from SQL Server.
You can now follow the numbered steps below to connect to a database:
The numbered steps to follow are shown below.
Here are the numbered steps above:
- Give your connection a sensible name (it's usually likely that you'll use it to connect to other tables).
- Type in or choose the name of your server.
- Choose whether you're going to use your Windows logon as authentication (as here), or type in a SQL Server database name and password.
- Choose which database you want to connect to.
- Click to go to the next stage of the wizard.
You will then nearly always ask to select the tables you want to import:
You'll nearly always choose the first (default) option in this dialog box.
You can now choose which table or tables you want to import:
You can tick the tables you want to import, and also rename them at this point.
At this point you would normally click on the Preview & Filter button to select the columns you want to import. If you have millions of rows of data, you can save time, memory and disk space by importing only the rows - and columns - that you need to use.
You should now have a table giving the columns you need:
We need the DirectorId field because we'll use this to link to the other table, imported from Word.
The next stage will be to tidy up the data so that the table and field names are sensible, and only the ones of interest for a pivot table builder are visible. We'll do that in the next stage of this blog.
Importing our Word table
For the SQL Server table above, we've created a connection (you can refresh the data at any time), but for Word we'll just copy and paste it in. First select the table of directors and copy it:
Select the table of data in Word and copy it to the clipboard.
Now paste it into PowerPivot:
Click on this Paste icon to paste the data into PowerPivot.
How good is this? You can now give your new table a name (I've called mine Director):
PowerPivot shows a preview of the data.
You can now see the imported data:
The table of directors pasted into PowerPivot.
Linking the two tables with a relationship
You should now have the two tables in PowerPivot:
The two tables imported into PowerPivot - there's nothing to say that one came from SQL Server and one from Word now.
To link them together, first switch to Diagram View:
If you can't see this, you may not have the latest version of PowerPivot installed. At the time of writing this is called SQL Server 2012 PowerPivot Addin for Excel and is version number 11.0.1750.32.
You can now drag the common field (DirectorId) from one table to the other:
Click and drag to create the relationship shown: each film's DirectorId column matches the director's Director id column value.
You've now imported all of your data, and should save your Excel workbook before continuing. We'll now create a tidy data model.
- Getting started with PowerPivot for Excel 2010
- Stage 1 - Importing the tables we need (this blog)
- Stage 2 - Creating a data model
- Stage 3 - Creating a Pivot Table
- Stage 4 - creating measures using DAX
- Stage 5 - KPIs in PowerPivot for Excel