DEV Community: Philip Saidi

SQL Joins, CTEs, Subqueries, and Window Functions: A Practical Guide

Philip Saidi — Tue, 20 Oct 2026 13:05:36 +0000

INTRODUCTION

This article covers four techniques which are joins to combine tables, subqueries to break questions into stages, CTEs to keep those stages readable, and window functions to rank and compare rows without losing detail. Each comes with practical examples so you can start using them right away.

SQL JOINS

Joins are used to combine data from two or more tables based on a related column.
It helps in :

Matching records using common columns.
Improving data analysis by combining related information.
Creating meaningful result sets from separate tables.

Types of SQL Joins

These are categorized into different types based on rows from two tables are matched and combined.
Consider the doctors and visits tables share a column doctor_id.

INNER JOIN

Also called JOIN
Used to retrieve rows where matching values exist in both tables.
Inner joins helps in:

Combining records based on related column.
Returning only matching rows from both tables.
Excluding non-matching data from the result set.
Ensuring accurate data relationships between tables.

Figure below shows inner join

select tableA.column1, tableA.column2, tableB.column1 from tableA as A INNER JOIN tableB as B ON A.matching_column = B.matching_column;
Example on the hospital data

SELECT * 
FROM doctors
INNER JOIN visits
ON doctors.doctor_id = visits.doctor_id;

Output of inner join query

LEFT JOIN

Also called LEFT OUTER JOIN.
Used to retrieve all rows from the left table and matching rows from the right table.
Useeful in :

Returning all records from the left table.
Showing matching data from the right table.
Displays NULL values where no match exists.

Figure shows left join

Example using the hospital data doctors table and visits table

SELECT * 
FROM doctors
LEFT JOIN visits
   ON doctors.doctor_id = visits.doctor_id;

Left Join Output

RIGHT JOIN

Also called RIGHT OUTER JOIN
Used to retrieve all rows from the right table and matching rows from the left table.
Useful in:

Returning all records from the right side table.
Showing matching data from the left side table.
Displays NULL values where no match exists in left table.

Figure show Right Join

Example using hospital dataset

SELECT patient_id, 
       patient_name, 
       visit_id
FROM patients as p
RIGHT JOIN visits as v
ON p.patient_id = v.patient_id;

Output of a Right Join

FULL JOIN

Used to combine the results of both left join and right join.
Helps in :

Returning all rows from both tables.
Showing matching records from each table.
Displays NULL values where no match exists in either table.
Provides complete data from both sides of the Join.

Figure shows Full Join

Example using hospital visits

SELECT * 
FROM patients
FULL JOIN visits
ON patients.patient_id = visits.patient_id;

Output of a Full Join

NATURAL JOIN

Combines two tables based on columns that have same name and compatible data types.
Returns only rows where the values in the common columns match.

Joins tables using common columns with same name.
Returns only rows where values in those columns match.
The common column appears only once in the result.

Example using hospital data

SELECT patient_id, 
       patient_name, 
       visit_id, 
       visit_date
FROM patients
NATURAL JOIN visits;

Output of a Natural Join

SQL COMMON TABLE EXPRESSIONS(CTEs)

Common table expressions are a temporary set in SQL that you can reference within a single query.
CTEs simplify complex queries, makes them easier to read and can be reused multiple times within the same query.

CTEs are used for:

Performing recursive operations.
Breaking down multi-step calculations into manageable parts.
Replacing nested subqueries in complex data retrieval tasks.

Consider the visits from the hospital visits table, lets find the average total_bill by insurance provider using
a CTE.

syntax

WITH avg_bill_by_insurance_provider AS
    (SELECT insurance_provider, AVG(total_bill) AS avg_bill
FROM visits
GROUP BY insurance_provider)
SELECT * 
FROM avg_bill_by_insurance_provider;

Output of CTE

WITH clause defines a CTE named avg_bill_by_insurance_provider.
The main query references this CTE to retrieve the average bill for each insurance provider.

syntax of a cte shown below

WITH cte_name as 
(SELECT query)
SELECT * 
FROM cte_name;

cte_name is a unique name for the CTE expression.
query _is a valid SQL query that returns a result set, which will be treated as a virtual table within the main query.
_select is the main query that can reference the CTE by it's name.

USES OF CTEs

Helps simplify and organize complex SQL queries.

Breaks complex queries into small, reusable parts.
Makes queries easier to read and understand.
Helps work with hierarchial data using recursion.

LIMITATIONS

Temporary : A CTE works only while the query runs, then it disappears.
Performance: On a very large data, CTEs can be slower if reused many times.
Restrictions : Some databases limit using CTEs with INSERT, UPDATE or DELETE.

SQL SUBQUERIES

