Excel Data Model

A worksheet is a grid of cells, and a grid has two hard limits that no amount of cleverness removes. First, it tops out at 1,048,576 rows. That's fine for a department and hopeless for transaction-level data from a real business. Second, a grid stores values in positions, so every relationship between two datasets has to be re-created by hand, row by row, with a lookup formula. Want each sales row to know its product's category? You write a XLOOKUP in a helper column, copy it down a million rows, and pray no one inserts a column. There's no native idea in the grid that two tables are related. It only knows cells.

The Data Model throws out both limits. It's a separate store inside your workbook, powered by the xVelocity in-memory analytics engine, known internally as VertiPaq. Power BI and SQL Server Analysis Services Tabular run on that very same engine. Three properties of it matter here:

• It's columnar and compressed. Instead of storing data row by row in cells, VertiPaq stores each column separately and compresses it hard. Give it a column of a few distinct region names and that column shrinks to almost nothing. So the model comfortably holds tens of millions of rows, far more than would ever fit on a sheet, and scans them fast. Analytical questions like "sum this column, filtered by that one" are what columnstores are built for.
• It relates tables natively. You declare a relationship between two tables once, something like "Sales' RegionID matches Region's RegionID", and from then on the whole model knows it. You don't need a lookup column or a copy-down, and nothing breaks when someone inserts a column. The relationship matches on keys, so it's immune to the positional breakage that plagues VLOOKUP.
• It computes measures once, for reuse everywhere. A DAX measure is a formula stored in the model. No cell holds it. Drop [Total Sales] into a PivotTable's grand total and it sums everything. Drop the same measure into a matrix broken out by region and it sums each region. Add a month slicer and it re-sums per your selection, all from one definition. In the grid you'd have written SUMIFS three different ways in three places. In the model you write the measure once and let context do the slicing.

How do you load data into the Data Model and view it?

There are three common ways to load a table into the model:

1. Power Query is the professional default. With your query built, choose Home ▸ Close & Load To… ▸ Only Create Connection, and tick Add this data to the Data Model. Your data goes straight into VertiPaq without ever landing on a sheet, which is ideal for big tables.
2. To load a sheet Table, select it, then Power Pivot ▸ Add to Data Model. Excel copies the table into the model.
3. While you build a PivotTable, go Insert ▸ PivotTable and tick Add this data to the Data Model. If the model already exists, choose Use this workbook's Data Model instead.

To see and edit the model, click Power Pivot ▸ Manage. That opens the Power Pivot window, with two views you toggle on the Home tab. Data View shows each table as a grid, and it's where you add calculated columns and type measures in the Calculation Area beneath. Diagram View shows tables as boxes, and you draw relationships there by dragging key to key.

Here's the shift to carry with you. On a sheet you build answers. In a model you build a machine that produces answers, and everything after this is a part of that machine.