The Types of Join Supported in PowerQuery

PowerQuery supports merges and joins that are quite similar to the ones offered by SQL. There are six different types of join supported by PowerQuery, and each one has its own use cases.

The six types of PowerQuery Join are:

• Left Outer
• Right Outer
• Left Anti
• Right Anti
• Full Outer
• Inner

For tables to be merged, each of the tables must have the same fields. The fields do not have to have identical names, but the data types should be the same. Integers are more efficient to merge than text fields, but text fields can be merged. However, you should remember that case matters with text fields!

Outer Joins

The left outer join will take all the rows in the first table, and find matching rows in the second table. The right outer join is similar, but takes the rows from table two and matches the ones in table one. The full join will look at all of the rows from both tables.

Anti Joins

A left anti join will return only the rows that match from the first table. A right anti join will return only the rows that match from the second table.

Inner Joins

An inner join is similar to an outer join but it returns only the rows that match.

The Cross Join

There is another type of join, the Cross Join, that isn’t seen very often, which compares across both tables and is the ‘seventh’ type of join.

Common Sources of Confusion

As mentioned earlier, joining integer fields or other types of numerical field is fairly easy. You can join text fields, and it will work, but it is something that should only be done if there is a clear and sensible use case for it. When joining text fields it is important to remember that the join is case sensitive.

This is a common ‘gotcha’. Text joins can sometimes behave in unexpected ways if the fields are not exactly alike.

Note, also, that the names of the field do not have to match. Indeed, the field names will often differ (in a school database, you may be joining based on ‘ID’ in the student database, and ‘StudentID’ in the class database, for example). What matters is that the contents of the field can be compared easily.

If you are joining text fields, then you may want to add an extra step to the process to take the contents of each field and convert them to upper case, so that you can compare without fear of typos or inconsistent capitalization causing issues. This will remove one of the most common sources of issues when it comes to joining tables in PowerQuery. Just add an additional field to the table, and convert the field that you were originally intending to join. You can also do this with a script to convert as you go, but with very large databases, this approach could become rather slow, so if the speed of execution of the query is a problem then you may want to try a different approach.