POWER BI -DATA MODELLING ,RELATIONSHIPS AND JOINS

vivian wawira — Sun, 18 Oct 2026 06:30:30 +0000

INTRODUCTION

Power BI is a powerful platform where raw data is collected from various sources , cleaned, analyzed and presented in interactive dashboards and reports to support decision making. Concepts like Data modelling ,relationships and joins are what make these reports interactive and insightful.

## 1.DATA MODELLING
In Power BI , data modelling is used to organize data into tables and define the relationships between them. This allows to filter, analyze and summarize your data in a more meaningful and elaborate manner. A well- designed data model helps to:

Create well structured and meaningful reports easily
Improve data quality by reducing errors, repetitions and other inconsistencies
Organize data that helps in better reporting

*### Data modeling approaches *

1. Flat Table
This is one single table that contains everything .For example , customer order, customer id, dates ,regions, groceries, sales .
It has no relationships ,no separtion.

Advantage: it is simple for beginners ,it has no joins.

Disadvantage: there is huge data redundancy and repetition .It shows poor performance

2.Star Schema
This is the most recommended and standard approach for Power BI.
It is one fact table at the center surrounded by dimensional tables .'

Advantages:

Easy for users to understand
It has low model complexity
simple DAX
minimal joins
very fast

Disadvantages:

data redundancy in dimensions
limited flexibility since it bult for specific ,pre-determined and analytical use cases and queries.

3.Snowflake Schema

It works just like a star Schema with the dimensional tables further broken down to several smaller relatable tables. Example: Dimlocation split into Dimcity->Dimcounty->Dimreligion.

Advantages: It is normalized there there is less data redundancy
Disadvantages:It has so many joins, complex DAX and slower queries therefore it could be complicated for business. It IS slower(increases complexity).

Snowflake schema is used in power BI when source system is highly normalized and cannot be denormalized.

Facts Tables and Dimensional Tables**

1. Facts Tables
This tables hold specific events transactions that are to be analyzed and used to establish relations.

Characteristics:

holds data in quantities,
it contains numerical records or events
it contains foreign and primary keys
holds data metrics
helps understand the grain of the data

Grain is the description of what each record in a table consist of

2. Dimensional Tables
This tables give a description of events that are held on the fact tables.

Characteristics:

uses descriptive context .Who,where,when
All characteristics have a primary key.

Relations in Power BI

A relationship is a logical connection between two tables using keys(primary keys and foreign keys). It allows filters to flow and avoids physically merging tables.
Primary Key (PK):is a unique identifier in dimension tables. Eg. CustomerID in DimCustomer is unique.

Foreign Key (FK): is a primary key in a fact tables. Eg. CustomerID appears many times in FactOrders.

Unique Values: PK must be unique, FK can have duplicates.Referential Integrity: Every FK in fact should have a matching PK in dimension
.Active vs Inactive: Only one active relationship between two tables. Second relationship (e.g. Order Date and Delivery Date both to DimDate) becomes inactive, activated with USERELATIONSHIP() in DAX.

Cardinalities:it refers to how data in one table can relate to data in another table based on numerical count of matching rows.

Types of Cardinalities:

One-to-Many (1:_): Refers to one row matching to many rows inanother table.it is Standard and recommended. One customer in DimCustomer can have many orders in FactOrders. Example: DimCustomerCustomerID -> FactOrdersCustomerID.
One-to-One (1:1): One row matches exactly one row. Rare. Use for extension tables or security. Eg. DimCustomer and DimCustomerDetails. Avoid unless necessary.
Many-to-Many (:): refers to multiple rows that can link into multiple rows in another table. Example: Customers buying multiple Products and Products bought by multiple Customers. In modern Power BI, it works but requires careful handling. Avoid if you can create a bridge table. Do not use as default.

Filter Directions

They help to show how filters flow through relations.
Examples of filter directions

Bidirectionalthis allows filters to flow from both ways ,example; Fact->Dim and Dim->Fact. We can use bidirectional for some cases like many-to-many bridge tables or when you need a dimension to filter another dimension through the fact.

-** single Direction** this is the most recommended filter direction ,the filters flow from dimension to fact (from DimCustomers to FactSales).Single direction filter is more efficient and predictable.

Joins In Power Query (merge query)

Joins help us to combine two tables into one using matching columns by using primary keys and foreign keys.

