Data Modelling, Joins, Relationships & Types of Schemas in Power Bi.

Melvin — Sun, 21 Jun 2026 12:08:28 +0000

Introduction

In todays data-driven business environment, organizations generate and store huge amounts of data from various sources. To transform this raw data into meaningful insights, it is important to structure it effectively.
Data modelling provides a framework for representing data, defining relationships between data elements and ensuring consistency and accuracy within analytical systems.
In Power Bi, data modelling plays a critical role in enabling efficient data analysis and reporting.

Data Modelling

What is data modelling?

Data modelling is the process of organizing and connecting data tables together for analysis, visualization and reporting.
It involves creating relationships between tables, creating measures and calculated columns and structuring data in a way that makes analysis and reporting accurate.
In power Bi, a good model typically:

Uses dimension tables
Enables fast filtering and aggregation
Minimizes redundancy for perfomance and accuracy
Separates data into fact tables

A strong data model is the foundation for accurate dashboards and scalable analytics.

Joins

Joins define how two tables are combined based on a related column.

Types of joins:

Inner Join -It returns only the matching rows that exist in both the right and left tables. If a value doesn't exist in both tables, the row is thus dropped.
It is used when you only want records that are common in both tables.
Left Join - Returns all rows from the left table and matching rows from the rigt. Missing matches become null.
Right Join- Returns all rows from the right table and matches from left.
Full Outer Join - Returns all rows from both tables, matched where possible.

Relationships in Power Bi

Relationships in power Bi are the vital links that connect two or more data tables based on common columns (keys)
They dictate how filters propagate across the data model allowing you to slice and aggregate information from multiple tables into a single cohesive report without needing to merge them.

Types of relationships in Power Bi

One-to-Many(1:*)- Found when a primary table links to a secondary, larger dataset.
-one customer:many orders
Many-to-One(*:1)- It connects a dimension table with unique values to a facts table containing many instances of the same value.
-Its the most common and recommended relationship.
Many-to Many(:)- Used when both tables have repeating values.
-Requires carefull handling as it can cause ambiquity.
One-to-One(1:1)- Used when a value in either table appears exactly once. It is relatively rare.

Types of Schemas

What is a schema in power Bi?
A schema refers to the logical structure and organization of tables in a data model.
Star Schema-
A star schema is a foundational data modelling technique that organizes data into a central fact table sorrounded by dimension tables.
Key components

Fact table- its the central table in a data model. It contains numerical values that you want to anlyze and usually links to dimension tables through keys.
Dimension tables - They are tables that contain descriptive information about the business entities in the tables.
They provide contexts to the numbers stored in fact tables and are used for filtering, grouping and labeling data in reports.

Advantages

One central fact table
Multiple dimension tables
Fast query perfomance

Snowflake Schema
It is a data modelling structure where dimension tables are normalized and split into multiple, related sub-tables to save data storage space and reduce redundancy.
Advantages

Better data integrity
Reduced storage

Disadvantages

Slightly slower queries
Requires more joins

How Excel is Used in Real-World Data Analysis

Melvin — Sat, 06 Jun 2026 16:24:42 +0000

Microsoft excel is a spreadsheet program used to collect, analyze, store, calculate and visualize data efficiently.

Applications of Excel in real-world data analysis

Making data-driven business decisions: Companies usually collect data e.g sales numbers, customer behaviour. Excel allows that data to be cleaned and structured using tables,dashboards,filters and formulas. Once the data is organized, excel supports better decisions by making data easy to clean, visualize and interpret.
Financial reporting: Excel plays a critical role in financial reporting by transforming raw data into meaningful insights that helps businesses, stakeholders understand their perfomance and make data-driven decisions. It helps build financial models, create charts and dashboards used for reporting.
Marketing perfomance: Excel is used in marketing perfomance to measure results from campaigns like ads, email marketing and monitor engagement rates on social media platforms e.g facebook, instagram. Thus it's used to create charts, and create comparison on perfomance across different campaigns.

Formulas and features of Excel i've learned so far:

Example: A(product) B(region) C(sales)

SUMIF() function - it is used to add up values that meet specific condition. Example, if you do want to get the total sales of oranges only.

=SUMIF(C2:C6,A2:A6,"Orange")

COUNTIFS() function - it is used to count how many rows meet multiple conditions at the same time. Example, How many times did apple sell in Nairobi?

=COUNTIFS(A2:A7,"apple",B2:B7,"Nairobi")

Data validation - it is a feature in excel that controls what type of data can be enterd into a cell. It enables users enter only valid and accurate data. Example, if you only want to enter gender as only male and female.

Conclusion

In conclusion, learning excel has changed the way I view data because it has shifted my thinking from seeing raw and messy data to seeing cleaned, transformed data that's used in data-driven decion making.

DEV Community: Melvin