Oz du Soleil demonstrates the Right Anti join in Get & Transform to merge two data sets. Oz shows you both the why and the how.
- Let's check out the right anti-join. You and I are hosting a new product orientation, and we've got two sets of data: the people who are required and people who are enrolled. We've got a question. Who is enrolled and is not required? We might have a situation where we're running out of space for people who are required and we'd like to know exactly who's enrolled and not required so we can get in touch with them to see if they can wait for another session.
To dig that information out, we're going to use the right anti-join. Our data sets are already in tables, so we have to make our queries. My cursor in the data set. Go to Data, From Table. Here is our required set of data, and I want to close and load this as a connection only because we're not doing anything to this data. If I load it to the workbook, it's just going to be the same thing that we already have. Hit the arrow, Close & Load To, Only Create Connection, Load.
And notice, in the workbook's query pane, this required query is indeed loaded as a connection only, so let's make our enrolled query. With the data in the data set, we're in Data, From Table. Hit the arrow, Close & Load To, Only Create Connection, Load. Now we're ready to do our right anti-join to find out who's enrolled and not required.
In the Data tab, New Query, Combine Queries, Merge. Let's go and select Right Anti, all the way at the bottom. And now, thinking about a Venn diagram as left and right, we have to think about this interface as top and bottom. The left side in the Venn diagram is going to be the top on this interface, so our Required, that's what we want up top. The bottom, Enrolled.
The fields that we want to match: Name. Name. OK. This might look funny because, look, we have the left side and the right side. The left side is expanded as the Name and Department. The right side is collapsed. And what did we want? We want the data on the right side that does not have a match on the left side, so we exactly want null on that left side. Let's expand the right.
We don't want to use original column name as prefix. OK. Now let's close and load it to the workbook. Bring it up a little closer. And here's our data telling us exactly what we wanted to know, who is enrolled and not required. So for the people who we want to see if they can wait for another session, we've got to contact Percival, Andrea, Zack, and Jo. And we were able to extract this information with the right anti-join.
- What is Get & Transform?
- Querying data
- Working with columns
- Using formulas
- Pivoting and unpivoting data
- Grouping data
- Appending a query
- Merging/combining data with joins