DEV Community: sam manox

Power BI Technical Article: Data Modelling, Relationships & Joins

sam manox — Sun, 13 Sep 2026 11:13:18 +0000

Introduction

Power BI is a business intelligence tool that enables the conversion of raw data into reports and dashboards. However, the quality of the Power BI report is influenced by how the data is prepared and structured in advance.
Data modelling is the act of organizing the tables, columns, keys, and relationships in such a way that allows Power BI to process how the various data parts are connected. A correctly constructed model brings better performance of the report, eases calculations in DAX, minimizes redundancy of data, improves scalability, and simplifies understanding of the solution.
The article describes key modelling schemes, fact and dimension tables, relationships, direction of filters, and joins in Power Query. It also illustrates the difference between the functions of merging tables in Power Query and creating relationships in the Power BI model.

1. Data Modelling in Power BI

What is Data Modelling?

Data modelling in Power BI involves designing the structure of tables and defining how they relate to one another. Instead of treating every dataset as one large table, related information can be separated into logical tables and connected using keys.

For example, a sales system may contain:

Customer information
Product information
Date information
Location information
Sales transactions

These can be organized into a model where a central FactSales table connects to several descriptive dimension tables.

A good data model:

Improves report and query performance
Makes DAX measures easier to write
Reduces unnecessary data duplication
Makes relationships and filter propagation easier to understand
Allows the model to scale as more data is added
Makes reports easier to maintain
Reduces the risk of incorrect calculations

1.1 Flat Table

A flat table stores all information in one table. All data is kept in one single grid or file without links to other tables; it lacks relational database connections, which often leads to repeated or redundant information.

Example

Sale_ID	Date	Customer	Product	Category	City	Quantity	Sales
1001	01/09/2026	John	Laptop	Electronics	Nairobi	1	80,000
1002	02/09/2026	Mary	Mouse	Electronics	Mombasa	2	3,000
1003	03/09/2026	John	Keyboard	Electronics	Nairobi	1	4,000

Structure

FLAT TABLE ┌───────────────────────────────────────────┐ │ Sales │ ├───────────────────────────────────────────┤ │ Sale_ID │ │ Date │ │ Customer │ │ Product │ │ Category │ │ City │ │ Quantity │ │ Sales │ └───────────────────────────────────────────┘

Advantages

Simple to understand
Easy to import
Convenient for small datasets
Requires fewer relationships
Suitable for simple analysis

Disadvantages

Creates significant data duplication
Can increase model size
Changes to customer or product information may need to be repeated across many rows
Can make data maintenance difficult
Less suitable for large datasets

Appropriate Use

A flat table can be appropriate for a small, simple dataset where the data is already clean and there are relatively few repeated descriptive values.
For large business intelligence solutions, however, a flat table is generally less desirable than a properly designed dimensional model.

1.2 Star Schema

A star schema consists of a central fact table surrounded by dimension tables.

Structure


                       ┌───────────────┐
                       │  DimCustomer  │
                       │ CustomerID PK │
                       └───────┬───────┘
                               │ 1
                               │
                               │ *
┌───────────────┐        ┌────▼───────┐        ┌───────────────┐
│    DimDate    │───────►│ FactSales  │◄───────│  DimProduct   │
│ DateKey PK    │  1:*   │ SaleID     │  1:*   │ ProductID PK  │
└───────────────┘        │ CustomerID │        │ ProductName   │
                          │ ProductID │         │ Category      │
                          │ DateKey    │         └───────────────┘
                          │ Quantity   │
                          │ Sales      │
                          └─────▲──────┘
                               │
                               │ *
                       ┌───────┴───────┐
                       │  DimLocation  │
                       │ LocationID PK │
                       │ City          │
                       │ Region        │
                       └───────────────┘

The fact table sits at the center, while dimensions provide descriptive information; the fact table has columns that link it to the other tables.

Advantages

Excellent for Power BI reporting
Simple and intuitive structure
Generally provides good query performance
Makes DAX easier to understand
Minimizes unnecessary duplication
Makes filtering predictable
Easy to extend with additional dimensions

Disadvantages

Requires careful modelling
Dimension tables may contain some repeated information within their own hierarchy
Requires appropriate primary and foreign keys

Appropriate Use

The star schema is particularly appropriate for:

Sales reporting
Financial reporting
Inventory analysis
Customer analytics
Production reporting
Operational dashboards

It is generally the preferred structure for Power BI semantic models, sitting between raw database tables and end-user tools, and turning complex schemas and SQL into everyday concepts like "Revenue" or "Customer."

1.3 Snowflake Schema

A snowflake schema extends the star schema by dividing dimensions into related tables. For example, instead of storing Product, Category, and Department in one DimProduct table, you can separate them.

Structure

                         ┌───────────────┐
                         │  DimCategory  │
                         │ CategoryID PK │
                         │ CategoryName  │
                         └───────▲───────┘
                                 │ *
                                 │
                              1  │
                         ┌───────┴───────┐
                         │  DimProduct   │
                         │ ProductID PK  │
                         │ CategoryID FK │
                         │ ProductName   │
                         └───────┬───────┘
                                 │ *
                                 │
                              1  │
                         ┌───────▼───────┐
                         │   FactSales   │
                         │ ProductID FK  │
                         │ CustomerID FK │
                         │ SalesAmount   │
                         └───────────────┘

