Now we have imported data into PowerPivot, we can create relationships between tables. A relationship establishes how the data from different sources should be correlated. You can create relationships between data sources by joining columns that contain same data.
You can manually create relationships or let PowerPivot to automatically detects existing relationships when importing data into a PowerPivot workbook.
This demonstration continues from the previous blog; Microsoft PowerPivot for Excel 2010 – Importing Data. So if you haven’t followed the last blog, you should go back and read it now. You can downloaded sample data from Codeplex Web site.
Why Create Relationships?
We have used VLookup to link data from different tables together so that we can do our in depth analysis. You can consider relationship is another form of VLookUp.
Relationships allow you to:
- Filter data in one table by selecting data from related tables.
- Integrate data from multiple tables into a PivotTable or PivotChart.
- Easily look up values in related tables using Data Analysis Expressions (DAX) formulas.
The latest version of Powerpivot (in Excel 2016) appears to be a lot different from the one described in this book (as Microsoft have made many changes in the 8 years since 2010 to Powerpivot, adding and removing many icons and adding more functionality, functions etc), so you should check before buying this book.
How to Load to Power Pivot in Excel 2010. When you click “Close and Load To” in Excel 2010, the dialog box is a bit different. Note that there is no option to send directly to Power Pivot like there was in the Excel 2013 example above. Powerpivot Excel 2010 X32 free download - PowerPivot for Microsoft Excel 2010 - x64, Timeline Excel 2010 Template, PowerPivot for Microsoft Excel 2010 - x86, and many more programs. PowerPivot for Excel 2010 is an unbelievable excel 2010 add-in as it delivers unmatched computational power from directly within the world’s most widely used spreadsheet application. The primary purpose of this add-in is to perform data analysis on large data sets.
Existing Data
You already have data from three different sources in your PowerPivot workbook:
- Sales and product data.
- Product category data.
- Store information.
Create New Relationships between the Stores and Entities
- Click the Stores tab.
- Right-click the EntityId column header and select Create Relationship.
- The Table field and the Column field are auto-populated in he Create Relationship dialogue.
- In the Related Lookup Table field, select dbo_DimEntity.
- In the Related Lookup Column field, select EntityKey.
- Click Create.
- You would find an icon displays at the top of the column which indicates the existence of a relationship.
Create New Relationships between the Stores and Sales
- Click the dbo_FactSales tab.
- Select the StoreId column.
- On the Design tab, click Create Relationship.
- The Table field and the Column field are auto-populated.
- In the Related Lookup Table field, select Stores.
- In the Related Lookup Column field, select StoreKey.
- Click Create.
Create New Relationships between the Products and Sales
- Click the dbo_FactSales tab.
- Right-click the ProductKey column header and select Create Relationship.
- In the Related Lookup Table drop down, select dbo_DimProduct.
- In the Related Lookup Column drop down, select ProductKey.
- Click Create.
Repeat the steps for the following relationships.
| Foreign Table | Foreign Column | Primary Table | Primary Key |
| dbo_FactSales | DateKey | dbo_DimDate | Datekey |
| dbo_FactSales | ChannelKey | DimChannel | ChannelKey |
| dbo_DimProduct | ProductSubcategoryKey | Dbo_DimProductSubcategory | ProductSubcategoryKey |
| dbo_DimProductSubcategory | ProductCategoryKey | ProductCategory | ProductCategoryKey |
- Click Manage Relationships on the Design tab.
- You can review all the relationships that have been created.
As long as you understand the data and the relationships among them, you don’t need an IT administrator to build the relationships. PowerPivot is a DIY tool!
Microsoft Excel Powerpivot 2010 Download
Andrew Chan is the owner and founder of ALG Inc.
We help you to make better and faster decisions!
Excel 2010 Power Pivot Download
This Excel tutorial explains how to create a pivot table in Excel 2010 (with screenshots and step-by-step instructions).
See solution in other versions of Excel:
What is a Pivot Table?
A pivot table is a tool that allows you to quickly summarize and analyze data in your spreadsheet.
You can use a pivot table when:
Powerpivot Excel 2010 Windows 10
- You want to arrange and summarize your data.
- The data in your spreadsheet is too large and complex to analyze in its original format.
If you want to follow along with this tutorial, download the example spreadsheet.
Steps to Create a Pivot Table
Power Pivot Excel Download
To create a pivot table in Excel 2010, you will need to do the following steps:
Before we get started, we first want to show you the data for the pivot table. In this example, the data is found on Sheet1.
Highlight the cell where you'd like to see the pivot table. In this example, we've selected cell A1 on Sheet2.
Next, select the Insert tab from the toolbar at the top of the screen. In the Tables group, click on the arrow under the PivotTable button and select PivotTable from the popup menu.
A Create PivotTable window should appear. Select the range of data for the pivot table and click on the OK button. In this example, we've chosen cells A1 to D13 in Sheet1 as indicated by
Sheet1!$A$1:$D$13.Your pivot table should now appear as follows:
Next, choose the fields to add to the report. In this example, we've selected the checkboxes next to the Order ID and Quantity fields.
Next under the Values box, click on the 'Sum of Order ID' and drag it to the Row Labels box.
Finally, we want the title in cell A1 to show as 'Order ID' instead of 'Row Labels'. To do this, select cell A1 and type Order ID.
Your pivot table should now display the total quantity for each Order ID as follows:
Congratulations, you have finished creating your first pivot table in Excel 2010!