DEV Community: Collins Sigei

Power Bi: Data Modelling, Relationships and Joins

Collins Sigei — Wed, 23 Sep 2026 16:05:26 +0000

Data Modelling

Data modelling involves organizing, connecting, and structuring data from different tables so that Power BI can analyze and summarize data it correctly.

Why is data modelling important?

Makes data easy to analyze as it organizes information into fact and dimension tables.
Ensures correct relationships by enabling information from different tables to work together
Reduces data errors and duplication as separating information into appropriate tables avoids unnecessarily repeating the same data.

Types of Tables

1. Fact Table

This contains the quantitative data that represents the transactions and events and holds numeric values that will be analyzed.
Fact table contains foreign keys. For instance, consider a hospital record dataset, the fact table contains foreign keys pointing to the related dimension tables like information about the departments, diagnosis, the doctors, patient information as well as other information such as admission type, insurance, length of stays.

2. Dimension Table

This contains descriptive attributes used to analyze the information in the fact table.
Dimension table contains primary key which creates a relationship with the fact table foreign key. In the hospital records dataset, the dimension tables contain information such as department ID, diagnosis ID, doctor ID, patient ID

3. Flat table
This is a single table where all the information needed for analysis is stored in one place, rather than being separated into multiple related tables.

Types of Schemas

Schema is a way of organizing and structuring tables and their relationships.

1. Star schema
Data is organized into one central fact table surrounded by dimension tables.
The fact table is at the center and the dimension tables are around the fact table.

Advantages of a Star Schema

Simple and easy to understand
Reduces data duplication
Makes analysis easier

Disadvantages of a start schema

Requires proper relationship management as incorrect relationship can result to inaccurate analysis
Data needs to be organized into appropriate fact and dimension tables before building the report

When is a star schema appropriate?

When working with large datasets that contain different types of information.
Building Powerbi dashboards that require interactive filtering and analysis
Creating reports that will be updated regularly, because the model is easier to maintain.

2. Snowflake schema
A snowflake schema is a model where dimension tables are broken into sub-dimensions, resembling a snowflake shape.

Advantages of snowflake schema

Provides better data organization as related information can be separated into logical tables.
Reduces data duplication as repeated information can be separated into its own table.

Disadvantages of snowflake schema

More relationships to maintain as incorrect relationships can lead to inaccurate results.
Can make reporting more difficult for users as users may need to understand several tables before they can identify the information they need.
May not be necessary for simple datasets

When is a snowflake schema appropriate?

Where dimensions are extremely large with repetitive hierarchies
When working with a large and complex dataset

Primary Key

Think of a primary key as an ID number that help tables in a dataset talk to each other.
This is a unique identifier in a table. In this instance, the primary key is the dimension table of dim_patients is patients Id in dim_doctors is doctors Id in dim_departments is departments Id

Foreign Keys

This is a primary key used in another table. In this instance, the primary keys from the dimension table are seen in the fact table and therefore referred to as foreign keys.

Understanding Relationships in Power Bi

When working with Power BI, data is often stored in multiple tables. For example, a hospital records dataset may have separate tables for patients, hospital visits, departments, doctors and treatments.

A relationship is a connection between two tables that shows how the data in those two tables are related.

For instance, in the hospital records dataset, we have the following tables:
Dim_Patients

Patient ID	Patient name	Patient gender
1	Amina Otieno	Female
2	John Kariuki	Male
3	Grace Naliaka	Female

We also have
Hospital visits table

Visit ID	Patient ID	Department
1001	1	Outpatient
1002	4	Laboratory
1003	3	Pharmacy

Both tables contain Patient ID. This allows the relationship to connect the two tables

In the Patients table, Patient ID is the primary key because each patient has a unique ID.

In the Visits table, Patient ID is the foreign key because it refers back to the patient in the Patients table.

This is a one-to-many (1:*) relationship.

It means that one patient can have many hospital visits.

One-to-One (1:1)
One record in one table corresponds to exactly one record in another table.
For instance, Patient ID and Patient Medical Profile tables

