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
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.
No comments:
Post a Comment