Power BI Beginner Level: 9 - Merging Tables

Table of Contents | Sample Input | Power BI Report File

Short summary: You can merge two tables in Power BI to add columns of the second table into the first table. For this you need to indicate by which columns to match both tables. This operation is equivalent to Excel’s VLOOKUP and SQL’s LEFT JOIN command but much easier. Merging tables is one of the most popular functionality when it comes to preprocessing of data.

In many business scenarios, we have several tables of input data which we need to analyze together. The simplest way of doing that is combining two (or more) tables into one table and then analyzing that one table.

There are two ways of combining tables:

• Merging, i.e. putting the tables beside each other
• Appending, i.e. putting the tables below each other

In this article I will teach you how to merge two tables. Before we can do that, we need to load the data into Power BI. You can merge two tables in Power BI if the second table has additional columns (=attributes) which you want to add to your first table.

Let's look at the sample input data below: You have a sales table on the left detailing sales transactions. Another table contains details about the products, including the product cost. Let's assume that you want to calculate the profit as sales amount minus product cost. For this, you need go get data from the second table side-by-side into the first table row-by-row using matching product name. In the old Excel way you would do this with the formulas VLOOKUP or XLOOKUP, in SQL databases you would do this with a LEFT JOIN. In Power Query it is referred to as a Merge.

To facilitate this, first load both tables into Power BI. You can download the sample input data and load it into Power BI using the Excel connector. Note you can only process data which you connect to in Power BI and this is read-only, meaning that the input data (in this case the Excel file) is only read from but never changed by Power BI.

Then go into the Power Query editor (via Transform data on the Power BI Home ribbon). In the Power Query editor, choose Merge Queries and then Merge Queries as New.

Select the two tables which you want to merge and then in each table select the column by which you want to merge by. Note that on the bottom you can select the join type. There are six join types to choose from, however you will use the default join type Left Outer most of the time. As the description says, it means taking all rows from the first table and adding matching columns from the second table, as you would when using VLOOKUP / XLOOKUP in Excel. You can read up on the other join types here. Also note that on the very bottom, you get the information of how many rows matched, in this case 10 out of 11 rows.

After you click Ok, you will get the second table in a new column. You need to expand that table by clicking the icon with the two arrows to the right of the column header. Next select the columns which you want, unselect “Use original column name as prefix”, and click Ok.

Note that in one query you can merge as many tables as you want by selecting Merge Queries on the home ribbon and repeating the process. This way you can easily merge all your data together into a wide table. This is great for Power BI beginners and much easier than doing the same thing in Excel with VLOOKUP / XLOOKUP. Note that even in Excel, you can use

Finally, let's display the merged table in the Power BI report as a table visual. We will add the profit column in a later article.

And there you have it, merging (joining) tables in a nice interface without writing a single line of code! Go give it a try and see you in the next article, where I will cover the other type of combining tables: Append.

Please like, share, and subscribe and feel free to ask questions in the comments below.

Next article:?Appending Tables