DAY 17: Excel - Power Pivot & Data Modeling (DAX basics)

Exercise 1: Enable Power Pivot and Load Data into Data Model

Goal: Learn to activate Power Pivot and import tables into the Excel Data Model.

Step-by-Step Instructions:

1. Enable Power Pivot: File → Options → Add-ins → Manage: COM Add-ins → Go → Check "Microsoft Power Pivot" → OK
2. Verify Power Pivot tab: You should see a new "Power Pivot" tab in the ribbon
3. Prepare your data: Have 3 worksheets ready - Orders (OrderID, CustomerID, ProductID, Sales, OrderDate), Customers (CustomerID, Name, City), Products (ProductID, Name, Category)
4. Load Orders table: Select any cell in Orders data → Power Pivot tab → Add to Data Model
5. Repeat for other tables: Load Customers and Products tables to Data Model
6. View in Power Pivot window: Power Pivot → Manage → You'll see all three tables loaded

💡 Tips:

• Power Pivot vs regular Excel: Power Pivot handles millions of rows, regular Excel limited to ~1M rows
• Table names: Excel converts ranges to proper tables automatically when loading to Data Model
• Data Model is in-memory: Faster than worksheet calculations for large datasets
• No duplicate checking: Power Pivot doesn't prevent duplicate IDs - ensure data quality first
• Keyboard shortcut: Alt+B+M opens Manage Data Model

✅ Expected Result: Three tables visible in Power Pivot window, ready for relationships

Exercise 2: Create Relationships Between Tables (Star Schema)

Goal: Build a star schema by creating relationships between fact (Orders) and dimension tables (Customers, Products).

Step-by-Step Instructions:

1. In Power Pivot window: Click Home → Diagram View (or click diagram icon)
2. Understand the layout: Orders is your fact table (center), Customers and Products are dimension tables
3. Create Orders-Customers relationship: Drag CustomerID from Orders table to CustomerID in Customers table
4. Create Orders-Products relationship: Drag ProductID from Orders to ProductID in Products
5. Verify relationships: You'll see lines connecting tables with "1" (one) and "*" (many) indicators
6. Check relationship details: Double-click any relationship line to see properties (should be 1-to-many)

💡 Tips:

• One-to-many relationships: One customer → many orders, one product → many orders
• Primary/Foreign keys: Dimension table IDs are primary keys (unique), fact table IDs are foreign keys
• Auto-detect: Power Pivot sometimes auto-detects relationships based on column names
• Relationship direction: Filters flow from dimension to fact table (one-way by default)
• Star schema benefits: Organized, performant, matches how business thinks about data

✅ Expected Result: Diagram showing Orders in center connected to Customers and Products, with 1:* relationships

Exercise 3: Create Calculated Columns with DAX

Goal: Add calculated columns to your tables using DAX formulas.

Step-by-Step Instructions:

1. In Power Pivot window: Switch to Data View (grid icon)
2. Click Orders table
3. Add Profit column: Click in "Add Column" header → Type: =Orders[Sales] - Orders[Cost] → Enter
4. Rename the column: Right-click column header → Rename → Type "Profit"
5. Add Profit Margin %: New column → =Orders[Profit] / Orders[Sales] → Format as Percentage
6. Add Year column: =YEAR(Orders[OrderDate]) → Rename to "OrderYear"

💡 Tips:

• DAX vs Excel formulas: DAX is column-based (operates on entire columns), Excel is cell-based
• Table[Column] syntax: Always reference columns as TableName[ColumnName]
• Calculated columns vs measures: Calculated columns store values in model, measures calculate on-the-fly
• When to use calculated columns: When you need to filter or slice by the calculated value
• Performance: Calculated columns increase model size, use sparingly for large datasets

✅ Expected Result: Orders table has three new columns: Profit, Profit Margin %, OrderYear with calculated values

Exercise 4: Create DAX Measures for Analysis

Goal: Write DAX measures for dynamic calculations in Pivot Tables.

Step-by-Step Instructions:

1. In Power Pivot: Click Orders table → Home → New Measure (or click in measure grid at bottom)
2. Create Total Sales measure: Measure Name: "Total Sales" | Formula: =SUM(Orders[Sales]) → OK
3. Create Total Profit: New Measure → Name: "Total Profit" | Formula: =SUM(Orders[Profit])
4. Create Average Order Value: =AVERAGE(Orders[Sales])
5. Create Customer Count: =DISTINCTCOUNT(Orders[CustomerID])
6. Create Profit Margin %: =[Total Profit] / [Total Sales] (references other measures!)

💡 Tips:

• Measures vs Calculated Columns: Measures don't store data, calculate based on pivot table context
• Measure syntax: No table prefix needed: =SUM(Orders[Sales]) or =[Total Sales] for measure references
• Context matters: Same measure shows different values based on rows/columns in pivot table
• Best practice: Use measures for aggregations (SUM, AVG, COUNT), calculated columns for row-level logic
• Format measures: Right-click measure → Format → Choose currency, percentage, etc.

✅ Expected Result: Five measures visible in field list, ready to drag into pivot tables

Exercise 5: Build Pivot Table Using Data Model and Measures

Goal: Create a pivot table that uses your relationships and measures for analysis.

Step-by-Step Instructions:

1. Return to Excel worksheet: Close Power Pivot window
2. Insert Pivot Table: Insert tab → PivotTable → Use this workbook's Data Model → OK
3. In PivotTable Fields: You'll see all tables with expand arrows
4. Add rows: Drag Products[Category] to Rows area
5. Add measures: Drag "Total Sales", "Total Profit", "Profit Margin %" to Values area
6. Add slicer for Year: Expand Orders table → Right-click OrderYear → Add as Slicer
7. Add slicer for City: From Customers table → Right-click City → Add as Slicer
8. Test interactivity: Click different years/cities and watch measures update

💡 Tips:

• Data Model pivot tables are different: Can use fields from multiple tables due to relationships
• Measures vs regular fields: Measures show ∑ icon, regular fields show different icons
• Slicers filter all: Slicers work across all tables when relationships are correct
• Cannot change values: Data Model pivot tables are read-only (can't double-click to drill to detail)
• Advanced features: Use Analyze tab → Fields, Items, & Sets for advanced layouts

✅ Expected Result: Interactive pivot table showing sales/profit by category, filterable by year and city using slicers

🔄 Connection to SQL: Power Pivot relationships are like SQL JOINs, and DAX measures are like SQL aggregate functions with GROUP BY!