Inner joins We use inner joins to keep only records that match on both tables.

2.Left Outer joins
We use left outer joins when we want keep all the rows from the left table (first table )and the matching rows from the second table .

3.Right Outer joins
We use this when we want to to keep all the rows from the second table and the matching rows from the first table

4. full outer join
We use full outer joins to keep all the rows from both tables whether they match or not
**
5.Left anti-join**
It is used to keep only rows in the first table that have no match in the second table.

6.Right ant-join
We use this type of joins to keep only the rows from the second table that have no match in the first table.

###Distinction Between Power Query Joins and Power BI Relationships

power Query Joins increases tale size ,reduces tables which my cause redundancy while Power BI Relationships keeps normalized and is more efficient.
Power Query Joins is used during data loading and data transformation before data modelling while Power BI Relationships are used after loading data, in the model view.

Conclusion

In conclusion, the most recommended Power BI Model is the STAR SCHEMA, since it provides the balance across all criteria:

Dax formulas are short ,clean and very performant since filters from from dimension to fact naturally
- Star Schema minimizes joins at query time hence easy and fast query and report performance.
- Developers and business users can be able to read and understand the visuals created easily with this model since the fact are at the center with the dimensions around.
- In star schema data scales cleanly because data can grow to as many fact rows as possible with the dimension tables remaining smaller.
- Data redundancy in star schema supports performance by de-normalizing dimension tables by keeping eg. cities, states and countries in one single table
- When making changes in a star schema only single dimension tables are updated leaving the fact table and historical tables untouched, this helps to reduce risk of breaking existing reports during updates
- Star schema results to ease of creating reports since dimension tables can be reused severally and the user does not need to take time to find tales, this helps to reduce mistakes and creates visuals that are clear and valid.
- Filter propagation in star schema ensures predictable filtering without ambiguity since it uses single direction filters.
- Star schema has low model complexity where complex data is broken down into modular and repeatable blocks .

Getting Started with Excel for Data Analytics: From Basics to Data Cleaning.

vivian wawira — Tue, 08 Sep 2026 20:49:20 +0000

Introduction to excel

Microsoft Excel is a spreadsheet that allows one to collect, organize, analyze ,calculate and visualize data in a more efficient manner. When you open excel the first thing you see is a toolbar known as a ribbon, it contains toolbars with various options that helps to make changes on any data presented on the worksheet

Excel workbook

The excel work book has a worksheet that is made of rows and columns, the rows run horizontally and are numbered(1,2,3...) while the columns run vertically and are named with letters (A,B,B...).On an excel workbook there are several work sheet that you can work from and your required to name each worksheet for easier reference.

Data Cleaning

Data cleaning is an essential step in data analytics. Before any data is analyzed data cleaning must be done to ensure data is accurate and reliable. Data cleaning involves commands like filter, sort, remove duplicates, correct UPPER case, Lower case or Proper case, Aggregating etc. This clean data helps create correct pivot chart and tables and dashboards for better decision making.

common dirty data that could be found when data cleaning

1. Duplicates
Duplicate rows that repeat the same employee.
Fix: Select all data → Go to Data tab → Click Remove Duplicates.

2. Irregular Capitalization
Names and departments written in different cases like NAIROBI, nairobi, Nairobi.
Fix: Use =PROPER() for names or =UPPER() / =LOWER() to make it consistent.

3. Missing Data / Blanks
Some cells were empty, e.g., blank Department or Salary.

4. Inconsistency in Text
Same location or job written differently:

Locations: nainobbi, Nairobi, NAIROBI
Jobs: waitre, waitress, watrss Fix: Use Find & Replace (Ctrl + H) or clean with TRIM() and PROPER().

Conclusion

Excel is an exciting Microsoft tool that helps us understand how and why things happened . It helps to analyze data and identify problems, progress or solutions. With data cleaning in Excel you able to create correct pivot tables and dashboards for better decision making.

DEV Community: vivian wawira

POWER BI -DATA MODELLING ,RELATIONSHIPS AND JOINS

INTRODUCTION

Facts Tables and Dimensional Tables**

Relations in Power BI

Filter Directions

Joins In Power Query (merge query)

Conclusion

Getting Started with Excel for Data Analytics: From Basics to Data Cleaning.

Introduction to excel

Excel workbook

Data Cleaning

common dirty data that could be found when data cleaning

Conclusion