What is Power Pivot? It is an add-in, and according to Microsoft: “Power Pivot is a data modeling technology that lets you create data models, establish relationships, and create calculations. With Power Pivot you can work with large data sets, build extensive relationships, and create complex (or simple) calculations, all in a high-performance environment, and all within the familiar experience of Excel.” Note that Power Pivot is currently NOT available for Mac. Power Pivot for Excel was first available in the year 2010.

Before diving into Power Pivot, you should know how to work with Pivot Tables. Before diving into Pivot Tables you should know the basics of using Excel and formulas such as SUM() , SUMIF(), and a few others. Also, before diving into Power Pivot, it would be best if you understood data modeling and relationships such as one-to-many. You should know the difference between a data (fact) table and a lookup (dimension) table.

With Power Pivot you can do the following.

• Query and store hundreds of millions of rows of data
• Combine the data with relationships
• The data may be from multiple sources and from multiple types
• Create Pivot Tables and Pivot Charts
• Create calculated columns and calculated fields
• Create measures and key performance indicators
• Publish Power Pivot reports in Microsoft SharePoint

Power Pivot is just like a normal Pivot Table except that it sits on top of an entire data model rather than a single table or range. The “Power” is the data model. The “Power” is the ability to join tables together with relationships and create a data model.

You create a data model by creating relationships between tables. For example, a customer can have many orders. Each order can have many types of products in it. With a pivot table, you are locked into having just one table of data.

You no longer need to join and merge and append all of your complex data together into one giant monstrosity of a table in order to analyze it. With Power Pivot, we have the “power” of relationships.

You might need to add Power Pivot to Excel. Go to File, Options, Add-ins, Manage and switch to COM add-ins.

YouTube

Have a look at the YouTube video called How to use Power Pivot in Excel | Full Tutorial. Kevin Stratvert works with three sets of information: Customers, Orders, and Cookies (products).

Here is another video about Power Pivot called Advanced Pivot Table Techniques: Combine Data from Multiple Sheets in Excel. Leila uses the Data Model to make relationships between her tables. There is a table called TableSales. The other two tables are called MasterArticle (product table) and MasterCustomer (customer table). Both of these two table are “Lookup” tables.

Power Pivot vs Power Query

What’s the difference? Power Query is all about automating the getting and cleaning data. Data can come from many different sources and may not always be clean and in the format you need it to be in. Power Pivot is all about analyzing data using the Pivot Table tools. Both of these tools are available in Excel and Power BI. Both tools are the foundation of how Power BI works. Power Query is an extract, transform, and load (ETL) processing tool.

Power Query helps you get data from multiple sources like CSV files, Excel files, SQL Server databases, the web or so on. It also helps with cleaning data. It can split text into multiple columns. You can add calculated columns. You can remove duplicates. You can remove certain rows. Perhaps you clean spaces from text. Power Query will help you save time if you routinely gather data, like getting data each month and cleaning it. According to Mynda Treacy, adding calculated column is more efficient in Power Query than Power Pivot.

Power Pivot is about analyzing data. We use pivot tables and charts. Here we have the DAX formula language to create Measures and key performance indicators (KPI). We can build relationships between tables instead of flattening the data with VLOOKUP or other formulas. We can work with millions of rows of data with Power Pivot because we can load the data to the Data Model instead of directly into a worksheet.

Have a look at the YouTube video called Power Query vs Power Pivot by Mynda Treacy at myonlinetraininghub.com. It’s only about 6 minutes long. It’s a very good introduction to what these two tools are.

Normal Pivot vs Power Pivot

What the difference between the normal pivot and the power pivot. Power Pivots have many tables. We have access to all of the tables in our data model. The “power” in Power Pivot is the Data Model. We can analyze the data based on any relationships we’ve defined. Power Pivot uses “measures” instead of “calculated fields”. With Power Pivot we use Data Analysis Expressions (DAX) in Calculated Columns and Measures. DAX is a formula language for Power Pivot.