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

24. Power Pivot and the Data Model

24.2 Load Tables into the Data Model

By Last updated:

Example first. The three Tables are on three sheets. One click per Table puts them into the Data Model. After that, the Power Pivot window shows three tabs at the bottom: Orders, Stores and Products.

Add to Data Model — in 30 seconds

What: Loading a Table into the Data Model copies its rows into the model. The sheet Table stays where it is and stays linked: change the sheet and refresh, and the model changes too.

Why: Only Tables that are in the model can be related to each other and used in DAX measures.

Tiny example: Click inside Orders › Power Pivot › Tables › Add to Data Model. The Power Pivot window opens with an Orders tab of 12 rows.

First look at the data (before → after):

Add to Data Model: before and after tables, fictional Blinkit Maharashtra data

Expected result — Power Pivot window (Data View): tables in the model

Table tab Rows Columns
Orders 12 7
Stores 4 3
Products 4 4
Before table and formula as text

Before — Sheet Orders: an Excel Table (not in the model yet)

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

Before — Table Stores (dimension)

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

Before — Table Products (dimension)

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

Formula / click path

Click in the Table › Power Pivot › Tables › Add to Data Model
or: Insert › PivotTable › From Table/Range › tick Add this data to the Data Model

Do it step by step:

  1. Click any cell in the Orders Table.
  2. Click Power Pivot › Tables › Add to Data Model. The Power Pivot window opens.
  3. Go back to Excel, click inside Stores and click Add to Data Model again.
  4. Do the same for Products.
  5. In the Power Pivot window, click each tab at the bottom and check the row counts.
  6. Change one Qty on the Orders sheet, then click Home › Refresh in the Power Pivot window and see the new Amount.

Other ways data reaches the model

From How
An Excel Table in this workbook Power Pivot › Add to Data Model (as above)
A Table, while making a PivotTable Insert › PivotTable › From Table/Range, tick Add this data to the Data Model
Power Query (files, folders, databases, web) In the editor: Home › Close & Load › Close & Load To…, choose Only Create Connection and tick Add this data to the Data Model (lesson 24.6)

Keep raw data out of the sheet when it is big. A query loaded with "Only Create Connection" lives only in the model, so a 5-lakh-row file does not have to sit on a sheet.

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

  1. Load Orders, Stores and Products into the Data Model.
  2. In the Power Pivot window, switch to each table and note the rows.
  3. Change O-101 Qty from 4 to 5 on the sheet and refresh the model.
Answer

Orders 12 rows, Stores 4, Products 4. After the change O-101 shows Amount ₹160 instead of ₹128, and total sales become ₹2,347. (Set it back to 4 before the next lesson.)

Ravindra Bagale's Tip

Do not type data inside the Power Pivot window. Change the source Table or file and refresh. The model is a copy, and edits made only in the copy are lost.