The last lesson was pretty brisk as we looked at how to get the Power Pivot add in in to Excel. However the lesson before that, we looked at the various ways to load data from the PowerQuery Editor into Standard Excel. These included loading data to tables, pivot tables and pivot charts or not loading the data at all.
Right well let’s start off at the beginning. What is PowerPivot?
What is PowerPivot?
PowerPivot is an Excel add in that supercharges pivot tables. They summarise data like PivotTables do but you can do four important things that you can’t do with normal PivotTables:
In a normal PivotTable, if you have a sufficiently large dataset (maybe getting into the hundreds of thousands of rows and plenty of columns, probably some of them calculated) you will eventually crash your computer or at least make it work really slowly. When we’re trying to create a dashboard or platform that other people can use and use easily, then asking them to wait 20 secs plus before they get their information does not go down well. And if you introduce the possibility that it might crash their computer? Well that just means that they’ll never look at the work that you do, even if it’s absolutely brilliant.
PowerPivot is designed to analyse millions of rows of data so this just isn’t a problem.
In a normal PivotTable, you have to have all the information for the PivotTable in a single table. So what do you do if you want to compare two or three datasets, say sales from 2019, 2020 and 2021? Bring all the data into a single table? That could be a pretty hefty table before you start worrying about matching up things like dates across multiple years, not to mention time consuming to do it!
This is no problem for PowerPivot though as you can connect the data model to all the datasets at once and include and compare information from all datasets against each other in a single PivotTable.
PowerPivot – You don’t have to use PowerQuery to put data into the Data Model. PowerPivot has its own connectors and you can connect to information held in the same workbook as the data model, or in a separate one.