Power Query – Power Pivot Overview

Module 5: Power Pivot Overview

Objective

Learn how to load data into the Data Model, build relationships, and use DAX measures in PivotTables.

Files Used

• Download PowerPivot_SalesData.xlsx

Enabling Power Pivot

Steps:

1. In Excel → File tab → Options
2. Go to Add-ins
3. At the bottom, choose COM Add-ins → click Go
4. Check Microsoft Power Pivot for Excel → click OK
5. The Power Pivot tab will appear on the ribbon

Loading Data to Power Pivot

Steps:

1. Import PowerPivot_SalesData.xlsx via Power Query
2. In the Power Query Editor, click Close & Load To...
3. Choose:
   • 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
2. Click Manage
3. This opens the Power Pivot window with your loaded table

Creating Relationships

Steps:

1. Go to the Power Pivot window → Diagram View
2. Drag and drop fields to link tables (if more than one table is present)
3. Or: go to the Design tab → Create Relationship

Creating a DAX Measure

Steps:

1. In Power Pivot window, click into the bottom row (under the table)
2. Type:

Total Revenue := SUM([Revenue])

1. Press Enter — the measure appears in the model

Using Power Pivot in PivotTables

Steps:

1. In Excel, go to Insert → PivotTable
2. Choose: Use this workbook’s Data Model
3. Build a PivotTable using fields from Power Pivot

Additional Exercise: Create PivotTable with DAX Measure

Goal: Analyze revenue by region.

Steps:

1. Build a PivotTable based on the Data Model
2. Use `Region` as a row field
3. Use `Total Revenue` as the values field
4. Sort by revenue descending

Extension: Add Calculated Column

Goal: Add a margin estimate.

Steps:

1. In the Power Pivot window, click a new column
2. Use formula:

EstimatedMargin := [Revenue] * 0.25

1. Use it in a new PivotTable analysis

---

→ 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