Advantages

Reduces duplication within dimensions
Useful for complex hierarchical data
Can represent normalized source systems more closely
Useful where dimensions are very large or have complex structures

Disadvantages

More tables and relationships
More complex filter paths
DAX and report development can become less intuitive
May require additional joins during analysis
More difficult for beginners to understand

Appropriate Use

Snowflake schemas can be appropriate where dimensions have complex hierarchies or substantial repeated data. However, unnecessarily snowflaking a Power BI model can add complexity without providing enough benefit.

Schema Comparison

Feature	Flat Table	Star Schema	Snowflake Schema
Number of tables	One	Multiple	Multiple
Simplicity	Very high	High	Lower
Data duplication	High	Low	Very low
Relationships	Minimal	Simple	More complex
DAX simplicity	Moderate	High	Lower
Scalability	Limited	Excellent	Excellent
Power BI suitability	Small models	Excellent	Selective
Maintenance	Difficult as data grows	Easy	More complex

2. Fact Tables and Dimension Tables

A dimensional model normally consists of fact tables and dimension tables.

Fact Table

A fact table records measurable business events. For example, FactSales may contain:

Sale_ID
Date_Key
Customer_ID
Product_ID
Location_ID
Quantity
Sales_Amount
Cost_Amount
Profit_Amount

Fact tables normally contain foreign keys linking them to dimensions and numeric values that can be aggregated. Examples include:

FactSales
FactOrders
FactTransactions
FactInventory
FactProduction

Dimension Table

A dimension table contains descriptive information used to analyze facts.

Descriptive Attributes: Stores qualitative data like names, categories, and locations (answering who, what, where, when)
Primary Key: Uses a unique identifier for each row to link with foreign keys in a fact table
Denormalized Structure: Built wide and flat with fewer rows than fact tables to speed up read performance

Examples include:

DimCustomer

CustomerID
CustomerName
CustomerType
Gender
City

DimProduct

ProductID
ProductName
Category
Brand
UnitPrice

DimDate

DateKey
Date
Month
Quarter
Year

DimLocation

LocationID
City
County
Region
Country

Facts answer questions such as: How many? How much? How often?

Grain or Granularity

The grain of a fact table describes exactly what one row represents.

For example, one row in FactSales might represent one product sold in one sales transaction. This distinction is extremely important:

If one row represents an individual product transaction, the table might have: SaleID | ProductID | CustomerID | Quantity | SalesAmount.
If one row represents an entire customer order, the grain is different.

A clearly defined grain prevents incorrect aggregation and double-counting.

Practical Star Schema Example

A retail company could use:

                  DimCustomer
                       │
                       │ 1:*
                       ▼
DimDate ───────► FactSales ◄────── DimProduct
                       ▲
                       │
                       │ 1:*
                       │
                  DimLocation

The central FactSales table stores transactions, while the dimensions provide context. For example, selecting Category = Electronics from DimProduct filters FactSales and allows the report to calculate total electronics sales.

3. Relationships in Power BI

A relationship defines how two tables are connected through common columns. For example:

DimCustomer.CustomerID
          │
          │ 1:*
          ▼
FactSales.CustomerID

CustomerID is unique in DimCustomer but can occur many times in FactSales. This allows Power BI to determine which sales belong to which customer; it acts like a link between the tables.

3.1 One-to-Many Relationship (1:*)

This is the most common relationship in a Power BI star schema. One record in the dimension can correspond to many records in the fact table.

Example

DimCustomer                 FactSales

CustomerID  1 ─────────── * CustomerID
1001                         1001
1002                         1001
1003                         1002

Customer 1001 can have many sales transactions.

When to Use

Use 1:* when:

The dimension contains unique values
The fact table contains repeated foreign-key values

This should normally be the default relationship in a star schema. In short: a single record in one table (the parent) can connect to multiple records in another table (the child), but each child record links back to only one parent.

3.2 One-to-One Relationship (1:1)

A one-to-one relationship means each record in one table corresponds to exactly one record in another table. It occurs when one record in a database table or entity connects to exactly one record in another table, and vice versa.

Example

EmployeeDetails             EmployeeSecurity

EmployeeID  1 ─────────── 1 EmployeeID

When to Use

It may be useful when information about the same entity has been intentionally split into two tables. However, a 1:1 relationship should not be used simply because tables can be joined — in many cases, combining the information into a single dimension is simpler.

3.3 Many-to-Many Relationship (:)

A many-to-many relationship occurs when multiple records in one table can relate to multiple records in another.

Example

Students * ───────── * Courses

A student can take many courses, and a course can have many students.

In dimensional modelling, many-to-many relationships should generally be handled carefully. A bridge table is often preferable:

DimStudent       BridgeStudentCourse       DimCourse
    1                  *   *                  1
    │──────────────────┘   └──────────────────│

The bridge table resolves the many-to-many association. In a standard star schema, a single row in a fact table links to only one row in a dimension table (a many-to-one relationship). However, real-world business processes often break this rule. A bridge table sits between the tables, containing pairs of foreign keys or group keys that map multiple dimension items to a single event or group.

