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:

1. Give your connection a sensible name (it's usually likely that you'll use it to connect to other tables).
2. Type in or choose the name of your server.
3. Choose whether you're going to use your Windows logon as authentication (as here), or type in a SQL Server database name and password.
4. Choose which database you want to connect to.
5. 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.

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.

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.