First, in the top part, you can select the column that you want to use for merging.

On the Home tab of the Ribbon, select Merge Queries. To create a query for that source, start by going to the Data (or Power Query) tab and selecting From Table/Range. In this example we will use a Table in Excel as the source. It could be from another workbook, a CSV file, a webpage, a database, or some other source. This other table does not have to be in the same workbook. Once we've established a connection for the lookup table, we can merge it with the data from another table. Step 2: Use the Merge Feature to Join the Tables From here, select Only Create Connection.

That brings up the Import Data window.

Click on the bottom half of the Close & Load split-button.

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). But for this step, we just want to create the connection without creating a new output table. Usually, when a query is run, it outputs the result in a new table in the workbook. To join two tables, we want to start by creating a connection-only query for the table that we will be looking up. Step 1: Create a Connection to the Lookup Table

And this tutorial will walk you through installing Power Query. That will give you a better understanding of how and why it's used. If all of this is sounding a little over your head because you are somewhat new to Power Query, take a break from this post and head over to my Power Query Overview. You can learn how to append tables in this post: How to Combine Tables with Power Query. If you are looking to combine data by stacking tables together, that is called an Append. However, Power Query allows us to automate this entire process, along with any other data cleanup work, and is less prone to formula errors. This is similar to what a VLOOKUP can accomplish with a formula. It basically creates a relationship between two tables to look up data and return matching results. The answer involves using the Merge (or join) feature in Power Query.

We received a great question from a member of the Excel Campus community, Bill Evans, who wanted to know how to take data from two tables that are formatted differently and combine them into a single sheet using Power Query.

How to merge cells in numbers for mac