Loading Tables into the Data Model

With Power Pivot enabled, the next step is to load each of the four prepared tables into the data model. Until a table is loaded, Power Pivot cannot see it — and no relationships or DAX measures can reference it.

Only proper Excel named tables can be loaded into Power Pivot directly. A named table is different from a plain data range — it has a defined structure, a name, and shows a "Table Design" tab in the ribbon whenever any of its cells are selected.

How to Load a Table

Repeat this process for each of the four tables:

1. Click any cell inside the Excel named table you want to load;
2. Go to the Power Pivot tab in the main ribbon;
3. Click Add to Data Model;
4. Power Pivot opens automatically and the table appears as a new tab in the grid view;
5. Return to the workbook and repeat for the next table.

Grid View vs. Diagram View

• Grid view: shows each loaded table as rows and columns — identical in appearance to the original worksheet table. Used to inspect data and write DAX calculated columns;
• Diagram view: shows each table as a card listing its fields. Once all four tables are loaded, this view is where relationships between them will be drawn in the next chapter.

Data Sync — How to Refresh

Power Pivot does not update automatically when the source worksheet changes. If a new row is added to a table, a column is renamed, or data is corrected in the workbook, those changes are not reflected in Power Pivot until a manual refresh is triggered.

1. Open the Power Pivot window via the Power Pivot tab → Manage;
2. Click the Data tab inside the Power Pivot window;
3. Click Refresh All — a status bar at the bottom confirms when the update is complete.

Task

Before you start

Open the provided workbook and check that you can see four sheet tabs at the bottom: Customers, Products, Dates, and Sales. Click into each sheet and confirm that the Table Design tab appears in the Ribbon when you click inside the data — this confirms each sheet is a named Excel Table. You should also confirm that each table name matches its sheet name: Customers, Products, Dates, and Sales. If any table is named something generic like Table1, rename it in the Table Design tab before you proceed.

Also confirm that your Power Pivot add-in is enabled. Click the Ribbon and check that a Power Pivot tab is visible. If it is not there, refer back to chapter 3.1 and re-enable it before continuing.

Task steps

Step 1 — Load the Customers table into the Data Model

• Click any cell inside the Customers table.
• Go to the Power Pivot tab in the Ribbon and click Add to Data Model. The Power Pivot window will open automatically.
• Confirm that a Customers tab has appeared at the bottom of the window and that your customer data is visible inside it.
• Close the Power Pivot window.

Step 2 — Load the Products table into the Data Model

Go to the Products sheet, click any cell inside the Products table, and repeat the same process:

• Power Pivot tab → Add to Data Model.
• Confirm the Products tab is now visible in Power Pivot alongside Customers.
• Close the Power Pivot window.

Step 3 — Load the Dates table into the Data Model

Go to the Dates sheet, click any cell inside the Dates table, and repeat:

• Power Pivot tab → Add to Data Model.
• Confirm the Dates tab appears. - Close the Power Pivot window.

Step 4 — Load the Sales table into the Data Model

Go to the Sales sheet, click any cell inside the Sales table, and repeat:

• Power Pivot tab → Add to Data Model.
• Open the Power Pivot window fully (**Power Pivot tab → Manage&&) and confirm that all four tabs are now present: Customers, Products, Dates, Sales.
• Click through each tab to verify the data is there.

Step 5 — Switch to Diagram View

In the Power Pivot window, click the Diagram View icon in the bottom-left corner (the second of the two small icons). You should see four boxes on the canvas, one for each table.

Step 6 — Verify via the PivotTable dialog

• Back in Excel, go to Insert → PivotTable.
• In the dialog, select Use this workbook's Data Model and click OK.
• In the PivotTable Fields pane on the right, confirm that all four tables are listed — Customers, Products, Dates, Sales — and that you can expand each one to see its column names.
• Once confirmed, close or delete this empty PivotTable without building anything.