Combine Multiple Data Sources with Power Pivot - Part 1

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

In general, when creating a PivotTable, our data is stored in one place. The data source is usually stored on a worksheet and we create a PivotTable based on the data source. 

However, what if we have multiple tables of data that we want to combine into one PivotTable? That is where Power Pivot and Power Query can help. 

Power Pivot is a utility in Excel that allows us to select multiple data sources and create relationships between the different data tables. We can use Power Query to combine files together and tidy up the data. 

Turn on the Power Pivot Ribbon

  • Click on File.
  • Click Options. 
  • Go to the Customize Ribbon page. 
  • In the list on the right-hand side, put a check next to Power Pivot. 

Combine Files and Link to a Folder

In our example, we want to build a Pivot Table that uses data from two transaction files, 'transactions_2019.xlsx' and 'transactions.2020.xlsx' that we have stored in a folder called 'Transaction_Data'. 

These files both contain the same column headings just different sales values. We can combine both of these files into one long file using Power Query. 

We can also set up a link to the 'Transaction_Data' folder so that when the 2021 file is added at the end of the year, all PivotTables and Pivot Charts automatically update to include the new data. 

  • From the Data tab, in the Get & Transform Data group, click Get Data, From File, and then From Folder. 
  • Select the folder 'Transaction_Data'. 
  • Click Open. 

  • From the menu at the bottom, click Combine and then Combine & Transform Data. 
  • Select 'Sheet1' to see a preview of the combined files. 

  • Click OK. 

The combined files will now open in the Power Query Editor. 

Tidy Data with Power Query

We can use Power Query to clean our dataset and prepare it for analysis in the PivotTable. 

We will not do a deep dive into Power Query but we will change a couple of things. 

For example, the first column titled 'Source.Name' shows us the file name for each record. We don't need this in our analysis. 

  • Click in the Source.Name column. 
  • Right-click and select Remove. 

It's also worth checking that the correct data type has been selected for each column. 

  • Click the icon to the left of the column heading. 
  • Change the data type where required. 

Load into Excel

Once we have finished tidying our data in Power Query, we can load it back into Excel. 

  • From the Home tab (in Power Query), in the Close group, click Close & Load. 

This will load the data back into Excel and format it as an Excel table. 

 