Subquery is a query nested inside another SQL query.
It allows complex filtering, aggregation and data manipulation by using the result of one query inside another.
It is essential when we need to perform operations like:

Apply Aggregate functions like SUM, COUNT, AVG dynamically.
Update data using values from other tables.
Delete rows based on conditions returned by another query. Example in the visit table to calculate the average total_bill of all patients.

SELECT * FROM visits
WHERE total_bill > (SELECT AVG(total_bill) FROM visits);

Subquery Output

The main query returns only the patients whose total_bill is higher than the average.

SQL clauses for Subqueries
WHERE _: Filters rows in the outer query based the results returned.
_HAVING : Filters grouped/ Aggregated results using values produced by Having clause.

Types of Subqueries

Single-Row Subqueries

A single-row subquery returns only one value as output.
Returns exactly one row as the result.
Commonly used with comparison operators such as =, >, <
.

Example using the patients table to find the oldest patient in the hospital visits data.

SELECT * 
FROM patients
WHERE patient_age = (SELECT MAX(patient_age) FROM patients);

Single row Subquery Output
The query returns the patient age whose age matches that of max value.

Multi-Row Subquery

Returns more than one value.
Returns multiple rows as result.
Requires operators that handle multiple values such as IN, ANY or ALL.

Example using doctors and visits table in the hospital visits data to find doctors located in Kisumu city.

SELECT * 
FROM visits
WHERE doctor_id IN(
SELECT doctor_id FROM doctors WHERE  city = 'Kisumu');

Output

Correlated Subquery

This depends on the outer query for its value.
It's a dependant query it references columns from the outer query.
Executed once for each row of the outer query making it slower for large datasets.

Examples of using subqueries with clauses

Fetching Data Using Subquery in WHERE Clause

This example uses a subquery in the WHERE clause. The inner query finds doctor_id of doctors who were paid in 'Cash' in the visits table and the outer query displays their details from the doctor table.

Query

Output

CTE vs. Subqueries
| Feature | CTE | Subquery |
|-------------------------|------------------------------------------|-------------------------------------------|
| Referencing | Can be referenced multiple times | Typically used once |
| Readability | Improves readability for complex queries | Can become difficult to read when nested |
| Efficiency | Optimized for multiple references | May be less efficient for repeated operations |

WINDOW FUNCTIONS

These Functions allows performing calculations across a set of rows that are related to the current row, without collapsing the result into a single value.

Commonly used for tasks like:

Aggregation
Ranking
Running totals

Uses OVER clause, which defines the 'Window' of rows for the calculation.

Window Function contains:

PARTITION BY _ This divides the data into groups using partition by.
ORDER BY _ Specifies the order of rows within each group using order by.

Functions like SUM(), AVG(), ROW NUMBER(), RANK(), and DENSE RANK() can be applied in a controlled way in window functions.

syntax

SELECT column_name1, 
    Window_function(column_name2)
OVER([PARTITION BY column_name3]
[ORDER BY column_name4]) AS new_column
FROM table_name;

Window_function - Function used for calculation or ranking column_name1 - column to display column_name2 - Function column column_name3 - PARTITION BY column column_name4 - ORDER BY column new_column - result alias table_name - source table

Types of Window Functions

There are two types namely:

Aggregate window functions.
Ranking window functions.

Examples using the hospital data.

Aggregate window functions.

Calculates aggregates over a window of rows while retaining individual rows.
These are:

SUM() - sums values within a window.
AVG() - calculates the mean of numbers.
COUNT() - counts the rows.
MAX() - returns the highest value.
MIN() - returns the least value.

Using AVG(), to calculate average total_bill within each insurance provider in the visits table.

SELECT insurance_provider, total_bill, 
   AVG(total_bill) OVER (PARTITION BY insurance_provider) as average_totalbill
FROM visits;

Output

Ranking window functions.

Provides rankings of rows within a partition based on a specific criteria.
Ranking functions includes:

RANK() - assigns ranks to rows, skips ranks for duplicates.
DENSE RANK() - assigns ranks to rows, with duplicates included.
ROW NUMBER() - assigns a unique number to each row in the result set.
PERCENT RANK() - shows relative rank of a row as percentage between 0 and 1.

RANK()

Used to assign ranks to rows within a group based on a specific order
Commonly used to organize and analyze data.
Using RANK(), we will rank doctors by city.

Output

Fixing Window functions issues

When using window functions certain issues like slow performance can be avoided by:

Partioning carefully - without partition by the whole table is treated as one group.
Check order by - it controls the calculation order in the window.
Optmise performance - on large datasets window functions might be slower, use indexes if needed.

