Types of merging of queries in Power Query or in Power BI

This article describes the differences between types of merging of queries. The screenshots are from Power Query, but it works very similar in Power BI. 

We are talking about merging of queries by some key (ID). This is not about the appending of queries.

The file with queries can be downloaded from here.

We will use these two tables, that are supposed to be merged. The values from the orange one should be assigned to rows in the blue one, when the ID is key. 

We will create the queries to both tables, and then in merging window select, which columns are keys in the tables. And then we have to select the way of merging – which is what this article describes.

So what is the difference between these options?

Left Outer

In this connection, all rows from first table are taken and related values from second table are related. If there are more related rows in the second table, all of them are assigned (so values from first table are multiplied) (like number “2” here.

RIght Outer

The same, just from the other side.

Full Outer

In the result, there will be all rows from both tables. If they have adequate row on the other side, they will be connected. If not, empty values are created.

Inner

The result contains only rows, that have appropriate value on the other side – other rows are ignored.

Left Anti

The result contains the left table, where values contained in the right table are removed. In other words, the result says, what is in first table and at the same time is not in the second table (nice to comparison).

Right Anti

The same from the other side.