DEV Community: Bradley Okello

Data Modelling, Relationships and Joins - PowerBI

Bradley Okello — Sat, 26 Sep 2026 11:17:44 +0000

Introduction

A Power BI report is only as good as the data model behind it. DAX calculations, visual interactions, refresh performance, and long-term maintainability all depend on how tables are structured, related, and filtered. This article explains data modelling concepts in Power BI, compares flat, star, and snowflake schemas, explains fact and dimension tables, covers relationship cardinalities and filter direction, demonstrates Power Query joins, and recommends a practical model design for real-world BI projects.

1. Data Modelling in Power BI

Data modelling in Power BI means designing the tables, columns, relationships, hierarchies, and measures that form the analytical layer of a report. It is not simply loading data and dragging fields onto a canvas. A good model decides:

Which tables should exist.
What grain each table represents.
Which columns are keys, attributes, or measures.
How tables relate to one another.
How filters should flow.
Which calculations belong in DAX versus Power Query.

A well-designed model matters because it directly affects:

Reporting - Users can slice data intuitively and consistently.
Analytics - Business questions are answered with correct aggregations.
DAX - Measures are simpler and less error-prone.
Performance - Fewer relationships, smaller tables, and efficient scans improve speed.
Scalability - New facts and dimensions can be added without redesign. Maintainability Logic is centralised and easier to audit.

1.1 Flat Table

Definition

A flat table stores all data in one wide table. Each row contains both descriptive attributes and numeric measures.

Structure

All columns - customer name, product name, date, category, quantity, sales amount - live in the same table.

FlatSales

OrderDate	CustomerName	ProductName	Category	Quantity	SalesAmount
2026-01-02	Alice	Bike	Bikes	1	500
2026-01-02	Alice	Helmet	Accessory	2	80
2026-01-03	Ben	Bike	Bikes	1	700

Advantages

Simple to create and understand.
Fast for very small datasets or quick prototypes.
No relationship design required.

Disadvantages

Heavy redundancy: customer and product details repeat on every row.
Large file size and slower refresh as data grows.
Difficult DAX: distinct counts and semi-additive measures become complex.
Poor scalability and maintainability.
Filtering is limited because there are no separate dimension tables.

When Appropriate

Very small personal reports.
One-off analysis where the data will not grow.
Initial data exploration before building a proper model.

Power BI Implications

Power BI can query a flat table, but the model will not take advantage of columnar compression as effectively as a star schema. Report performance and DAX simplicity usually suffer.

1.2 Star Schema

Definition

A star schema consists of one or more central fact tables surrounded by dimension tables. It is the most common and recommended schema for Power BI.

Structure

The fact table stores business events and numeric measures. Dimension tables store descriptive attributes and connect to the fact table through keys.

Sales Schema Diagram

