A common requirement when building BI solutions is the need to join two tables together to form a new result that includes some columns from both tables in a single query. Fortunately, Power BI makes this task very simple with the Merge Queries feature. Using this feature requires that you select two tables and then determine which column or columns will be the basis of how the two queries are merged. After determining the appropriate columns for your join, you will select a join type. The join types are listed here with the description that is provided within the product.

Left Outer (all from first, matching from second)
Right Outer (all from second, matching from first)
Full Outer (all rows from both)
Inner (only matching rows)
Left Anti (rows only in first)
Right Anti (rows only in second)

Many of you may already be very familiar with these different join terms from SQL programming you have learned in the past. However, if these terms are all new to you I recommend reviewing Visualizing Merge Join Types in Power BI, courtesy of Jason Thomas in the Power BI Data Story Gallery: https://community.powerbi.com/t5/Data-Stories-Gallery/Visualizing-Merge-Join-Types-in-Power-BI/m-p/219906. This visual aid is a favorite of many users that are new to these concepts.

To examine the Merge Queries option, you will pick up from where you left off with the Unpivot examples in the previous section.

With the Population query selected, find and select Merge Queries | Merge Queries as New under the Home Ribbon.
In the Merge dialog box, select the Income query from the dropdown selection in the middle of the screen.
Then, multi-select the Country and Year columns under the Population query, and do the same under the Income query. This defines which columns will be used to join the two queries together. Ensure that the number indicators next to the column headers match. If they don't, you could accidentally attempt to join on the incorrect columns.

Next, select Inner (only matching rows) for the Join Kind. This join type will return rows only when the columns you chose to join on exist in both queries. Before you click OK, confirm that your screen looks like this:

Once you select OK, this will create a new query called Merge1 that combines the results of the two queries. Go ahead and rename this query Country Stats.
You will also notice that there is a column called Income that has a value of Table for each row. This column is actually representative for the entire Income query that you joined to. To choose which columns you want from this query, click the Expand button on the column header. After clicking the Expand button, uncheck Country, Year, and Use original column name as the prefix then click OK.
Rename the column called Income.1 to Income.
Finally, since you chose the option Merge Queries as New in Step 1, you can disable the load option for the original queries that you started with. To do this, right-click on the Income query in the Queries pane and click Enable Load to disable it. Do the same thing for the Population query. Disabling these queries means that the only query that will be loaded into your Power BI data model is the new one, called Country Stats:

To begin using this dataset in a report, you would click Close & Apply. You will learn more about building reports in Chapter 5, Visualizing Data.

In this section, you learned how the Merge Queries option is ideal for joining two queries together. In the next section, you will learn how you could solve the problem of performing a union of two or more queries.