Power Query – Power Pivot Overview
Jump to navigation
Jump to search
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
Enabling Power Pivot Add-in
Steps:
- Open Excel.
- Go to the File tab → click Options.
- In the Excel Options window → select Add-ins.
- At the bottom, next to Manage, select COM Add-ins from the dropdown → click Go....
- In the list, check Microsoft Power Pivot for Excel → click OK.
- The Power Pivot tab will now appear in the ribbon.
Importing Data via Power Query into the Data Model
Steps:
- Go to the Data tab → click Get Data → From File → From Workbook.
- Select and open PowerPivot_Customers.xlsx.
- In Navigator → check the sheet → click Transform Data.
- In Power Query Editor → click the Home tab → click the dropdown under Close & Load → choose Close & Load To....
- In Import Data dialog:
- Select Only Create Connection
- Check the box Add this data to the Data Model
- Click OK
- Repeat these steps for PowerPivot_Products.xlsx and PowerPivot_Sales.xlsx.
Opening the Power Pivot Window
Steps:
- Go to the Excel ribbon → click on the Power Pivot tab.
- Click Manage.
- The Power Pivot window will open showing the loaded tables.
Creating Relationships Between Tables
Steps:
- In the Power Pivot window → click the Diagram View button in the top-right.
- Drag from CustomerID in the Sales table to CustomerID in the Customers table.
- Drag from ProductID in the Sales table to ProductID in the Products table.
- You should now see the relationships as arrows between the tables.
Creating a DAX Measure
Steps:
- In the Power Pivot window → click on the Sales table.
- Scroll to the bottom of the table.
- In the empty cell under the data → type the formula:
Total Sales := SUM([Total])
- Press Enter.
- The measure is now available in PivotTables.
Building a PivotTable from the Data Model
Steps:
- In Excel → go to the Insert tab → click PivotTable.
- In Create PivotTable dialog:
- Select Use this workbook’s Data Model
- Choose location (New worksheet or Existing worksheet)
- Click OK
- Use fields from all related tables:
- Add `Region` from Customers to Rows
- Add `Category` from Products to Columns
- Add `Total Sales` to Values
Extension: Add a Calculated Column in Power Pivot
Steps:
- In the Power Pivot window → select the Sales table.
- Scroll to the rightmost column → click into the empty column labeled Add Column.
- Type:
=[Quantity] * 0.1
- Press Enter.
- Rename the column to `EstimatedCommission`.
→ Continue to Module 6: Summary and Final Exercises
Return to Main Page