Combining information from two or more separate tables or lists is a common Excel requirement. The various Excel lookup functions can help achieve this, as can the ability to merge tables in Power Query. However, whichever method you use to combine the separate data sources, items missing from either table, or mismatched items, could cause errors. For example, if you had a list of product sales and a list of product categories, and you needed to calculate total sales by category, were one or more products to be missing from the product category table, then the overall totals might omit the sales values of those missing products.
Power Query join kinds
Here are our two example tables. We will click in each in turn and use Data Ribbon tab, Get &Transform Data group, From Table/Range to load each one into the Power Query editor. Initially, we will then just use The Close & Load dropdown in the Power Query editor Home Ribbon tab to choose Close & Load to… and set the ‘load to’ location as: Only Create Connection. This makes the Table available in the Power Query editor without loading it to a Table in the workbook:The first stage of the merge process is to choose each of our Tables in turn using the dropdowns. We then need to tell Power Query how to match our Tables. In our case, we will use the ProductID to make the match so we click on the ProductID column heading in both Tables. Note that it is also possible to match using multiple columns.
Once we have nominated our matching columns, we can choose the Join Kind from the dropdown. Usually, this is likely to be one of the Outer or Inner kinds, but we are specifically looking for items that don’t match, so we will start off by using the Left Anti join to find rows that are missing from our second Table:
When our source Tables change, we can just refresh our exception Tables to see whether there are any unmatched items.
Related links
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.