Power Query – Power Pivot Overview

Module 5: Power Pivot Overview

Objective

Learn how to load multiple related tables into the Data Model using Power Query, create relationships in Power Pivot, and build PivotTables using DAX calculations.

Files Used

• Download PowerPivot_Customers.xlsx
• Download PowerPivot_Products.xlsx
• Download PowerPivot_Sales.xlsx

Enabling Power Pivot Add-in

Steps:

1. Open Excel.
2. Go to the File tab → click Options.
3. In the Excel Options window → select Add-ins.
4. At the bottom, next to Manage, select COM Add-ins from the dropdown → click Go....
5. In the list, check Microsoft Power Pivot for Excel → click OK.
6. The Power Pivot tab will now appear in the ribbon.

Importing Data via Power Query into the Data Model

Steps:

1. Go to the Data tab → click Get Data → From File → From Workbook.
2. Select and open PowerPivot_Customers.xlsx.
3. In Navigator → check the sheet → click Transform Data.
4. In Power Query Editor → click the Home tab → click the dropdown under Close & Load → choose Close & Load To....
5. In Import Data dialog:
   • Select Only Create Connection
   • Check the box Add this data to the Data Model
   • Click OK
6. Repeat these steps for PowerPivot_Products.xlsx and PowerPivot_Sales.xlsx.

Opening the Power Pivot Window

Steps:

1. Go to the Excel ribbon → click on the Power Pivot tab.
2. Click Manage.
3. The Power Pivot window will open showing the loaded tables.

Creating Relationships Between Tables

Steps:

1. In the Power Pivot window → click the Diagram View button in the top-right.
2. Drag from CustomerID in the Sales table to CustomerID in the Customers table.
3. Drag from ProductID in the Sales table to ProductID in the Products table.
4. You should now see the relationships as arrows between the tables.

Creating a DAX Measure

Steps:

1. In the Power Pivot window → click on the Sales table.
2. Scroll to the bottom of the table.
3. In the empty cell under the data → type the formula:

 Total Sales := SUM([Total])

1. Press Enter.
2. The measure is now available in PivotTables.

Building a PivotTable from the Data Model

Steps:

1. In Excel → go to the Insert tab → click PivotTable.
2. In Create PivotTable dialog:
   • Select Use this workbook’s Data Model
   • Choose location (New worksheet or Existing worksheet)
3. Click OK
4. Use fields from all related tables:
   • Add `Region` from Customers to Rows
   • Add `Category` from Products to Columns
   • Add `Total Sales` to Values

Additional Exercise: Analyze by Region and Product

Goal: Analyze total sales by region and product category.

Steps:

1. Create a PivotTable based on the Data Model.
2. Add `Region` (Customers) to Rows.
3. Add `Category` (Products) to Columns.
4. Add `Total Sales` measure to Values.
5. Optional: Apply formatting or sort highest to lowest.

Extension: Add a Calculated Column in Power Pivot

Steps:

1. In the Power Pivot window → select the Sales table.
2. Scroll to the rightmost column → click into the empty column labeled Add Column.
3. Type:

 =[Quantity] * 0.1

1. Press Enter.
2. Rename the column to `EstimatedCommission`.

Navigation

→ 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