Primary Keys and Foreign Keys

A primary key uniquely identifies a record. For example:

DimCustomer

CustomerID
1001
1002
1003

CustomerID must be unique.

A foreign key references the primary key of another table — a field in one table that points to the unique ID (primary key) in another table to connect them.

How It Works

The Link: It builds a bridge between two lists of data so the database knows they belong together.
The Rule: It acts like a security guard, stopping you from adding data to a table if it does not match a real ID in the connected table.

FactSales

CustomerID
1001
1001
1002
1003
1001

The same CustomerID can appear many times because a customer can make many purchases.

Referential Integrity

Referential integrity means that foreign-key values should correspond to valid records in the related dimension. For example, if FactSales contains CustomerID 1005 but DimCustomer does not contain CustomerID 1005, the model has a referential integrity problem.

Active and Inactive Relationships

An active relationship is normally used automatically when Power BI propagates filters between tables. An inactive relationship exists but is not used automatically.

For example, a FactSales table might contain both OrderDate and DeliveryDate. DimDate could have relationships to both columns, but normally only one relationship is active. DAX can explicitly use the inactive relationship when required, for example with USERELATIONSHIP().

4. Filter Direction

Filter direction determines how filters travel between related tables.

Single-Direction Filtering

In a typical star schema, filtering moves from the dimension to the fact.

DimProduct
    │
    │ Filter
    ▼
FactSales

Suppose a report user selects Product Category = Electronics. The filter travels from DimProduct to FactSales, so only sales associated with Electronics are included in the calculation. This is generally the preferred approach because it is predictable and reduces ambiguity; thus requires defining clear objectives, communicating openly, and documenting specific expectations.

Bidirectional Filtering

Bidirectional filtering allows filters to travel in both directions.

DimProduct
    ↕
FactSales

Although this can be useful in specific scenarios, it should not be enabled unnecessarily. Potential problems include:

Ambiguous filter paths
Unexpected results
Circular filtering paths
More complicated DAX behavior
Increased model complexity

Therefore, single-direction filtering should normally be preferred in a standard star schema, while bidirectional filtering should be used only when there is a clear modelling reason.

5. Joins in Power Query

Power Query is the data preparation and transformation component of Power BI. A join, performed using Merge Queries, combines information from two tables based on matching columns. For example:

Customers

CustomerID	CustomerName
1	John
2	Mary
3	Peter

Orders

OrderID	CustomerID	Amount
101	1	10,000
102	1	5,000
103	2	8,000
104	4	3,000

CustomerID is the matching column.

5.1 Left Outer Join

A Left Outer Join retains all records from the left table and matching records from the right table.

Customers LEFT JOIN Orders

CustomerID	CustomerName	OrderID	Amount
1	John	101	10,000
1	John	102	5,000
2	Mary	103	8,000
3	Peter	null	null

Customer 3 is retained even though there is no order.

Use Case: Useful when you want all customers and any orders associated with them.

5.2 Right Outer Join

A Right Outer Join retains all records from the right table and matching records from the left table.

Customers RIGHT JOIN Orders

CustomerID	CustomerName	OrderID	Amount
1	John	101	10,000
1	John	102	5,000
2	Mary	103	8,000
4	null	104	3,000

Order 104 is retained even though CustomerID 4 does not exist in Customers.

5.3 Full Outer Join

A Full Outer Join retains all records from both tables.

Customers FULL JOIN Orders

CustomerID	CustomerName	OrderID	Amount
1	John	101	10,000
1	John	102	5,000
2	Mary	103	8,000
3	Peter	null	null
4	null	104	3,000

This is useful when the objective is to identify both matched and unmatched records.

5.4 Inner Join

An Inner Join retains only records that have matching values in both tables.

CustomerID	CustomerName	OrderID	Amount
1	John	101	10,000
1	John	102	5,000
2	Mary	103	8,000

Customer 3 and Order 104 are excluded because they do not have matches on both sides.

Use Case: Useful when only matching records are required.

5.5 Left Anti Join

A Left Anti Join returns records from the left table that have no matching record in the right table.

Customers LEFT ANTI JOIN Orders

CustomerID	CustomerName
3	Peter

This can be used to identify customers who have never placed an order.

5.6 Right Anti Join

A Right Anti Join returns records from the right table that have no matching record in the left table.

OrderID	CustomerID	Amount
104	4	3,000

This identifies orders containing CustomerIDs that do not exist in the Customers table.

Join Summary

Join Type	Records Retained
Left Outer	All left + matching right
Right Outer	All right + matching left
Full Outer	All records from both
Inner	Only matching records
Left Anti	Unmatched left records
Right Anti	Unmatched right records

6. Power Query Joins vs Power BI Relationships

Although both concepts connect tables, they perform different functions.

Power Query Merge

A Power Query merge occurs during the data preparation stage. It combines columns from one query with another query based on matching values.

Customers
     +
Orders
     ↓
Merged Table

The result can contain: CustomerID, CustomerName, OrderID, OrderDate, Amount. A merge can therefore physically create a wider table containing columns from both sources.