erDiagram
    DimDate ||--o{ FactSales : "1:*"
    DimCustomer ||--o{ FactSales : "1:*"
    DimProduct ||--o{ FactSales : "1:*"
    DimLocation ||--o{ FactSales : "1:*"

Example tables:

FactSales

DateKey	CustomerID	ProductID	LocationID	Quantity	SalesAmount
20260102	1	100	10	1	500
20260102	1	101	10	2	80
20260103	2	100	20	1	700

DimCustomer

CustomerID	CustomerName	City
1	Alice	Nairobi
2	Ben	Mombasa

DimProduct

ProductID	ProductName	Category
100	Bike	Bikes
101	Helmet	Accessory

Advantages

Excellent Power BI performance due to columnar compression and simple relationships.
Simple DAX: measures aggregate the fact table and filters flow from dimensions.
Intuitive for report authors and business users.
Scalable: new facts and dimensions can be added with minimal disruption.
Supports conformed dimensions shared across multiple fact tables.

Disadvantages

Requires ETL work to separate facts and dimensions.
Some redundancy remains in dimension tables, which is usually acceptable.
Poorly designed dimensions can still cause performance issues.

When Appropriate

Most business intelligence projects.
Any model with multiple business questions, multiple facts, or growth expectations.
Models using DAX heavily.

Power BI Implications

Power BI’s VertiPaq engine is optimised for star schemas. Relationships are simple, filter propagation is predictable, and DAX is easier to write and tune.

1.3 Snowflake Schema

Definition

A snowflake schema is a star schema where dimension tables are normalised into additional related tables.

Structure

Instead of one wide DimProduct, product category and department are stored in separate tables.

Product Hierarchy Snowflake Schema

erDiagram
    FactSales }o--|| DimProduct : "*:1"
    DimProduct }o--|| DimCategory : "*:1"
    DimCategory }o--|| DimDepartment : "*:1"

Example:

DimProduct

ProductID	ProductName	CategoryID
100	Bike	1
101	Helmet	2

DimCategory

CategoryID	CategoryName	DepartmentID
1	Bikes	10
2	Accessories	20

DimDepartment

DepartmentID	DepartmentName
10	Outdoor
20	Safety

Advantages

Reduces redundancy in dimension data.
Can simplify governance when dimensions are very large and shared.
Useful when source systems are already normalised.

Disadvantages

More tables and relationships increase model complexity.
DAX may require more relationship navigation.
Performance can degrade if too many small joins are introduced.
Report authors may find it harder to locate fields.

When Appropriate

Very large dimensions where normalisation saves significant space.
Enterprise models with shared conformed dimensions.
Source systems that already provide normalised dimension tables.

Power BI Implications

Power BI can handle snowflake schemas, but the star schema is generally preferred. Snowflake dimensions should be used selectively, and the reporting layer should still feel like a star.

2. Fact Tables and Dimension Tables

Fact Tables

Fact tables store numeric business events and foreign keys. They are usually tall and narrow.

Typical fact table columns:

Foreign keys to dimensions: DateKey, CustomerID, ProductID, LocationID.
Degenerate dimensions: OrderNumber, InvoiceNumber.
Measures: Quantity, SalesAmount, CostAmount, DiscountAmount.

Examples:

FactSales
FactOrders
FactTransactions
FactInventory

Dimension Tables

Dimension tables store descriptive attributes used for slicing and grouping. They are usually shorter and wider.

Typical dimension table columns:

Primary key: CustomerID, ProductID, DateKey.
Descriptive attributes: CustomerName, City, Country, ProductName, Category, Year, Month.

Examples:

DimCustomer
DimProduct
DimDate
DimLocation
DimEmployee

Measures vs Attributes

A measure is a numeric value that can be aggregated, such as SalesAmount. An attribute is a descriptive field used for filtering or grouping, such as ProductCategory or CustomerCity.

Grain of a Fact Table

The grain defines what one row in the fact table represents. For example:

FactSales grain: one row per product per sales order line.

If a row represents an entire order, then quantity and sales amount cannot be analysed by product. Grain must be defined before modelling because it determines which dimensions can relate to the fact table.

Practical Star Schema Example

A retail company wants to analyse sales by date, customer, product, and location.

Sales Star Schema Diagram

erDiagram
    DimCustomer ||--o{ FactSales : "connects to"
    DimProduct ||--o{ FactSales : "connects to"
    DimDate ||--o{ FactSales : "connects to"
    DimLocation ||--o{ FactSales : "connects to"

FactSales:

DateKey
CustomerID
ProductID
LocationID
Quantity
SalesAmount

DimDate:

DateKey
Date
Year
Quarter
Month

DimCustomer:

CustomerID
CustomerName
City
Country

DimProduct:

ProductID
ProductName
Category
Subcategory

DimLocation:

LocationID
StoreName
Region
Country

This design allows a user to slice SalesAmount by Year, Customer City, Product Category, or Store Region with simple, fast DAX.

3. Relationships in Power BI

A relationship in Power BI connects two tables using common columns, usually a primary key in one table and a foreign key in another. Relationships are necessary because data is distributed across multiple tables. They allow filters to propagate and enable DAX to aggregate related data correctly.

Relationship Cardinalities

One-to-Many (1:*)

This is the most common relationship.

How it works:

One row in the dimension table relates to many rows in the fact table.
The dimension side contains unique values.
The fact side can contain repeated values.

Example:

DimCustomer[CustomerID] 1 → * FactSales[CustomerID]
CustomerID is unique in DimCustomer but appears many times in FactSales.

Customer to Sales Relationship

erDiagram
    DimCustomer ||--o{ FactSales : "1:*"

    DimCustomer {
        int CustomerID PK
    }
    FactSales {
        int CustomerID FK
    }

When to use:

Almost always between dimensions and facts.
It is the foundation of a star schema.

When not to use:

Avoid if the “one” side is not unique.
Avoid if the relationship creates ambiguous filter paths.

One-to-One (1:1)

How it works:

One row in each table relates to exactly one row in the other.
Both sides contain unique values.

Example:

DimEmployee[EmployeeID] 1 ↔ 1 DimEmployeeDetails[EmployeeID]

Employee 1-to-1 Relationship

erDiagram
    DimEmployee ||--|| DimEmployeeDetails : "1:1"

When to use:

Separating optional or sensitive attributes.
Merging two tables with the same grain when a relationship is preferred over a merge.

When not to use:

If the tables can be merged cleanly.
If it adds complexity without analytical benefit.

Many-to-Many (:)

How it works:

Many rows in one table relate to many rows in another.
Usually implemented through a bridge table.

Example:

Students and Courses. A student takes many courses, and a course has many students.

Student-Course Many-to-Many Relationship

erDiagram
    DimStudent ||--o{ BridgeEnrollment : "1:*"
    DimCourse ||--o{ BridgeEnrollment : "1:*"

When to use:

True many-to-many business relationships.
Bridge tables with measures or weighting factors.

When not to use:

Avoid direct many-to-many relationships unless necessary.
They can produce unexpected results and complicated DAX.

Key Concepts

Primary Key - Unique identifier in a dimension table, e.g. DimCustomer[CustomerID].
Foreign Key -Column in a fact table that references a dimension, e.g. FactSales[CustomerID].
Unique Values - Required on the “one” side of a 1:* relationship.
Cardinality - The number of rows that can relate: 1:*, 1:1, :.
Referential Integrity - Every foreign key value should exist in the related primary key table. If not, Power BI may show blank rows.
Active Relationship - The default relationship used for filter propagation and DAX.
Inactive Relationship - A relationship that exists but is not active by default. Used with USERELATIONSHIP in DAX.

Example: CustomerID contains unique values in DimCustomer because each customer appears once. It appears multiple times in FactSales because one customer can place many orders. This is a classic 1:* relationship.

Active and inactive relationships are common with dates. FactSales may have OrderDateKey, ShipDateKey, and DeliveryDateKey. Only one can be active at a time. A measure can use USERELATIONSHIP to activate another date relationship for specific calculations.

4. Filter Direction

Filter direction determines how filters move between related tables.

Single-Direction Filtering

Filters flow from the “one” side to the “many” side.

Example:

DimProduct[Category] filters FactSales.

If a user selects Category = Bikes, Power BI filters DimProduct to Bikes, and that filter propagates to FactSales, showing only bike sales.

Product to Sales Relationship (Single Filter Direction)

erDiagram
    DimProduct ||--o{ FactSales : "Filters ──>"

Single-direction filtering is the default and recommended approach for most star schemas.

Both / Bidirectional Filtering

Filters can flow in both directions.

Example:

DimProduct filters FactSales, and FactSales can filter DimProduct.

Bidirectional filtering is sometimes used for:

Many-to-many bridge tables.
Dimension-to-dimension filtering.
Certain DAX patterns.

However, it should be used carefully because it can cause:

Ambiguous filter paths.
Unexpected results.
Slower performance.
Circular relationship issues.
Unnecessary model complexity.

Example of ambiguity: If DimCustomer and DimProduct both filter FactSales, and FactSales filters both back, Power BI may not know which path should influence the other. This can produce incorrect totals or require complex DAX overrides.

Best practice: Use single-direction filtering by default. Use bidirectional only when a specific analytical requirement cannot be met otherwise, and document it clearly.

5. Joins in Power Query

A join combines rows from two tables based on matching column values. In Power BI, joins are performed in Power Query using Merge Queries.

Example tables:

Customers

CustID	Name	City
1	Alice	Nairobi
2	Ben	Mombasa
3	Carol	Kisumu
4	David	Eldoret

Orders

OrderID	CustID	Amount
101	1	500
102	1	300
103	2	700
104	4	200
105	5	900

Left Outer Join

How it works:

Returns all rows from the left table.
Returns matching rows from the right table.
Unmatched right columns become null.

Records retained:

All Customers.
Matching Orders.

Expected output:

Inner / Left Join Result

CustID	Name	OrderID	Amount
1	Alice	101	500
1	Alice	102	300
2	Ben	103	700
3	Carol	null	null
4	David	104	200

Order 105 is excluded because there is no matching customer.

Right Outer Join

How it works:

Returns all rows from the right table.
Returns matching rows from the left table.
Unmatched left columns become null.

Records retained:

All Orders.
Matching Customers.

Expected output:

Right Join Result

CustID	Name	OrderID	Amount
1	Alice	101	500
1	Alice	102	300
2	Ben	103	700
4	David	104	200
null	null	105	900

Carol is excluded because she has no order.

Full Outer Join

How it works:

Returns all rows from both tables.
Matches where possible.
Unmatched columns become null.

Records retained:

All Customers and all Orders.

Expected output:

Full Outer Join Result

CustID	Name	OrderID	Amount
1	Alice	101	500
1	Alice	102	300
2	Ben	103	700
3	Carol	null	null
4	David	104	200
null	null	105	900

Inner Join

How it works:

Returns only rows that match in both tables.

Records retained:

Only matching Customers and Orders.

Expected output:

Inner Join Result

CustID	Name	OrderID	Amount
1	Alice	101	500
1	Alice	102	300
2	Ben	103	700
4	David	104	200

Carol and Order 105 are excluded.

Left Anti Join

How it works:

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

Records retained:

Customers without Orders.

Expected output:

Unmatched Customers (Left Anti Join Result)

CustID	Name	City
3	Carol	Kisumu

Right Anti Join

How it works:

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

Records retained:

Orders without Customers.

Expected output:

Orphan Orders (Right Anti Join Result)

OrderID	CustID	Amount
105	5	900

Power Query Illustration

1. Left Outer Join

Keeps all customers; unmatched customers get null order details.

ID	Name	OrderID	CustID
1	Alice	101	1
1	Alice	102	1
2	Ben	103	2
3	Carol	null	null
4	David	104	4

2. Right Outer Join

Keeps all orders; orphan orders get null customer details.

ID	Name	OrderID	CustID
1	Alice	101	1
1	Alice	102	1
2	Ben	103	2
4	David	104	4
null	null	105	5

3. Full Outer Join

Keeps everything from both tables, filling in nulls where there are no matches.

ID	Name	OrderID	CustID
1	Alice	101	1
1	Alice	102	1
2	Ben	103	2
3	Carol	null	null
4	David	104	4
null	null	105	5

4. Inner Join

Only returns exact matches between both tables.

ID	Name	OrderID	CustID
1	Alice	101	1
1	Alice	102	1
2	Ben	103	2
4	David	104	4

5. Left Anti Join

Isolates customers who have never placed an order.

ID	Name
3	Carol

6. Right Anti Join

Isolates orphan orders that don't match any customer.

OrderID	CustID
105	5

In Power Query: Home → Merge Queries → select matching columns → choose join kind → expand the new table column.

6. Power Query Joins vs Power BI Relationships

Aspect	Power Query Merge	Power BI Relationship
Stage	Before data is loaded	After data is loaded, in the model
Effect	Physically combines columns/rows into a new query	Creates metadata linking tables
Grain	Can change or duplicate rows	Preserves table grain
Storage	Creates wider tables	Keeps tables separate
Filtering	Not a model filter; it is a transformation	Enables filter propagation
DAX	Not directly used by DAX relationships	Essential for DAX and visuals
Use case	Cleaning, enriching, lookup, creating bridge tables	Analytical modelling and slicing
Risk	Excessive merging creates wide, redundant models	Excessive relationships can create ambiguity

A Power Query merge physically combines columns from tables. A Power BI relationship does not combine tables; it tells the model how tables are related.

You would choose a merge when:

You need to add lookup columns during ETL.
You need to create a bridge table.
You need to flatten a small dimension for a specific report.
You need to perform anti-join validation.

You would choose a relationship when:

You want filter propagation.
You want to keep fact and dimension tables separate.
You want simple DAX and good performance.
You want a reusable star schema.

Excessive merging can destroy a good model. It creates wide tables, duplicates data, increases refresh time, and makes DAX harder. Keeping fact and dimension tables separate is usually preferable because it supports a clean star schema, improves compression, and simplifies reporting.

7. Recommended Power BI Model

For a typical business intelligence project, I recommend a star schema with conformed dimensions and single-direction relationships.

Sales Star Schema Diagram

erDiagram
    DimCustomer ||--o{ FactSales : "filters"
    DimProduct ||--o{ FactSales : "filters"
    DimDate ||--o{ FactSales : "filters"
    DimLocation ||--o{ FactSales : "filters"

Relationships:

DimDate[DateKey] 1 → * FactSales[DateKey]
DimCustomer[CustomerID] 1 → * FactSales[CustomerID]
DimProduct[ProductID] 1 → * FactSales[ProductID]
DimLocation[LocationID] 1 → * FactSales[LocationID]

Filter direction:

Single-direction from each dimension to the fact table.
Avoid bidirectional filtering unless a specific many-to-many requirement demands it.

Additional practices:

Mark DimDate as a date table.
Hide primary and foreign keys from report view.
Use explicit measures instead of implicit aggregations.
Create bridge tables for many-to-many relationships.
Use inactive relationships and USERELATIONSHIP for role-playing dimensions such as Order Date and Ship Date.
Use Power Query merges in staging queries, not as a replacement for the model.

Justification

Factor	Star Schema Benefit
Query and report performance	Columnar compression and simple relationships scan faster.
DAX simplicity	Measures aggregate a single fact table with predictable filters.
Model readability	Fact and dimension roles are obvious.
Scalability	New facts and dimensions can be added easily.
Data redundancy	Dimension attributes are stored once per dimension.
Maintainability	ETL and model logic are easier to audit.
Ease of reporting	Users see familiar business entities.
Filter propagation	Single-direction filters are predictable.
Model complexity	Fewer relationships than snowflake, simpler than flat.

A flat table is acceptable only for very small or temporary solutions. A snowflake schema may be useful when dimensions are extremely large or already normalised, but it should be used selectively. The star schema remains the best balance of performance, simplicity, and scalability for Power BI.

Conclusion

Data modelling, relationships, and joins are not isolated technical topics. They are the foundation of a reliable Power BI solution. A flat table is easy but does not scale. A star schema is the standard because it balances performance, DAX simplicity, and maintainability. A snowflake schema can reduce redundancy but adds complexity. Fact tables store measurable events at a defined grain, while dimension tables provide descriptive context. Relationships connect these tables, with 1:* single-direction filtering being the default best practice. Power Query joins are transformation tools, while Power BI relationships are analytical tools. By choosing a star schema, keeping facts and dimensions separate, and using relationships carefully, you create a model that is faster, clearer, and easier to maintain.

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

Bradley Okello — Sat, 05 Sep 2026 14:01:29 +0000

Introduction

In one of my previous articles: Getting Started with Excel for Data Analytics: From Basics to Data Cleaning I gave the basics to getting started with excel. This article walks through an end-to-end project: building an interactive Excel dashboard to analyze product performance on Jumia. By analyzing product data, we can understand how discounts, customer reviews, and ratings influence online shopping trends.

The final deliverable is a professional dashboard that turns raw data into actionable pricing, promotion, and customer-engagement insights, all within Microsoft Excel.

1.Project Objective

The primary goal is to build a professional, interactive Excel dashboard that transforms raw Jumia product data into useful business intelligence. By the end of this project, stakeholders should be able to answer five core questions:

Does a larger discount drive more customer reviews (engagement)?
Are highly rated products associated with stronger engagement?
How do price and rating move together?
Which products are the best performers based on ratings and reviews?
Which products may need a different pricing or marketing strategy?

This is not merely a chart-building exercise; it is an analysis project. Every cleaning decision must be traceable, calculations validated, and recommendations grounded in evidence from the workbook.

2. Dataset and Business Questions

Dataset Overview

The dataset (Excel_jumia_dataset.xlsx) contains 115 product rows with the following columns:

Column	Description
`Product`	Product name (text)
`Current price`	Selling price in KSh; may include ranges (e.g., `"KSh 1,620 - KSh 1,980"`)
`old price`	Original price; may also include ranges
`Discount`	Numeric discount fraction (e.g., `0.38`)
`Review`	Number of reviews (stored as negative integers; blanks possible)
`Ratingd`	Star rating as text (e.g., `"4.5 out of 5"`); blanks possible

Business Questions

The dashboard is designed to answer the five questions above, each addressing a different aspect of product performance:

Discount vs. Reviews: Are price cuts effective at driving customer engagement?
Rating vs. Reviews: Do better products naturally attract more attention?
Price vs. Rating: Is there a relationship between what customers pay and how they perceive quality?
Top/Bottom Performers: Which products deserve promotion, and which need improvement?
Strategy Recommendations: Which products require pricing or marketing adjustments?

3. Initial Data-Quality Audit

Before any analysis, a thorough data-quality audit is essential. As one guide notes, "Most failures start before design: messy source data, unclear metrics, and too many visuals on one sheet".

Identified Issues

Issue	Details
Price formats	Current price and old price contain text (`"KSh 950"`) and sometimes ranges (`"KSh 1,620 - KSh 1,980"`)
Review column	All non-blank entries are negative integers (e.g., `-2`, `-55`), which is unconventional
Rating column	Stored as text (`"4.5 out of 5"`) rather than numeric
Missing values	Several rows lack reviews or ratings entirely
Duplicate rows	Some products appear multiple times (e.g., "Balloon Insert" appears at least twice)

Summary Statistics (Pre-Cleaning)

Total products: 115
Products with missing reviews: ~55 rows
Products with missing ratings: ~55 rows
Price ranges: KSh 38 to KSh 3,750+
Discount range: 0.01 to 0.64

The dataset is messy but analyzable. A perfect sample for demonstrating Excel's data-cleaning capabilities.

4. Cleaning and Preparation Decisions

Tools Used

Power Query (Get & Transform) for reproducible, traceable data cleaning
Excel formulas for calculated columns and validation

Cleaning Steps

Before everything else open a new worksheet and name it cleaned_data. Copy the top raw data (headers + 116 rows of fields) and paste it into cell A1 of the new sheet created.

4.1 Price Columns

Issue: Text with currency symbols and commas; sometimes ranges.

Solution: Extract the minimum price for Current price and the maximum price for old price.

// Power Query: Extract first number from text
= Text.BeforeDelimiter([Current price], " - ")
// Then remove "KSh " and commas, convert to number

4.2 Review Column

Issue: Negative integers or blanks.

Solution: Convert to positive integers using =**ABS**(Review). Treat blanks as 0 (no reviews).

4.3 Rating Column

Issue: Text format *4.5 out of 5*.

Solution: Extract the numeric part.

m // Power Query: Extract rating = Number.From(Text.BeforeDelimiter([Ratingd], * *))

4.4 Missing Values

Reviews: Fill blanks with 0
Ratings: Leave as null (exclude from rating-based analyses)

4.5 Duplicates

Remove duplicate rows using Power Query's Remove Duplicates function.

Cleaning Summary

Before	After
Text prices with KSh	Numeric prices (KSh)
Negative reviews	Positive integers
`4.5 out of 5`	`4.5` (numeric)
Blank reviews	`0`
Duplicate rows	Removed

5. Excel Formulas and Enrichment Fields

Key Formulas Used

Extract Numeric Price from Text

excel =**VALUE**(**SUBSTITUTE**(**LEFT**(A2, **FIND**(* *, A2)-1), *,*, ""))

Extract Rating

excel =**VALUE**(**LEFT**(Ratingd, **FIND**(* *, Ratingd)-1))

Convert Negative Reviews

excel =**ABS**(Review)

Validate Discount

excel =(old_price - current_price) / old_price

Enrichment Fields Added

To enable deeper analysis, I added several calculated columns:

Enrichment Field	Formula / Logic	Purpose
`Price_Group`	`IF(current < 500, Low, IF(current < 1500, Medium, High))`	Segment by price tier
`Discount_Group`	`IF(Discount < 0.25, Low, IF(Discount < 0.45, Medium, High))`	Segment by discount level
`Rating_Category`	`IF(Rating < 3, Poor, IF(Rating < 4, Average, Excellent))`	Segment by rating quality
`Performance_Score`	`(Rating * 10) + (Reviews / MAX(Reviews) * 10)`	Composite score for ranking
`Absolute_Discount`	`old_price - current_price`	Absolute discount amount

6. PivotTable and Analysis Workflow

PivotTable Setup

PivotTables are the backbone of Excel dashboards as they help you slice, dice, and visualize data with no formulas needed. I created PivotTables on a separate support sheet, following the best practice of keeping them isolated from the dashboard itself.

Analysis Workflow

Load Cleaned Data: Power Query loads transformed data into a worksheet table.
Create PivotTables: Build separate PivotTables for each analysis:
- Average price, discount, rating, and total reviews (KPIs)
- Discount vs. Reviews (scatter data)
- Rating vs. Reviews (scatter data)
- Price vs. Rating (scatter data)
- Top/Bottom performers (sorted tables)
Build Charts: Connect PivotCharts to each PivotTable.
Assemble Dashboard: Place charts, KPIs, and slicers on a single dashboard sheet.

Scatter Plot Setup

For correlation analysis (Discount vs. Reviews, Rating vs. Reviews, Price vs. Rating), I used:

X-axis: Independent variable (Discount, Rating, or Price)
Y-axis: Dependent variable (Reviews)
Trendline: To visualise correlation direction

7. Dashboard Design and Slicer Connections

Design Principles

Following best practices for dashboard design:

Simplicity: Avoid pie charts and 3D charts; prioritise bar, line, and scatter charts
Information hierarchy: Primary KPIs at the top, charts in the middle, filters on the left
Colour discipline: Keep colours to three or fewer
Clarity: Every chart has a clear title and purpose

Dashboard Layout

+----------------------------------------------------------+
|       Jumia Product Performance Dashboard                   |
|       Total: 112  |  Avg Discount: 36.8%  |  Avg Rating: 4.1 |
|       Total Reviews: 723                                    |
+----------------------------------------------------------+
|   [Slicers: Discount Group | Price Group | Rating Category] |
+----------------------------------------------------------+
| Discount vs Reviews  |  Rating vs Reviews  | Price vs Rating |
| (Scatter + Trend)    |  (Scatter + Trend)  | (Scatter + Trend) |
+----------------------------------------------------------+
| Top 10 by Rating     |  Top 10 by Reviews  | Bottom 10 by Perf. |
| (Bar Chart)          |  (Bar Chart)        | (Bar Chart)         |
+----------------------------------------------------------+
|   Key Insights:                                          |
| • No strong correlation between discount and reviews      |
| • High rating doesn't guarantee many reviews              |
| • Top performer: 137-Piece Cake Decorating (4.6, 55)     |
+----------------------------------------------------------+

Slicer Connections

Slicers provide interactivity, allowing users to filter the entire dashboard dynamically:

Discount Group slicer (Low / Medium / High)
Price Group slicer (Low / Medium / High)
Rating Category slicer (Poor / Average / Excellent)

All PivotTables and PivotCharts are connected to the same slicers, ensuring consistent filtering across the dashboard.

8. Key Findings

8.1 Discount vs. Reviews

Finding: There is no strong positive correlation between discount size and review count. Products with very high discounts (>50%) often have low to moderate review counts.

Product	Discount	Reviews	Rating
137-Piece Cake Decorating	24%	55	4.6
Electronic Digital Caliper	35%	49	4.6
3D Waterproof Shower Curtain	49%	44	4.6
6-in-1 Bottle Opener	64%	0	N/A

Insight: Price cuts alone don't guarantee engagement. Medium discounts (20–40%) tend to attract more reviews than very high discounts.

8.2 Rating vs. Reviews

Finding: High rating does not guarantee many reviews. Some 5-star products have only 1–2 reviews (e.g., Anti-Skid Coaster, Peacock Pillow). Conversely, lower-rated products sometimes have more reviews.

Product	Rating	Reviews
Anti-Skid Coaster	5.0	2
Peacock Pillow	5.0	2
5-PCS Cooking Pot Set	2.1	13

Insight: Engagement is driven by factors beyond rating, product category, visibility, and promotion matter more.

8.3 Price vs. Rating

Finding: No clear relationship between price and rating. Products at all price points can be highly or poorly rated.

Product	Price (KSh)	Rating
Classic Black Cat Pillow	171	5.0
LED Wall Digital Clock	2,999	4.6
VIC Wireless Vacuum	1,220	2.9

Insight: Price does not consistently predict quality perception.

8.4 Top Performers

Based on composite scoring (rating + reviews), the top performers are:

137 Piece Cake Decorating Tool (4.6, 55 reviews)
Electronic Digital Display Caliper (4.6, 49 reviews)
3D Waterproof Shower Curtain (4.6, 44 reviews)
100 Pcs Crochet Hook Set (4.7, 39 reviews)
LASA Folding Table (4.8, 5 reviews)

8.5 Underperformers

Products needing attention:

Product	Rating	Reviews	Discount
Wall-mounted Plug Fixer	2.0	1	50%
5-PCS Cooking Pot Set	2.1	13	55%
Artificial Potted Flowers	2.2	6	45%
380ML USB Blender	2.3	7	50%

Observation: Many underperformers have high discounts but poor ratings, suggesting product quality issues rather than pricing problems.

9. Business Recommendations

For Underperforming Products (Low Rating + Low Reviews)

Investigate product quality: Poor ratings despite high discounts suggest quality issues
Improve product listings: Better images, descriptions, and specifications
Consider discontinuation: If quality cannot be improved, remove the product

For High-Rated, Low-Review Products

Increase visibility: Use Jumia's advertising or promotional campaigns
Encourage reviews: Offer voucher rewards, loyalty points, or discount coupons for verified feedback
Feature in curated collections: Highlight high-rated products in category pages

For Pricing Strategy

Avoid excessive discounts: Products with >50% discounts don't show higher engagement
Optimise discount ranges: Medium discounts (20–40%) appear most effective
Shift from price-driven to value-driven strategy: Focus on product quality and customer experience rather than competing solely on price

For Overall Platform Growth

Incentivise reviews systematically: Build a review culture through rewards
Spotlight high-rated categories: Use top performers as case studies
Build trust through reviews: Transparent customer feedback drives long-term loyalty

10. Limitations and Lessons Learned

Limitations

Limitation	Impact
Missing data	Several rows lack reviews or ratings, reducing sample size
No product categories	Manual classification would enable deeper segmentation
Price extraction assumption	For ranges, we used lower bound for current and upper bound for old price
Review count ≠ engagement quality	Some products may be new; review counts don't capture sentiment
Observational analysis	Correlations do not imply causation

Lessons Learned

Data cleaning is the most critical step: It is important to clean your data because it produces a structured dataset ready for analysis.
Power Query saves time: Automating cleaning steps makes the process reproducible and traceable
Slicers transform usability: Interactive filters make dashboards far more useful than static charts
Simplicity wins: A dashboard is only useful if someone can open it, read it in under a minute, and trust the numbers
Excel remains relevant: Despite modern BI tools, Excel's flexibility and ubiquity make it an essential skill

11. Links to Resources

GitHub Repository

The complete project, including the Excel workbook, cleaning scripts, and documentation, is available on GitHub:

🔗 GitHub Repository

Dashboard File

Direct download link to the Excel dashboard:

Download Jumia_Product_Dashboard.xlsx

Conclusion

This project demonstrates how Microsoft Excel combined with Power Query, PivotTables, and slicers can transform raw e-commerce product data into a professional, interactive dashboard. The analysis reveals that discounts alone do not drive engagement and that high ratings do not guarantee many reviews. Instead, product quality, visibility, and strategic pricing matter more.

For Jumia and other e-commerce platforms, the path to growth lies not in price wars but in value-driven strategies: improving product quality, spotlighting top performers, optimising discount ranges, and building trust through authentic customer reviews.

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

Bradley Okello — Mon, 31 Aug 2026 13:11:23 +0000

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

In today's data‑driven world, Excel is often the first analytics tool many professionals encounter as it has a low barrier to entry, is ubiquitous, and is far more powerful than most people realise. Yet many users feel overwhelmed when faced with a messy, disorganised spreadsheet and don't know where to start. This article will guide you from the most basic operations all the way to the core skills of data cleaning, helping you master Excel for analytics from the ground up.

1. Building a Solid Foundation:

Before diving into analysis, it's essential to understand what analytical features Excel offers. Its built‑in capabilities cover the entire data workflow; from cleaning to visualisation, all within one application.

Overview of Core Features

The key data analytics features in Excel include:

Data entry and organisation – manual input and external data import, paired with find/replace, remove duplicates, etc.
Filtering and sorting – quickly filter data by criteria and sort by values or alphabetical order.
PivotTables – one of the most powerful analytical tools, allowing rapid summarisation and cross‑tabulation of large datasets.
Charting tools – column, line, pie, and many other visualisation options.
Formulas and functions – covering statistical, financial, logical, and many other categories.

2. Data Cleaning: Turning "Dirty Data" into Clean Data

Data cleaning is the most important and often the most time‑consuming step in the analytics process. It is an essential step that prepares the data for analysis.

Core Steps of Data Cleaning

Data cleaning typically follows these steps:

Step 1: Data preparation

Ensure your data sources are reliable; if sources have inconsistent formats, standardise them first.
Use Text to Columns to convert messy formats into a consistent structure.
Use Remove Duplicates to quickly eliminate duplicate records.

Step 2: Data standardisation

Use Find and Replace to unify date formats (e.g., all dates to YYYY‑MM‑DD).
Set up Data Validation to prevent future input errors.
Use the TRIM function to remove excess spaces from text.

Step 3: Handling missing values

If missing values are few, delete the rows with missing data.
Fill using the Fill Series feature or functions (e.g., replacing with averages).
For time‑series data, use linear interpolation.

7 Must‑Know Data Cleaning Functions

Functions are indispensable for cleaning data. Here are seven of the most commonly used:

CLEAN – removes non‑printable characters
Text imported from other applications may contain characters that are not printable in your current operating system. CLEAN removes them in one go.
TRIM – removes extra spaces
Text copied from web pages often contains excessive spaces.
=TRIM(A1) removes all spaces except single spaces between words. This is especially important when cleaning user‑entered data.
IFERROR – replaces error values
When functions like VLOOKUP return an error because a lookup fails, IFERROR can replace that error with a specified value (e.g., blank or "Not Found"), preventing errors from interfering with your analysis.
REPLACE – replaces characters at a specified position
This function replaces a given value with another. It is often used to mask personal information such as phone numbers or ID numbers: =REPLACE(C2,7,8,"****").
LEFT / RIGHT / MID – extract characters

LEFT(C2,6) – extracts 6 characters from the left (e.g., to get an area code).

MID(C2,7,8) – extracts 8 characters starting at position 7 (e.g., to extract a birthdate).

RIGHT(B2,LEN(B2)-FIND(".",B2)) – extracts a file extension.

These text functions are not only useful for cleaning, but also for standardising data and converting formats. For instance, converting all text to uppercase or lowercase to ensure consistency.

Filtering: Quickly Spot Problem Data

Filtering is another powerful tool for data cleaning. Click anywhere in your data range, then select Filter from the Data tab. This quickly applies filter arrows to each column.

By combining multiple criteria (date ranges, amount thresholds, specific text, etc.), you can precisely locate particular records within your dataset. Combining filters with text‑based helper columns allows for even more dynamic and targeted cleaning.

3. Best Practices for Data Cleaning

To wrap up, here are some golden rules for data cleaning:

Always keep a raw copy – duplicate your original data before cleaning so you can always roll back.
Standardise first, match later – before using lookup functions like VLOOKUP, ensure both sides have consistent formatting.
Use helper columns liberally – break complex cleaning logic into several helper columns, tackling one step at a time.
Validate your results – after cleaning, use PivotTables or conditional formatting to check for any remaining issues.
Document your steps – get into the habit of noting what you did; it helps with review and reuse later.

Conclusion

From basic operations to data cleaning, Excel offers a complete suite of tools for data analytics. Master these skills, and you will transform raw, chaotic data into clean, structured, analysis‑ready datasets. The core of Excel data analytics is simply to get your data clean first, and then ask the right questions in the right way.

From Local Folder to Github - A Beginners Guide to Using Git and SSH

Bradley Okello — Thu, 20 Aug 2026 13:41:41 +0000

Introduction

This article covers my journey from taking a project from my local folder all the way to GitHub using git and SSH. It is a beginner-friendly guide, no prior experience with version control is required.

What are Git, GitHub and SSH?

These are the key players in any project and it is essential we get a better understanding of what they are and how they interact with each other to come up with a project in the net.

1. Git: Your Time Machine for Code

Git is a Version Control System (VCS) that tracks changes in your files over time. We refer to it as a time machine for our code as it allows us to:

Save snapshots of our projects at different points
Experiment with new features without breaking our working code
Collaborate with others without overwriting each other's work
Revert to previous versions if something goes wrong

2. GitHub: Your Code's Home on the Internet

GitHub is a cloud-based platform that hosts Git repositories. It is the equivalent of a social network for developers where we can:

Store our code securely on the cloud
Share our project with the world
Collaborate with other developers
Access our code or project remotely from anywhere

3. SSH: Your Secure Connection

SSH (Secure Shell) is a protocol that provides a secure way to connect our local computer to GitHub. It's more of a digital fingerprint that proves it's really you without the hassle of countless authentication procedures.

Understanding the Git Workflow

Before we start pushing code to GitHub, we need to understand the four major stages our files go through when using Git:

1. Working Directory

This is where we do our actual work. When we create, edit or delete files in our project folder, they exist in our working directory. Git sees these changes but hasn't started tracking them yet.

2. Staging Area (Index)

This is the "waiting room" for our changes. When we tell Git which files we want to save, we add them to the staging area. This allows us to be selective about which changes make it into our next commit.

3. Commit

A commit is a permanent snapshot of our staged changes. It's like taking a photo of our project at a specific moment. Each commit has a unique ID and includes a message describing what we changed.

4. Push

This is the final step where we upload our commits from our local machine to GitHub. Once pushed, our code is safely stored in the cloud and accessible to others (or just to you from other devices).

Prerequisites

Before we begin our journey, make sure you have:

Git installed on your computer (download from Git)
A GitHub account (sign up at GitHub)
A terminal or command prompt (Terminal on Mac/Linux, Command Prompt or PowerShell on Windows)

Step-by-Step Guide: From Local Folder to GitHub

Step 1: Initialize Git in Your Local Folder

First, let's tell Git that our folder is special and should be tracked. Open your terminal and navigate to your project folder using the cd (change directory) command:

cd path/to/your/project

Now, initialize a new Git repository:

git init

You should see a message like:Initialized empty Git repository in /path/to/your/project/.git/
We have just created a hidden .git folder that contains everything Git needs to track your project.

Step 2: Check Your Repository Status

Before making any changes, it's good practice to check the status of your repository:

git status

This command will show you which files are untracked (new) or modified. Initially, you'll see all your files listed as "untracked."

Step 3: Add Files to the Staging Area

Now, let's start tracking your files. To add all files in your current directory to the staging area:

git add .

The dot (.) means "everything in the current folder." You can also add specific files:

git add index.html
git add style.css

Run git status again, and you'll see your files in green, labeled as "changes to be committed."

Step 4: Create Your First Commit

Now that your files are staged, let's create a commit:

git commit -m "Initial commit: adding all project files"

The -m flag allows you to add a commit message directly in the command. Always write meaningful commit messages as they'll help you understand the history of your project later.

Step 5: Set Up SSH for GitHub

To connect to GitHub securely without typing credentials each time, we need to set up SSH keys.

Generate an SSH Key

In your terminal, run:

ssh-keygen -t ed25519 -C "your_email@example.com"

GitHub documents Ed25519 for new SSH keys, with RSA available mainly for legacy systems that do not support Ed25519. (GitHub Docs)

Replace the email with the one associated with your GitHub account. You'll be prompted to:

Choose a location to save the key (press Enter for the default)
Enter a passphrase (optional but recommended for extra security)

Add the SSH key to the SSH agent

The SSH agent helps manage your private SSH key and can remember its passphrase during your session. GitHub documents platform-specific agent setup steps. (GitHub Docs)

Start the SSH Agent

For the Windows method documented by GitHub, open: PowerShell as Administrator. To do that:

Click Start.
Search: PowerShell
Right-click PowerShell.
Choose: Run as administrator

Run:

Get-Service -Name ssh-agent | Set-Service -StartupType Manual

Then:

Start-Service ssh-agent

These are the current commands documented by GitHub for starting the Windows OpenSSH agent. (GitHub Docs)

Add Your Private SSH Key

Now close the Administrator PowerShell window.
Open a normal PowerShell window.
Run:

ssh-add c:/Users/YOUR_USERNAME/.ssh/id_ed25519

Replace:
YOUR_USERNAME with your actual Windows username.
Example:

ssh-add c:/Users/John/.ssh/id_ed25519

If you do not know your Windows username, you can run:

whoami

You may receive something like:
DESKTOP-12345\John
Your username in this example is:
John
If the key has a passphrase, enter it.
GitHub documents this Windows ssh-add path format. (GitHub Docs)

Avoid an SSH Agent Conflict

Windows may have both:

Windows OpenSSH;
Git for Windows' bundled SSH.

GitHub notes that this can sometimes result in Git asking for the passphrase even though the key was added to the Windows agent. (GitHub Docs)

To ensure Git uses Windows OpenSSH, run this on git bash:

git config --global core.sshCommand"C:/Windows/System32/OpenSSH/ssh.exe"

You only need to configure this once.

Add the SSH Key to GitHub

Copy your public key to your clipboard:

cat ~/.ssh/id_ed25519.pub | clip

The public key should now be in your clipboard.
GitHub documents this Windows-compatible form as an alternative that avoids PowerShell's handling of the < operator (GitHub Docs)

If that does not work, display it:

cat ~/.ssh/id_ed25519.pub

You will see one long line similar to:
ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAA... johnkamau@gmail.com
Copy the entire line.

Go to GitHub → Settings → SSH and GPG keys
Click "New SSH Key"
Paste your public key and give it a descriptive title (like "My Laptop")
Click "Add SSH Key"

First Connection Warning

On your first connection you may see a message similar to:
The authenticity of host 'github.com' can't be established.

You may also see an Ed25519 fingerprint.

GitHub's current documentation shows its GitHub.com Ed25519 fingerprint as:
SHA256:+DiY3wvvV6TuJJhbpZisF/zLDA0zPMSvHdkr4UvCOqU

Before accepting a first-time SSH host, compare the fingerprint shown in your terminal with GitHub's published fingerprint.

GitHub instructs users to verify the fingerprint before typing yes.(GitHub Docs)

If it matches, type:
yes
and press Enter.

Successful SSH Connection

If everything is correctly configured, you should get a message similar to:
Hi YOUR_USERNAME! You've successfully authenticated, but GitHub does not provide shell access. This is a success message.

GitHub documents this expected successful response.(GitHub Docs)

Step 6: Create a Repository on GitHub

Now it's time to create your project's home on GitHub:

Log in to GitHub.com
Click the "+" icon in the top-right corner and select "New repository"
Give your repository a name (you can use the same name as your local project)
Add an optional description
Choose the repository visibility: Public (everyone can see) or Private (only you and collaborators)
Don't initialize with README, .gitignore, or license files (we already have our code)
Click "Create repository"

Step 7: Connect Your Local Repository to GitHub

GitHub will show you instructions for connecting your existing repository. We'll use the SSH option:

git remote add origin git@github.com:yourusername/your-repository-name.git

Replace yourusername with your GitHub username and your-repository-name with your repository name.

This command tells Git that the remote repository (the one on GitHub) should be referred to as "origin."

Step 8: Push Your Code to GitHub

Now for the moment of truth; uploading your code to GitHub:

git push -u origin main

Let's break this down:

git push: Upload commits to a remote repository
-u origin main: Set the upstream branch to main on the origin remote
This is a shortcut so you can usegit pushin the future without all the parameters

Step 9: Verify Your Push

Go to your GitHub repository page and refresh. You should see all your files there!

Conclusion

We have just completed our journey from a local folder to GitHub! We have learned the essential Git workflow; from creating commits in your working directory to securely pushing them to GitHub using SSH. This is the foundation of modern software development, opening doors to collaboration, backup, and professional version control.