Find Records that Don't Have a Match in Both Tables

This video covers the ANTI JOIN. This occurs in SQL when two tables returns rows from the first table where no matches are found in the second table. It's consider the opposite of a semi-join when in two tables the result returns rows from the first table where one or more matches are found in the second table. In this example an anti-join is finding records where there's no match between both tables. However in Excel Power Query, there is not anti join option when using the merge feature. However the workaround in to use the left and right anti join features and then append the results. Confused? Well check out the video to see how it could be done.

Create a Full Outer Join in Excel Power Query

If you wanted to merge two tables together to figure out what records may have "missing', blank or null values in the fields, a full join or full outer join may be the solution for you. The use for this type of join can be for reconciliation or trying to discover what type of values you don't have from one table to another. If you want to see all records from table, look into using the full outer join merge capability in Excel Power Query.

Lookup Two Tables to Bring Back Matches in Both

This video will cover the INNER JOIN merge capability in Excel Power Query. This type of join returns records from two reference tables that have matching values in both tables. We are basically looking for matching records from two tables and only those records that have a common field ( or key) are brought back in a results table. In INNER JOIN comes from SQL and it's among the different join types available. It's nice that it comes with Power Query so you don't need a fancy database like Oracle, SQL Server or MySQL.

Use the MAXIFS and MINIFS Functions

If you've got Excel 2016, you've got the MINIFS and MAXIFS functions. They let you find the minimum (lowest) and maximum (highest) values based on one or more conditions (criteria). The criteria can be numeric or text and this video will show you how.