In my previous article http://youngsday.com/2013/08/21/entrepreneur-toolkit-1-powerpivot-intro/ I have introduced Microsoft BI tool named PowerPivot.
To begin with, the main advantages that PowerPivot provide are:
- Ease of Use (with DAX – Data Analysis Expressions formula)
- Relational Data (Relational data model based on tables, columns and relationships)
- Pivot Tables
DAX formulas is another topic to be described in more than 2 articles. So let’s skip that part for now.
I’m assuming that most of you already know the formulas that are used to link two different columns from same/different tables. The first one that comes to your mind is probably VLOOKUP and HLOOKUP functions.
Here is the good news:
In PowerPivot, you don’t need to get all the columns from the table array (meaning col_index num) one by one. Once you create a relationship between two tables according to one common column, you can use it for as many columns as you want.
- A relationship is a linkage between two data sources that establish how data should be correlated together in PowerPivot
- Relationships join data together from unrelated sources.
- Some relationships are created automatically in data imports while others require manual creation in PowerPivot.
In this illustrative, the objective is to match customers with the relevant sales data. Instead of writing many vlookup formulas next to each other, you can just create a relationship between these two tables, using “name column” as lookup value in both tables. Please see the illustrative below:
Once you click “create relationship” in Powerpivot Window > Design, you will have insert your table, column (to be matched), related lookup table, related lookup column. So for the previous example;
Table: Customer table – Column: Customer table[name] – Related Lookup Table: Sales Data – Related Lookup Column: Sales Data[name]
Most probably you will be working with multiple tables and multiple columns, which means you can easily get lost within the same document. Or, you might need to change or delete the relationships between tables. Here is what you need to do:
If you find it hard to read from a list of tables, you can also view your relationships from “diagram view”, as below:
In my next article, I will explain DAX formulas in detail, which also contains RELATED function for using these relationships.
Image source: Flickr