Skip to content

12. Data Model

Goal: Load the data into Power BI. Check the relationships and arrange the star schema.

Step 1: Load the data

  1. In Power Query, click Home > Close & Apply.
  2. Power BI loads the data. A progress bar shows the status.
  3. When the load finishes, the Report view appears.

The load completes the ETL process:

Extract (Excel) -> Transform (Power Query) -> Load (Power BI)

Load the data The tables after Close & Apply.

Step 2: Check the tables

  1. Look at the Data panel on the right.
  2. Count the tables. You must have six:
  3. Calendar
  4. CA Population (if you merged it into Location, it is inside Location)
  5. Customers
  6. Location
  7. Products
  8. Sales

Step 3: Group the queries (optional)

When a project has many tables, use query groups.

  1. In Power Query, right-click in the Queries panel.
  2. Click New Group.
  3. Name it Sales Data.
  4. Make a second group: Supporting Data.
  5. Drag the tables into the correct group.

This step keeps the project tidy.

Step 4: Open Model view

  1. Click the Model view icon on the left side of Power BI.
  2. The relationships between the tables appear as lines.

Step 5: Check the relationships

Power BI detects most relationships automatically from the matching columns.

  1. Look at the line between Sales and Customers.
  2. The line end shows 1 at the Customers side and * (many) at the Sales side.

This is a one-to-many relationship:

Table Cardinality Why
Customers 1 Each customer ID appears one time
Sales Many One customer buys many times

The same rule applies to Location, Products, and Calendar.

Step 6: Create the Calendar relationship

If Power BI does not create this relationship, make it by hand:

  1. In Model view, find the Calendar table.
  2. Click and hold the Date column.
  3. Drag it to the Purchase Date column in the Sales table.
  4. Power BI draws a new relationship line.

Model view with relationships The model view: Sales in the center, dimension tables around it.

Step 7: Arrange the model

  1. Drag the Sales table to the center.
  2. Place the dimension tables around it: Calendar, Customers, Products, Location.
  3. Zoom out if the tables are too large.

The layout is a star: one fact table in the center, dimension tables around it.

Fact vs dimension

  • Fact table: has numbers and many rows. Example: Sales.
  • Dimension table: has descriptions. Example: Products.
  • A project needs at least one dimension table. It can also work with no dimension table.

Delete carefully in Model view

If you delete a table in Model view, Power BI also deletes it in Power Query. The data model is the source of truth. If this happens, close Power BI without saving, then open it again.

Step 8: Save your work

  1. Click File > Save as.
  2. Choose a folder.
  3. Name the file PowerBI Sales Report.
  4. Click Save.

Save often

Save the file after each big step. Power BI does not have automatic recovery for all changes.

Next: Continue to 13. Report View and Tables.