From the course: Excel: Managing and Analyzing Data

Overview of Power Query joins - Microsoft Excel Tutorial

From the course: Excel: Managing and Analyzing Data

Start my 1-month free trial

Overview of Power Query joins

“

- [Narrator] Since we're talking about managing and analyzing data, it is vital for me to share with you Power Query's 6 Joins. Now this is going to be a brief overview. And I do have a course on the library called Excel Power Query Getting Transformed, where I do a much deeper dive. Power Query gives us six joins in the menu. Full outer join, left outer, right outer, inner join, left anti join, right anti join. And they can be kind of hard to understand. Anti join? That sounds like the opposite of join. So, let's look at this example. We have two sets of data representing people who live in this spectacular lake front apartment building. We've got the owners and residents, and then notice that Andy is both an owner and a resident. Hannah is only a resident. Enid is both an owner and a resident. And Margie is only an owner. Okay? Let's look at the data this way. Now we've got three datasets. People who are owners only, people who are residents only, and the three people who are both a resident and an owner. Now, let's see this. Oh, the Venn diagram. Now it's clearer to see a left side, a right side, and an inner. And now let's step through the types of joins. A left outer join. That captures everybody on the left side. Everybody who is an owner and where they are also a resident, a match will be made. Okay, the left outer join is excluding people who are on the right side only. Next. Full outer join. We want everybody in both datasets and where there are matches we want them matched up. In this case, Andy, Enid, and Poppy, since they are on both datasets, their data will be matched up. Right outer join. We want everybody on the right side. And if they have a match on the left side, we want that. So we have the people who are only residents, and people who are residents and owners. The inner join, we only want the people who are on both lists are in both datasets. In this case, the people who own and live in this swanky building. The right anti joins. Anti joins mean we want in this case, everybody who is on the right side, who is not also on the left side. So here we only have residents, not resident owners. And finally, the left anti join. And you probably guessed. We have everybody who is on the left side only. We want only the owners. So now here is where this really gets tricky. When we look at the power query interface, this merge window, there's a top and a bottom, not a left and a right side. And so you have to tilt and think about the left side being the top, and the right side being the bottom. As long as you can remember that, you'll be okay. And that's our overview of the six joins.

Contents