Login to download
  • 00:04 In this lesson, we're going to take a look at how we can use Power Pivot and
  • 00:08 Power Query to combine data together from multiple different sources and
  • 00:13 create one big PivotTable.
  • 00:15 Now, we've already seen in previous lessons how we can create a PivotTable
  • 00:20 based off of one data set.
  • 00:21 But what if we have data stored off in different tables within a worksheet?
  • 00:26 Or maybe we have data saved off in different workbooks altogether?
  • 00:31 What if we want to combine all of those tables of data from all of those
  • 00:36 different sources and use them in one PivotTable?
  • 00:39 Well, that is where Power Pivot can help us out.
  • 00:42 Now, before we begin, the first thing you need to do is make sure that you have
  • 00:46 the Power Pivot ribbon turned on and visible.
  • 00:48 So you can see mine up here, Power Pivot has a few different items on it.
  • 00:54 So if you can't see that, make sure that you jump into File,
  • 00:58 go down to Options, and Customize Ribbon.
  • 01:01 And then just simply make sure that you have a tick where it says Power Pivot.
  • 01:06 So let's start out by taking a look at the data that we want to analyze with
  • 01:10 a PivotTable.
  • 01:12 Now, notice here that I have opened up File Explorer and
  • 01:15 I have a folder called Transaction_Data.
  • 01:18 So if we double-click to go into this folder,
  • 01:21 notice that I have two files in there, transactions_2019 and transactions_2020.
  • 01:26 Now, if we open up transactions_2019 and take a look inside this file,
  • 01:32 you can see that we have a whole list of different transactions for the year 2019.
  • 01:38 Now, also notice we have the date of the transaction, we have a transaction ID,
  • 01:42 we have a product number, an account number, and then the amount.
  • 01:46 Now, just simply analyzing this data here in a PivotTable wouldn't be particularly
  • 01:51 meaningful, why?
  • 01:52 Well, we don't know which products this product number relates to based off of
  • 01:56 this data in this spreadsheet.
  • 01:58 The same for account number and transaction ID.
  • 02:01 Now, maybe I have this information stored off in different files.
  • 02:06 So if you go back to File Explorer, notice I have a file called Transaction_Analysis.
  • 02:12 And what we have here are basically two tables that add meaning to those account
  • 02:17 numbers and product numbers.
  • 02:19 So you can see here the account number is going to be one of these four numbers, and
  • 02:23 you can see the account that it relates to.
  • 02:25 The same for products, we have the product numbers here and
  • 02:28 the products that they relate to.
  • 02:30 So if I want to have meaning when I'm doing my analysis, I need to make sure
  • 02:35 I bring in these two tables as well as my transaction data information.
  • 02:40 Now, we're going to close this down for the time being, and
  • 02:43 just quickly jump back to our Transaction_Data folder.
  • 02:46 So we have two files in here, both of these are exactly the same,
  • 02:50 just the values are different because they're for different years.
  • 02:54 Now, it might be that we want to set this up so that when the next year's
  • 02:58 transaction file is added to this folder, any analysis that we've done,
  • 03:03 whether it be a PivotTable or a pivot chart, automatically updates to
  • 03:07 include that new data and we don't have to do too much work.
  • 03:11 So let's open up Excel and see how we do this.
  • 03:14 Now, the first thing we're going to do here is we're going to jump across to
  • 03:17 the Data tab, and we're going to go to the Get & Transform Data group.
  • 03:21 And if we click Get Data, we can go to From File, and
  • 03:25 one of the options here is to link to a folder.
  • 03:28 So when you link to a folder, anything new that you add to that folder is
  • 03:32 automatically going to be included in your analysis.
  • 03:36 So I'm going to choose the Transaction_Data folder,
  • 03:39 which is where I have those two files.
  • 03:41 Let's click on Open.
  • 03:42 So Excel is going to go away,
  • 03:44 and it's going to create a connection to that folder.
  • 03:47 And at this stage, I get a choice of what I want to do.
  • 03:50 Now, because these two files are exactly the same, as in the column headings in
  • 03:55 each file are the same, I'm going to combine them all into one file so
  • 03:59 I don't have them split across these two separate files.
  • 04:02 Now, I also might want to do a little bit of tidying up in Power Query.
  • 04:07 So if we take a look at the options that we have along the bottom,
  • 04:11 one of them is Combine & Transform Data.
  • 04:13 So that's exactly what I want to do.
  • 04:15 So Power Query is going to go away, it's going to combine those two files into one
  • 04:20 file, and then we can transform the data.
  • 04:23 So I now have the Combine Files dialog box.
  • 04:26 If I click on Sheet1,
  • 04:27 it's going to show me a preview as to what my file is going to look like.
  • 04:32 So that looks pretty good, let's click on OK.
  • 04:35 And it's going to combine those files and open them up in Power Query so
  • 04:39 that I can start to clean up that data if I need to.
  • 04:41 So now, what we should find is that if we go to the Date column, for example, and
  • 04:47 click the drop-down, I can see dates in there for both 2019 and also 2020.
  • 04:52 So the files have been combined into one.
  • 04:55 Now, at this stage, we can start to do some basic tidying up using some of these
  • 05:00 options in the Power Query Editor.
  • 05:02 For example, notice we have this Source Name column at the beginning,
  • 05:06 which is just showing us the file that this data is coming from.
  • 05:09 Now, I don't really need to include this in my analysis, so
  • 05:13 I'm going to right click and choose to remove this column.
  • 05:16 Now, pretty much everything else looks pretty good.
  • 05:18 We don't have a great deal of data in here,
  • 05:20 I just want to check that the data types are correct for each column.
  • 05:24 So notice here, we have Date, that's fine.
  • 05:27 This one is the whole number, that's fine because it's a transaction ID.
  • 05:32 Same for product number, account number, that's fine.
  • 05:36 The amount, well, I'm going to change this to currency.
  • 05:39 And that is all the tidying that I'm going to do.
  • 05:41 Once we're happy, we can click Close & Load.
  • 05:45 And that's going to load it back into Excel.
  • 05:48 And hopefully, it puts the information into an Excel table for us.
  • 05:52 And you can see it's given it the default name of Transaction_Data,
  • 05:57 which is perfect.

Lesson notes are only available for subscribers.

Gift this course