Hello all and welcome back to the Excel Tip of the Week! This week, we have a Creator level post in which we are looking at the various options with Power Query’s Merge option.
If you want a refresher on Power Query, check out our recent webinar.
Merge in turn
Power Query’s Merge operation allows you to combine data from two or more data sources by reference to a key column. It’s similar to a lookup in regular Excel, only much more powerful. Here’s a basic use case:
The two simple tables on the left tell us customer values and the responsible salespersons; we can merge these using the Customer ID column to create the combined table on the right. This is automated so adding to either table later on will pull through to the combined table.
When creating this merge, we chose a join kind:
The kind used here was “Left Outer” – which we are told is all the rows in the first table, and those with a matching Customer ID from the second table. In previous posts in this series we’ve looked at the “Full Outer” kind – which takes all the rows from both tables. Let’s look at all the options.
Purchase orders and invoices
For this we’ll use these two small datasets:
We’re going to explore what we get out of merging these two tables (the PO one first), through each of these options:
This adds the invoice received data to our PO table where it is available.
Right Outer
As you would expect, this allows us to make the opposite to the Left Outer – a duplicate of the Invoices Received table, showing the PO data where we have it.
Full Outer
A Full Outer brings the full detail from both tables into the merge, allowing us to create a complete invoice table. This requires a bit more manipulation than the other kinds, as we have to merge the two incomplete Invoice number and Amount columns with custom columns.
Inner
This returns only the three invoices which have been both PO’d and marked as received.
Left Anti
This is a report of all the purchase orders for which we have not received an invoice.
Right Anti
Finally, we have the list of invoices received that we don’t have POs for.
It’s important to think carefully about what join kind is right for your use case – not only to get the right data, but because the ordering of tables can make a difference. While in theory a Right Outer is equivalent to doing a Left Outer with the tables in the opposite order, after any join the left table is always the one that’s fully loaded and the right one is always in a collapsed Table that needs expanding. So it’s much easier to have your primary table as the first (left) table.
You can see all these examples and work through the steps in the Power Query Editor in the attached file .
Excel community
This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.