JCARS LOGISTICS DATA ANALYSIS: FROM RAW DATA TO INTERACTIVE POWERBI BUSINESS INSIGHTS.

Philip Saidi — Tue, 29 Sep 2026 17:20:31 +0000

INTRODUCTION

JCars Logistics imports, sells, and delivers vehicles to customers across different regions in Kenya. The company collects information about its sales transactions, vehicles, customers,
branches, sales representatives, payments, deliveries, logistics costs, returns, cancellations, and customer experiences. Management would like to use this information to better understand how the business is performing and identify areas that require attention.

TOOLS USED FOR THIS DATASET

PowerQuery for cleaning the dataset.
PowerBI for Modelling, Relationships, Visualization, and Dashboard.

DATA OVERVIEW

Step one: Understand the data before you build

Grain of the data what each row and _column _represents.
I then sorted the columns into groups: transaction, customer, geography, people and channel, vehicle, money, status and experience.
The dataset had 277 rows and 32 columns.
Columns were named column 1 to column 32 each representing different data as shown below:
OrderID, Order date, Delivery date, customer name, age, type, Region, county, city, branch, sales rep, lead, car details, units sold, selling price, Discount, revenue recorded, delivery fee, logistic fee, customer rating, review count and returned
The loaded data shown below

DATA QUALITY

The most important step in data analysis.
This helps in discovering data quality issues.
After loading the data in powerBI here are some of the issues found:

The Order ID was not an ID. There were five prefix styles (LC1000, LCL-1001, ORD1004, ord1020, CAR1086), 19 blanks or N/A, and three numbers used twice.
Dates were in six formats. Some were numbers (46066), some 26-Mar-25, some Aug 29, 2025, and four were 2026-13-04, which is year-day-month. Worst were the 47 slash dates like 03/04/2026, which are valid as both 3 April and 4 March.
Categories exploded. Toyota appeared as Toyota, TOYOTA, Toyta, Totoya, Toyota Kenya. In total, Car Make had 62 spellings for 10 real car makes. Sales reps had 80 "names" for 10 people, including Dan1el Kimani, where a digit 1 had replaced the letter i.
Discounts came in over 50 different spellings. 7%, 0.07, 7, 7 percent, ten percent, No Discount, #ERROR.
Numbers were formatted as text as shown below.
Five columns held money: price, unit cost, delivery fee, logistics cost, and recorded revenue. They mixed KSh, KES, plain numbers, $, USD, EUR, ZAR, R, a ?, and an M suffix (9.14M).
Customer names, Region, Branch, City were not in proper case and inconsistent spellings like COUNTY GOVERMENT, Msa, Nkr, Faith ACHIENG.
Numerical values that held monetary values had negative values like -22,738,440 and customer rating of -1.
Customer Age as text.

DATA CLEANING

After identifying the data quality issues, I hit transform data.
First step I categorized according to:

Customer details
Location details
Currency details
Payment details
Customer rating

First and foremost I made first rows as headers (promoted headers) as shown below

Customer, sales rep details cleaning

Here I cleaned the customer name, customer type and age
Customer names were in improper case as shown below
Select the customer name column -> Rightclick-> Transform-> Capitalize each word ->OK
The Age I changed from text format to wholenumber type by
Selecting customer age -> Rightclick -> Change type -> Wholenumber -> OK
Sales rep had Improper case too, I transformed to proper case and used Replace values with the inconsistent words like Dan1el to Daniel

Location data quality issues

I cleaned includes city names like Nkr changed to Nakuru, Msa to Mombasa, Eldo to Eldoret by replacing values Selecting column -> Right click -> Replace values -> Write the value to be replaced and value to replace with (Msa -> Mombasa )->OK
Then changed to proper case by Transform -> Capitalize each word ->OK

Currency

Currency was to be standardized to KES or Ksh.
Currency was in USD or $, EURO, ? assumed as EURO and ZAR/R.
I used 1USD = 129.35, 1EURO = 147.15 and 1ZAR = 7.8.
I changed the values to fixed decimal numbers in the rows affected holding monetary values like Revenue, Delivery fee, Logistics fee, Unit selling price and others.
The negative values I assumed they were whole and changed them to a positive number.

Date column

This contains delivery date and order date.
Date was in serial numbers like 4648, mixed date formats as text.
I changed the format to date format and Removed errors.

Payment methods and Customer rating

The issues with payment methods was improper case, names were mispelled.
I used Replace values with correct spelled words.
Customer rating was in words , I used Replace values with the correct values like 4.5 out of 5 with 4.5.

DATA MODELLING

After cleaning I had one Flat table.
To start modelling process, I had to categorize the Flat table into separate Dimension table and one Fact table.
I categorized according to each components of the table namely:
I used duplication of the flat table into 8 tables and removed columns one by one until I got the required details for each.

