Join data with RELATED - Microsoft Excel Tutorial

- [Instructor] All right, next up let's talk about the related function. Now, related returns related values in each row of a table based on its relationships with other tables in the model. So, the syntax is incredibly easy. You just point it to a column reference, and that's the column that contains the values you're trying to retrieve. So, important note here. Related works almost exactly like a Vlookup. It uses those relationships between tables to basically reach into one table and retrieve values and pull them back into the column of another table. And since this function requires row context, you can only use it as either a calculated column, or as part of an iterator function that cycles through tables row by row. So, we talked about how filter was one example of that. And in the next video, we're gonna be talking about X functions like sumx, maxx, countx, and more. So, quick pro tip here. You might be tempted to use the related function because it's easy and because it's kind of nice and convenient to try to get all of your data together in the same place. But remember that by doing so, you're creating redundant calculated fields and you're increasing your file size. So I'd recommend only using related for calculated columns if you absolutely need to. Otherwise, there are generally ways to use related within functions like filter or sumx. So we're gonna hop into Excel and I'm gonna show you kind of part a of the related demo, which is the calculated column version. And then in the next video, when we talk about iterator functions, we're gonna revisit that related column and embed it into an iterator measure instead. Okay, back in the Foodmart data model, and I know it's been a while since we've done a calculated column. But let's go ahead and hop into our Power Pivot data model. And I'm gonna wanna be in Data View here. And let's go to the transactions data tab. Now, again, I'm gonna show you kind of part a of the related demo, which is the calculated column part. And, essentially what I'm gonna do here is add a calculated column here in the transactions table to retrieve some related information from the product lookup table. And the information that I'm gonna try to retrieve here is the product retail price. So, it's as simple as this. In the formula bar, I'm gonna type equals related. And I simply point to the table, product lookup, and the field that I'm looking for. Product lookup, product retail price, I'll tab to lock that in. Close the parenthesis and press enter. And there you go. It's got the row context that it needs to look up this product ID, follow the relationship to the product lookup table, find the retail price in a neighboring column, and then bring it right back here into the transaction table. So now let's name this header. Retail Price. And now it's gonna be even more important to use fully qualified column references, 'cause we've now got product retail price in the product table, and retail price here in the transaction table. So, there you go. Super simple. That's how you'd use related to follow a table relationships and retrieve related values from another table.