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, click the File tab in the top left corner.
2. Select Options from the bottom of the left menu.
3. In the Excel Options window, select Add-ins.
4. At the bottom, in the Manage dropdown, choose COM Add-ins.
5. Click the Go... button next to it.
6. In the COM Add-Ins window, check the box next to Microsoft Power Pivot for Excel.
7. Click OK.
8. The Power Pivot tab should now be visible in the Excel ribbon.

Loading Data to Power Pivot

Steps:

1. In Excel, go to the Data tab → click Get Data → choose From Workbook.
2. Select the file PowerPivot_SalesData.xlsx.
3. In the Navigator pane, select the relevant sheet(s) and click Transform Data.
4. In Power Query Editor, verify the data is clean. Then click the dropdown next to Close & Load → choose Close & Load To....
5. In the Import Data dialog:
   • Select Only Create Connection
   • Check the box for Add this data to the Data Model
6. Click OK.
7. The data is now loaded into the Power Pivot model, not into a worksheet.

Opening the Power Pivot Window

Steps:

1. Click the Power Pivot tab on the Excel ribbon.
2. Click the Manage button.
3. The Power Pivot window will open in a new window, showing your data.

Creating Relationships

Steps:

1. In the Power Pivot window, go to the top ribbon and click on Diagram View.
2. You'll see a visual layout of all tables in your model.
3. If there are multiple tables, you can click and drag a field (e.g., `ProductID`) from one table to a matching field in another (e.g., `ProductID` in Sales and Products).
4. Alternatively, go to the Design tab → click Create Relationship.
5. In the dialog, select the two tables and matching columns.
6. Click OK.

Creating a DAX Measure

Steps:

1. In the Power Pivot window, ensure you're in the Data View.
2. Scroll to the bottom of the data table (below the columns) where you'll see a cell labeled Add Measure or an empty row.
3. Click in that row and type the formula:

 Total Revenue := SUM([Revenue])

1. Press Enter.
2. The measure will now appear in the Fields list and can be used in PivotTables.

Using Power Pivot in PivotTables

Steps:

1. In Excel, go to the Insert tab.
2. Click on PivotTable.
3. In the Create PivotTable dialog:
   • Select the option: Use this workbook’s Data Model.
   • Choose where you want the PivotTable to be placed (new worksheet or existing one).
4. Click OK.
5. In the PivotTable Fields pane, you'll see your Power Pivot tables.
6. Drag fields into Rows, Columns, and Values as needed.

Additional Exercise: Create PivotTable with DAX Measure

Goal: Analyze revenue by region.

Steps:

1. Follow the steps to insert a PivotTable based on the Data Model.
2. In the PivotTable Fields pane:
   • Drag `Region` into the Rows area.
   • Drag the DAX measure `Total Revenue` into the Values area.
3. Click any cell in the PivotTable → right-click → choose Sort → Sort Largest to Smallest.

Extension: Add Calculated Column

Goal: Add a column that estimates margin as 25% of revenue.

Steps:

1. In the Power Pivot window → go to the table where you want the column.
2. Scroll to the rightmost column → click into the empty column labeled Add Column.
3. Type the formula:

 =[Revenue] * 0.25

1. Press Enter.
2. Power Pivot will automatically name the column (you can rename it to `EstimatedMargin`).
3. Return to Excel and create a new PivotTable using this field as needed.

---

→ 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