Dim_customer

This will hold all customer details like:

Customer name
Customer age
Customer ID

Sales representative

This will hold the sales rep details:

Sales rep name
Lead source
Sales_rep ID

Order status

This represents order and delivery status:

Delivery status
Order status
Status ID

Dim_location

Contains the location details like:

Branch
Region
County
City
Location ID

Payment method

Represents payment methods used:

Payment method
Payment ID

Dim_date

Represents the dates for time series analysis.

Delivery date
Order date

Dim_car

Represents the car details:

Car make
Vehicle
Fuel type
Transmission
Color
Car model
Vehicle year
Car ID

Dim_Fact_table

This contains all numeric values and all foreign keys.

Customer rating
Revenue recorded
Delivery fee
Logistic fee
Units sold
Unit selling price
Primary key (OrderID)

After categorizing the tables into dimtables and one fact table, I checked for duplication by Select the column -> Remove duplicates -> OK
Then I added an index column by Selecting the table -> add index column -> Choose from 1 -> OK and rename accodring to the table created.
I closed and applied for loading in PowerBI for creating relationships.

RELATIONSHIPS

Data is loaded to PowerBI.
To establish relationships, I used the Primary keys in the Dimension tables to Foreign keys in the jcars_fact_table.
The cardinality used is one to many because is the required standard in star schema approach, with a Filter direction of single , and made the relationship active.
The relationships was represented by a star schema, with one fact table at the centre with an active relation with all the dimension tables as shown below.

DAX MEASURES

I created some important DAX measures to represent the Business performance.
Select column jcars_fact_table -> Create a new measure -> Write the formule example Total revenue =SUM(Revenue recorded) -> Enter
To display the measure go to Report view -> Select a card visual -> Look for the created measure in the jcars_fact_table -> click on it -> The measure will be displayed on the card

DAX MEASURES CREATED

These are some of the DAX measures I created.

Revenue = sum(jcars_fact_table[Revenue Recorded])
Total cost of units sold = SUMX(jcars_fact_table,jcars_fact_table[Unit Cost] * jcars_fact_table[Units Sold])
Total cars sold = sum(jcars_fact_table[Units Sold])
Gross Margin % = DIVIDE([Gross Profit],[Revenue], 0)
Gross Profit = [Revenue] - [Total cost of units sold]
Average Customer rating = AVERAGE(jcars_fact_table[Customer Rating])
Total Transcations = COUNTA(jcars_fact_table[Order ID])
Unpaid Revenue = CALCULATE([Revenue],dim_order_status[Payment Status] IN {"Pending", "Partially Paid", "Unpaid"})
Return Rate % = DIVIDE(CALCULATE([All Orders], dim_order_status[Returned] = "Y"), CALCULATE([All Orders], dim_order_status[Returned] = "Unknown"))

DASHBOARD

I created my dashboards with DAX measures as my Key Performance Indicators namely:

Revenue
Gross Profit
Gross margin
Return Rate
Unpaid Rate
Units sold
Total transcations made I also included Slicers for Customer type and Regions for Interactivity, with Visuals as shown below.

BUSINESS INSIGHTS

What the data said

Headline: KSh 408.6M revenue, 128 vehicles, KSh 7.8M gross profit, 0.02 gross margin.
More Findings:

More Discounts does not mean more units sold.

Discount    Units sold  Gross margin
0–3%          1            16.0 % 
over 3-5%   5            49.0 % 
over 5-7%   7            12.0%  
over 7-10%      1                14.0%
over 10-15% 1            2.0%

Revenue is up margin is down
Quarterly gross margin slid from 15.0% (Q1 2025) to -3.0% (Q2 2026) while Q4 2026 revenue hit a period high of KSh 49.2M, with a gross margin of 53.0%.
Athi River Branch had the highest Revenue of 53.9M and a gross margin of 49.0%, with least is Kakamega Branch with a Revenue of 1.9M and a gross margin of -1.0%.
Volume is not value in lead sources. Whatsapp is the biggest revenue source (KSh 65.8M) but earns 36.0% margin, with 9 units sold and Walk-in had 16.2M revenue with a negative margin of -17%, selling 11 units.
24% of active revenue (≈ KSh 96.33M) is Pending , Partially Paid or Unpaid. The exposure is spread across every customer type (NGO and Corporate each ≈ KSh 64.81M), so it is a process issue, not one bad customer.
Toyota and SUVs dominate, and customer types differ sharply in margin. Toyota is 8% of revenue and SUVs 15%%, which is strong but a concentration risk. Dealers earn the best margin (14.0%) while NGOs, at 16% of revenue. Cash and Wire deals earn 30.0% against 24% for RTGS and Cash.
Customer ratings are not linked to revenue. Correlation of rating with order revenue is −0.08, and the average rating is 3.6 out of 5 for every customer type (3.56–3.62). customer satisfaction is not a function of deal size or customer type.

