Merge Queries in Power Query Editor

In Power BI we can merge queries, we are combining the data from multiple tables into one based on a column that is common between the tables. This process is like the JOIN clause in SQL.

Step 1: Let’s create the “Students” table.

Power Query M

let
    StudentTable = #table(
        {"Student ID", "Name", "Branch Code"},
        {
            {101, "Ashish", "ELE"},
            {102, "Neha", "MEC"},
            {103, "Raj", "CSE"},
            {104, "Divya", "ELE"},
            {105, "Sunil", "CSE"},
            {106, "Meena", "MEC"},
            {107, "Rakesh", "CIV"},
            {108, "Pooja", "ELE"}
        }
    )
in
    StudentTable

The output of the above code is shown below:

Step 2: Let’s create the “Branch” table.

Power Query M

let
    BranchTable = #table(
        {"Branch Code", "Branch Name", "HOD"},
        {
            {"ELE", "Electrical", "Dr. Singh"},
            {"MEC", "Mechanical", "Dr. Rao"},
            {"CSE", "Computer Science", "Dr. Gupta"},
            {"CIV", "Civil", "Dr. Mehta"}
        }
    )
in
    BranchTable

The output of the above code is shown below:

Step 3: Select the Students table and then click on Merge Queries and then Merge Queries as New.

Select the table and its Join Kind. A join kind specifies how a merge operation will be performed.

Note:

Column names don't need to match between tables for the Merge operation. However, the columns must be of the same data type, otherwise the merge operation might not yield correct results.
The position—left or right—of the tables becomes very important when we select the correct join kind to use.

There are different Join Kinds:

Left Outer: Returns all rows from the first table and matching rows from the second table. If there are no matches between the left and right tables, a null value is the result of the merge for that row.
Right Outer: Returns all rows from the second table and matching rows from the first table.
Full Outer: Returns all rows from both tables.
Inner: Only matching rows from both tables.
Left Anti: Returns rows from the left table that are not available in the right table.
Right Anti: Returns rows from the right table that are not available in the left table.

In the output we can see the rows it is going to return. We can also use the Fuzzy matching operation.

The resultant table is shown in the image below:

Now let’s expand the Branch column.

After expanding, we can see the output table:

Previous Next