Power Query – Power Pivot Overview

Module 5: Power Pivot Overview

Objective

In this module, you’ll get an introduction to Power Pivot and learn how to build a basic data model from Power Query output.

Files Used

• Download PowerPivot_SalesData.xlsx

What is Power Pivot?

Power Pivot is an Excel add-in that allows you to build relationships between tables and perform advanced data analysis using the Data Model.

It works seamlessly with Power Query by allowing you to load cleaned and transformed data directly into the Data Model.

Enabling Power Pivot

Steps:

1. In Excel, go to the File tab → Options
2. Select Add-ins
3. At the bottom, next to Manage, choose COM Add-ins and click Go
4. Check Microsoft Power Pivot for Excel → Click OK
5. You will now see a Power Pivot tab in the Excel ribbon

Loading Data to Power Pivot

Steps:

1. Import the file PowerPivot_SalesData.xlsx via Power Query
2. In the Power Query Editor, click Close & Load To...
3. In the import dialog, select:
   • Only Create Connection
   • Check Add this data to the Data Model
4. Click OK

Opening the Power Pivot Window

Steps:

1. Go to the Power Pivot tab in Excel
2. Click Manage
3. This opens a separate Power Pivot window showing the data model

Creating Relationships

Steps:

1. If you have multiple tables in the model, go to the Diagram View
2. Drag fields from one table to another to create relationships (e.g. Customer ID → Customer ID)
3. You can also go to the Design tab → Create Relationship

Adding Measures (DAX)

You can create calculated fields (measures) using DAX (Data Analysis Expressions).

Steps:

1. In the Power Pivot window, click into the bottom row of the table
2. Type a formula, e.g.:

Total Revenue := SUM([Revenue])

1. Press Enter — the measure will appear in the model and can be used in PivotTables

Using Power Pivot in PivotTables

Steps:

1. Go to Excel → Insert → PivotTable
2. In the dialog, select Use this workbook’s Data Model
3. Choose fields from your Power Pivot tables to build a PivotTable with relationships

---

→ Continue to Module 6: Summary and Final Exercises

Return to Main Page

• Module 1: Introduction to Power Query
• Module 2: Importing and Managing Data
• Module 3: Data Transformation Techniques
• Module 4: Automation and Parameters
• Module 5: Power Pivot Overview
• Module 6: Summary and Final Exercises