Power BI Relationship

A relationship is created in the data model after data loading/transformation. It does not physically combine the tables.

DimCustomer
     │
     │ 1:*
     ▼
FactSales

The two tables remain separate. Power BI uses the relationship to propagate filters and evaluate calculations.

Key Differences

Power Query Merge	Power BI Relationship
Data preparation stage	Data modelling stage
Combines columns	Connects tables logically
Creates a resulting query/table	Tables remain separate
Used for transformation	Used for analysis
Can increase table width	Preserves dimensional structure
Similar to SQL JOIN	Similar to a logical model relationship

When should we merge like real ?

A merge may be appropriate when:

A column is genuinely required in the resulting table
The data is naturally one table after transformation
You need to clean or enrich a dataset before loading it
Combining the tables reduces unnecessary complexity

However, excessive merging can produce extremely wide tables with duplicated descriptive data. For example, repeatedly merging customer, product, location, and other descriptive information into FactSales can effectively recreate a flat table.

Why Keep Fact and Dimension Tables Separate?

Keeping them separate is normally preferable because:

It reduces redundancy
It makes the model easier to understand
It improves dimensional analysis
It supports reusable dimensions
It simplifies DAX
It allows dimensions to filter multiple fact tables
It makes the model easier to maintain and expand

For example:

                 DimCustomer
                      │
                      ▼
DimDate ────────► FactSales ◄──────── DimProduct
                      ▲
                      │
                      │
                 DimLocation

The same DimDate could also be used to analyze FactOrders, FactReturns, and FactInventory.

7. Recommended Power BI Model

For a typical business intelligence project, a star schema is recommended:

                       DimCustomer
                            │
                            │ 1:*
                            ▼
                       FactSales
                            ▲
                            │ 1:*
       ┌────────────────────┼────────────────────┐
       │                    │                    │
       │                    │                    │
   DimProduct            DimDate            DimLocation
       │                    │                    │
       └──────────── 1:* ───┴──── 1:* ──────────┘

More precisely, each dimension should normally have a one-to-many relationship to the fact table:

DimCustomer  1 ───────── * FactSales
DimProduct   1 ───────── * FactSales
DimDate      1 ───────── * FactSales
DimLocation  1 ───────── * FactSales

Recommended Design Principles

Use a star schema provides a good balance between performance, simplicity, and scalability. 2.Keep dimensions separate from facts reduces redundancy and makes the model easier to maintain.
Use 1:*\ relationships dimension tables should normally contain unique keys, while fact tables contain repeated foreign keys.
Prefer single-direction filtering filters should normally flow Dimension then Fact, reducing ambiguous filter paths and making report behaviour easier to predict.
Define the grain of every fact table before building measures, determine exactly what one row represents.
Use meaningful primary and foreign keys like DimProduct.ProductID and FactSales.ProductID.
Use bidirectional relationships carefully should only be introduced where the business requirement genuinely requires them.
Avoid unnecessary snowflaking snowflake design can be useful for complex dimensions, but a simpler star schema is generally easier to work with in Power BI.
Use Power Query for transformation cleaning, filtering, splitting, merging, and shaping data should generally happen before the data reaches the semantic model.
Use relationships for analysis once tables are appropriately prepared, relationships should connect the fact and dimension tables without unnecessarily physically merging them.

Conclusion

The key to effective Power BI reporting is having a well-constructed data model. Data modelling defines how Power BI interprets the data of the business, affecting various aspects of reporting such as performance, DAX calculations, filtering, and maintenance of models.

A flat table is easy to use, minimizing overhead costs when working with smaller datasets. However, as the data grows, dealing with flat tables can become problematic. A snowflake schema provides excellent normalization but adds additional tables and complexity in building relationships. In business intelligence applications, a star schema is considered the most efficient approach, balancing simplicity and performance.

Fact tables need to have a defined level of detail, while dimension tables should have the descriptive characteristics that allow understanding of the facts. Dimensions should have one-to-many relationships with facts, which have unique primary keys and repeating foreign keys.

The correct interpretation of relationship connectors is important to provide proper filter propagation, meaning that single relationships from dimensions to facts should generally be applied. Bidirectional relationships should be used only when necessary, as they can create confusion.

Finally, it is important to differentiate between the concepts of Power Query merging and Power BI relationships. For a typical business intelligence project, the recommended architecture is therefore:

       DIMENSIONS
            │
            │ 1:*
            ▼
       FACT TABLE
            │
            │
      DAX MEASURES
            │
            ▼
       POWER BI REPORT

This approach provides a model that is readable, scalable, efficient, and easier to maintain, while allowing users to analyze business performance from multiple perspectives.

Power BI Technical Article: Data Modelling, Relationships & Joins

sam manox — Sun, 13 Sep 2026 10:18:07 +0000

Introduction

1. Data Modelling in Power BI

What is Data Modelling?

Data modelling in Power BI involves designing the structure of tables and defining how they relate to one another. Instead of treating every dataset as one large table, related information can be separated into logical tables and connected using keys.
For example, a sales system may contain:

