Traditionally, VLookup has been one of the most important tools for merging data in Excel, but the process requires multiple steps and can easily tire analysts who must merge multiple columns across many datasets. Instead, let’s take a look at how we can do this same process all within the Excel Power Query editor.
In this example, we’re using two individual datasets, the first containing basic member information, such as income, education, phone number, etc., and the second containing member loan information, such as the loan amount, interest rate, loan status, etc. Each dataset also contains a member ID, which will allow us to join the data on that common field in order to compare all of this data side by side. Here’s how to proceed:
- First, we’ll take a look at each dataset to roughly analyze their contents, and then open up a new worksheet for our merged dataset.
- Next, we’ll click on the “Data” tab of our new worksheet and select “From Text/CSV” because the files that we’re working with are csv files. You can also import Excel files by selecting “Get Data.”
- We’ll start by importing our file on basic member info, which will bring us into the Power Query editor.
- Next, we’ll bring in our other dataset by selecting “New Source” and “Text/CSV” and we’ll see the dataset on loan information added to the left-hand side.
- To merge these two datasets, we’re going to select “Merge Queries” and “Merge as New” so that we can have a separate space for our new dataset.
- We know that the common data field is “member ID” so we’re going to select that column for both datasets.
- Now, we’re going to delete some of the columns that we don’t need from our member information dataset. When we’re finished, we’re going to click on the icon next to “member_loans” to expand the dataset we just brought in.
- Here, we’re going to select the columns that we want from our member loans dataset. We don’t need to bring in member ID, as it’s already a column in our member info dataset.
- Once we’re finished selecting, we can rearrange these columns as it makes sense for our analysis.
- And finally, we’ll hit “Close and Load” to see the finished, merged dataset in our blank worksheet.