Patient ID	Patient name	Patient gender
1	Amina Otieno	Female
2	John Kariuki	Male
3	Grace Naliaka	Female

Patient ID	Blood Group	Insurance No
1	O+	INS 001
2	A+	INS 002
3	B+	INS 003

Here, each patient has one medical profile.

This is a one to one (1:1) relationship

The Patient ID in the Patients table identifies the patient, while Patient ID in the Medical Profile table is used to connect that patient's profile to them.

Many-to-Many (:)
Many records in one table can be related to many records in another table.
The hospital records dataset provides a good example as in a table of Patients and Doctors.

A patient can see several doctors and at the same time, a doctor can see many patients, so many patients can see many doctors

Understanding Joins

Data is often stored in different tables. For example, a hospital records dataset may have separate tables for patients, visits, doctors, and departments.

Step 1:Load the data, then select home and click of transform data to go to power query.

Step 2: Select the first table and head to merge queries. The first table selected will be the left table.

Step 3:In the merge window, select the table you want to join with the first table. From the two table, select the column from each table that are common from both tables.

Step 4: Choose the join type from the dropdown menu

A join allows us to combine data from two tables using a common column. For instance, the Patients and hospital visits tables may both contain Patient ID. This common column can be used to join the two tables.

1. Inner Join
An inner join returns only records that have a matching value in both tables.
In this hospital records table, the dim_patients and the hospital visits table, it will only return the common values in the table which is:

Patient ID	Patient name	Patient gender
1	Amina Otieno	Female
3	Grace Naliaka	Female

2. Left Outer Join
A left outer join keeps all records from the left table and adds matching information from the right table.

3. Right Outer Join
It keeps all records from the right table and brings in matching records from the left table.

4. Full Outer Join
A full outer join keeps everything from both tables. Provides everything from both tables, whether they match or not.

For instance the patients table has patients Id 1,2,3 while the visits table has patients Id 1,3,4 Therefore, the

Full outer join will provide 1,2,3,4

Patient ID	Patient name	Patient gender
1	Amina Otieno	Female
2	John Kariuki	Male
3	Grace Naliaka	Female
4	Ahmed Ahmed	Male

5. Left Anti Join
This is useful for finding records in the left table that do not exist in the right table.

For instance the Patient table: 1,2,3 and Hospital visits table: 1,4,3.

The left anti join returns 2

6. Right Anti Join
It returns records that exist in the right table but not the left table.

For instance the Patient table: 1,2,3 and Hospital visits table: 1,4,3.

The left anti join returns 4

Recommendation and Conclusion

For a Business Intelligence (BI) project, I would use a star schema as the main data modelling approach, with a fact table at the centre and dimension tables surrounding it.

For example, in a hospital analysis project, the model could contain:

Fact Visits – records of hospital visits, patients information, departments, doctors, and treatment administered.
Dim Patient – patient information.
Dim Department – department information.
Dim Doctor – doctor information, where required.

The fact table would contain foreign keys that connect it to the dimension tables through one-to-many (1:*) relationships.

Dim Patient (1) → Fact Visits (*)
This means one patient can have many visits, while each visit belongs to a particular patient.

Overall, the goal is to keep the model simple, well-structured, and suitable for accurate reporting and analysis.

Building an Interactive Excel Dashboard for E-commerce Product Analysis: A Case Study of Jumia Products

Collins Sigei — Sun, 06 Sep 2026 02:35:36 +0000

Introduction

This project involved building an interactive Excel dashboard to analyse Jumia products. The dataset contains information such as product, prices, customer ratings, reviews and discounts. Here in are the steps taken to ensure that the raw data translates into meaningful insights to help identify product performance and customer satisfaction.

Data Cleaning and Preparation

Initially, the dataset was cleaned to remove any inconsistencies, blanks, and corrected to ensure that the data was ready for analysis. Below is the raw dataset

Remove Duplicates Created a new worksheet from the raw dataset and named it as cleaned. Duplicates were removed from the cleaned worksheet to ensure all repeated records are expunged and only the unique records remain.
Format the numerical values to the appropriate category

