How to Merge Tables in Power BI

While working on Power BI reports, I receive data from multiple tables or different data sources. Many times, the information we need is split across these tables, and using them separately can cause issues when creating table visuals in Power BI.

To solve this problem, we need to merge tables in Power BI. We can do this using the Power Query Editor, or we can use the UNION DAX function to create a new table in Power BI.

In this tutorial, I will explain how to merge tables in Power BI. I will also show how to merge tables with different columns and how to merge tables using Power BI DAX.

Merge Tables in Power BI

For this example, we will use the Team Appearances data, which contains two tables: Team A and Team B, as shown below.

Team A:

Team B:

Now, merge the tables by following the steps below:

1. Open Power BI Desktop and load the two tables above.
2. Then, under the Home tab, click Transform Data to open the Power Query Editor.

3. In the Power Query editor, under the Home tab, expand Merge Queries, and you can see two options:
   • Merge Queries: Merges another table into the current table and updates it.
   • Merge Queries as New: Merges tables and creates a new table, without changing the original tables.

4. Then click Merge Queries as New. A pop-up window will open where you can select the table names and matching columns, and then click the OK button.

5. After you click the OK button, you can see that the two queries are merged and combined into a new table.
6. Next, expand the merged column and select the column you want to show in the new table. In this example, I will expand the Team B Members Count column.

Then, you can see that the value has been expanded and is displayed as shown below.

This is how you can merge two different tables in a Power BI report.

Merge Tables with Different Columns in Power BI

In the above example, after clicking Merge Tables, we selected the same column from both tables. However, we can also merge tables by selecting different columns. Now, let us see how to merge tables in Power BI using different columns.

In this example, I am using the same Team Appearances data.

To do this, follow the steps below:

1. Load the data into Power BI Desktop using Get Data. Then, click Transform Data from the ribbon to open the Power Query Editor.

1. In the Power Query editor, under the Home tab, expand Merge Queries, then click Merge Queries as new.
2. In the merge query, the pop-up window selects the Table names and columns, then clicks the OK button.

3. Then, expand the column and select the column you want to display in the merged table.
4. Next, you can see that it displays the team names for the selected value and, for unmatched values, the null value.

This is how to add a merge table in Power BI with different columns.

Merge two tables using Power BI DAX

In the above two examples, we saw how to merge two tables using the Power Query Editor. However, we can also merge or combine tables using DAX in Power BI.

In this section, let us see how to merge two tables using Power BI DAX and create a new table from existing tables.

1. Open Power BI Desktop and load the data that you want to merge.
2. Then, under the Modeling tab, click on New table.

3. In the formula bar, provide the following DAX expression:

Merged Table = UNION('Team A','Team B')

Where:

1. Merged Table: This is the name of the new table that will be created.
2. UNION: It combines rows from multiple tables into one single table.
3. Team A & Team B: All rows from Team A and Team B will be included.

The screenshot below shows the newly merged table values in the Power BI Table view.

This is how you can merge or combine two tables using DAX in Power BI.

Conclusion

In this tutorial, I covered different ways to merge tables in Power BI. I explained how to merge tables using the Power Query Editor, how to merge tables with different columns, and how to combine tables using Power BI DAX (UNION function).

You may also like:

• Remove Leading Zeros in Power BI
• Count Rows and Distinct Values in Power BI Power Query
• Convert Number to Text in Power BI
• Sort Slicer by Another Column in Power BI
• Best Way to Add a Hyperlink to a Text Column in Power BI