Power BI Union Two Tables [With Examples]

In this Power BI tutorial, we will learn What is Union Function in Power BI and how to Unite two different tables using the Power BI Union function. We will see different examples of Power BI Union Two Tables. Moreover, we will also cover below mentioned headings:

• Power BI union two tables DAX
• Power BI union two tables with different numbers of columns
• Power BI union two tables select columns
• Power BI union two tables power query
• Power BI union two tables distinct
• Power BI union two tables with filter

What is Power BI Union Function?

• The Power BI Union function creates a union that joins a table from a pair of tables or from two tables.
• It returns the new table with all the rows from the two different tables and the Power Bi union function must have the same number of columns.
• The below-highlighted code is the syntax of the Union function in Power BI.

UNION(<table_expression1>, <table_expression2> [,<table_expression>]…)

Power BI union two tables DAX

Let us see how we can union the two tables using the Power BI Dax union function in Power BI.

In this example, we are going to use the vehicles table data Excel sheet consisting of two sheets called Bikes and Cars as displayed below:

Bikes Table

Cars Table

• Open the Power Bi desktop, and load the data source into the desktop using the get data option. Once the data has been loaded select the New Table option presented under the Modeling tab from the ribbon.

• In the New table formula section, enter the below DAX formula to union the two different tables and select the check icon.

Union Table = UNION(Bikes,Cars)

Where,

1. Union Table  = New Table name
2. UNION = Function name
3. Bikes, Cars = Existing Table names

In the below screenshot, you can see that the new table unites and displays all the row data values from the existing two tables.

This is how to unite the two tables using the Power BI Dax union function in Power BI.

Power BI union two tables with different numbers of columns

Here we will discuss whether it is possible to unite the two tables with a different number of columns using the Union function in Power BI.

• No, it is not possible to unite two tables with different numbers of columns. To Unite two tables using the Power Bi Union function the column number count must be the same for the two tables else an error occurs.
• In the below example, I have explained the same vehicle data in different scenarios where the Bike Table consists of 4 columns, and the Cars Table consists of 5 columns I have added the Planned Sale Date column as an extra.

Bikes Table

Cars Table

Now load the data into the Power Bi desktop, Select New Table, and apply the below DAX formula to unite two tables with a different number of columns.

Unite Tables with different number of columns = UNION(Bikes,Cars)

• You can see the below screenshot, it displays the error as a ‘UNION ‘ must have the same number of columns.
• So it is not possible to unite two tables with different numbers of columns in Power BI.

It is not possible to unite the two tables with a different number of columns using the Union function in Power BI.

Power BI union two tables select columns

Let us see how we can union two tables by selecting columns presented in the existing using the UNION and SELECT COLUMNS functions in Power BI.

In this example, we will select the Bike Name Bike Model from the Bikes Table. In the same way, we will select the car names and Car Models from the Car Table using the UNION and SELECT COLUMNS functions in Power BI.

Load the vehicle data table data source into the Power Bi desktop using the get data option. Select New Table from the ribbon and write the below DAX formula and select the check icon.

United Table = Union(
selectcolumns(Bikes,"Bike Name", Bikes[Bike Names], "Bike Model", Bikes[Bike Model]),
selectcolumns(Cars,"car Name", Cars[Car Names], "car Model", Cars[Car Model])
)

Where,

1. United Table  = New Table name
2. UNION, select columns  = Function names
3. Bikes, Cars = Existing Table names
4. Bike Names, Bike Model, Car Names, Car Model = Existing Column names.

The Screenshot below displays the new table united and displays all the row data values only for the selected column values from the existing two tables using the UNION and SELECT COLUMNS functions in Power BI.

This is how to unite two tables by selecting columns presented in the existing using the UNION and SELECT COLUMNS functions in Power BI.

Power BI union two tables power query

Here we will discuss how we can union two tables using the Power Query editor in Power BI.

In this, we use the append query option to combine the two tables using the query editor in Power BI.

• Open Power bi desktop and load the source data using the get data option.
• Then click on Home ->transform data from the ribbon on the power bi desktop, it will automatically redirect to the power query editor, where we will union two tables.

• To combine the two tables, we will use the Append Queries feature option in the Power Query editor.
• In the Power Query editor. Then, click Home > Append Queries (drop down) > Append Queries As New as shown below:

In the Append Query Popup window, select the Two tables option and choose the First table as Bikes and the second table as Cars and then select the Ok button as below:

• In the below screenshot, you can see that the bikes and cars table is united and displayed as a new table. It displays the null values for the non-matching column names.

= Table.Combine({Bikes, Cars})

This is how to union two tables using the Power Query editor in Power BI.

Also, Read: Power BI combine columns from two tables

Power BI union two tables distinct

Let us see how we can union two tables by using the Power Bi DISTINCT and UNION functions of Power Bi.

In this example, we will remove the duplicate values and display unique values by using the Distinct function in Power BI.

• In the Power Bi desktop, load the data using the get data option. Once the data has been loaded select the New Table option under the Modeling tab.
• Then, write the below-mentioned DAX formula in the new table formula bar to display the unique values from the existing two tables.

United Table = DISTINCT(UNION(ALL(Cars),ALL(Bikes)))

Where,

1. United Table  = New Table name
2. UNION, DISTINCT  = Function names
3. Bikes, Cars = Existing Table names

• The Screenshot below displays the new table united and displays all the row data values from the existing two tables.
• It removes the duplicate values and displays only the unique value data from the existing two different tables using the UNION and DISTINCT functions in Power BI.

This is how to union two tables by using the Distinct and UNION function of Power Bi.

Power BI union two tables with filter

Let us see how we can union two tables with a filter using the Power BI Filter and Union functions in Power BI.

In this example, we will unite the bikes and car table and then we will filter the bikes and cars in red color using the Power BI Union and filter functions in Power BI.

• Open Power bi desktop and load the data using the get data option. Then select the New Table under the Table tools and write the below DAX formula in the formula bar and select the check icon.

United Filter Table = FILTER (
    UNION (
     SELECTCOLUMNS(Bikes,"Bike Name", Bikes[Bike Names], "Bike Model", Bikes[Bike Model],"Bike Color",Bikes[Color]),
     SELECTCOLUMNS(Cars,"car Name", Cars[Car Names], "car Model", Cars[Car Model],"Car Color",Cars[Color])
    ),
    [Bike Color] = "Red"
)

Where,

1. United Filter Table  = New Table name
2. UNION, FILTER = Function names
3. Bikes, Cars = Existing Table names
4. Bike Names, Bike Model, Car Names, Car Model, Color = Existing Column names.

In the below screenshot, you can see the new table filters and displays the data of the bikes and cars in red color from the united two tables.

In the same way, here you can see I have changed the color from red to Silver. So it filters and displays the data value from the existing united two tables based on the condition applied as shown below:

This is how to union two tables with a filter using the Power BI Union and filter functions in Power BI.

In this Power BI tutorial, we have learned about Power BI union two tables with various examples. Moreover, we also covered below topics:

• Power BI union two tables DAX
• Power BI union two tables with different numbers of columns
• Power BI union two tables select columns
• Power BI union two tables power query
• Power BI union two tables distinct
• Power BI union two tables with filter

You may also like:

• Power BI Group by Measure
• Power BI Multi-row Card
• Convert Text to Number in Power BI