The prices column are formatted and converted to currency
The review column is revised from the negative value and made to whole numbers.
On the ratings column, the out of 5 is expunged and only the whole numbers retained.
The blanks in each column are replaced by Not provided

Excel techniques, formulas and analysis performed

Discount Amount
Created a discount column and calculated the difference between the old and current price. Old price - Current price

Discount Category
Grouped products into categories of High discount, Medium discount, and Low discount, based on the discount percentage column.
=IF(D2>=40%, "High Discount", IF(D2>=20%, "Medium Discount", "Low Discount"))

High discount - more than or equal to 40%
Medium discount - more than or equal to 20%
Low discount - less than 20%

Rating Category
Classified products based on their ratings into Excellent, average and poor based on the ratings column using the following function:

=IF(ISBLANK(F2), "Not Provided", IF(F2>=4.5, "Excellent", IF(F2>=3, "Average", "Poor")))

Blank spaces - Not provided
Excellent - more than or equal to 4.5
Average - more than or equal to 3
Poor - less than 3

Price Category
Classified products into high price, medium price, and low price, using the below function based on the price column:

=IF(B2>=2000, "High Price", IF(B2>=1000, "Medium Price", "Low Price"))

High price - more than or equal to 2000
Medium price - more than or equal to 1000
Low price - less than 1000

Pivot Tables and Charts

Pivot table and charts helps to summarize the relationship between different categories.

Go to the cleaned dataset and select Ctrl+A
Go to insert and then select pivot tables

Several pivot tables were created to analyze the following:
Top 10 products by discount: This shows the relationship between the top products that had the most discount.
Slicers of the product and discount were added to the pivot tables

Top 10 products by ratings: This entails top products by ratings.

Discount mix: This shows the relationship between the discount category and the product

Dashboard

The cleaned and analyzed data was used to develop an interactive dashboard presenting e-commerce product performance and comparisons. The dashboard helps to examine product pricing, discounts, ratings, reviews, and customer engagement.

Finding

The average product rating is 3.89/5 which indicates moderate customer satisfaction.
Higher discounts do not necessarily result in higher customer engagement. High-discount products actually have lower average reviews than medium-discount products.

Recommendation

Adopt a medium discount category that is around the 20%–40% range, instead of automatically pushing products into 40% discount territory. This will create more engagement from the customers.

Git workflow

Collins Sigei — Sun, 30 Aug 2026 11:53:15 +0000

Git workflow: Working Directory, Staging, Commit and Push

This will demonstrate how to create folders, files, and pushing a data set to Github using Git.
Our data set is: Car Imports Analysis.

Create a folder in desktop

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop
$ mkdir Car-Imports-Analysis

Create a data folder inside Car-Imports-Analysis

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis
$ mkdir data

Create a file inside Car-Imports-Analysis

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis
$ touch README.md

Add content to README.md file

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis
$ echo "#Car-Imports-Analysis" >README.md

Used Nano terminal text editor to write the texts and display in README.md file.

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis $ nano README.md

`Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis
$ nano README.md`

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis
$ cat README.md
#Car-Imports-Analysis
##Project Overview
This project analyses the Car Imports data records for different car models in the respective years
##Tools Used
- Microsoft Excel
- Github
- Git
- Power Bi

Initialize the project with Git using git init:
Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis $ git init Initialized empty Git repository in C:/One Drive/OneDrive/Desktop/Car-Imports-Analysis/.git/
Connecting the local project 'Car Imports Analysis' to Github using SSH key:


Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis (main)
$ git remote add origin git@github.com:Collins-Sigei/Car-Import-Analysis-.git

The following are the steps towards pushing the project to Github:

Staging the project:

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis (main)
$ git add .
warning: in the working copy of 'data/Car Imports Data Records.csv', LF will be replaced by CRLF the
 next time Git touches it

Commit

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis (main) $ git commit -m "Add Car Imports Analaysis Records Project" [main (root-commit) 9a2d029] Add Car Imports Analaysis Records Project 2 files changed, 1009 insertions(+) create mode 100644 README.md create mode 100644 data/Car Imports Data Records.csv

