Hello all. In this section let’s look at the basics of power pivot. Power pivot is one of the powerful tools which can help in getting multiple data source and create a pivot table out of it. The data source can be data thru ODBC, from database (like SQL, Access), Azure, Teradata, other excel sheets etc. In this section, we shall create relationship between 3 different excel sheets and build a pivot table to interactively play with data from all 3 worksheets.
Here, we have created 3 x worksheets – HR data that has basic employee data, L&D data where we track number of trainings employee took and Sales data for the employees. To create relation between all 3 excel sheets we need to have a common data – in our case that’s the employee ID
Let’s open a new excel sheet -> Go to Data Tab -> under data tools, click power Pivot. This will enable Power Pivot ribbon tab. Under power pivot Tab -> click Manage. This will open the power pivot window
We have to get all the 3 x excel sheets into this sheet. In the power pivot window, under Get External data section -> click “From Other Sources” -> Scroll all the way down and select Excel File -> click Next -> Browse and select the 1st Excel -> Ensure you check the “Use first row as column headers” -> click Next -> Edit the Friendly name -> click Finish. Short video on the above steps below:
Follow the above steps for extracting all the 3 excel sheets. Once extracted all, the power pivot window should look like this – with the 3 tabs with the friendly name as we chose.
Now, we need to create relationship between all three sheets. As we know the common field for all 3 is Emp#. In the power pivot window, under view -> click diagram view to get all the 3 sheets in a pictorial view -> Select Emp# attribute in the LD sheet and drag it to the HR Data sheet’s Emp# -> Select Emp# in the Sales sheet and drag it to the first sheet. Short video here:
Now that we have made the relationship, next let’s build the pivot table. In the same power pivot window, click on pivot table -> choose the destination for the pivot table. In the PivotTable Fields we shall see all the sheet’s data. Now we can play with the data interactively. For example, we can take Emp# from HR data, take number of Mandatory courses each employee did from LD data and take total sales value for each employee from Sales data.
We can play with the data as much as we like from all the 3 x excel sheets. This was a quick and very basic section of what a power pivot is and what it can do.
Here is the video tutorial on power pivot
I hope you like this section and please provide us with your feedback to improve the content. Thanks for reading.