Power BI: Data Modelling, Relationships and Joins

Introduction

When I first started working with Power BI, I thought my main job was simply to import a dataset, clean it, create a few charts and build a dashboard. I did know there was more to all that until I discovered the term data modelling.

There were new terms such as tables, relationships, cardinality, joins, keys and something called Model View.At first, it felt confusing. But once I understood how tables connect, Power BI started making much more sense.

In this article, I will explain data modelling, relationships and joins in Power BI using a simple e-commerce example.

1. What is Data Modelling?

Data modelling is basically the process of organising your tables and defining how they connect to each other.Instead of putting every piece of information into one big table, we can separate the information into dimension tables.

For example, imagine an online shop.

We can have tables like:

Customers

CustomerID	CustomerName	County
C001	Stacy	Nairobi
C002	Brian	Kiambu
C003	Aisha	Mombasa

Products

ProductID	ProductName	Category
P001	Laptop	Electronics
P002	Headphones	Electronics
P003	Sneakers	Fashion

Sales

SaleID	CustomerID	ProductID	Quantity	Sales
S001	C001	P001	1	65000
S002	C002	P002	2	5000
S003	C001	P003	1	4500
S004	C003	P001	1	65000

We can see that the Sales table doesn't contain the customer's name, county, product name or category.Instead, it contains CustomerID and ProductID.These IDs allow Power BI to connect the tables.

2. What is a Key?

A key is a column that helps us identify and connect records.

For example:

• CustomerID identifies a customer.
• ProductID identifies a product.

In the Customers table:

CustomerID	CustomerName
C001	Stacy
C002	Brian
C003	Aisha

Each CustomerID appears once.But in the Sales table, the same CustomerID can appear many times:

SaleID	CustomerID	Sales
S001	C001	65000
S003	C001	4500

This gives us a one-to-many relationship.

3. Understanding Relationships

A relationship tells Power BI:

"These two tables are connected through this column."

For example:

Customers[CustomerID] -> Sales[CustomerID]
This tells Power BI which customer made each sale.

Relationships also allow filters to propagate from one table to another. This is what makes it possible to use fields from different tables together in a report.

The model would look something like:

        Customers
       CustomerID
            |
            | 1
            |
            | *
          Sales
       CustomerID
       ProductID
            |
            | *
            |
            | 1
         Products
        ProductID

This is a very common structure in Power BI.

• The 1 represents the side where the value is unique.
• The * represents the side where the value can appear multiple times.

4. One-to-Many Relationships

The most common relationship we all encounter is the One-to-Many (1:*)

For example:
One customer can make many purchases.

Customers Sales

C001 ──────────────── S001
  │
  └────────────────── S003

C002 ──────────────── S002

Customer C001 appears once in the Customers table but appears multiple times in the Sales table.

Therefore:

• Customers = One
• Sales = Many

Power BI calls this the relationship cardinality.

5. Why Do Relationships Matter?

If we create a dashboard containing:

• Total Sales
• Sales by County
• Sales by Product Category
• Sales by Customer

The Sales table contains the actual transaction amounts.But the county is stored in the Customers table.The category is stored in the Products table.
The relationships tell Power BI how the tables are connected so that it will be able to know which category or county belongs to each sale.

For example:

Customers
   ↓
CustomerID
   ↓
Sales
   ↑
ProductID
   ↑
Products

Because these relationships exist, we can use:Customers[County]together with Sales[Sales] enabling Power BI to calculate sales by county.

6. Creating a Relationship in Power BI

In Power BI Desktop, go to Model View.You will see your tables displayed visually.You can then connect matching columns, such as:
Customers[CustomerID] to Sales[CustomerID]

Power BI can sometimes detect relationships automatically when tables are loaded, but you can also create or edit them manually through Manage relationships.

When creating a relationship, you may meet terms such as:

Cardinality

Examples include:

• One-to-many
• One-to-one
• Many-to-many

Cross-filter direction

This determines how filters move between related tables.For a beginner-friendly model, keeping relationships simple and using a clear model structure is usually easier to understand and maintain.

7. What Are Joins?

This is another important concept:

Joins.

A join combines information from two tables based on a matching column.
For example, if we have:

Customers

CustomerID	CustomerName
C001	Stacy
C002	Brian
C003	Aisha

Sales

SaleID	CustomerID	Sales
S001	C001	65000
S002	C002	5000
S003	C001	4500

We can join the tables using CustomerID.

The result could look like:

SaleID	CustomerID	CustomerName	Sales
S001	C001	Stacy	65000
S002	C002	Brian	5000
S003	C001	Stacy	4500

Now the customer's name has been brought into the Sales table.

8. What's the Difference between Joins and Relationships?

This was one of the things that confused me when I started.Although joins and relationships connect tables, they are not exactly the same thing.

A Join

A join combines tables during the data preparation stage.In Power BI, this is commonly done in Power Query using Merge Queries.

A Relationship

A relationship connects tables in the data model.The tables remain separate, but Power BI understands how they are related.

So to remember simply:

• Join = combine the data.
• Relationship = connect the tables.

9. Common Join Types

When using Merge Queries in Power Query, you'll see several types of joins .

Inner Join

This returns only rows where there is a match in both tables.
For example:

Table A	Table B
C001	C001
C002	C002
C003	C004

An inner join would return:

C001
C002

The unmatched values are excluded.

Left Outer Join
This keeps all rows from the left table and brings in matching information from the right table.

For example:

Customers	Customer Details
C001	C001
C002	C002
C003

A left join keeps:

C001
C002
C003

Even though C003 has no match in the second table.This is one of the most commonly useful joins when you want to preserve your main dataset.

Right Outer Join

This is the opposite of a left join.It keeps all rows from the right table and matching rows from the left table.

Full Outer Join

A full outer join keeps all rows from both tables.It is useful when you want to identify both matching and unmatched records.

10. Where Do Joins Happen in Power BI?

Joins are created in Power Query.

Power Query can be transformed through:

Home -> Transform data

Then, in Power Query:

Home -> Merge Queries

You select:

• The first table
• The second table
• The matching column(s)
• The type of join

After the merge, you can expand the new column to bring the required fields into your table.

Power Query supports several join kinds, including inner, left outer, right outer, full outer, left anti, right anti, left semi and right semi joins.

11. So Should Everything be Joined?

No.
This is where data modelling becomes important.You don't necessarily need to combine every table into one giant table.A well-designed Power BI model often keeps tables separate and connects them using relationships.

For example:

                Customers
                    |
                    |
                    ↓
Products -> Sales <- Date

Here, Sales is the central table containing transactions, while Customers, Products and Date provide additional information for analysing those transactions.

This type of structure is closely related to a star schema, which is recommended as a useful modelling approach for Power BI.

12. A Simple Example in a Dashboard

Let's say our dashboard has a slicer for:

County

We select:

Nairobi

The Customers table contains the county information.

Because Customers is related to Sales through CustomerID, the filter can flow to the Sales table.

Power BI can then update our visual to show something like:

Total Sales in Nairobi
KSh 69,500

We did not have to filter every sale ourselves.The relationship did the work behind the scenes.That's the beauty of data modelling.

13. A Few Things to Check Before Creating Relationships

Before connecting two tables, check the columns you're using.

1. Make sure the data types match

For example:

CustomerID should not be Text in one table and Whole Number in another.
It is recommended that the columns used in a relationship have compatible data types.

2. Check for duplicates

The "one" side of a one-to-many relationship needs unique values.
For example, CustomerID should appear once in the Customers table.

3. Check for missing values

If Sales contains a CustomerID that doesn't exist in Customers, you may have an unmatched record.

4. Choose the correct relationship

Don't automatically choose many-to-many just because Power BI allows it.
Many-to-many relationships can be useful, but they require more careful modelling and are sometimes handled using a bridging table.

14. My Simple Mental Model

After learning all of this, this is how I think about it:

Power Query
This is where I clean, merge, change data types, remove errors and prepare tables.

Model View
"Let me tell Power BI how my tables are connected."This is where relationships come in.

Report View
"Now let me analyse and visualise the data."
The three work together:

       POWER QUERY
            ↓
   Clean & Transform
            ↓
      DATA MODEL
            ↓
   Connect Tables
            ↓
       REPORT
            ↓
   Analyse & Visualise

15. Final Takeaway

Data modelling can sound complicated at first, but the basic idea is simple.

• Relationships connect tables inside your Power BI model.
• Joins combine data based on matching values, usually during data preparation in Power Query.

Understanding the difference is important because it helps you decide whether you should:

• Merge two tables
• Keep them separate
• Create a relationship
• Use a one-to-many relationship
• Use another relationship type
• Or rethink the structure of your model

Honestly, once relationships start making sense, Power BI becomes much less intimidating. You stop seeing a bunch of random tables and start seeing a connected data model.

That is when Power BI starts to feel like a real analytical tool rather than just a dashboard-making app.