Customer information
Product information
Date information
Location information
Sales transactions
These can be organized into a model where Fact Sales is connected to several descriptive dimension tables.
A good data model is important because it has the following qualities:
Improves report and query performance.
Makes DAX measures easier to write.
Reduces unnecessary data duplication.
Makes relationships and filter propagation easier to understand.
Allows the model to scale as more data is added.
Makes reports easier to maintain.
Reduces the risk of incorrect calculations.

1.1 Flat Table

A flat table stores all information in one table. All data is kept in one single grid or file without links to other tables; it lacks relational database connections, which often leads to repeated or redundant information.
Example
Sale _ID Date Customer Product Category City Quantity Sales
1001 01/09/2026 John Laptop Electronics Nairobi 1 80,000
1002 02/09/2026 Mary Mouse Electronics Mombasa 2 3,000
1003 03/09/2026 John Keyboard Electronics Nairobi 1 4,000

Structure
FLAT TABLE
┌───────────────────────────────────────────┐
│ Sales │
├───────────────────────────────────────────┤
│ Sale_ID │
│ Date │
│ Customer │
│ Product │
│ Category │
│ City │
│ Quantity │
│ Sales │
└───────────────────────────────────────────┘
Advantages
• Simple to understand.
• Easy to import.
• Convenient for small datasets.
• Requires fewer relationships.
• Suitable for simple analysis.

Disadvantages
• Creates significant data duplication.
• Can increase model size.
• Changes to customer or product information may need to be repeated across many rows.
• Can make data maintenance difficult.
• Less suitable for large datasets.

JUMIA PRODUCT PERFORMANCE DASHBOARD ANALYSIS

sam manox — Sat, 05 Sep 2026 12:12:38 +0000

Introduction

This project analyzes a sample of product listings from Jumia, an e-commerce marketplace, to understand how price, discount depth, ratings, and customer engagement (review counts) relate to one another and to users- insights that a seller or marketplace team could use. The entire workflow data cleaning, threshold definition, flagging, ranking, correlation analysis, and visualization was built in Microsoft Excel, using table-based formulas, named ranges, quartile-based thresholds, and native charts.

What we need to consider is: Do price and discounting actually drive customer engagement and satisfaction?

Dataset Overview

Source: Product listings exported from Jumia (Kenya), covering household, electronics, and lifestyle categories.
Size: 115 products, 6 original columns: Product, Current price, old price, Discount, Review, Rating.
Price range: KSh 38 – KSh 3,750.
Discount range: 1% – 64%.
Data completeness: Price and discount data are complete for all 115 products. Review count and rating are missing for 58 of 115 products (50%); this gap is preserved and clearly labeled throughout the analysis rather than treated as zero.

Raw data issues found and corrected during cleaning:

Prices stored as text with currency symbols and thousands separators like "KSh 1,980".
One product listed as a price range rather than a single value like "KSh 1,620 - KSh 1,980".
Ratings stored as text "4.5 out of 5" instead of numeric values.
Review counts stored as negative numbers like -14, which appears to be a data extraction artifact rather than a true negative quantity.
Discount stored as text with a % sign.

Data Enrichment

Each raw data issue was resolved with a specific understanding of the data:

Field	Issue	Resolution
`Current price` / `old price`	Text with `KSh` and commas; one row was a price range	Stripped currency text and commas, converted to numeric; the single range row was resolved to its midpoint
`Ratingd`	Text like `"4.5 out of 5"`	Extracted the numeric portion, converted to a decimal number
`Discount`	Text with `%`	Stripped the `%` and converted to numeric
`Review`	Stored as negative integers	Derived a new `Engagement` field = `ABS(Review)`, since review counts cannot be negative; the true engagement measure is this absolute value

Threshold definition (quartile-based, not arbitrary):

All high/low/strong/weak categories in this project are defined using the first (Q1) and third (Q3) quartiles of the actual dataset, calculated with QUARTILE.INC, so the thresholds adapt to the data rather than relying on a fixed guess:

Metric	Q1	Q3
Price	KSh 500	KSh 1,663
Discount	27%	49%
Rating	3.0	4.6
Engagement (reviews)	5	14

Derived flags (each computed with an Excel formula referencing these named threshold cells):

Price Tier: Low / Medium / High
High Discount & Low Rating: Discount ≥ 49% AND Rating ≤ 3.0
High Discount & Low Engagement: Discount ≥ 49% AND Reviews ≤ 5
Many Reviews & Average Rating: Reviews ≥ 14 AND Rating between 3.0–4.6
Excellent Rating: Reviews ≥ 14 AND Rating ≥ 4.6

Every flag returns "Missing" instead of 0 or blank when the underlying rating or review data doesn't exist, so the dashboard never silently misrepresents a data gap as poor performance.

Key Insights

Demand is concentrated, not broad-based. Only 16 of 57 rated products (28%) meet the "Strong Engagement" threshold (≥14 reviews).
Discounting does not reliably drive demand. 13 of the highest-discount products (≥49%) still fall below the engagement threshold.
A handful of high-discount products carry real reputation risk. 6 products combine deep discounts with weak ratings (≤3.0); one of them also has the highest review count in the whole dataset.
Price, discount, and rating are essentially uncorrelated. All three Pearson correlations tested came out near zero (|r| < 0.15, R² < 0.02).
*The catalog has a solid base of "reliable, average" sellers. * 10 products combine strong engagement with a respectable but not exceptional rating, representing the best low-effort improvement opportunity.

