Below are the steps for merging these two tables: Click on any cell in the Customer Info table. Go to the INSERT tab and click on the Table option under the Tables section. Refer to the below screenshot. May 20, 2020 To combine, or append, your tables together, you need to create a connection to each of them in Power Query. Go to the Power Query editor by clicking on From Table/Range on the Data or Power Query tab (depending on which version of Excel you are using). This brings up a preview of your data. To create a connection. Combining multiple tables in a PivotTable The scenario. These data sets could be on separate worksheets, but for ease of demonstration, I have included them on. First, we need to turn our data into Excel tables. This puts our data into a container so that Excel. I have a few tables imported from two different systems/databases/tables and I need to merge them into one table in Power Pivot. All of the import tables have some common fields such as Name, Value. Staff Expenses, Staff Sales table. The output table at lest with 3 columns (Name, Value, and Data Source).
Seldom does the user of PowerPivot have all of the data they need in one nice, neat data source. More than often it will be necessary to import data from a variety of sources and make that data work together. It's time to start building on what we've learned over the last few days to accomplish this feat.
First, launch Excel 2010 and use the PowerPivot import wizard to import the following tables from the AdventureWorksLT2008 database: Address, Customer, CustomerAddress, Product, ProductCategory, SalesOrderDetail, SalesOrderHeader. (Note, for a refresher on importing data please see my blog post, Import Filters in PowerPivot.)
Now we need a second source of data. Follow the instructions in my post Creating Tables in PowerPivot to enter the data below into Excel 2010, copy and paste it into a new PowerPivot table.
If you recall when we import data from a relational database, PowerPivot examines the foreign key relationships found in the database to create relationships between the tables it imports. In this situation though, the CountryInfo data didn't get imported from a database, instead it was pasted in from a manually entered spreadsheet. Thus, PowerPivot has no information with which it can implicitly create a relationship.
We do want to create one however, so we can link the longer country name in the Address table to the CountryInfo data and thus be able to use the briefer country abbreviations. As PowerPivot was designed to work with many sources of data, it has an easy way to create these relationships.
In the PowerPivot window, click on the Table tab at the very top. All the way to the right you will notice a button group named Relationships. Click the Create Relationship button.
As the above dialog shows, this allows you to create a relationship, or a link between two tables in PowerPivot. Here we are creating a link between the Address table and the CountryInfo table on the CountryRegion field. When complete just click Create to create the relationship.
Merge Two Tables In Word
If you want to verify the relationship was indeed created, or review any of the relationships PowerPivot inferred when it imported the tables from the AdventureWorksLT2008 database, just click the Manage Relationships button in the Table Toolbar's Relationships group.
On the very first row you'll see the newly created relationship between the Address and CountryInfo tables. You'll also see the other relationships that were created during the import process from the SQL Server database. The three buttons at the top let us Create new relationships, Edit existing ones, or Delete ones no longer needed. Note that the altering or deleting of relationships has no effect what so ever on the original source data (SQL Server or the Excel 2010 spreadsheet). It only affects the tables as stored in PowerPivot.
Now let's see the new relationship in action. Close the Manage Relationships window, and on the PowerPivot Home tab create a new PowerPivot table (Pivot Table, Single Pivot Table). Go ahead and put it in a new worksheet.
In the Gemini Task Pane, go to the SalesOrderHeader table and drag the LineTotal field into the Values area. Next, drag the Name field from the Product table into the Row Labels area. Now for the magic, in the CountryInfo table drag the CountryAbbr field into the Column Labels area. Your pivot table should look something like this:
Because of the relationships that were inferred or that we created, PowerPivot was able to link the data like so:
1. SalesOrderDetail linked to SalesOrderHeader on the SalesOrderID column.
2. SalesOrderHeader linked to Address on the ShipToAddressID=AddressID.
Power Pivot Append Two Tables
3. Address linked to ContryInfo on the CountryRegion column.
In the Gemini Task Pane, go to the SalesOrderHeader table and drag the LineTotal field into the Values area. Next, drag the Name field from the Product table into the Row Labels area. Now for the magic, in the CountryInfo table drag the CountryAbbr field into the Column Labels area. Your pivot table should look something like this:
Because of the relationships that were inferred or that we created, PowerPivot was able to link the data like so:
1. SalesOrderDetail linked to SalesOrderHeader on the SalesOrderID column.
2. SalesOrderHeader linked to Address on the ShipToAddressID=AddressID.
Power Pivot Append Two Tables
3. Address linked to ContryInfo on the CountryRegion column.
To validate this for yourself, just return to PowerPivot and look at the Manage Relationships dialog to see all the links.
The need to combine data from many sources is a common task, one that will most certainly be done by users of PowerPivot. Using the techniques shown here, you can create and manage the relationships that will link data from these disparate sources together and leverage the power of PowerPivot.
