Full Anti-Join and the New XLOOKUP with “if not found”

This video is a 2-for-1. You get the new XLOOKUP and a Full Anti Join in Power Query (Get & Transform).

What is a full anti-join? If there was a class held on Monday and Wednesday and you’d like to know who attended one session or the other BUT NOT BOTH, you would need a full anti-join. One way to do this would be to do a left anti-join and then a right anti-join, then append the results.

Another way, as I show in this video, is to append the 2 datasets, create an inner join, then use an anti-join to isolate the people who are in one group or the other but not both.

XLOOKUP!
WOW! This was revised within the last 24 hours to include a 6th argument for: “if not found.”

This is exciting because a lot of times we’ll do a lookup and anticipate results where the lookup value isn’t found. The fix is to wrap a lookup formula in the IFNA function. No more!

The 6th argument, “if not found” is a valuable feature because we no longer need the extra step of wrapping IFNA around the formula. And the 6th argument is optional.

#FullAntiJoin
#PowerQuery
#InnerJoin
For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2