Combine Multiple Data Sources with Power Pivot - Part 2

Locked lesson.

Upgrade

Quick reference

Combine Multiple Data Sources with Power Pivot and Power Query

Learn how to create a PivotTable that uses data from multiple sources. 

When to use

We create a PivotTable using Power Pivot and Power Query whenever we have data stored in different sources. 

Instructions

Combine with Other Files

Now we have our combined transaction files loaded into Excel, we also want to combine with two more tables of data, 'Accounts' and 'Products' that are stored in the 'Transaction_Analysis.xlsx' workbook.

  • Click in the Transaction_Data table. 
  • Go to the Power Pivot tab.
  • Click Add to Data Model. 

The transaction data will now show in a tab in the Power Pivot window and we can bring in other tables of data. 

  • From the Home tab, in the Get External Data group, click From Other Sources. 
  • Scroll down and choose Excel File. 
  • Click Next. 
  • Click Browse. 
  • Select the 'Transaction_Analysis.xlsx' file. 
  • Select both tables, 'Products' and 'Accounts'. 

  • Click Finish. 
  • Click Close. 

Create a Consolidated PivotTable

Now we have all tables added to the data model, we can create a consolidated Pivot Table. 

  • From the Home tab (Power Pivot), click the drop-down under PivotTable. 
  • Click PivotTable. 
  • Select New Worksheet. 

A blank PivotTable report will be created in Excel. The PivotTable Fields pane will show all tables and fields. 

In theory, we should be able to use any field from any table to build our PivotTable report. 

  • Drag the Products field from the Products table into Rows. 
  • Drag the Amount field from Transaction_Data into Values.

Notice that the values in the Sum of Amount column are wrong. Also, notice the yellow warning message. 

We are getting these issues because the tables are not linked. In order for Excel to calculate correctly, all tables need to have a relationship. 

We create relationships between tables using a common field. If tables do not share a common field, we can create a link table that uses a field from one table and a field from another. 

That is outside the scope of this course, but it's worth bearing in mind. 

Create Relationships Between Tables

Tables can only be linked by a common field. 

  • From the Power Pivot tab (in Excel), in the Data Model group, click Manage. 
  • From the Home tab (in Power Pivot), in the View group, click Diagram view. 

Currently, our tables are not linked together. 

  • Drag the AcctNo field from the Accounts table and drop it onto the AcctNo field in the Transaction_Data table. 

  • Drag the ProdNo field from the Products table and drop it onto the ProdNo field in the Transaction_Data table. 

When we go back to Excel, the PivotTable can now calculate correctly. 

Login to download
  • 00:03 So now that we have this data in a table,
  • 00:06 we need to bring in those other two tables from the transaction analysis file.
  • 00:12 So I'm going to click in my transaction data table, we're going to go up
  • 00:16 to the Power Pivot ribbon, and we're going to say, Add to Data Model.
  • 00:20 So this now opens up Power Pivot and you can see we have a tab at the bottom
  • 00:25 called Transaction Data, and it's showing that information.
  • 00:29 So what I can now do is I can add in those other tables.
  • 00:33 So if we go up to the Home ribbon, choose From Other Sources, and
  • 00:37 scroll down, we can choose to import data from another Excel file.
  • 00:42 Let's click on Browse and those two tables were in this file just here,
  • 00:47 Transaction Analysis.
  • 00:48 Let's click on Open.
  • 00:50 I'm going to say yes, Use the first row as column headers, click on Next and
  • 00:55 you can see it's found both of those tables.
  • 00:57 So I want to select both of them and then click on Finish.
  • 01:01 So you can see it's transferred successfully and when we click on Close,
  • 01:05 I now have two additional tabs just here with the account details and
  • 01:09 the products information.
  • 01:11 Now, if I was to try and
  • 01:12 create a PivotTable at this stage by jumping up to where we have PivotTable and
  • 01:17 choosing that option, and we're going to put it on a new worksheet.
  • 01:21 You can see over in the PivotTable fields area, we now have access to
  • 01:26 the fields from all three of these tables as opposed to just one table.
  • 01:31 But check this out.
  • 01:32 If I try and use let's say, I want the product names in the rows,
  • 01:37 all looks good so far.
  • 01:38 But then if I try and grab a field from a different table, for
  • 01:42 example, the amount, check out what happens.
  • 01:46 I'm getting the same amount repeated over and over again.
  • 01:51 Now why is that?
  • 01:52 Well, check out this little yellow message that we have.
  • 01:55 It says that there are no relationships between these tables.
  • 01:59 So Excel considers all of these three tables to be completely separate and
  • 02:03 not linked together in any way at all, which is why it can't calculate correctly.
  • 02:08 So, what we're going to do, is we're going to jump back to our Power Pivot
  • 02:13 window and we can just do that by clicking Manage Data Model.
  • 02:16 And we're going to jump into Diagram view.
  • 02:19 And this is where we can see a view of all of the tables that we have.
  • 02:23 This is our main table transaction data and then we have products and accounts.
  • 02:28 And you can arrange these however you like.
  • 02:31 And what I can do here is link the accounts and
  • 02:34 products table to the main transaction data table using a common field.
  • 02:38 And that's really important, in order to do this and link these,
  • 02:43 they need to share a field.
  • 02:44 So, for the accounts table, the account number is the shared field,
  • 02:48 we have that in both tables.
  • 02:50 So, I'm going to drag and drop to account number.
  • 02:53 Now we're going to do the same with the products table,
  • 02:56 we're going to drag and drop product number onto the table.
  • 03:00 So now these are linked by common fields.
  • 03:02 So what we should find is when we close this Power Pivot window,
  • 03:05 all of our numbers have automatically updated because Excel can now work out
  • 03:09 the calculations because everything has a relationship and is linked together.
  • 03:13 And the beauty of this is that we can now use any field from
  • 03:14 any of the tables that we've imported to perform our analysis.
  • 03:16 And the cool thing about this is that because of
  • 03:21 the way that we've set this up by linking to a folder,
  • 03:26 every time that we add a new file to that folder,
  • 03:31 everything is going to update.
  • 03:34 So if we go back and I've got a folder here called New Data,
  • 03:39 which contains the transactions 2021 file.
  • 03:42 So if I copy this, Ctrl C, to the Transaction Data folder, Ctrl V.
  • 03:47 So what we should find is that if we go back to that Transaction Data table, click
  • 03:53 on the Query tab and choose to Refresh, is going to pull through that new data.
  • 03:59 And if I click the Date drop-down, you can see there we have 2021.
  • 04:04 Now, when I go to my PivotTable and
  • 04:09 refresh this as well and scroll down,
  • 04:15 there is our 2021 data.

Lesson notes are only available for subscribers.

Gift this course