CHALLENGES AND LESSONS LEARNED

PowerQuery issues with performance and ran slower.
Discovering that the delivery fee sits inside recorded revenue changed the profit definition. Testing a formula against a recorded column paid off more than any single cleaning step.
Recorded values are not automatically right. The recorded revenue column was less reliable than a recalculation from its own components.
Lesson: understand the business meaning of each field before modelling. Most of the value came from questions asked before touching a visual.

ASSUMPTIONS

The currency column the figures which were in EURO and ? were all marked as EURO.
Recorded Revunue has Delivery calculated in it.

CONCLUSION

Recommendations

Review Thika's pricing and discounting before scaling it.
Shift lead-generation effort toward Instagram and Referral, and review how Phone Call leads are handled.
Set collection targets and ageing reports, and start capturing payment dates.
Define "Returned" and record return date, reason and refund value.
Fix data entry at the source with dropdowns, currency and date validation, and real Order IDs. Cleaning once does not stop the mess coming back.

What I learned

Test formulas against recorded values. Finding that the delivery fee sits inside revenue came from a validation check, not from a cleaning step, and it changed the whole profit definition.
Certainty has limits. Ambiguous dates cannot be resolved perfectly. A documented rule plus a flag beats a silent guess.
Excluding data has a cost. Showing the 68% coverage next to the KPIs is more honest than a clean-looking number.
Questions come before visuals. Most of the value came before I dragged a single chart onto the canvas.

Code, data, model and the full data-quality log are in the:Github

DATA MODELLING 101 IN POWERBI

Philip Saidi — Wed, 16 Sep 2026 03:58:48 +0000

WHAT IS DATA MODELLING IN POWERBI.

It's a process of setting up tables, relationships, calculations and access for data analysis scenarios.
Data modelling also define the data structure of single or many tables and there relationships.
A well defined model delivers fast performance, accurate results, simple DAX, and an intuitive experience for data analysts.
This topic will cover the essential principles of data modelling in PowerBI, with strong focus on recommended star schema, relationships and joins(both in PowerQuery and data model).

Why Data Modelling matters.

Good modelling delivers:

Faster query performance and lower memory consumption.
Simple and more reliable DAX.
Easier maintenance and scalability.
Better usage for business us

Model Schemas

We will focus on two types of schemas:

Star schema

The data model in this schema demonstrates the relationship between one fact table and many different dimension tables.
Figure below shows components of star schema.

Fact Table:
The fact table sits at the center of the schema and stores the measurable, quantitative data used for analysis. Examples include:

Sales amount
Units sold
Discount
Profit Each record in a fact table represents a business event (e.g sales transcation).

Dimension Tables:
Dimension tables surround the fact table and contain descriptive attributes that add context to the facts. Common dimensions include:

Product details
Customer details
Time attributes
Employee or store information These tables allow users to slice, dice, filter, and group the fact data for analysis (e.g., sales by region, by month, by product category).

Features of Star schema.

Fact table contains numeric measures.
Dimension table storing descriptive attributes.
High query performance.
Easy to understand, even for non-technical users.
Allows fast joins and simple queries.

Example of a star schema:(sample-chocolate-sales-data-all)

To demonstrate how a star schema works, consider a sample chocolate data where each shipments details is stored in a fact table and is analyzed through sorrounding dimension tables.

Shipments fact table contains numeric measures such as amount, boxes along with foreign keys to each dimension such as product id, geographical id and sales person id.
Sorrounding dimension tables provide descriptive attributes that allow the facts to be examined from different perspectives.
Locations dimensions describes the shipment location and product dimensions describes the product being shipped.
Calendar dimension shows the date in years, months the product was shipped.

Advantages of a Star schema

Easier to perform simple queries.
Simplified business reporting logically.

Disadvantages of a Star schema

Denormalized data can introduce duplicates.
Not as adaptable as normalized models when analysis needs change frequently.
Weak support for many to many relationships.

Snowflake Schema

Is data model technique where dimension tables are normalized into multiple related sub-tables.
The snowflake schema applies only to dimension tables, not the fact table.
Figure shows the snowflake schema.

Dimension tables are normalized into multiple related tables, creating a hierarchial structure.
Fact table is still located at the center of the schema, surrounded by dimension tables.
Each dimension table is further broken down into multiple related tables. Example: A customer dimension may contain a cityID that links to a separate city dimension tables storing city, state, country.

Features of a Snowflake Schema

