Today, we will look to begin using PowerPivot (See here where we covered setting it up) to analyse our data. To do so it will involve bringing four sheets together in order to produce our reports. Previously we looked at loading data in PowerQuery and the process here is a small bit different. These will be our raw data file(both GPS and HR), player information and season information file.
You might be wondering why we are looking our data in this manner, where it is stored across multiple sheets then analysed in another. Isn’t it easier to just keep it all in one sheet? Well initially it might be, but over time as your data grows and grows, you will find Excel start to slow down. Every time you add new data into the file it will take that bit longer to refresh those pivot tables or charts. By having it separated out like this it allows us to have quite a large store of data without it slowing down our sheets to any great extent. Again, the goal here is to make everything quicker in Excel so we can spend less time using it!
First we will load the sheets into PowerPivot , as the process is identical for each sheet I will go through it once and you simply need to repeat for each sheet you wish to bring in.
- Select Power Pivot tab
- Select “Manage”, the Power Pivot window should open now.
- Select “From Other Sources”, scroll to the bottom of the list of options and you will see Excel
- Select the relevant file by clicking browse, select “Test Connection” (not necessary but a good step to take), then Next
- Now you can select what tables of data you wish to bring in, tick the ones you want and Finish
- Power Pivot will now start importing your data and hopefully say successful once complete.
- Select finish and your data should now be ready for PowerPivot
- Repeat for all relevant files
I won’t go into this in detail here but we have the option of carrying out some data manipulation at this stage which will occur in the background but be available for our final analysis/report. PowerPivot works through a language called DAX which although might appear similar to Excel formulas, the syntax and basic constructs of DAX is quite different. If you have looked into PowerBI before you will be familiar with DAX. Over the next few posts we will go through some formulas you might look to use with your own data. The benefit of using PowerPivot to perform these calculations is we don’t need huge files storing the information or tables with formulas which can be easily corrupted. To begin with here are a few sample formulas than can be used:
Adding Columns (Useful to combine: Distance in different speed zones; Time in HR Zones; Efforts in Acceleration Zones)
Select cell in empty column, formula is:
- ColName := [Col1] + [Col2]
Similarly, subtract, divide, multiple:
- ColName := [Col1] – [Col2]
- ColName := [Col1] / [Col2]
- ColName := [Col1] * [Col2]
A few things to note about the above formulas:
- [ColName] reference columns no need for the [@] like when working in tables in Excel
- ColName := means new column named ColName, if i wanted to change it without affect the name I would only use =
Next we will cover linking sheets together in PowerPivot and where we can start to use this to aid our analyse.
SportSciData 