Understanding Data Modelling in Power BI: Joins, Relationships, and Schemas Explained

Dalton Imbiru — Sun, 05 Apr 2026 20:22:55 +0000

Data modelling is the foundation of effective data analysis in Power BI. A well-structured model ensures faster performance, accurate calculations, and easier report building. This article breaks down everything you need to know—from SQL joins and Power BI relationships to schemas and practical implementation steps.

What is Data Modelling?

Data modelling is the process of organising data into tables and defining how those tables relate to each other so that analysis becomes meaningful and efficient.

In Power BI, data modeling involves:

Structuring tables (Fact and Dimension)
Defining relationships between tables
Optimizing performance and usability

SQL Joins Explained (With Examples)

Joins combine data from two or more tables based on a common column.

Examples:

Customers

CustomerID	Name
1	John
2	Mary
3	Alex

Orders

OrderID	CustomerID
101	1
102	2
103	4

INNER JOIN

Returns only matching records from both tables.

Result:

CustomerID	Name	OrderID
1	John	101
2	Mary	102

Use case: When you only want valid matches (e.g., customers who made purchases).

LEFT JOIN

Returns all records from the left table + matching from the right.

Result:

CustomerID	Name	OrderID
1	John	101
2	Mary	102
3	Alex	NULL

Use case: Show all customers, even those without orders.

RIGHT JOIN

Returns all records from the right table + matching from the left.

Result:

CustomerID	Name	OrderID
1	John	101
2	Mary	102
NULL	NULL	103

Use case: Show all orders, even if customer info is missing.

FULL OUTER JOIN

Returns all records from both tables.

Result:

CustomerID	Name	OrderID
1	John	101
2	Mary	102
3	Alex	NULL
NULL	NULL	103

Use case: Complete data reconciliation.

LEFT ANTI JOIN

Returns rows from left table that have no match in right table.

Result:

CustomerID	Name
3	Alex

Use case: Identify customers who never purchased.

RIGHT ANTI JOIN

Returns rows from right table with no match in left table.

Result:

OrderID	CustomerID
103	4

Use case: Identify orphan records (e.g., invalid orders).

Joins in Power BI (Power Query)

Power BI implements joins in Power Query.

Steps:

Go to Home → Transform Data
Select a table
Click Merge Queries
Select second table
Choose matching column(s)
Select join type:

Inner
Left Outer
Right Outer
Full Outer
Left Anti
Right Anti
1. Expand columns to finalize

Relationships in Power BI

Unlike SQL joins (which combine tables physically), Power BI relationships connect tables logically.

Types of Relationships

1. One-to-Many (1:M)

One record in Table A → Many in Table B
Example: Customers → Orders

Most common relationship

2. Many-to-Many (M:M)

Many records in both tables
Example: Students ↔ Courses

3. One-to-One (1:1)

One record matches exactly one record
Example: Employee ↔ Employee Details

Cardinality

Defines how tables relate:

One-to-Many
Many-to-One
Many-to-Many

Cross-Filter Direction

Controls how filters flow between tables.

Single direction → One way (recommended)
Both directions → Two-way filtering (use carefully)

Active vs Inactive Relationships

Active relationship → Default used in visuals
Inactive relationship → Exists but is not used unless activated via DAX (USERELATIONSHIP)

Example:

Order Date (Active)
Ship Date (Inactive)

Creating Relationships in Power BI

Method 1: Model View

Go to Model View
Drag one column to another
Relationship is created automatically

Method 2: Manage Relationships

Go to Home → Manage Relationships
Click New
Select:

Tables
Columns
Cardinality
Cross-filter direction
1. Click OK

Joins vs Relationships (Key Difference)

Feature	Joins	Relationships
Where used	Power Query	Data Model
Result	Combines tables	Keeps tables separate
Performance	Can increase size	More efficient
Flexibility	Static	Dynamic

Fact vs Dimension Tables

Fact Table

Contains measurable data (numbers)

Examples:

Sales
Revenue
Quantity

Dimension Table

Contains descriptive attributes

Examples:

Customer Name
Product Category
Date

Example Model

FactSales

OrderID
CustomerID
ProductID
SalesAmount

DimCustomer

CustomerID
Name

DimProduct

ProductID
Category

Data Modeling Schemas

Star Schema

One central fact table
Connected to multiple dimension tables

Snowflake Schema

Dimensions are normalized (split into multiple tables)

Example:

Product → Category → Department

Flat Table (Denormalized / DLAT)

All data in one table

Use Cases

Schema	When to Use
Star	Most Power BI reports
Snowflake	Complex hierarchical data
Flat Table	Small datasets or quick analysis

Role-Playing Dimensions

A role-playing dimension is a table used multiple times for different purposes.

Example: Date Table

Order Date
Ship Date
Delivery Date

In Power BI:

Duplicate the Date table
Create separate relationships

