If you’ve ever cursed your workbook’s labryinthine lookups and poorly performing PivotTables, you need Power Pivot. But, just like with its twin tool Power Query, it’s not so clear how to even load your data into it. Let’s take a look at how to do that with the three Superstore data files made famous by Tableau.
Setting up the Power Pivot add-in
Before getting started, check your home ribbon for a Power Pivot tab. If you don’t see it, follow these instructions to turn it on. Please note that Power Pivot is currently not available for all versions of Excel such as those for Mac.
Power Pivot versus the data model
Confused that you enabled something called Power Pivot to use another something else called the data model? I hear you. The difference is that the data model is just one element of the larger Power Pivot add-in:
As the diagram suggests, the data model is responsible for managing individual tables and relationships between those tables. Other PowerPivot tools such as KPIs and calculated measures in DAX are layered atop that “core” data model.
Loading to the data model through Power Query
While there are a number of ways to load tables to the data model, we will do so through Power Query. This way we can build any repeatable data cleaning processes atop these tables as the need arises.
Let’s start with the orders table. Click on any cell in the table and select Data > From Table/Range in the Get & Transform Data menu:
From inside the Power Query Editor, select Close & Load To on the home tab of the ribbon:
I would suggest selecting “Only Create Connection” as the output option so these queries aren’t needlessly printed to another worksheet. In any case, you must check off “Add this data to the Data Model” at the bottom to load the table into Power Pivot:
Click OK. You can follow the same steps to load the other two tables to the data model.
Viewing the data model
Next, let’s confirm that all tables have indeed been added to the data model. Navigate to the Power Pivot tab of the ribbon and click Manage:
Here is where we can manipulate and view the data model in a number of ways. Head over to the View group of the home tab and select Diagram View:
This will present a visual schema of each table in the data model and how they are related:
Currently, there are no relationships between these tables and we know this because there are no lines connecting them. Let’s do that now.
Creating a relationship between tables
Power Pivot and the data model is all about leveraging relationships between tables for enhanced data analysis. But we need to create those relationships first before manipulating with them. Think of the shared or “lookup” column that you would use to connect one data source to another with VLOOKUP(). Similar idea here to create the relationships.
First, we will connect returns to orders via the Order ID column. This can be done simply by clicking and dragging from one Order ID to the other. You should see a line form to denote the relationship between the tables.
We can do the same with the other two tables by connecting orders and people based on Region.
Once the relationships are formed you should see something like this:
Loading the results back to Excel
Nice work! You have now created a data model with relationship right from your Excel workbook. Head back to the Home tab of the Power Pivot editor, then click PivotTable. Go ahead and add the PivotTable to a new worksheet when prompted.
You now have a “Power” PivotTable in your workbook that will allow you to use the full power of DAX! This PivotTable will allow you to work with columns and measures from all three tables at once:
Because our source tables are located in the same workbook as the data model, you will see the table names listed twice in this PivotTable. You want to use the tables which include the cylindrical database icon as those that are part of the data model.
For example, we can pull data from both the returns and orders tables into the same PivotTable without a problem:
Recap
There is much more you can do with Power Pivot than our little PivotTable above, and even better ways to make the same PivotTable! But it all starts with loading in and setting up the data model, which you did successfully here. Nice work!
What questions do you have about Power Pivot? Do you see where this could be helpful for you? Let me know in the comments.
Leave a Reply