I share in this post a recent learning about how to use data relationships in Excel (instead of e.g. INDEX/MATCH or XLOOKUP)
You can skip the part how to activate Power Pivot in Excel since it is now integrated by default in Excel:
Motivation
If I had known it was possible before, I wouldn't have used so much INDEX/MATCH formulas.
This is a nice intuitive way to expand a table with information from another table using a real relationship in the Data Model.
How To
See this nice video How to use Power Pivot in Excel | Full Tutorial - YouTube (by Kevin)
in the Menu Data -> (Data Tools) Manage Data Model
- Create a relationship between 2 Tables
- Add a Calculated Column using a formula using the RELATED function (in Power Pivot, not Excel)
If your have a Users Table with userid and name and want to expand the userid in another table with the user real name, it is as simple as adding a relationship (linking by userid the two tables) and add a 'Name' column with the formula RELATED(Users[Name]]
The Excel Table is then expanded with the calculated column.
Note: the related formula only works from a Table with a many-to-1 relationship.
If you have some duplicates in the target table, the relationship will automatically be forced to a 1-to-many and you can then not use this approach. Revert to the INDEX/MATCH formula in this case.
No comments:
Post a Comment