Uses normalized tables to reduce redundancy and improve consistency.
Are built around a central fact table with connected dimension tables.
Dimensions can be split into multiple levels, allowing detailed analysis.
Requires more joins which can slow performance on large datasets. -Scales well for large data, but its complexity makes it harder to manage.

Example of Snowflake schema.

Employee diemnsion includes atrributes like employeeID, Name, DepartmentID, Region, and Territory.DepartmentID links to the department table, which holds department details like Nmae and location.

Advantages

Improves data integrity through normalization.
Reduces redundancy and storage usage.
Supports detailed hierarchical drill-down.

Disadvantages

Increased schema complexity.
More joins, leading to slower query performance.
Normalization may offer minimal storage savings compared to the entire warehouse.
Not recommended unless the hierarchy is essential and widely used in queries.

Difference Between Snowflake and Star Schema

Feature             Star schema      Snowflake schema
Dimension structure- Denormalized     - Normalized
Query performance -Faster(few joins) -Slower(more joins) 
Storage requirement- Higher          - Lower
Complexity         - Simple          - More complex
Use case - Simple analytics      - Complex hierarchies.

What are Table Relationships?

A relationship described between two or more tables via a common attribute is termed a table relationship. It is very crucial as it enables users to access data from two separate tables with ease.

Types of Table Relationships

There are four types of table relationships

One-to-One (1:1): One row in a table that can only link to one row in another table.

One-to-Many (1:*): One row matching to many rows in another table.

Many-to-One (*:1): Many rows in a table, matching one unique row in another table.

Many-to-Many (:): Multiple rows that can link into multiple rows in another table.

Cross filter direction

Cross filter considers the columns from both the tables that join them together and allows the user to tell the direction of filtering allowed.

Single: It represents a single direction filter. In this filtering choices in connected tables work on the table where values are being aggregated.
Both: It represents a bi-directional filter. In this, for filtering both tables connected are considered as one single table.

We need to identify key fields for Relationships

A table relationship works by matching key fields.

They can be fields with the same name in both fact and dimension tables.
Mostly we use the primary key of one table as a foreign key in another table and helps in establishing a relationship between two tables. Example: Table below shows primary keys like GeographicalID in the location table.

Impact of table relationships in powerBI

Incorrectly defined table relationships can lead to performance issues, such as slow query execution.
Complex relationships with large datasets may result in increased memory.

How to optimize table relationships

Avoid unnecessary relationships.
Keep the number of relationships to a minimum.
Use Bi-directional filtering only when necessary.
High cardinality relationships can impact performance.

Merged Tables in PowerQuery

This merges data from two or more tables into single table by using PowerQuery.
It's based on common columns between tables.

Importance of merging tables.

Enables user to perform analysis, reporting, and visualization on combined datasets without the need for complex joins/relationships between tables.

Merging Tables using Merge queries command

First load the data sets into powerbi as shown below:

Click on Transform data -> click on merge queries command

Select the left table for merge and next right table for merge.

Select type of join you want perform, the join operations are used to join the tables.

Types Of Joins

Left outer join:- Merges all the rows from the left table and matching rows from right table.
Right outer join:- Merges all the rows from the right table and matching rows from left table.
Full outer join:- Merges all the rows from the both tables.
Inner join:- Merges only same rows from the same tables.
Left anti:- Merges only rows from left table.
Right anti:- Merges only rows from right table.

Fuzzy Matching

Fuzzy matching is a technique used to identify and match similar strings or text values within a dataset by measuring their similarity based on various algorithms and criteria.

Impact of Merged tables on performance in powerbi

Tables can impact performance during data refresh.
Large merged tables can increase data model size and memory usage thus affecting performance.

How to make Merge queries work:

Limit the number of transformations applied to merged tables.
Apply filters and aggregations to reduce the number of rows and columns in merged tables before loading into data model.

CONCLUSION

For most business intelligence work, I would consider a star schema with one-to-many relationships, single-direction filtering, and a dedicated Date dimension.

Star schema is easier to perform simple queries in DAX and has simplified business reporting logically compared to a Snowflake schema which offers complex operations.
One to manyrelationships because it keeps filter propagation predictable: dimensions filter facts, not the other way around.
Date table which keeps time reporting possible for time series data analysis and reporting.

Jumia Product Performance Dashboard: Analyzing Pricing, Discounts, and Customer Reviews

Philip Saidi — Sun, 06 Sep 2026 13:35:06 +0000

Introduction

The project introduces a Jumia dataset in csv file.
Jumia is an ecommerce powerhouse operating accross Africa.
The goal of this project is to create an interactive Excel dashboard that provides insights into the
performance of products listed on Jumia

Overview