Common Data Modeling Issues

Ambiguous relationships
Happens with multiple paths between tables

Many-to-many confusion
Can lead to incorrect aggregations

Circular relationships
Causes errors

Poor performance
Caused by flat tables or too many joins

Solution:

Use star schema
Avoid unnecessary bi-directional filters
Keep relationships simple

Step-by-Step: Building a Model in Power BI

Step 1: Load Data

Home → Get Data

Step 2: Clean Data (Power Query)

Remove duplicates
Handle nulls
Merge tables if needed

Step 3: Create Relationships

Go to Model View
Drag and connect tables

Step 4: Validate Model

Check cardinality
Ensure no ambiguous paths

Step 5: Optimize

Use star schema
Reduce columns
Avoid many-to-many unless necessary

Conclusion

Data modeling in Power BI is not just technical, it’s strategic. Understanding joins helps you prepare data, while relationships allow you to analyze it efficiently. By structuring your data into fact and dimension tables and choosing the right schema (preferably star), you create a model that is both powerful and scalable.

Mastering these concepts transforms Power BI from a simple visualization tool into a robust analytics engine and ultimately changes how you interpret and interact with data.

How Excel is Used in Real-World Data Analysis

Dalton Imbiru — Mon, 23 Mar 2026 08:59:53 +0000

What is Excel?

Microsoft Excel is a spreadsheet program developed by Microsoft that is used to organize, analyze, and visualize data.
Think of it as a digital table made up of rows and columns, where each box (a cell) can hold numbers, text, or formulas.

Microsoft Excel is more than just a spreadsheet tool; it is a practical engine for handling real-world data across industries. From small businesses to global corporations, Excel is used daily to organize information, uncover patterns, and support decision-making.

In business, Excel is often used for budgeting and financial analysis. For example, a company can track its monthly expenses and revenue, then use formulas like SUM to calculate totals or IF statements to determine profit or loss. A manager might build a financial model to predict future performance based on past trends, helping guide decisions such as hiring or investment.

Image showing SUM OF Employees salary.
FORMULAS:
=SUM(G2:G877)
=SUMIFS(AF2:AF877, AE2:AE877, "London", W2:W877, "Single", S2:S877, "Female")

In data analysis, Excel allows users to clean, sort, and interpret large datasets. Features like PivotTables make it easy to summarize data—for instance, a sales team can quickly see which product sells the most in a specific region. Functions like VLOOKUP or XLOOKUP help combine data from different tables, which is useful when matching customer records or inventory lists.

Image showing Pivot Table
FORMULAS:
VLOOKUP(10865,A1:AJ877,4,FALSE)
INDEX(G2:G877,MATCH(10865,A1:A877,0))

In education, students and teachers use Excel to manage grades and analyze performance. A teacher might calculate class averages using AVERAGE, identify top-performing students, or visualize results using charts like bar graphs and pie charts.

In logistics and operations, Excel helps track inventory, orders, and deliveries. A small business, for example, can monitor stock levels and use conditional formatting to highlight items that are running low. This helps prevent shortages and improves efficiency.

In human resources, Excel is used to manage employee data, payroll, and attendance. Functions can calculate salaries, deductions, and overtime automatically, reducing manual work and errors.

Excel is also widely used for data visualization. Charts and dashboards turn raw numbers into insights—like showing sales growth over time or comparing performance across departments. This makes it easier for decision-makers to understand complex information quickly.

Ultimately, learning Excel has changed how I interact with data. Instead of seeing numbers as static information, I have began to recognize patterns, relationships, and insights. It has shifted my thinking from simply recording data to actively interpreting it, enabling more informed decisions in both academic and professional settings.

DEV Community: Dalton Imbiru

Understanding Data Modelling in Power BI: Joins, Relationships, and Schemas Explained

What is Data Modelling?

SQL Joins Explained (With Examples)

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

LEFT ANTI JOIN

RIGHT ANTI JOIN

Joins in Power BI (Power Query)

Steps:

Relationships in Power BI

Types of Relationships

1. One-to-Many (1:M)

2. Many-to-Many (M:M)

3. One-to-One (1:1)

Cardinality

Cross-Filter Direction

Active vs Inactive Relationships

Creating Relationships in Power BI

Method 1: Model View

Method 2: Manage Relationships

Joins vs Relationships (Key Difference)

Fact vs Dimension Tables

Fact Table

Dimension Table

Example Model

Data Modeling Schemas

Star Schema

Snowflake Schema

Flat Table (Denormalized / DLAT)

Use Cases

Role-Playing Dimensions

Common Data Modeling Issues

Step-by-Step: Building a Model in Power BI

Step 1: Load Data

Step 2: Clean Data (Power Query)

Step 3: Create Relationships

Step 4: Validate Model

Step 5: Optimize

Conclusion

How Excel is Used in Real-World Data Analysis

What is Excel?