Hi guys, bit of a post that’s separate to the recent ones today. Going to quickly cover how to make sure you have PowerPivot and PowerQuery installed and usable on your version of Excel.
First of all, just to go through what they will allow us to do:
- PowerQuery (Also known as Get and Transform in new versions)
- Automate manipulation and combining of datasets
- Ensure data is formatted the same every time before adding to the raw data storage
- PowerPivot
- Automate analysis incl:
- Pull together our different raw datasets and combine to produce reports (tables & graphs)
- Perform calculations on raw datasets to produce different metrics
- Automate analysis incl:
Unfortunately this is one area where Mac users are at a real disadvantage as both PowerPivot and PowerQuery are not available for Excel Mac*.
If you are running an old version of Excel then activating is the same for both:
- File
- Options
- Add-Ins
- .com
- Tick boxes (Note older versions will have boxes for both Pivot and Query)
- OK
For newer versions of Excel you may need to activate the add-in for PowerPivot as above, however PowerQuery should be available already. It won’t be as clear how to use it, this will be covered in the next blog post. Now you should have a tab on your ribbon for both PowerPivot and PowerQuery in older versions and just the one for PowerPivot in the new one.
As the name might suggest PowerPivot works through PivotTables, for those unfamiliar with them, they will be covered later in the blog. These will likely be involved in the majority of your Excel work.
People might be wondering why look to make Excel more complicated when it can be tricky enough already. The goal of this blog is to make the harder parts of Excel automated and have all the messy stuff carried out in the background so all you need to do is add data, refresh and select what data to analyse. Unfortunately, this can require a bit more work in the beginning but lead to less work in Excel and more time actually looking at your data and drawing actionable insights from it!
So thats everything for today….next up how to use Query to automate initial analysis of a raw data export!
*Further on down the line when R becomes more common on the blog I will do a series on common methods used in R which can replicate the actions of PowerQuery
SportSciData 