Summary
Although we can create a pivot table relatively quickly in Excel, there are a few drawbacks that Power Query can solve. One of the biggest problems with Excel is that it can only have just over a million rows of data. The reality of this is that once you start going over a few hundred thousand, Excel becomes a bit sluggish.
Power Pivot allows us to work with huge datasets, which creates smaller and faster workbooks than standard pivot tables. One of the ways Power Pivot makes this more effective is by loading the data into the data model of Excel and not into a worksheet. We are then able to create relationships between the different tables and we do not have to worry about using VLOOKUP
to create one table with everything in it.
By creating pivot tables based on this model, we can analyze multiple tables of data more easily and efficiently.
In the next chapter, we will introduce the Power Query interface, create a basic Power Query, and discover how to send data back...