DEV Community: Nesta Munene

Data Modelling, Relationships & Joins

Nesta Munene — Sun, 13 Sep 2026 20:03:46 +0000

Introduction

A Power BI report is only as good as the data model sitting underneath it. For instance, two analysts can start with identical raw data and end up with wildly different reports; one fast, easy to extend, and simple to write DAX against; the other slow, tangled, and fragile the moment a new requirement shows up. The difference almost always comes down to data modelling decisions made before a single visual was ever placed on a canvas.

This article will walks you through how Power BI models data using flat tables versus star and snowflake schemas, the role of fact and dimension tables, how relationships and filter direction actually work, the difference between a Power Query join and a Power BI relationship, and finally, a justified recommendation for how to structure a typical BI project.

1. Data Modelling in Power BI

Data modelling is the process of organizing your tables and the connections between them before you start building visuals. It answers questions like: should this be one big table, or several smaller ones? How do they relate to each other? Which table holds the numbers you want to measure, and which tables hold the descriptive context around those numbers?

This matters for several practical reasons:

1.DAX simplicity: a well-structured model lets a single measure like SUM(Sales[Amount]) work correctly across every report filter, without needing to be rewritten for each new chart.
2.Performance: Power BI's engine (VertiPaq) is optimized to compress and query well-structured, related tables far more efficiently than one enormous flat table.
3.Scalability: adding a new dimension (say, a Promotions table) to a well-modelled star schema is a five-minute job; retrofitting it into a flat table can mean rebuilding half your columns.
4.Maintainability: when business logic changes (e.g., a new way of categorizing customers), a well-modelled table only needs updating in one place, not duplicated across every report.

Flat Table

Definition: a single, wide table containing every piece of data-transactional facts and descriptive attributes in one place. This is the Excel-style approach: one row per transaction, with customer name, product name, region, date, and sales amount all sitting in the same row.

Structure: no separate tables, no relationships, just one table with many columns.

erDiagram
    SALES_FLAT {
        int OrderID
        date OrderDate
        string CustomerName
        string CustomerCity
        string ProductName
        string ProductCategory
        string Region
        int Quantity
        decimal SalesAmount
    }

Advantages:
1.Simple to understand at a glance.
2.No relationships to configure, so it's fast to build for very small, one-off analyses.

Disadvantages:
1.Massive data redundancy: if "Nairobi" appears as a customer's city on 500 orders, that text is repeated 500 times instead of stored once.
2.Poor performance at scale: a wide, repetitive table compresses far worse than normalized tables, and Power BI's engine has to do more work per query.
3.Difficult to maintain: updating a customer's city means finding and updating every row that customer appears in, rather than one row in a dimension table.
4.DAX becomes harder to reuse across contexts once a table conflates multiple grains (e.g., "one row per order" and "one row per customer" living together).

When appropriate: small, static datasets, quick one-time analyses, or genuinely simple use cases with no plans to scale. It's a reasonable starting point, but not something to scale a serious BI solution on.

Star Schema

Definition: a central fact table (holding measurable business events such as; sales, orders, transactions) surrounded by multiple dimension tables (holding descriptive context such as customers, products, dates, locations), each connected directly to the fact table by a relationship.

Structure: the fact table sits in the middle; each dimension connects to it independently, with no dimension connecting to another dimension.