Discount Analysis

Products were split into Low (≤27%), Medium (27–49%), and High (>49%) discount tiers using the quartile thresholds above.

Discount Tier	Avg. Reviews	Avg. Rating	Count
Low (≤27%)	16.7	4.19	13
Medium (27–49%)	12.8	3.89	33
High (>49%)	7.6	3.53	11

Contrary to what a common belief may suggest, sales spikes are not because of a discount pattern: the number of reviews for the least discounted products is more than double in comparison with the most discounted products, with their average rating also being higher. The Pearson correlation between discount and review count is equal to r=-0.14 and R²=0.02, which is a very weak negative correlation, indicating that it is consistent with the tier breakdown.

13 products sit in the "Promotion Inefficiency" flag (discount ≥49%, reviews <14), nearly half of the High discount tier. Six products combine a high discount with a low rating (≤3.0), the clearest quality-risk segment in the dataset.

Ratings and Reviews Analysis

Rating distribution across the 57 products with data:

Rating Category	Count	% of Rated Products
Low (≤3.0)	17	30%
Average (3.0–4.6)	26	46%
Excellent (>4.6)	14	25%

Average review count by rating band tells an important story: Average-rated products (16.0 avg. reviews) actually out-engage Excellent-rated products (8.0 avg. reviews). The correlation between rating and review count is essentially flat (r = 0.06), meaning higher-rated products are not systematically the most-reviewed ones on this platform. This suggests visibility and quality are currently decoupled, a real opportunity, since improving quality on already-visible "Average" products is likely a faster win than trying to generate visibility for untested high-quality products.

Price and rating also show no meaningful relationship (r = 0.11, R² = 0.01); higher-priced items are not rated meaningfully better or worse than lower-priced ones.

Products

A few individual products stand out from the ranked analysis:

Highest risk: 120W Cordless Vacuum Cleaner, the single highest review count in the dataset (69), paired with a weak 2.8 rating and a 49% discount. High visibility with a poor quality signal makes this the top candidate for a quality/fulfillment investigation.
Most expensive: 32PCS Portable Cordless Drill Set KSh 3,750, the top of the Price tier, but with very limited engagement (5 reviews), raising a visibility question rather than a quality one. -Strong performers (Strong Engagement + Excellent Rating):6 products meet both criteria, the smallest, most valuable segment in the catalog, and the best candidates for promotional placement since they carry the lowest risk of disappointing a new customer.
Reliable, improvable sellers: 10 products combine high engagement with an "Average" rating and proven demand, with room to move into the "Excellent" tier through targeted quality or listing improvements. Below is a representation of the dashboard used to show visuals:

Conclusion

This study has revealed that there is no significant linear correlation between price, discount depth, customer rating, or customer reviews on the 115 Jumia products studied. The data does not support the premise that deeper discounts create more attraction among customers or that higher prices represent better quality. However, this dataset reveals two specific patterns that can be put into real use:

A small set of high-discount, low-rating products (6 items), most notably a cordless vacuum cleaner with the platform's highest review count, represents a concentrated reputation risk that deserves direct quality investigation rather than further discounting.
A larger set of high-engagement, average-rated products (10 items) represents the most efficient improvement opportunity in the catalog: these products already have proven demand, so incremental quality or listing improvements are likely to yield a better return than trying to generate new demand from scratch or discounting further.

Recommended next steps

Begin investigating the high discount/low rating items concerning their quality, fulfillment, or listing accuracy. The highest review count is the first to be worked on.
The 13 "promotion inefficiency" items should be audited for listing content and targeting before any further discounts are put in place.
Proceed with making any extra quality or content modifications for the 10 high-engagement and average-rating products.
Try to push for an entire collection to be available for the review & rating to be done, as half the catalog is currently invisible to this analysis.
Price & discount should be thought of as secondary tactics, as they do not seem to be efficient forms of measuring engagement/rating level in this research. Need to make any extra quality or content modifications for the 10 high-engagement and average-rating products.

Limitations:
The dataset does not include sales volume, conversion rate, listing age, product category, or complaints and returns. The correlations mentioned above are drawn from a single data collection and must be validated through experiments, that is, by conducting experiments on controlled discounts. However, it must be understood that only 50% of products have existing data on ratings and reviews; the others are missing.

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

sam manox — Sat, 29 Aug 2026 05:26:14 +0000

Introduction

Microsoft Excel is the most popular tool for data work and analysis. Its user-friendly environment makes it easy to understand, organize, clean, and explore data.
For a data analyst or scientist, knowing how to work with Excel goes beyond entering information into cells. Excel can be used to inspect datasets, identify errors, standardize values, filter records, sort information, remove duplicates, and prepare structured data for further analysis; in other words, clean data to draw meaningful insights from it.
This article demonstrates the key Excel concepts covered during Week 1 from the Human Resources dataset. The dataset contains employee information such as employee ID, department, salary, hire date, age, gender, performance score, employment type, office location, project count, training hours, and manager feedback score.
The original dataset contains 876 employee records and 21 columns. It is intentionally named "dirty", meaning that it contains data that an analyst should be able to clean to be able to use for insights and analysis.

