1. Getting started in Power Pivot
Hey there! Throughout the screencasts in this course, we will play the part of analysts working to help a fictional gym, PowerPivot Gym, analyze their class attendance rates for April 2024. We’ll do this all by using Power Pivot. Let’s get started!
The first thing we need to do is enable the Power Pivot add-on. Go to File, look for Options, all the way at the bottom left, and then go to the Add-ins section. At the bottom, select COM Add-ins, then click Go. Check the box for Power Pivot, then click OK.
Now Power Pivot appears in our ribbon bar. Let’s add data from our workbook directly to the data model by clicking on Add to Data Model, selecting the data range, and then OK.
This has brought us into the Power Pivot window, where we can manage our data model. This is a great start, but what if we need to connect to external data?
There are a lot of options to choose from here. However, Power Pivot lacks data transformation tools, which means that the data needs to be clean and ready to use before we import it, which is rarely the case, so it’s better to use Power Query to connect to external data. Based on this course’s prerequisites, you should already be familiar with Power Query, so let’s review how to do this.
Go back to our workbook, click Get Data, then from Excel workbook. From here, we can browse for our workbook. We’ll select the sheet we want and click transform. Now we’re in the Power Query Editor. This is where we can preview and make changes to our data. When importing a dataset, it’s always a great idea to explore it first, and we should enable the Column Quality, Column distribution and column profile under the Data Preview options in the View ribbon.
Now, let’s review the data, and I’ve already noticed some problems. First, the time column is formatted as a date-time, which gives us weird-looking values. Let’s fix this by changing the data type to time, and we can replace the previous step since we’re correcting it. Remember that Power Query records the steps here in the right pane under applied steps whenever it does data transformation.
The next thing that looks bad is the dates. This is suppose to be just for April 2024, but it looks like we’ve got some dirty data, so we’ll filter the data to be just for anything in April. Much better! Now, let’s add this to our data model so Power Pivot can use it.
In the left corner, click the Close & Load dropdown, and Close & Load to. We don’t really need this as a table, instead, we just need it as a connection, and most importantly, we need to check the box to Add this to the Data Model, then OK.
Now we can go back to our Power Pivot window by clicking Manage, and we’ll see our data has already been loaded into the data model! Cool, huh?!
Alright, now you’ve seen it, it’s your turn to give it a try.
2. Let's practice!