erDiagram
    DimCustomer ||--o{ FactSales : "CustomerID"
    DimProduct ||--o{ FactSales : "ProductID"
    DimDate ||--o{ FactSales : "DateKey"
    DimLocation ||--o{ FactSales : "LocationID"

    DimCustomer {
        int CustomerID PK
        string CustomerName
        string Segment
    }
    DimProduct {
        int ProductID PK
        string ProductName
        string Category
    }
    DimDate {
        int DateKey PK
        date FullDate
        string Month
        int Year
    }
    DimLocation {
        int LocationID PK
        string City
        string Region
    }
    FactSales {
        int OrderID
        int CustomerID FK
        int ProductID FK
        int DateKey FK
        int LocationID FK
        decimal SalesAmount
        int Quantity
    }

Advantages:
1.Eliminates redundancy: "Nairobi" is stored once in DimLocation, not once per transaction.
2.Fast query performance: Power BI's VertiPaq engine is specifically optimized for this shape.
3.Simple, predictable DAX: filters flow cleanly from dimension to fact in one hop.
4.Easy for report builders to understand and navigate.

Disadvantages:
1.Requires upfront design work: you need to identify your facts and dimensions before building, rather than just dumping in a flat export.
2.Slightly less intuitive for someone used to reading a single spreadsheet.

When appropriate: the default choice for the vast majority of real world Power BI projects: sales analysis, product performance dashboards, operational reporting, and anything expected to grow over time.

Performance and complexity implications: star schemas are what Power BI is built to perform best on. Model complexity is low and predictable, one hop from any dimension to the fact table and this directly keeps DAX formulas simple, since Power BI's automatic filter propagation does most of the work.

A worked example, built from a real dataset: to make this concrete, I took a Kenya crop production dataset that originally arrived as a single flat table — one row per crop record, with County, Crop Type, Season, Planting Date, Harvest Date, and a set of numeric measures (Revenue, Profit, Yield, Production Cost, Market Price) all sitting in the same wide table. I rebuilt it into a proper star schema in Power Query, splitting it into a fact table and four dimensions:

1.Kenya_Crops_Dataset (Fact) - Planted Area, Yield, Market Price, Production Cost, Revenue, Profit, plus Planting Date and Harvest Date
2.DimCounty - County
3.DimCropType - Crop Type
4.DimSeason - Season
5.Calendar - Date

Each dimension was created by referencing the original fact query, keeping only it's one relevant column, and removing duplicates, turning a flat table into a normalized set of tables ready to be related.

Figure 1: The finished star schema in Power BI's Model View. Note the two lines running to Calendar one solid, one dashed

Snowflake Schema

Definition: an extension of the star schema where dimension tables are further broken down into related sub-dimensions, rather than each dimension being a single flat table.

Structure: instead of DimProduct holding both product details and category details in one table, a snowflake schema splits this into DimProduct → DimCategory, connected to each other before either connects back to the fact table.

erDiagram
    DimCategory ||--o{ DimProduct : "CategoryID"
    DimProduct ||--o{ FactSales : "ProductID"
    DimCity ||--o{ DimLocation : "CityID"
    DimLocation ||--o{ FactSales : "LocationID"

    DimCategory {
        int CategoryID PK
        string CategoryName
    }
    DimProduct {
        int ProductID PK
        string ProductName
        int CategoryID FK
    }
    DimCity {
        int CityID PK
        string CityName
        string Country
    }
    DimLocation {
        int LocationID PK
        int CityID FK
        string Region
    }
    FactSales {
        int OrderID
        int ProductID FK
        int LocationID FK
        decimal SalesAmount
    }

Advantages:
1.Further reduces redundancy in cases where a dimension itself has repeating descriptive data (e.g., many products sharing the same category name and description).
2.Can better reflect a genuinely hierarchical business structure (Category → Sub-category → Product).

Disadvantages:
1.More tables means more relationships, meaning more "hops" a filter has to travel through to reach the fact table, this adds query overhead and can slow report performance compared to a star schema.
2.More complex DAX and more places for a broken relationship to hide.
3.Harder for report builders to navigate, finding the right field means knowing which of several linked tables it lives in.

When appropriate: large enterprise models with genuinely deep, reused hierarchies, or where a dimension is large enough that normalizing it meaningfully reduces storage and improves maintainability. It's a deliberate trade-off, not a "better" version of a star schema.

Performance and complexity implications: every additional join level is an additional cost. It is good to default to a star schema and only snowflake a specific dimension when there's a clear, measurable reason to.

2. Fact Tables and Dimension Tables

Fact tables store the measurable, numeric events of a business; the things you want to sum, average, or count. Think of sales transactions, orders placed, website clicks, or support tickets logged. A fact table is typically long with many rows and narrow few columns: mostly foreign keys pointing to dimensions, plus a handful of numeric measures.

Dimension tables store the descriptive context that explains who, what, where, and when around those facts - customer names, product details, dates, locations. Dimension tables are typically wide with many descriptive columns but short rows than the fact table.

	Fact Table	Dimension Table
Stores	Measurable business events	Descriptive attributes
Example content	SalesAmount, Quantity, OrderID	CustomerName, ProductCategory, City
Typical shape	Many rows, few columns	Fewer rows, many columns
Examples	FactSales, FactOrders, FactTransactions	DimCustomer, DimProduct, DimDate, DimLocation

Measures vs. descriptive attributes: a measure is a number you aggregate SUM(FactSales[SalesAmount]). A descriptive attribute is something you group or filter by, but never sum. Grouping sales by Region makes sense; summing "Region" does not.

Grain / granularity: this is one of the most important and most overlooked concepts in fact table design. Grain describes what a single row in the fact table represents. Is one row "one order" or "one line item within an order" or "one day's total sales for one product"? Every measure and every relationship depends on this being clearly defined and consistent - mixing grains in one fact table (some rows representing an order, others representing a daily summary) breaks aggregations silently.

Practical example, the Kenya Crops model: in the star schema built for Section 1, the grain of the fact table is one row per crop production record. Its numeric measures - Yield, Revenue, Profit, Production Cost, Market Price, are the facts. County, Crop Type, and Season are descriptive attributes, which is exactly why they were pulled out into their own dimension tables rather than left as repeating text in every fact row. Each dimension connects directly to the fact table - the star shape from Section 1 - letting a report slice total revenue or yield by any combination of county, crop type, season, or date, without duplicating that descriptive text across every row the way the original flat table did.

Figure 2: The fact table's grain, one row per production record visible here before it was split into the star schema. This single, wide table is the "before" state Section 1's flat-table discussion refers to.

3. Relationships in Power BI

A relationship is a defined connection between two tables, based on a shared column, that tells Power BI how rows in one table relate to rows in another. Relationships are necessary because splitting data into multiple tables (as a star or snowflake schema does) only works if Power BI has a way to reassemble the connections between them at query time - this is the direct successor to the manual XLOOKUP work in Excel, done once structurally instead of formula-by-formula.

One-to-Many (1:*)

How it works: one row in Table A can relate to many rows in Table B, but each row in Table B relates back to only one row in Table A. This is by far the most common relationship type in a star schema.

Example: one row in DimCustomer (a single customer) relates to many rows in FactSales (that customer's many orders). In the Kenya Crops model, the same pattern holds between DimCounty and the fact table: one county (e.g. "Nakuru") relates to many crop production records.

*Figure 3: Power BI's relationship dialog, showing the County relationship's cardinality.

When to use it: this is the default, expected relationship between any dimension and its fact table.

When not to use it: it shouldn't be used to connect two tables that both hold transactional-level data with no clear "one side" - that usually signals you actually have a many-to-many situation being modelled incorrectly.

One-to-One (1:1)

How it works: one row in Table A relates to exactly one row in Table B, and vice versa.

Example: a DimCustomer table split into DimCustomerProfile (name, segment) and DimCustomerContact (email, phone), where each customer has exactly one matching row in each.

When to use it: rare in practice - usually only when a table has genuinely been split for organizational or security reasons (e.g., separating sensitive contact details from general profile data).

When not to use it: if you find yourself creating a 1:1 relationship as a workaround, it's almost always a sign those two tables should simply be merged into one - a 1:1 relationship adds model complexity without a real modelling benefit in most cases.

Many-to-Many (:)

How it works: rows in Table A can relate to many rows in Table B, and rows in Table B can relate to many rows in Table A.

Example: a DimProduct table and a DimPromotion table, where one promotion can apply to many products, and one product can be part of many different promotions at once.

When to use it: when the real-world business relationship genuinely has no "one" side - this does happen, but it should be a deliberate modelling decision, often resolved with a bridge table in between rather than a direct many-to-many link.

When not to use it: many-to-many relationships are harder for Power BI to filter through efficiently and can produce ambiguous or unexpectedly duplicated results if used casually. Most textbook "many-to-many" cases are better modelled with an intermediate bridge table that breaks the relationship into two clean one-to-many hops.

Keys, Cardinality, and Integrity

1.Primary Key: a column that uniquely identifies each row in a table. e.g., CustomerID in DimCustomer, where every value appears exactly once.
2.Foreign Key: a column in another table that refers back to a primary key. e.g., CustomerID in FactSales, where the same customer's ID can (and should) appear many times, once per order.
3.Unique values: a primary key's defining requirement, no duplicates. CustomerID in DimCustomer must be unique for the relationship to behave correctly.
4.Cardinality: describes how many times a key value can repeat on each side of the relationship - this is what defines whether a relationship is 1:1, 1:, or *:.
5.Referential integrity: the guarantee that every foreign key value in the fact table actually has a matching row in the dimension table - an order referencing CustomerID = 507 should only exist if a customer with ID 507 actually exists in DimCustomer. Broken referential integrity (an order with no matching customer) shows up as blank or "unknown" values in reports.
6.Active vs. inactive relationships: Power BI allows only one active relationship between two tables at a time. Any additional relationship between the same two tables must be marked inactive, and can only be invoked deliberately inside a DAX measure using USERELATIONSHIP().

I ran into this directly while building the Kenya Crops model: the fact table has both a Planting Date and a Harvest Date, and both logically need to relate to the same Calendar dimension. Power BI let the Planting Date relationship save as active (visible as the solid line to Calendar in Figure 1), but automatically marked the second relationship, to Harvest Date, as inactive - drawn as the dashed line in that same diagram the moment I tried to create it, because a Calendar table can only actively filter a fact table through one date column at a time.

To actually use the inactive relationship, it has to be explicitly invoked inside a measure:

  Total Yield by Harvest Date = 
  CALCULATE(
      SUM('Kenya_Crops_Dataset'[Yield]),
      USERELATIONSHIP(Calendar[Date], 'Kenya_Crops_Dataset'[Harvest Date])
  )

Without USERELATIONSHIP(), any measure placed on a Calendar-based visual would default to filtering by Planting Date only - Harvest Date would simply be ignored by the model unless specifically activated this way.

Why CustomerID is unique in DimCustomer but repeats in FactSales: this is the cardinality relationship in action. DimCustomer describes each customer once - the "one" side. FactSales records every transaction that customer made - the "many" side. The same CustomerID legitimately appears multiple times in FactSales because that customer placed multiple orders, while it can only appear once in DimCustomer because a customer is only described once.

4. Filter Direction

Filter direction controls which way a selection in one table affects another related table.

Single-direction filtering (the Power BI default for most relationships): a filter applied to the "one" side (a dimension) flows down to the "many" side (the fact table), but not the other way around. Selecting "Nairobi" in DimLocation filters FactSales down to only Nairobi's transactions - but filtering FactSales some other way (say, only orders over KSh 5,000) does not, by default, filter which cities show up in DimLocation.

Bidirectional filtering: the filter can travel both directions - a selection in the fact table can also filter back up into a connected dimension.

Example: selecting "Electronics" in DimProduct filters FactSales down to only electronics sales - this is standard single-direction behaviour and is exactly what powered the slicers in my earlier Jumia Excel dashboard (clicking "Excellent" on the Rating Category slicer filtered the connected pie charts the same way a dimension filter flows down to a fact table in Power BI).

Why bidirectional filtering should be used carefully: turning on both-direction filtering can create ambiguous filter paths - situations where Power BI has more than one possible route a filter could travel to reach a table, and can't determine which one should win. This is especially risky in models with multiple relationships between the same tables, or several dimensions connected to more than one fact table. It also adds real query overhead, since the engine now has to evaluate filter propagation in both directions across every calculation. The general guidance is to leave relationships single-direction by default and only switch to bidirectional when a specific, well-understood use case genuinely requires it - not as a default "just in case" setting.

5. Joins in Power Query

A join (called a Merge Query in Power Query) combines two tables based on matching values in a shared column - this happens during data preparation, before the model is even built, unlike a relationship which connects already-loaded tables inside the model.

Consider two example tables:

Customers
| CustomerID | Name |
|---|---|
| 1 | Asha |
| 2 | Brian |
| 3 | Carol |

Orders
| OrderID | CustomerID | Amount |
|---|---|---|
| 101 | 1 | 500 |
| 102 | 1 | 300 |
| 103 | 4 | 750 |

Notice CustomerID 4 appears in Orders but not Customers, and Carol (CustomerID 3) has no orders - these gaps are exactly what each join type handles differently.

Left Outer Join

How it works: keeps every row from the left table (Customers), and matches in any corresponding rows from the right table (Orders) where they exist.
Retained: all rows from the left table; matched rows from the right, or blanks where there's no match.
Example output: Asha's two orders appear twice (once per order); Brian is unmatched - wait, Brian has no order in this data, so Brian would appear once with blank Order fields; Carol appears once with blank Order fields. CustomerID 4's order is dropped, since it's not in the left table.

Right Outer Join

How it works: the mirror image - keeps every row from the right table (Orders), matching in Customer data where it exists.
Retained: all rows from the right table; matched rows from the left, or blanks where there's no match.
Example output: all three orders appear (101, 102, 103); the first two show Asha's name, the third (CustomerID 4) shows a blank Customer name, since no matching customer exists.

Full Outer Join

How it works: keeps every row from both tables, matching where possible and leaving blanks where there's no match on either side.
Retained: everything - matched and unmatched rows from both tables.
Example output: Asha's two orders, Brian with blanks, Carol with blanks, and CustomerID 4's order with a blank customer name - nothing from either table is dropped.

Inner Join

How it works: keeps only rows where a match exists in both tables.
Retained: only the overlapping, matched rows.
Example output: just Asha's two orders (101 and 102) - Brian and Carol are dropped (no orders), and order 103 is dropped (no matching customer).

Left Anti Join

How it works: keeps only rows from the left table that have no match in the right table - effectively the opposite of an inner join.
Retained: unmatched left-table rows only.
Example output: Brian and Carol - customers with no orders at all. Useful for finding "customers who have never ordered anything."

Right Anti Join

How it works: the mirror of a left anti join - keeps only rows from the right table with no match in the left.
Retained: unmatched right-table rows only.
Example output: order 103 - an order that references a customer who doesn't exist in the Customers table. Useful for finding orphaned or data-quality-flagged records, similar to the negative Review values I had to catch and fix in my Jumia Excel dataset.

6. Power Query Joins vs. Power BI Relationships

These solve a similar-sounding problem - combining related data - but they work at completely different stages and in fundamentally different ways.

Does a Power Query merge physically combine data? Yes. A merge in Power Query happens during data loading/transformation, and it produces a genuinely new, combined table (or adds new columns pulled in from the second table) - the two tables' data is physically brought together into one result before the model is even built.

Does creating a relationship combine the tables? No. A relationship leaves both tables completely separate and intact inside the model. It only tells Power BI how to look across from one to the other at query time - conceptually much closer to how XLOOKUP worked in my Excel dashboard, pulling a value across on demand, without ever merging the two sheets into one.

Stage of the workflow:
1.Merges happen in Power Query (the "Get Data" / transformation stage) before the data lands in the model.
2.Relationships are created in Model view, after the tables already exist as separate entities inside the report.

When to choose a merge instead of a relationship: when you need specific columns from Table B pulled directly into Table A as part of a single flattened result - for instance, if a single visual absolutely needs a column that DAX can't easily reach across a relationship, or you're deliberately building a single reporting table for a specific narrow purpose.

How excessive merging affects the model: overusing merges tends to recreate the flat-table problem from Section 1 - data gets duplicated across many wide, overlapping tables, storage and refresh times increase, and you lose the clean, redundancy-free structure a star schema is designed to give you.

Why keep fact and dimension tables separate: separate tables connected by relationships keep the model small, avoid duplicating dimension data across every fact row, let Power BI's engine compress and query efficiently, and make it easy to reuse the same dimension (say, DimDate) across multiple fact tables without rebuilding it each time. Merging everything into one table trades all of that away for a false sense of simplicity.

7. Recommended Power BI Model

For a typical business intelligence project, I would recommend a star schema, with single-direction, one-to-many relationships flowing from each dimension into a central fact table, reserving bidirectional filtering only for specific, well-justified cases.

Justification:

1.Query and report performance: Power BI's VertiPaq engine is purpose-built to compress and query star schemas efficiently. A snowflake schema's extra join hops add measurable overhead at scale; a flat table's redundancy bloats storage and slows refresh.
2.DAX simplicity: with a clean star schema, most measures are simple aggregations (SUM, AVERAGE, DISTINCTCOUNT) that automatically respect whatever filters are applied across any connected dimension, thanks to default filter propagation - no need for complex USERELATIONSHIP() gymnastics or ambiguous-path troubleshooting.
3.Model readability: a star shape is genuinely easier for anyone (including a future version of the analyst who built it) to look at and immediately understand - every dimension is one hop from the fact table, full stop.
4.Scalability: adding a new dimension later (a new DimPromotion, say) is a clean, additive change - connect it directly to the fact table, no restructuring required.
5.Data redundancy: dramatically lower than a flat table, since descriptive attributes live once in each dimension rather than repeating on every transaction row.
6.Maintainability: updating a product's category means updating one row in DimProduct, not hunting through thousands of fact rows.

I would only introduce snowflaking for a specific dimension where there's a clear, demonstrated reason - a genuinely large, deeply hierarchical dimension where normalizing meaningfully reduces redundancy - rather than snowflaking by default. And I would keep relationships single-direction unless a specific reporting requirement (like a genuine many-to-many scenario needing a bridge table) demands otherwise, since the performance and ambiguity costs of bidirectional filtering outweigh the convenience in most everyday reporting needs.

A Note on Data Quality in the Worked Example

Building this model surfaced two real data-quality issues worth naming rather than hiding, in the same spirit as the cleaning work documented in an earlier Excel project I completed on Jumia product data:

1.Missing Harvest Dates: roughly 3% of records had a blank Harvest Date, which had to be filtered out of the Calendar table before it could be used as a relationship key - a dimension table's key column cannot contain blanks.
2.A mismatched category: the DimSeason dimension, built by extracting unique values from the fact table's Season column, ended up with only three values (Long Rains, Short Rains, Unknown), while the fact table itself also contained a fourth value, "Dry Season," not captured in that extraction. Left unresolved, any report slicing by Season would silently drop or misclassify those rows - a reminder that even a mechanically correct relationship can sit on top of an incomplete dimension, and that building a star schema doesn't eliminate the need to also check the data feeding it.

Conclusion

The shift from an Excel-style flat table to a Power BI star schema mirrors a lesson from building the Jumia product dashboard: the shape you organize data into isn't cosmetic, it directly determines how easy, fast, and trustworthy every downstream calculation and chart will be. A star schema with clean, single-direction relationships is the closest thing Power BI has to a default best practice, and understanding why it wins - not just that it does, is what separates rearranging tables from actually modelling data.

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

Nesta Munene — Sun, 06 Sep 2026 10:30:29 +0000

Introduction

E-commerce platforms like Jumia generate a constant stream of data on pricing, discounts, and customer feedback, but that data is only useful once someone actually digs into it. For this project, I worked with a dataset of 111 products listed on Jumia to understand how price, discounting, and customer reviews relate to one another, and to turn that understanding into something Jumia's sellers could actually act on.

This article walks through the full process of cleaning a messy real-world dataset, building out the analysis with Excel formulas and Pivot Tables, constructing an interactive dashboard, and the business insights that came out the other end including a couple of assumptions that didn't survive contact with the actual numbers.

Dataset Description

The dataset included six core fields per product:

Product - product name
Current Price - selling price in KES
Old Price - price before discount
Discount - percentage discount applied
Review - number of customer reviews
Ratings - average customer rating out of 5

Before any of this was usable, it needed cleaning.

Data Cleaning and Preparation

The original dataset

Two issues stood out immediately:

The Review column was entirely negative, values like -2, -14, and -69, which makes no sense for a count of anything. This pointed to a sign error somewhere upstream in how the data was collected or exported, and was corrected to positive values.
One row's Current Price was stored as text and range "1,620 - 1,980", a price range typed into a single cell instead of a number. This one bad cell was enough to silently break SUM, AVERAGE, and correlation calculations across the whole column unless it was fixed.

I also had to think carefully about a more subtle issue: roughly half the dataset that is 57 of 111 products, had no Review or Rating data at all. Rather than ignore this gap, I treated it as a finding in its own set. Any conclusion about "what makes a product well reviewed" in this analysis only applies to the half of the catalog that actually has review data.
The empty fields

Enrichment: Calculated Columns

With the base data clean, I added:

Discount Amount = Old Price − Current Price
Rating Category: Poor (below 3), Average (3–4), Excellent (4.5 and above)
Discount Category: Low (below 20%), Medium (20–40%), Above/High (over 40%)
Price Category: Low, Medium, High, based on where a product's price falls within the overall range

The Cleaned and Enriched Dataset

Excel Techniques and Analysis
Descriptive statistics across the 111 products:

Total products 111
Average current price KES 1,181
Average discount 37%
Average rating 3.88
Total reviews 721

I did Correlation analysis, using =CORREL() to test three commonly assumed relationships:

Discount % vs. Review -0.14 No relationship
Rating vs. Reviews +0.07 No relationship
Price vs. Rating +0.01 No relationship

One thing I want to highlight about the Price vs. Rating result specifically, because it changed how I read the rest of the analysis. When I first built a Pivot Table comparing average price across Poor/Average/Excellent rating groups, the averages climbed 998, 1,369, 1,381 which looked like evidence that pricier products get rated higher. But the correlation was 0.01, essentially zero. Building a scatter plot settled it, ratings of 2.0 through 5.0 were scattered across the entire price range, with no visible upward drift. The Pivot averages were misleading because the Poor and Average rating groups only had 12 and 22 products respectively, which are small enough that a couple of outlier prices could swing the average without reflecting any real underlying pattern. It was a good reminder that a summary average can tell a different story than the full spread of the data, and it's worth checking both before drawing a conclusion.

Ranking and segmentation, I used SORT, INDEX+SEQUENCE, and XLOOKUPto build Top-10 lists (by Rating, Reviews, and Discount), and FILTER combining AND (*) and OR (+) logic, to isolate specific product segments.

Strong customer demand (Reviews ≥ 20 AND Rating ≥ 4): 9 products, led by a 137-Piece Cake Decorating Tool Set (55 reviews, 4.6 rating).
High discount but low rating (Discount ≥ 40% AND Rating < 3): 10 products, including a 5-PCS Stainless Steel Cooking Pot Set discounted 55% but rated only 2.1.
High discount but low engagement (Discount ≥ 40% AND Reviews ≤ 3): 9 products — heavily discounted, but barely bought or reviewed.
Products needing better pricing or marketing strategies (Reviews ≤ 3 OR Rating < 3): 23 products in total.

Category breakdowns via Pivot Tables and PivotCharts

Rating Category: 22 Average, 22 Excellent, 12 Poor, 55 Not Provided
Discount Category: 57% Above, 21% Medium, 19% Low Discount (approximate)
Price Category: 50% Low, 43% High, 7% Medium

Dashboard Creation Process

The final dashboard pulls all of this into one sheet, organized into four sections:

Overview/KPI cards - Total Products, Average Price, Average Discount %, Average Rating, Total Reviews, styled as five distinct summary cards.
Product Performance - three bar charts for Top 10 by Rating, by Reviews, and by Discount.
Trend Analysis - three scatter charts (no connecting lines, so the actual spread of the data is visible rather than hidden inside a single line or average), each labeled with its correlation value directly in the title.
Product Categories - three pie charts (Rating, Discount, and Price Category), connected via slicers so that clicking a single category, instantly re-filters all three pie charts.

Key Insights and Business Recommendations

Are higher discounts leading to higher customer engagement? No. The correlation is -0.14, and the scatter plot backs this up with no visible trend across the full 0–70% discount range.

Do highly rated products have higher or lower prices? Essentially neither. The correlation is 0.01. This was the most counter-intuitive finding of the project, especially since an initial Pivot Table look made it seem otherwise until I checked the raw scatter of the data.

Which products are performing best based on reviews and ratings? The 9 "strong demand" products sheet, consistently high reviews paired with strong ratings, are the most validated products in the catalog and worth featuring or promoting.

**Which products may need improved pricing strategies? **The 23 flagged products split into two genuinely different problems; some like a DIY File Folder rated 5 with only 1 review, simply aren't being seen, which is a marketing or visibility issue. Others like the discounted Cooking Pot Set above, have a real satisfaction problem that a bigger discount won't fix.

Recommendations for Jumia sellers:

Don't treat discount depth as an engagement lever - the data shows no payoff from deeper discounts on their own.
Close the data gap - nearly half the catalog has no review or rating at all, which limits how much sellers and buyers alike can trust any single product's track record.
Audit the 9 "high discount, low engagement" products specifically - Since the discount clearly isn't the barrier, so something else likely is.
Don't lump visibility problems in with quality problems - a 5-star product with one review needs marketing. A heavily discounted product with a 2-star rating needs a quality fix, not more discounting.

Closing Thoughts

The most useful part of this project wasn't the dashboard itself, it was the number of instinctive assumptions that "bigger discount = more reviews," and "pricier = better rated" that turned out not to hold up once actually tested. Building the habit of double-checking a Pivot Table average against a correlation and a scatter plot, rather than trusting the first number that looks convincing, was my biggest practical lesson from this project.

The full workbook which has raw data, cleaned data, all analysis sheets, Pivot Tables, charts, and the final interactive dashboard, is available in my GitHub repository linked below.

GitHub repo: [https://github.com/mimi580/Jumia-Product-Performance-Dashboard-An-E-commerce-Data-Analysis-Project/blob/main/Excel%20Jumia%20Dataset.xlsx]

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

Nesta Munene — Sun, 30 Aug 2026 07:33:55 +0000

Overview

I have just covered excel basic functions and cleaning data for correct interpretation and derivation of insights. Learning about Statistical and Aggregate functions and finally about Conditional formatting and calculations.

What is Excel?

Simply put, I describe excel as a grid of boxes called cells, arranged in rows which are numbered 1, 2, 3... and columns which are lettered A, B, C.... Each cell can hold a number, a word, a date, or a formula.

Basics to Know

Formulas start with an equals sign (=). If you type =5+5 into a cell, Excel shows you 10. If you type =A1+A2, it adds together the numbers sitting in cells A1 and A2. This is the whole foundation of everything else. Formulas let a cell calculate something instead of just displaying a fixed number typed in.

The Messy Data I Was Working With

Looking at this data, some of the issues present include but not limited to:

Spelling mistakes. "Markting" instead of "Marketing."
Missing information.
Inconsistent naming and cell formatting.

Step 1 of Data Cleaning: We figure out how bad the data set is.

To do this, we will use two formulas:

=COUNTA()- this will tell us how many cells in the selected range have some value typed in them.
=COUNTBLANK() - this formula will tell us how many blank cells there in a selected range.

Step 2: Fix Inconsistent Spelling and Capitalization and Spaces

For this, we will use two formulas.

=TRIM() - This formula removes any invisible characters.
=PROPER() - Tis formula fixes capitalization, so "human resources" becomes "Human Resources.
=LOWER() - This formula sets all characters to lower case. It is very handy when generating emails.
=LEN() -This formula will tell you how many characters are in a cell, including the invisible ones.
Although the =Len() character count formula works best when used alongside other character counting formulas like, =LEFT(), =RIGHT(), =MID()

Step 3: Removing Duplicates

This is a strenuous activity if it were to be manually by scrolling one cell/column after the other.
Looking at the dirty data, we can see that there are double entries.
In excel we can remove duplicates easily by using =COUNTIF() formula.

Step 4: Formatting Columns Correctly

This can apply in cases where the column,ie the date or salary column is not correctly formatted, the values will be misconstrued and not accurately represent the data as they should.

Aggregate Functions

These add up or combine a whole range of numbers into one result.

=SUM() =adds everything in a range.
=AVERAGE() = finds the mean value.
=MIN()= smallest value in a range.
=MAX() = largest value in a range.
=COUNT() =counts how many cells contain numbers.
=COUNTA() = counts how many cells have anything typed in them.

Conditional Calculations

These only calculate based on a set condition.
=IF()= returns one result if a condition is true, another if false.
=SUMIF() and =SUMIFS() = adds numbers only where a condition (or several conditions) is met.
=COUNTIF() and =COUNTIFS() = counts entries that meet a condition.

Conclusion

This is week one in excel, learning the basics and building a foundation. The functions are quite versatile in their use once you understand how to use them to clean, organize and extract insights from a raw set of data.

My First Github Project: From a Local Folder to Github Using Git and SSH

Nesta Munene — Sat, 22 Aug 2026 20:34:06 +0000

Introduction

I am on week two of learning Data Science and Analytics and I have just been introduced to Git and Github. Basing by the name I could tell that they are somehow connected. I got started by learning about configuring git through bash and creating a github account. Then creating folders using Git Bash, moving backward while on bash terminal, renaming on bash terminal, deleting, staging to eventually pushing a folder from local folder to github using Git bash and also pushing a local folder to Github using VS Code studio.

Steps Taken During Git Bash Set-up and Configuration to the eventual pushing of the folder to Github.

Downloading and Installing Git
Running git --version to ensure it installed correctly
Then the Git Bash configuration where you run git config --global user.name "Your Name" to set your name and then git config --global user.email "Your Email" to set your email. It is usually recommended to use the actual email that is linked to your Github account.
Then run git config --global user.name and git config --global user.email to confirm that the values actually went through.
Then run git config --global --list to show what you have configure so far, also it will show the branch you are on.
The sixth step is setting up the SSH key which will be used to access my github since I intend to connect with my Github using SSH instead of HTTPS. Setting the SSH is by using ssh-keygen -t ed25519 -C "your email" this is one of the crucial codes to remember about Bash. Then press enter to save on the default folder, then set passphrase (It will be blank as you do this, don't panic) if an existing key exists, you will be asked to overwrite or not.
After setting the passphrase, it will be saved in the default folder, type in cat ~/.ssh/id_ed25519.pub to get the SSH key which we shall link to Github.
Open up Github account, login/create a new one, navigate to profile icon, then settings, then click on SSH and GPG keys, then select New SSH key and paste the key from Git Bash and our Git Bash and Github are now linked and we can push straight from Bash to Github repo.
The project I was working on was on Hospital Health Data Analysis. I had the .xlsx file ready but in my downloads folder. I could not move it in the convectional manner through files explorer but through Git Bash.
Back to Bash, I typed pwd this is the command for printing the working directory so that you know from which folder you are working in.

Creating of the Project Folder and README.md File

To create the Projector folder in which I was to work from and eventually push to Github, I used cd Onedrive to move to Onedrive, this way I could backup my work always, then mkdir Project001 to make directory, after I used ls command to see what folders and files were in my folder. cd stands for change directory which you can loosely translate as moving into another folder from the current one. mkdir is the bash command for creating a folder and ls is the command for listing, which is used to get a list of the all the content in a folder.
Once the project was created, I created another folder for data through mkdir data while I was in the project folder, then I had to create a README.md file which had the notes and about of the project detailed what the project was about, the tools used, challenges faced etc. To create the README.md file I used touch README.md

Moving/Copying the .xlsx File from Download to the Project Folder

Once the project folder was ready, I had to copy the file from downloads folder to the project folder I had just created using Bash. This, I achieved by using the bash command cp ~/Downloads/name of the .lsx file ~/Onedrive/Project001/data/ cp means copy and mv means move, you can use either cp or move.
Then I used command ls to confirm all was in order in the Project and data folder.

Editing the README.md

First off, the .md stands for markdown language. We have already created the README.md by using Bash command touch README.md. To write on the README.md we use command echo "all about your project" > README.md a couple of things to know here is that echo is the command to print/write, then the > points to where the writing is to be done. The use of one > means overwrite what was already existing, so to add onto what is existing on the README.md we use double >>
Then to create a space so as to write the next paragraph, I used command echo "" >> README.md this command creates a space or line jump.
I used cat README.md to show what was in the README.md file. To edit the README.md file comfortable and easily, I used the Bash command nano README.md which opens a new tab to allow you to edit the README.md like you would a txt or MS word file. Also, when correcting a README.md file, I used mv README.md REEDME.md this works to rename a file or folder, then command cd .. works to go back one step from the current folder.

Preparing the Folder for Pushing to Github.

Now that we have our project folder all working and the files in place, we are ready to stage our folder for push to Github.

Run pwdto ensure you are in the right folder.
Then cd to the right folder.
Run ls command to ensure all files and folders are in place.
Next, we initialize git in the project folder by using bash command git init.
Then we stage everything in the project folder by using git add . command.
Next, we confirm if the staging worked by using command git status
Here we commit the message by using git command git commit -m "the message"
We confirm which Git branch we are in by running git branch
Here we link our Github with the Bash by using command git remote add origin 'the ssh link copied from github from the repository created
Then command git remote -v
Here we now push our project folder. This is done using command git push -u origin main Once this is done and all was done correctly, the folder will be pushed to Github, if any error is faced, ensure the project folder had data in it and that you configured git bash and linked to Github Correctly.

Conclusion

This is week two of Data Science and Analytics, I have a basic and solid understanding of Git Bash, I intend to push more on practice so that I can have this in my fingertips. I am excited of my journey, this is the beginning and I can't wait to delve deep all the way in the world of data.

Next, I will be talking about pushing local folder to Github using VS Code which is faster and somehow easier, but we do not do it easy, do we?
Learn more