1. Understanding the Excel Environment

The first step in working with Excel for analytics is understanding how information is organized.
An Excel worksheet consists of:

Rows, which normally represent individual observations or records.

Columns, which represent variables or attributes.

Cells, which contain individual values.

Worksheets, which allow related tables to be organized within the same workbook.

In the HR dataset, one row represents an employee record while each column represents an employee attribute.
For example, the columns include:

Column	Description
Employee ID	Unique identifier for an employee
First Name	Employee's first name
Last Name	Employee's surname

Understanding the dataset structure matters because it determines what types of analysis you can perform later.

2. Inspecting the Dataset before Cleaning

A common mistake we make when working with data is changing values immediately without first understanding the dataset. The first step should always be data profiling for a data analyst. I inspected the HR dataset to determine its size, column types, missing values, duplicate records, and inconsistent entries by using the filter function.

The dataset contains 876 rows and 21 columns. It also contains missing values across several fields. In total, there are approximately 291 missing cells.

For example, the Employee ID column contains missing values, while fields such as Hire Date, Gender, Bonus, Department, and Annual Training Hours also contain incomplete records. For example, some department names may appear as "HR", "H.R", "Human Resources", or "Human Resource". Although these values refer to the same department, Excel treats them as different text values. Similarly, office locations contain variations such as_ "London", "Londn", "San Francisco", "San Fransisco", and "SF". These needed cleaning for the department; I retained “HR” and sorted the location, for example, _“Londn” to “London”.

3. Data Types in Excel

Correct data types are essential for analysis.
Common Excel data types include:

Text
Numbers
Dates
Currency
Percentages
Logical values such as TRUE and FALSE

For example, Salary should be stored as a number rather than text because we may need to calculate average salary, total payroll, minimum salary, maximum salary, Sumif, Sumifs, and Averageifs.

Hire Date should be stored as an actual date so that we can calculate employee tenure or group employees by year.
The HR dataset contains several examples of inconsistent data types.
For instance, the Work Experience column contains numeric values such as 28, but some records contain values such as "5 years". Project Count is mostly numeric, but at least one value appears as "ten". The Bonus column also contains values such as 5575.99 and text values such as KES 1877.71
These values need to be converted into a consistent numeric format before mathematical calculations can be performed reliably.

4. Sorting and Filtering Data

Sorting and filtering are two of the most basic but useful Excel features for analysts.

Sorting

Sorting allows records to be arranged according to a particular field.
For example, I could sort the HR dataset by:

Salary from highest to lowest
Hire Date from oldest to newest
Performance Score from highest to lowest
Annual Training Hours from highest to lowest

If I want to identify employees receiving the highest salaries, I can sort the Salary column in descending order.

Filtering

Filtering allows specific records to be displayed while hiding records that do not meet a particular condition.

For example, I could filter:
Department = Finance to view only Finance employees. I could also filter: Employee Type = Permanent or: Remote Work Status = Fully Remote

Filtering is particularly useful when investigating data-quality problems because it allows an analyst to isolate values that don’t make sense.

For example, filtering the Department column makes it easy to identify variations such as "H.R", "Human Resource", "Human Resources", and "Hr".

5. Identifying Missing Values

Missing data is one of the most common problems in analytics, and understanding why it is missing. For instance, a blank cell does not automatically mean that the information is zero. It could mean that the information was not collected, was unavailable, or was accidentally omitted.
In the HR dataset, missing values appear in several columns.
For example:

Employee ID has missing values.
First Name and Last Name contain missing values.
Salary contains missing values.
Hire Date contains missing values.
Gender contains missing values.
Bonus contains missing values.
Training Hours contains missing values.

Manager Feedback Score contains missing values.
Excel provides several ways to identify missing values. A simple method is filtering a column and selecting Blanks or using the COUNTBLANK function:

=COUNTBLANK(A2:A877)

This counts the number of blank cells in the specified range.
For more complex analysis, conditional formatting can also be used to visually highlight blank cells.

It is important not to automatically replace every missing value with zero. The correct treatment depends on the meaning of the variable. For example, a missing bonus may mean the employee received no bonus, but it could also mean that the bonus information was not recorded. The analyst should understand the business context before deciding how to treat it.

6.Converting Text into Numerical Values

Textual representations of numbers pose a challenge when analyzing data. For example, the Bonus column has records like:
KES 1877.71. Excel processes this as a number only when the currency text is removed. We use this formula:

=VALUE(SUBSTITUTE(K2,"KES ",""))

With SUBSTITUTE deleting "KES" and VALUE converting the remaining text into a number.
For example, if the original record is:

KES 1877.71

the result will be:

1877.71
The same applies to the Work Experience column too. In this case, if you have 5 years, the numerical part is extracted and converted. This is important since I might need to calculate average work experience later on.

6. Simple Excel Calculations for Analysis

Once the data has been cleaned, Excel can be applied to perform productive business calculations.
For example, total salary can be computed as follows:

