Here is a quick listing of the key differences from a usage point of view.
It will help you choose the right type based upon the analytical objectives.
Pivot Table |
Power Pivot Table |
| 1 million row limit | No upper limit. Much larger data can be handled on the same PC |
| Many Vlookup columns required to decode Codes into Descriptions. | No Vlookup required. Use Relationships to connect two tables. |
| Grouping possible on Dates, Numbers and Text | Grouping not possible. Separate approaches required for Data Grouping depending upon the data type. |
| Custom Date Grouping not possible | Any kind of customization of date and time related grouping possible using the Date Table. |
| Data from only one source can be used. | Data from multiple sources can be combined to create the Power Pivot |
| Only one table and its columns available for analysis | All related tables and their columns in the Data Model are available |
| Very slow performance with large data | Very fast performance even with large data |
| Requires you to split large data, create multiple Pivots and struggle to combine the results | Large data can be analyzed in a single step, eliminating the wastage of time completely |
| Calculated columns have limitations | Calculated columns can use the full functionality of DAX |
| Calculated fields and Items have limitations | Calculated Fields / Measures are extremely powerful and flexible using DAX functions |
| Often, we need to use the Pivot as an interim step and use references to data area cells in a manually created report. | Usually, much more powerful reports can be created with manual referencing outside the Power Pivot using DAX |
| Pivot Table cannot be split into individual cells without losing its analytical capability and linkage to source data. (This is just a paste special – values operation) | Power Pivot can be exploded into formulas so that each cell can be moved as required. It still retains linkage with source data and a refresh is possible at all times. |
| Image URLs cannot be used | Image URLs can be used to identify columns to be used for pictures. These are used in Power View and Power BI Desktop for creating visually appealing reports |
| All columns are Visible | Unwanted, redundent or ambiguous columns can be hidden from Pivot, Power View, Power Map and Power BI to simplify analytical manipulation |
| KPIs are not supported | KPIs can be created using the Power Pivot Data Model |
| Summary option is decided by data type and data consistency. By default numeric columns use SUM, non-numeric use COUNT. If numeric column has blanks or text, default option is COUNT. | Summary option can be set as default for each column (field). |
| Sorting is in either natural order or custom list. | Sorting order can be specified based upon another column in the table. |
Which one to use when?
If you have large amount of data with slow performing Excel files, you need Power Pivot.
If you rely on getting data from multiple sources and combine them into a single table before creating a Pivot Table, you also need Power Pivot. This is true even if your data is not very large.
If you rely on too many vlookup based columns, you need to use Power Pivot Data Model and create relationships bwetween tables.
If you have data which needs special calculations, aggregations, custom date / time intervals or specific treatment based upon complex business requirements, you most certainly need Power Pivot.
If none of these conditionas apply, then regular Pivot Table is a good start.
In any case, I recommend using BOTH in a given situation. In fact, you should try the traditional Pivot Table, Power Pivot, Power View, Power Map, and natural language query, with different types of Conditional Formatting with EVERY type of data you get.
It may sound like an overkill, but I am not suggesting that you do exactly the SAME analysis using different tools. Each tool is capable of doing something unique and useful. Add up all those things and you get unbelievable amount of useful information which you can act upon to manage whatever you do in a much better manner. That concept, incidentally, is called Business Intelligence.
Enjoy.
One Response
This is an useful article