Ravindra BagaleCourses & study guides मराठी Track your progress

24. Power Pivot and the Data Model

24.1 Turn On Power Pivot and Meet the Data Model

By Last updated:

Example first. Shraddha Bagale has three Tables: Orders (12 rows), Stores and Products. Today she adds City and Category to Orders with two XLOOKUP helper columns before every PivotTable. With the Data Model she loads the three Tables once, links them, and builds a PivotTable by City from Stores and Amount from Orders. No helper columns. Total sales stay ₹2,315.

Power Pivot and the Data Model — in 30 seconds

What: The Data Model is a small database inside the workbook: several Tables plus the relationships between them. Power Pivot is the add-in window where you see and manage that model.

Why: It replaces helper lookup columns, handles far more rows than a sheet (a sheet stops at 1,048,576 rows) and lets you write DAX measures, the same language Power BI uses.

Tiny example: One PivotTable shows Pune ₹940 using City from Stores and Amount from Orders. Before, that needed =XLOOKUP([@StoreID],tblStores[StoreID],tblStores[City]) in every row.

First look at the data (before → after):

Power Pivot and the Data Model: before and after tables, fictional Blinkit Maharashtra data

Expected result — With the Data Model: what the workbook now holds

Item Value
Tables in the model Orders, Stores, Products
Rows in Orders 12
Relationships Orders[StoreID] → Stores, Orders[ProductID] → Products
Helper columns needed 0
Total sales (check) ₹2,315
Before table and formula as text

Before — Without the Data Model: helper columns added to Orders

OrderID StoreID Amount (₹) City (XLOOKUP) Category (XLOOKUP)
O-101 BLK-PUN-01 128 Pune Dairy
O-102 BLK-NSK-01 180 Nashik Fruits
O-103 AMN-NGP-01 195 Nagpur Staples
O-104 BLK-PUN-01 220 Pune Snacks
O-105 AMN-SLP-01 90 Solapur Fruits
… … … … …

Formula / click path

File › Options › Add-ins › Manage: COM Add-ins › Go…
Tick Microsoft Power Pivot for Excel › OK
Power Pivot › Data Model › Manage

Do it step by step:

  1. Click File › Options › Add-ins.
  2. At the bottom choose Manage: COM Add-ins and click Go….
  3. Tick Microsoft Power Pivot for Excel and click OK. A Power Pivot tab appears on the ribbon.
  4. Click Power Pivot › Data Model › Manage. The Power Pivot window opens (empty for now).
  5. Close it. In the next lesson you load the Tables into it.
  6. If Power Pivot is not in the list, your Excel edition may not include it; the Data Model still works from Insert › PivotTable › Add this data to the Data Model.

The three practice Tables used in this module (type them on three sheets and press Ctrl + T on each; name them Orders, Stores and Products):

OrderID Date StoreID ProductID CustomerID Qty Amount (₹)
O-101 03-10-2026 BLK-PUN-01 P01 C01 4 128
O-102 05-10-2026 BLK-NSK-01 P02 C02 2 180
O-103 11-10-2026 AMN-NGP-01 P03 C03 3 195
O-104 18-10-2026 BLK-PUN-01 P04 C04 2 220
O-105 22-10-2026 AMN-SLP-01 P02 C05 1 90
O-106 29-10-2026 BLK-PUN-01 P01 C01 6 192
O-107 02-11-2026 BLK-PUN-01 P02 C02 3 270
O-108 06-11-2026 BLK-NSK-01 P04 C06 2 220
O-109 09-11-2026 AMN-NGP-01 P01 C03 5 160
O-110 14-11-2026 AMN-SLP-01 P03 C05 4 260
O-111 20-11-2026 BLK-PUN-01 P03 C04 2 130
O-112 27-11-2026 BLK-NSK-01 P02 C06 3 270
StoreID City Platform
BLK-PUN-01 Pune Blinkit
BLK-NSK-01 Nashik Blinkit
AMN-NGP-01 Nagpur Amazon Now
AMN-SLP-01 Solapur Amazon Now
ProductID Product Category Price (₹)
P01 Cow Milk 500 ml Dairy 32
P02 Grapes 500 g Fruits 90
P03 Poha 1 kg Staples 65
P04 Bhakarwadi 250 g Snacks 110

All rows are fictional practice data. Amount = Qty × Price from the Products table.

Practice task (try it before you open the answer):

  1. Turn on Power Pivot.
  2. Type the three Tables and name them Orders, Stores and Products.
  3. Check the total of the Amount column.
Answer

Total Amount = ₹2,315 from 12 orders. If you get a different number, compare each Amount with Qty × Price.

Ravindra Bagale's Tip

Name your Tables before they go into the Data Model. Renaming later is possible, but every measure you already wrote shows the old name until you fix it.