The dataset contains information about products listed on Jumia with the following columns:
• Product: Name of the product.
• Current Price: The current selling price of the product (in KSh).
• Old Price: The original price before discount (in KSh).
• Discount: The percentage discount offered on the product.
• Review: The number of customer reviews received by the product.
• Rating: The average customer rating of the product (out of 5).

Objectives of the objectives

Identify errors in the dataset
Perform data cleaning
Perform analysis for trends
Visualize your results using pivot charts
Use slicers to interact with the indicators
Create a dashboard with all the KPIs

Data Cleaning

I copied and named the cleaning sheet cleaned.
Autofitted column width to accomodate the long product names by
CTRL + A ->Home -> Format -> Autofit column width -> OK
Applied a filter for all the columns by Select First Header row -> Home -> Sort and Filter -> Filter-> Apply filter -> OK
Changed the header row background color Select First Header row -> Home -> Fill color -> Select the desired color -> OK to differenciate from the main data.

Data Errors Identified

Duplicates
The current and old price are in text format
Rating is mispelled and in text format
Review column is in negative number
Price in a row is in range of two numbers

Removing duplicates

Select the entire data -> Go to data tab -> Remove duplicates -> Choose all columns -> OK

Change current price and old price to currency format.

Select the current/old price column -> Use Find and replace -> First Replace Ksh to Blank -> OK -> Select price column -> Home -> Number -> Choose currency on the dropdown menu -> choose Ksh on the currency symbol -> select 1 decimal place -OK

Change Rating to number format

I used Find and replace
Select Rating column -> Home tab -> Find and select -> Replace or CTRL + H -> example 2.0 out 5 with 2, 2.5 out of 5 with 2.5
Changed the name by renaming the Ratingd to Ratings.

Change Review to a positive number

Select the Review column
CTRL + H for find and replace-> Replace - with blank-> OK

Price in a row is in range of two numbers

I calculated the average of the two numbers in range by
=AVERAGE(2200,3200)

Data Enrichment

The project required we create 3 columns

Price category

High price greater ksh 3000
Medium price greater or equal ksh 2000
Low price less than 1000 I used a nested IF statement in the inserted column and renamed it price category. =IF(B2>3000,"High Price",IF(B2>=2000,"Medium Price","Low Price"))

Discount Category

