PowerPivot Fundamentals

What is PowerPivot and why should I learn it?

PowerPivot is an Excel add-in that allows the user to analyze much greater quantities of data, as well as build sophisticated data models.  

PowerPivot provides several unique advantages over using Excel's standard features.  A few of these include:

Multiple Sources for Pivot Tables

Generally speaking, in Excel, we are limited to using a single dataset as the source of a Pivot Table (we will exclude the internal data model from this discussion).  But with PowerPivot, you can select different data sets, create relationships (effectively creating a relational database), and build a single Pivot Table from these multiple sources.

Power to Handle More Data

There are approximately 1,048,576 rows (and 16,384 columns) available on every Excel worksheet.  This is more than most of us would ever need, but for some, it is not enough.  Excel users that work with large sets of data generally agree that once data goes beyond 700,000 rows, you run the risk of Excel crashing on you.  PowerPivot, on the other hand, increases row capacity up to 100 million rows of data, thereby vastly increasing the amount of data that can be collected and analyzed.  The more data collected, the more accurate your projection.

Special DAX functions

PowerPivot incorporates both Excel functions, as well as DAX (Data Analysis eXpressions) functions.  DAX is a special type of programming that increases the specificity of potential results.  One example of a DAX function is CALCULATE, which works like a SUMIFS in Excel, except you can expand beyond the standard SUM operation to include any function with the filter effect added to it.

Simplicity of Calculations

In PowerPivot, two types of calculations are possible: Calculated Columns and Measures.  A Calculated Column is similar to creating a Calculated Field in your Pivot Table.  Calculated Fields is an area of Excel that never received much investment by Microsoft, so it remains somewhat archaic, unpolished, and largely ignored by most Excel users.  Calculated Columns in PowerPivot, on the other hand, are much more progressive.  Measures are aggregate calculations that exist solely for Pivot Tables

With both Calculated Columns and Measures, PowerPivot employs a very easy-to-use auto-population method.  Not only does the function name appear in a dropdown when you begin to type (like regular Excel), but the table name and column name appear in a similar fashion, making it incredibly easy to enter calculations.

The Engine for BI

Data Analytics is a new, trendy method of gathering large amounts of transactional data and then performing powerful analyses on this data for Business Intelligence (BI) purposes.  PowerPivot serves as Microsoft’s BI engine.  PowerPivot collects, organizes, and analyzes your data.  You can then create Pivot Tables and Pivot Charts or go beyond this and use Power BI to create amazing visuals and dashboards.

The list of advantages that PowerPivot offers is a long one, but we will stop here for now.  Remember, that PowerPivot is a free add-in for Excel that works closely with Excel to create powerful analyses.  Enabling PowerPivot means that you can open up your data to more variety of sources, greater quantities, specialty functions, ease of calculating, and BI-related visuals.  PowerPivot does not replace Excel, it works with Excel and greatly enhances it.