Select and copy the SSH key from Github to gitbash
Connect local project to github

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis (main)
$ git remote add origin git@github.com:Collins-Sigei/Car-Import-Analysis-.git

Push the project to Github

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis (main)
$ git push -u origin main
Enter passphrase for key 'C:\Users\Barta/.ssh/id_ed25519':
Enumerating objects: 5, done.
Counting objects: 100% (5/5), done.
Delta compression using up to 4 threads
Compressing objects: 100% (5/5), done.
Writing objects: 100% (5/5), 48.25 KiB | 345.00 KiB/s, done.
Total 5 (delta 0), reused 0 (delta 0), pack-reused 0 (from 0)
To github.com:Collins-Sigei/Car-Import-Analysis-.git
 * [new branch]      main -> main
branch 'main' set up to track 'origin/main'.

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

Collins Sigei — Sun, 30 Aug 2026 11:27:22 +0000

Microsoft Excel

Microsoft Excel is a spreadsheet program used to calculate data, organize information, and perform detailed analysis. It structures data into rows and columns, allowing users to perform mathematical operations and create charts.

Excel User Interface

This entails the parts that the user interacts while using Microsoft Excel.

Ribbon - A large panel near the top holding tools categorized across tabs like Home, Insert, Page Layout, and Formulas.
Quick access toolbar - A small, customizable toolbar at the very top-left corner that holds frequent commands like Save, Undo, and Redo.
Name box - Located to the left of the formula bar, it displays the cell reference or address of your currently active cell (e.g., A1).
Formula Bar - A long input bar next to the name box used to type, view, and edit data or formulas inside active cells
Column Bar - The horizontal gray bar running across the top of the worksheet grid that displays letters (A, B, C, etc.) to identify each vertical column
Worksheet tab - A small clickable label at the bottom of the Excel window that represents an individual sheet inside your workbook.

Data Formatting in Excel

Changing visual presentation of data without altering its actual values.

Number Formatting

This involves changing the how the value looks in a cell without altering the actual value stored in the cell.

Select the Home tab
Go to the Number ribbon
Select from the dropdown options the appropriate format to apply to the data set.

Data Sorting

This is arranging the data set in a particular order, either from largest to smallest in numeric values and from A to Z in text form.

Data Filtering

This allows specific data to be isolated from a large dataset

Click any single cell inside a dataset
On the data tab, in the sort and filter section, select filter

Freezing Panes

This allows the selected column and row to be visible when scrolling through a large dataset.

Go to the view tab
Select the row and/or column that should be visible
Head to windows ribbon under the view tab and click on freeze panes

Data Cleaning

Process of ensuring that the data does not not have incorrect information, duplicates, inconsistencies and errors.

Step 1. Remove Duplicates

Expunge the repeated data from the dataset

Select the whole dataset through ctrl+A
Go to the Data tab
Under data tools ribbon, select remove duplicates
Check the box "My data has headers" and press Ok

Step 2. Formatting

Ensure the respective columns have the correct format type, that is numerical and text formatting to align with the values in that column.

Step 3. Find and Replace

Use this to remove inconsistencies in each column

Select the specific column and click ctrl+H
Input the category that is duplicated and replace with one term to refer the selected category
Replicate the process in the other columns

Step 4. Remove Blanks

Utilizing the find and replace method, remove the blanks from each column and replace with either 'unknown' or 'not provided'

Text Functions

=UPPER() Converts the text to uppercase
=LOWER()Converts the text to lowercase
=PROPER() Capitalizes the first letter of each word and makes the rest lowercase

Aggregate Functions

=SUM() Adds all numbers in a range together
=AVERAGE() Calculate the mean of a range
=MEDIAN () Finds the number that appears in the middle when arranged in a particular order
=MODE()Find the most repeated value in an ordered set
=MAX() Finds the highest value in a data set
=MIN() Finds the lowest value in a data set

Date Functions

=Today() Returns the current date
=Now() Returns both the current date and time
=Year()Extracts the year from a given date
=Month() Extracts the month from a given date
=Day() Extracts the day from a given date
=Datedif() Calculates the difference between two dates