High discount greater than 40%
Medium discount less than or equal 40%
Low discount less than 20% I use a nested ID statement and renamed the column discount category =IF([@Discount]="","Missing",IF([@Discount]<20%,"Low Discount",IF([@Discount]<=40%,"Medium Discount","High Discount"))

Rating Category

Excellent greater than 5
Average less than or equal 4.5
Poor less than 3 I used a nested IF statement here too and renamed Rating category. =IF([@Ratings]="","Missing",IF([@Ratings]<3,"Poor",IF([@Ratings]<=4.5,"Average","Excellent")))

Discount Amount

I used subtraction from old price to current price.
Renamed the column Discount amount.
=[@[Old price]]-[@[Current price]]

DATA ANALYSIS USING PIVOT TABLES AND CHARTS.

I created pivot tables to help me with data analysis and data visualization.
From the data analysis

- Click anywhere in the data table
- Go to insert table 
- Choose pivot table
- Choose from existing table and a new worksheet
- OK

I created a pie chart and a bar chart showing discount by reviews, ratings category by average reviews, price and rating using the pivot tables created.

Excel Dashboard

I used these slicers to connect all the pivot tables/charts.
Created a pivot table -> Insert -> Slicer -> Choose columns (Rating category, Price category, Discount category) -> OK

From the Slicers and pivot tables/chart I created a Dashboard displaying all the charts, slicers for interaction and Key performance Indicators shown below

CONCLUSION

From the dataset given ratings,reviews,prices and large discounts do not guarantee for huge sales.Jumia should stock product that makes a different in quality this will attract customers.

Here is the link to the project files for collaboration:GITHUB

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

Philip Saidi — Sat, 29 Aug 2026 20:13:33 +0000

Overview

What is data and data analysis

Data is information that helps people or business make decisions.

Types of data

Text (Abcde)
Numbers (12348.0)
Dates (12/3/1993, 12-5-2026)
Currency ($45.5, KES200.5)

Data analysis

The process of making data to a useful information using different tools such as Excel.
It entails processes like

Data formatting
Data validation
Data cleaning
Data analysis
Data visualization

What is Excel

Excel is a spreadsheet application used to create, view, edit , clean, analyze and visualize data.

Excel use cases

Create data or record data for reference.
To record data /data entry.
Data analysis and give insights.
Business intelligence on light weight.
Project management.
Cleaning data.
Create beautiful visual representation of data.

Creating data with Excel for Students grades

Open excel application in your pc
Open Blank workbook name it Students grades
Save by clicking File then Save as.
Choose a location to save and name the file
Click on cell A1 and name it FIRSTNAME
Press TAB to move to the next cell
Name it LASTNAME, next QUIZ 1, QUIZ 2, QUIZ 3, and AVERAGE as shown below
Autofit the columns by Clicking Home tab -> Format -> Autofit column -> OK.
Enter the data as shown in the screenshot and save.

Introduction to Excel Cleaning data.

The first step to data cleaning:

Sample data errors

Wrong date format
Incorrect data types
Blank cells/ missing values
Duplicates
Wrong format of data types
Missing values
Incorrect information

Formatting data

You can format numbers, texts, rows and columns

Number formatting

Numbers always align to the right of the cell.
Follow these steps to format numbers to desired data type

Select the column you want to format
Click Home tab/ribbon and find Number group
A drop down menu will occur, choose which number format you want and click OK.

Text Formatting

Text are a string or words like John, Lorry, HR.
Text are aligned to the left of the cell
Follow these steps to format texts

Select the text you want
Click Home tab
Choose to color or make it bold in the font section
You can change the background color and click OK.
Save your work.

Row and Column formatting.

Follow these steps

Select an entire row/column example ROW 1
Select Home tab and Click B to make it Bold.

Data Cleaning Using Excel

Data cleaning is the art of formatting and correcting data so that it can be correct and accurate for use in data analysis.

For this We will use a random data set from the internet called kenya-school-grades-dirty.
Start with removing duplicates

Select all data
Go to data tab
Click remove duplicates.
Select the column you want
Click OK.

In the data set names are not in proper case and have spacing.
We rectify this by:

Selecting the columns Student Name.
Right click and insert a new column
In the empty column write the formula =TRM(PROPER(B2:B221)) and Enter.
This removes any whitespace and capitalize the first letter.
Insert another cell and copy the corrected student name and delete the previous one then save.

In the gender it has more than one identifier that is M, f, Female, FEMALE, MALE.
We can correct this by removing the repeating gender by find and replace method.

Select the gender column.
Select Home tab
Find and select -> Replace
A dialogue box will pop up
Type what you what to find and replace all it with the correct gender.

Date format
In the last column the date format is inconsistent

Select column you want to format(Date recorded)
Go to Home tab
Then Click Number group
Scroll to date
Select the date format that is correct.

Data validation

Restricting users on what is allowed to be entered in a given column.
Follow these steps:

Select column you want to validate.
Click on data ribbon
Data validation group
Choose list
Type the data that you want to be allowed in that column
Click OK.

CONCLUSION

Excel is important in both data analysis, cleaning and visualization.

# MY FIRST GITHUB PROJECT: FROM A LOCAL FOLDER TO GITHUB USING GIT AND SSH.

Philip Saidi — Sat, 22 Aug 2026 08:37:21 +0000

What is git and github

Git is a command line tool used to create project folders and files that can be linked to your github account repositories.
Github is a website where you put all your projects folders and files to enable collaboration with you team or anyone who can contribute to that project.
Repository is a project folder that contains all of your project files.

Creating A Folder And Files

We first create a folder in your desktop using gitbash.
I followed these steps.

open gitbash
First know the current directory, if you in the Root directory tab change directory to Desktop.
Make directory with the name of the Folder.
Change the directory to the current Folder created.
Make a file in the folder you created, like app.py or README.md.
Write into the README.md and save.
Display the contents of the file.
Return to the previuos folder y_ou created. Use the code below for reference

pwd
cd Desktop
mkdir Folder name like Kenya-Health-Records
cd Kenya-Health-Records
touch File name with an extension like app.py or README.md
echo "# DATA CLEANING USING EXCEL" >README.md
cat README.md 
cd . .

Pushing Repo To Github Using Gitbash

While still in gitbash current folder I created.
I did this commands on the CLI

Run git init to initialize a git repo
Run git status to check I repo is ready 
Run git add . 
Run git commit -m "write your commit message"
Run git log --oneline to check the commit message in a oneline
Run git branch to confirm if you are in the main branch
Run git branch -M main to change master to main branch

Open github account, login and follow these steps.

Create a repository and give it a name and description.
Go to your quick set ip and click SSH.
Copy the url command
Open gitbash and make sure you are in the current folder you want to push to github._ Follow this commands below

Run git remote add origin "paste the url command from github"
Run git remote -v
Run git push - u origin main 
You will be prompted for your paraphrase you created while setting git
You will see a successful message that your folder successful pushed to github

Now go to your github account and refresh.You will see the files and folder you created in the repository you created on github.
Give yourself a clap