=SUM(E2:E870)

Average salary can be established as follows:

=AVERAGE(E2:E870)

Maximum salary can be obtained as follows:

=MAX(E2:E877)

Minimum salary can be derived as follows:

=MIN(E2:E877)

The number of employees can be obtained as follows:

=COUNTA(A2:A877)

Conditional calculations may be implemented. For example, total salary could be computed for the employees of Finance:

=SUMIF(D:D,"Finance",E:E)

To determine how many employees work in a specific department, A COUNTIF formula may be used:

=COUNTIF(D:D,"Finance")

These formulas illustrate how cleaning is closely connected to the process of analysis. If the Department column is not standardized, the results of those calculations will be unreliable, and analysis won't make any sense.

Conclusion

Insights gained from the dataset are that, from the Interaction with the HR dataset, raw data hardly ever comes in a perfect structure. Even in a straightforward dataset of employees, one may have missing values, misspellings, inconsistent capitalization, repeated entries, and information that comes in various formats. Data cleaning involves decision-making. While Excel can help in automatically detecting errors, it is up to the human analyst to determine what the proper value is. Even though the value appears as a number, it may be stored as a text string, causing some formulas and analyses to fail.

Excel is a good tool for learning this process because it shows you the data issues and offers solutions on how to deal with them. Thus, developing proficient skills in using Excel for data cleaning is an important step to being a good data analyst.

Understanding the Git Workflow: Working Directory, Staging, Commit, and Push

sam manox — Sun, 23 Aug 2026 05:51:48 +0000

The first time we were introduced to Git, we really didn't know where our code was going to be saved until I learned that Git is part of a control system. It keeps track of changes made to files in a project so you can: track what changed, see previous versions of what you were working on to undo mistakes, work on different features, collaborate with others on the code or project we were working on, and back up projects to GitHub. Once you have added the knowledge of the stages of a Git workflow, then you have total control of your project from the previous history to the current workspace.
During our learning, we were able to cover four stages of a Git workflow: working directory, staging, committing to the local repository, and pushing to the remote repository.

Working Directory

This is our working directory where we type our code, a folder where our code lives. When opening a file in the code editor and making changes, we are working in the Working Directory; at this moment Git is aware that something has changed, but it hasn't recorded anything permanently yet. If we check the status by writing a command like git status, it will show the files that have been modified or deleted. Here, the changes are tracked but not yet saved.
Suppose we edited a file here, and we check the status. Git sees the changes, but they are unstaged. For example
SQL CODE like;

select customer_id, product_id 
            From Customers
Then we make a change to this code; let's say 
        Select customer_id, product_id,
            Sum (sales_amount) AS total_amount
            From customers 
            Group by customer_id

At this point, the code has changed in your code editor but no permanent version of this on Git, so this is the working directory. The changes haven’t been committed; it tells you I can see the changes, but you haven’t told me to include them in Git.

Staging area

This is the preparation stage for me, like telling Git to take this screenshot and prepare it for a more committed state. Include these changes in my next commit. If we were working on a data analysis project, you would tell Git to bash.

</>Bash
Add sql/analysis.sql

Then check the status

</>Bash
git status

The result will be

changes to be committed:
    Modified: sql/analysis.sql

The file is now staged, and Git lets you choose which changes to include in your next commit.
We move changes from the Working Directory into the Staging Area using:
git add <filename>
Or, to stage everything that has changed:
git add

Staging gives a chance to know what to commit at each stage, making it clear and clean, and it is a preview stage.

The commit (Local Repository)

Now, since our changes have been staged, we save them permanently to project history using the commit command.
git commit -m "A clear, descriptive message about what changed"
like; </> bash

git commit -m "Added customer sales analysis"

This creates a commit that is a snapshot of the changes that you have made and is stored in the local repository. We also know that the commit at this stage has a unique ID, the author's email and name, a commit message, and a timestamp, including the reference to the previous commit.
The commits at this stage are stored locally; no one can access them, and they are a permanent safe place where you can come back to refer to if you make any errors at a given point.

The Push (Remote Repository)

Everything that we have been doing from the beginning to this point has just been happening on our computers or on our code editor platform; to share this information with our team, like we have been working on the health report in Kenya, and we have been cleaning that data, and we want to share those changes we have made, we have to push the commits to a Remote Repository like GitHub or GitLab. This can be achieved through;
git push origin main
Here, origin is the default name Git gives to the remote repository you added manually, and main is the branch you're pushing to.
Once the changes have been pushed, the commit is available for access by anyone with access to your local repository. Now your work has been shared with your teammates. It is always a good habit to check for updates before pushing your commit.

These stages are more important as a beginner; I used to ask why you just save directly, as Google Docs does, then I realized that software projects sometimes are more complex. Git stages give you control over what to commit at each stage; you can make changes to any errors you may have before committing them or saving them permanently, and you can understand each stage of your project or code history. Without forgetting, pushing and pulling help you collaborate with others, making work easier.
Work you edit your file normally git add <file> you make changes to be included. git commit -m "message" saves permanently git push origin <branch> uploads commits to be shared git status shows what changed, staged.

Each stage exists for a specific reason.