DEV Community: Antonina Wambui

Turning Vehicle Sales Data into Actionable Business Intelligence: A Power BI Analysis of JCars Logistics

Antonina Wambui — Wed, 30 Sep 2026 14:05:58 +0000

1. Introduction

Business data is only useful when it can support better decisions.

For JCars Logistics, vehicle sales records contain information about customers, vehicles, branches, payments, deliveries, revenue and costs. However, the original dataset contained inconsistencies that could affect the reliability of analysis.

This project uses Power BI to transform the raw vehicle-sales data into a structured business intelligence solution.

The analysis focuses on four main areas:

Sales performance
Financial performance
Operational efficiency
Customer and market behaviour

Rather than looking only at total sales, the project investigates where revenue is coming from, where costs are affecting profitability, how branches are performing, and which transactions require further attention.

Project Objective:
To transform raw vehicle sales data into an interactive Power BI solution that helps management understand performance, identify operational issues and make data-informed decisions.

2. Understanding the Dataset

The original dataset contained 276 transaction records and 32 columns.

Each row represents one vehicle sold in one sales transaction.

The dataset contained information from several areas of the business:

Business Area	Examples
Transactions	Order ID, Order Date, Delivery Date
Customers	Customer Name, Customer Type, Age
Vehicles	Make, Model, Vehicle Type, Fuel Type
Geography	Region, County, City, Branch
Sales	Sales Representative, Lead Source
Finance	Selling Price, Cost, Discount, Revenue
Operations	Delivery, Logistics, Payment
Customer Experience	Rating, Reviews

The transaction-level grain was retained during the transformation process.

3. Data Quality Assessment

Before creating the Power BI model, the dataset was assessed for data-quality problems.

Several issues were identified, including:

Inconsistent Order ID formats
Multiple date formats
Invalid customer ages
Multiple currencies
Inconsistent category names
Incorrect or questionable zero values
Invalid vehicle years
Inconsistent discount formats
Invalid customer ratings
Branch and yard naming differences
Spelling errors in sales representative names

For example, customer ages included unrealistic values such as 0, 5, 121 and -5.

The dataset also contained financial values in KES, USD, EUR and ZAR, making currency standardisation necessary before financial analysis.

4. Data Cleaning Approach

The cleaning process was based on the business meaning of each field rather than applying one rule to every column.

Examples of Cleaning Decisions

Data Issue	Action Taken
Inconsistent Order IDs	Standardised the identifier format
Invalid dates	Converted valid dates and changed unrecoverable values to null
Unrealistic ages	Changed unreliable values to null
Multiple currencies	Converted monetary values to KES
Category inconsistencies	Standardised spelling, casing and abbreviations
Invalid vehicle years	Corrected values where evidence existed; otherwise used null
Invalid discounts	Unreliable values were changed to null
Invalid ratings	Converted valid text formats and removed unreliable values
Sales representative spelling errors	Matched names against the available representative list

The objective was not simply to make the dataset look clean, but to make the cleaned values suitable for analysis.

5. Currency Standardisation

All monetary values were converted to Kenya Shillings (KES).

The exchange rates used were:

Currency	Rate to KES
USD	129.54
EUR	147.84
ZAR	7.93

The corrupted ? currency symbol was investigated by comparing the affected values with related financial information. The values were treated as USD where the surrounding evidence supported that interpretation.

Example Power Query Function

fnMoneyKES = (v as any) as nullable number =>
    let
        raw = Text.Trim(Text.From(v) ?? ""),
        upperRaw = Text.Upper(raw),

        currency =
            if Text.Contains(upperRaw, "KES") 
                or Text.Contains(upperRaw, "KSH") then "KES"

            else if Text.Contains(raw, "$") 
                or Text.Contains(upperRaw, "USD") then "USD"

            else if Text.Contains(upperRaw, "EUR") then "EUR"

            else if Text.Contains(upperRaw, "ZAR") 
                or Text.StartsWith(Text.Trim(raw), "R ") then "ZAR"

            else if Text.Contains(raw, "?") then "USD"

            else "KES",

        numText = Text.Select(raw, {"0".."9", "."}),
        numVal = try Number.From(numText) otherwise null

    in
        if numVal = null then null
        else if currency = "USD" then numVal * 129.54
        else if currency = "EUR" then numVal * 147.84
        else if currency = "ZAR" then numVal * 7.93
        else numVal

6. Data Validation

After cleaning, additional checks were performed to identify inconsistencies that could affect the analysis.

One important validation involved comparing:

Payment Status
       ↓
Delivery Status

This identified 14 inconsistent records:

10 records were marked as Paid but had Delivery Cancelled
4 records were marked as Payment Cancelled but had Delivered

These records were not automatically changed because there was insufficient evidence to determine which field was incorrect.

Instead, they were flagged for further investigation.

Other validation checks included:

Comparing Car Model with Vehicle Type
Checking Branch and City relationships
Checking Sales Representative and Branch relationships
Investigating missing Vehicle Type values
Reviewing financial values across related columns

7. Data Model

The cleaned flat table was transformed into a Star Schema.

The model contains:

Fact Table

FactSales

Dimension Tables

DimDate
DimCustomer
DimVehicle
DimLocation
DimSalesRep
DimPayment
DimLeadSource
DimDeliveryStatus

8. Model Structure

                  DimDate
                     |
                     |
DimCustomer ---- FactSales ---- DimVehicle
                     |
                     |
               DimLocation
                     |
                     |
               DimSalesRep
                     |
                     |
                DimPayment
                     |
                     |
              DimLeadSource
                     |
                     |
           DimDeliveryStatus

The relationships were configured mainly as:

Dimension [1] ───────── [*] FactSales

where [1] represents the one side and [*] represents the many side.

9. FactSales Table

The FactSales table contains transaction-level information.

Column	Role
OrderID	Transaction identifier
OrderDate	Transaction date
DeliveryDate	Delivery date
CustomerID	Foreign Key
VehicleID	Foreign Key
LocationID	Foreign Key
SalesRepID	Foreign Key
PaymentID	Foreign Key
LeadSourceID	Foreign Key
DeliveryStatusID	Foreign Key
Returned	Transaction attribute
UnitsSold	Measure
UnitSellingPrice	Measure
UnitCost	Measure
DiscountPct	Measure
DeliveryFee	Measure
LogisticsCost	Measure
RevenueRecorded	Measure
CustomerRating	Measure
ReviewCount	Measure

10. Dimension Tables

Dimension	Primary Key	Main Attributes
`DimDate`	Date	Year, Quarter, Month, MonthYear
`DimCustomer`	CustomerID	Customer Name, Type, Age
`DimVehicle`	VehicleID	Make, Model, Type, Year, Fuel
`DimLocation`	LocationID	Region, County, City, Branch
`DimSalesRep`	SalesRepID	Sales Representative
`DimPayment`	PaymentID	Payment Method, Status
`DimLeadSource`	LeadSourceID	Lead Source
`DimDeliveryStatus`	DeliveryStatusID	Delivery Status

11. Customer Data Limitation

The source dataset did not contain a unique customer identifier.

Therefore, the customer dimension was created using a combination of:

Customer Name
      +
Customer Type
      +
Customer Age

This means that customer-level findings should be interpreted carefully because the dataset does not provide a verified master customer list.

12. DAX Measures

Several DAX measures were created to support the analysis.

Total Revenue

Total Revenue =
SUM(FactSales[RevenueRecorded])

Total Gross Profit

Total Gross Profit =
SUMX(
    FactSales,
    FactSales[RevenueRecorded]
        - (FactSales[UnitsSold] * FactSales[UnitCost])
)

Gross Profit Margin

Gross Profit Margin % =
DIVIDE(
    [Total Gross Profit],
    [Total Revenue]
)

Return Rate

Return Rate % =
DIVIDE(
    CALCULATE(
        COUNTROWS(FactSales),
        FactSales[Returned] = "Yes"
    ),
    COUNTROWS(FactSales)
)

Logistics Cost Percentage

Logistics Cost % of Revenue =
DIVIDE(
    [Total Logistics Cost],
    [Total Revenue]
)

13. Business Questions

Instead of designing the dashboard around visuals first, the analysis was driven by specific business questions.

Question 1

Which vehicle categories generate high revenue but weak profitability?

Question 2

Which regions and branches contribute the most revenue?

Question 3

Is there an observable relationship between customer ratings and transaction value?

Question 4

Which locations experience higher delivery times or logistics costs?

Question 5

Which transactions contain conflicting payment and delivery information?

Question 6

How concentrated is revenue among high-value customers?

14. Dashboard Structure

The Power BI report was divided into five analytical pages.

Page 1 — Business Overview

The first page provides a high-level summary of the business.

Key visuals

Total Revenue
Gross Profit
Gross Profit Margin
Return Rate
Revenue Trend
Revenue by Region
Revenue by Vehicle Make

Main question

How is the business performing overall?

Page 2 — Product & Sales Performance

This page focuses on vehicle sales.

Analysis includes

Vehicle Make
Vehicle Model
Vehicle Type
Units Sold
Revenue
Gross Profit
Gross Profit Margin

Main question

What are we selling, and are those sales profitable?

Page 3 — Regional & Branch Analysis

This page examines geographical performance.

Hierarchy

Region
   ↓
County
   ↓
City
   ↓
Branch

Metrics

Revenue
Gross Profit
Logistics Cost
Delivery Time
Units Sold

Main question

Where is the business performing well, and where are operational issues appearing?

Page 4 — Customers & Sales Channels

This page focuses on customer and sales performance.

Analysis includes

Customer Revenue
Customer Ratings
Lead Sources
Sales Representatives
Payment Methods
Customer Type

Main question

Who is generating value and how are customers reaching the business?

Page 5 — Operations & Exceptions

This page focuses on records that require further investigation.

Analysis includes

Payment/Delivery mismatches
Delivery duration
Logistics Cost %
Returned vehicles
Cancelled transactions

Main question

Which operational records require management attention?

15. Key Findings

Revenue Concentration

The cleaned dataset recorded approximately:

KSh 1.48 Billion in total revenue

Toyota contributed the largest share among vehicle makes.

Rift Valley and Central were also major contributors to regional revenue.

This indicates that overall revenue is influenced by both vehicle demand and regional performance.

Customer Revenue Concentration

The top 10 customers generated approximately:

KSh 299.3 Million

This represents approximately 20% of total revenue.

This provides an opportunity to monitor high-value customers separately from the wider customer base.

Revenue vs Profitability

High revenue did not always translate into positive profitability.

Vehicle Type	Gross Profit Margin
SUV	8%
Sedan	-9%
Crossover	-6%
Van	-28%
Truck	-66%

SUVs generated the highest revenue among the vehicle types at approximately KSh 845 Million, while some categories recorded negative margins.

These results should be interpreted alongside the data-quality limitations identified during cleaning.

16. Delivery Performance

The analysis identified a noticeable difference in average delivery time.

Metric	Days
Nairobi Average Delivery Time	26.29
Overall Average Delivery Time	15.18

Nairobi therefore requires additional operational investigation, particularly when delivery time is considered alongside logistics costs.

17. Exception Analysis

One of the most important findings was the presence of conflicting payment and delivery statuses.

14 Inconsistent Transactions
│
├── 10 → Paid + Delivery Cancelled
│
└── 4 → Payment Cancelled + Delivered

These records were not changed automatically.

Instead, they were flagged for investigation because the available information did not establish which status was incorrect.

18. Vehicle Profitability Concerns

Several vehicle categories and makes showed negative margins.

Examples identified in the analysis include:

Vehicle Make	Gross Profit Margin
BMW	-37%
Isuzu	-31%

The results should be investigated against:

Unit Cost
Selling Price
Currency Conversion
Discounts
Missing Financial Values

This is important because data-quality issues can influence profitability calculations.

19. Management Focus Areas

Based on the analysis, several areas deserve further investigation.

1. Vehicle Pricing

Review vehicle categories recording negative margins to understand whether acquisition costs, discounts or selling prices are contributing to the results.

2. Payment and Delivery Reconciliation

Review the 14 inconsistent transactions against the original business records.

3. Nairobi Delivery Operations

Investigate the reasons behind the relatively long average delivery time and associated logistics costs.

4. High-Value Customers

Monitor the contribution of high-value customers to understand customer concentration.

5. Data Collection

Improve future data capture by introducing:

Unique Customer IDs
Standardised currencies
Validated dates
Controlled category fields
Consistent payment and delivery statuses

20. Project Limitations

The analysis has several limitations.

Customer Identification

There was no unique customer identifier in the source data.

Missing Financial Information

Some Unit Cost and Unit Selling Price values could not be reliably recovered.

Conflicting Operational Records

Some payment and delivery statuses were inconsistent.

Source Dataset

The analysis represents the information available in the supplied dataset and may not capture every factor affecting actual business performance.

21. Lessons Learned

This project reinforced an important principle:

Data cleaning is part of data analysis, not a separate task.

A value cannot always be judged as incorrect simply because it looks unusual.

Sometimes another column provides the context needed to understand it.

For example:

Unclear Currency
       ↓
Compare Financial Fields
       ↓
Determine Most Supported Interpretation

Another example:

Vehicle Type Missing
       ↓
Check Car Model
       ↓
Compare With Similar Records
       ↓
Determine Whether Inference Is Supported

And:

Payment Status
       ↓
Compare With Delivery Status
       ↓
Identify Possible Exceptions

The project therefore highlighted the importance of making cleaning decisions based on evidence.

When there was not enough evidence to confidently correct a value, it was safer to use null or flag the record for investigation.

22. Conclusion

The JCars Logistics dataset provided an opportunity to examine the business from several perspectives.

Using Power Query, Power BI data modelling, DAX and interactive visualisations, the raw transaction data was transformed into a structured analytical solution.

The final report connects:

Sales
  ↓
Revenue
  ↓
Costs
  ↓
Profitability
  ↓
Operations
  ↓
Customer Experience

The project demonstrates how Power BI can transform raw business records into an interactive decision-support tool.

More importantly, it shows that meaningful analytics depends not only on creating attractive dashboards, but also on understanding the data, documenting assumptions, validating relationships and questioning unusual results.

Github Repository

Understanding Data Modelling, Relationships and Joins in Power BI

Antonina Wambui — Thu, 24 Sep 2026 08:55:49 +0000

Introduction

When working with Power BI, importing data is only the beginning. The real challenge comes when you have multiple tables and need to make sure they work together correctly.

This is where data modelling, relationships, and joins become important.

For example, imagine a retail company that wants to analyse its sales. It may have information about customers, products, dates, locations, and individual sales transactions stored in different tables.

If these tables are not structured properly, reports can become difficult to build and DAX calculations may produce unexpected results.

In this articles, I will explain how data modelling works in Power BI, the different modelling schemas, relationship and cardinality, filter directions, and the different types of joins available in Power Query.

1. What is Data Modelling in Power BI?

Data Modelling is the process of organising tables, columns, and relationships so that data can be analysed correctly in Power BI.

Instead of putting every piece of information into one large table, we can separate the data into tables based on their purpose and then connect them using relationships.

A good data model is important because it can help with:

Better report performance
Simpler DAX calculations
Easier filtering
Reduced data redundancy
Easier maintenance
Scalability as the amount of data increases
Better understanding of the report structure

For example, a sales model might contain:

FactSales
DimCustomer
DimProduct
DimDate
DimLocation

These tables can then be connected using keys.

2. Data Modelling Approaches
There are several ways of structuring data.
Three common approaches are flat table, star schema, and snowflake schema

Flat Table
A flat table keeps most or all information in a single table.

For example:

SalesID	Customers	Product	Category	Location	Date	Quantity	Revenue
S001	Amina	Maize Flour	Food	Nairobi	10/09/26	2	300
S002	Brian	Cooking Oil	Food	Mombasa	10/09/26	1	250
S003	Carol	Soap	Household	Nairobi	11/09/26	3	450

┌─────────────────────────────┐
│         Sales Table         │
├─────────────────────────────┤
│ SaleID                      │
│ Customer                    │
│ Product                     │
│ Category                    │
│ Location                    │
│ Date                        │
│ Quantity                    │
│ Revenue                     │
└─────────────────────────────┘

Advantages

Simple to understand
Easy to create
Useful for small datasets
Requires fewer relationships

Disadvantages

Repeated information creates data redundancy
The table can become very large
Changes to descriptive information may need to be made in many rows
Can make the model harder to maintain
It may not be ideal for large analytical solutions.

A flat table can be useful for a small project or simple anaylsis, especially when the dataset does not contain many different entities.

Star Schema
A star schema separates business transactions from descriptive information.

The central table is normally a fact table, while the surrounding tables are dimension tables

For example:

                 ┌───────────────┐
                 │ DimCustomer   │
                 └───────┬───────┘
                         │
                         ▼
┌───────────────┐  ┌───────────────┐  ┌───────────────┐
│  DimProduct   │──│   FactSales   │──│    DimDate    │
└───────────────┘  └───────┬───────┘  └───────────────┘
                           │
                           ▼
                    ┌───────────────┐
                    │  DimLocation  │
                    └───────────────┘

The fact table stores events or transactions, while the dimension tables provide context about those events.

Advantages

Easy to understand
Works well with Power BI
Usually produces simpler DAX
Makes filtering easier
Reduces unnecessary repetition
Easier to maintain and expand

Disadvantages

Requires relationships between tables
More tables can initially seem complicated
Poorly designed relationships can cause incorrect results

For many Power BI reporting projects, a star schema is a practical way of organising analytical data.

Snowflake schema
A snowflake schema is similar to a star schema, but some dimension tables are further divided into related tables

For example, instead of storing the category directly in DimProduct, we could create a separate DimCategory table.

                 ┌─────────────────┐
                 │   DimCategory   │
                 └────────┬────────┘
                          │
                          ▼
                 ┌─────────────────┐
                 │   DimProduct    │
                 └────────┬────────┘
                          │
                          ▼
                 ┌─────────────────┐
                 │    FactSales    │
                 └─────────────────┘

Advantages

Reduces repeated information
Can be useful when dimensions have multiple levels
Can represent complex organizational structures

Disadvantages

More tables and relationships
More complex model
Can make DAX and filtering harder to understand
More joins/relationships may be required

A snowflake schema can be useful when the source data naturally contains hierarchical dimensions, but unnecessary splitting can add complexity

3. Fact Tables and Dimension Tables
Understanding fact and dimension tables is one of the most important parts of data modelling.

Fact Table
A fact table normally contains business events or transactions.

For a retail company, FactSales could contain:

SaleID	CustomerID	ProductID	DateID	Quantity	Revenue
S001	C001	P001	D001	2	300
S002	C002	P002	D001	1	250
S003	C001	P003	D002	3	450

The table contains numerical values that can be used for calculations, such as:

Quantity
Revenue
Cost
Profit
Discount

What is grain?
The grain tells us what one row in the fact table represents.

For example:
One row in the FactSales represents one product sold in one sales transaction.

Defining grain before creating a model is important because it helps determine what information belongs in the fact table.

Dimension Table
Dimension tables contain descriptive information that gives context to the facts.

For example:
DimCustomer

CustomerID	CustomerName	City
C001	Amina	Nairobi
C002	Brian	Mombasa

DimProduct

ProductID	ProductName	Category
P001	Maize Flour	Food
P002	Cooking Oil	Food

DimDate

DateID	Date	Month	Year
D001	10/09/26	September	2026
D002	11/09/26	September	2026

Dimension tables answer questions such as:

Who brought the product?
What product was sold?
When was it sold?
Where did the sale happen?

The fact table tells us what happened, while dimensions help explain who, what, when, and where.

4. Relationships in Power BI
A relationship connects tables through a common column.

For example, CustomerID can connect DimCustomer to FactSales.

Dimension Table	Key	Fact Table	Foreign Key	Relationship
DimCustomer	CustomerID	FactSales	CustomerID	1:*
DimProduct	ProductID	FactSales	ProductID	1:*
DimDate	DateID	FactSales	DateID	1:*
DimLocation	LocationID	FactSales	LocationID	1:*

One-to-One (1:1)

One row in one table corresponds to one row in another table.

| Table A | Relationship | Table B |
|---|---|---|
| A001 | 1 : 1 | A001 |
| A002 | 1 : 1 | A002 |
| A003 | 1 : 1 | A003 |

One-to-Many (1:*)

One row in one table can be related to many rows in another table.

| DimCustomer | Relationship | FactSales |
|---|---|---|
| C001 | 1 : * | C001 |
| C001 | 1 : * | C001 |
| C001 | 1 : * | C001 |
| C002 | 1 : * | C002 |
| C002 | 1 : * | C002 |

This relationship can be useful when information belonging to the same entity has been separated into two tables.

However, if the tables always need to be used together, it may be worth considering whether they really need to remain separate.

Many-to-Many (:)

Many rows in one table can be related to many rows in another table.

| Students | Relationship | Courses |
|---|---|---|
| S001 | * : * | C001 |
| S001 | * : * | C002 |
| S002 | * : * | C001 |
| S002 | * : * | C003 |
| S003 | * : * | C002 |

Many-to-many relationship should be designed carefully because they can make filtering and calculations more complicated.

Where possible, a bridge or intermediate table can help create a clearer model.

Examples:

1:1 → One employee ↔ One employee profile
1:* → One customer ↔ Many sales
: → Many students ↔ Many courses

6. Active and Inactive Relationships
Power BI can have relationships that are either active or inactive.

An active relationship is normally used automatically when filters and calculations are performed.

An inactive relationships exists in the model but is not automatically used for filtering.

For example, a sales table may contain:

OrderDate
ShipDate

Both dates could connect to the same DimDate table, but only one relationship can normally be active between the same two tables for a given path.

Inactive relationships can be used in specific DAX calculations when needed.

7.Filter Direction
Relationships also determine how filters move between tables.

Single-direction filtering
With single-direction filtering, a filter normally moves from the dimension table towards the fact table.

DimProduct  ───────────────→  FactSales
              Filter

Suppose I select **maize flour** in a report.

Power BI can use the relationship to filter fact sales and show only sales involving Maize Flour.

This is commonly used in star schemas because it creates a clear filtering path.

**Bidirectional filtering**
Bidirectional filtering allows filters to travel in both directions.

text
DimProduct ◄──────────────► FactSales
Filter

Although this can be useful in certain scenarios, it should be used carefully.

Using bidirectional relationships unnecessarily can create:

Ambiguous filter paths
Unexpected results
More complicated models
Difficulty troubleshooting calculations

For this reason, single-direction filtering is often preferred unless there is a clear reason to use both directions.

8. Joins in Power Query
Relationships are not the only way to work with multiple tables.

Power Query provides Merge Queries, which allows us to join tables based on matching columns.

Imagine we have:

Customers

CustomerID	CustomerName
C001	Amina
C002	Brian
C003	Carol

Orders

|CustomerID | OrderID |
|C001 | O101 |
|C002 | O102 |
|C004 | 0103 |

Here, CustomerID is the matching column.

Power Query provides several join types.

9.Left Outer Join
A left outer join keeps all rows from the first/left table and matching rows from the second/right table.

Expected result:

CustomerID	CustomerName	OrderID
C001	Amina	O101
C002	Brian	O102
C003	Carol	null

Carol is retained even though she has no matching order.

This is useful when we want to keep every customer and see whether they have corresponding orders.

10.Right Outer Join
A right outer join keeps all rows from the second/right table and matching rows from the first/left table.

Expected results:

CustomerID	CustomerName	OrderID
C001	Amina	O101
C002	Brian	O102
C004	null	O103

C004 is retained even though that customer does not exist in the Customers table.

11. Full Outer Join
A full outer join keeps all rows from both tables, whether or not there is a match.

Expected results:

CustomerID	CustomerName	OrderID
C001	Amina	O101
C002	Brian	O102
C003	Carol	null
C004	Null	O103

This can be useful when identifying records that exist in one table but not the other.

12. Inner Join
An inner join keeps only records that have a match in both tables.

Expected results:

CustomerID	CustomerName	OrderID
C001	Amina	O101
C002	Brian	O102

C003 and C004 are removed because they do not have matching records on both sides.

13. Left Anti Join
A left anti join returns records that exist in the left table but have no matching record in the right table.

Expected result:

CustomerID	CustomerName
C003	Carol

This is useful when finding customers who have never placed an order.

14. Right Anti Join
A right anti join returns records that exist in the right table but have no matching record in the left table.

Expected result:

CustomerID	OrderID
C004	O103

This can help identify orders whose customer information is missing from the Customers table.

15. Power Query Joins vs Power BI Relationships
One thing I found important when learning Power BI that a join and a relationship are not the same thing.

Power Query Merge
A merge happens during the data preparation stage.

It can bring columns from one table into another table.

For example, we could merge Customers and Orders using CustomersID.

The resulting table contains information from both tables.

Power BI Relationship
A relationship happens in the data modelling stage.

It does not physically combine the tables.

Instead, Power BI keeps the tables separate and uses the relationship to allow filters and calculations to work across them.

Merge combines data. Relationship connects data.

When should I merge?
A merge can make sense when:

You genuinely need columns from another table in the same table
You are cleaning or preparing data
The resulting structure is simpler and appropriate for the analysis.

However, excessive merging can create very wide tables, duplicate information, and make a model harder to maintain.

When should I use a relationship?
Relationships are useful when the tables represent different business entities.

For example:

And for the second illustration:

text
DimCustomer
DimProduct
DimDate
DimLocation
↓
FactSales

Keeping these tables separate makes the model easier to understand and allows dimensions to be reused for different analyses.

16. My Recommended Power BI Model
For a typical business intelligence project, I would generally structure the model around a star schema.

                    ┌─────────────────┐
                    │  DimCustomer    │
                    │   CustomerID    │
                    └────────┬────────┘
                             │ 1:*
                             ▼
┌─────────────────┐   ┌─────────────────┐   ┌─────────────────┐
│   DimProduct    │──►│    FactSales    │◄──│     DimDate     │
│   ProductID     │   │     SaleID      │   │     DateID      │
└─────────────────┘   │     Quantity    │   └─────────────────┘
                      │     Revenue     │
                      └────────┬────────┘
                               │ 1:*
                               ▼
                    ┌─────────────────┐
                    │   DimLocation   │
                    │   LocationID    │
                    └─────────────────┘

The reason I would use this structure is that it provides a good balance between simplicity, performance, scalability, and maintainability.

The fact table contains measurable business events, while the dimension tables provide descriptive information.

I would normally use:
- One-to-many relationships
- Dimension tables on the 1 side
- Fact tables on the * side
- Single-direction filtering from dimensions to facts
- Unique primary keys in dimension tables
- Foreign keys in fact tables

For example:

text
DimProduct [1] ───────── [*] FactSales

This allows selecting a product to filter the corresponding sales records.

A star schema also makes it easier to write and understand DAX measures because the model has clear relationships and predictable filter paths.

A flat table can still be useful for a small or simple dataset, while a snowflake schema can be appropriate when dimensions naturally contain several levels of hierarchy. However, adding unnecessary tables and relationships can increase model complexity.

Conclusion

Data modelling is an important part of building a Power BI solution. It is not simply about importing tables and creating charts. The way tables are structured and connected affects how easily we can analyse the data and how reliable our results will be.

Understanding fact tables, dimension tables, schemas, relationships, cardinality, filter direction, and joins makes it easier to build models that can handle real business questions.

One of the biggest distinctions to remember is the difference between a Power Query merge and Power BI relationship. A merge combines data during data preparation, while a relationship connects separate tables during modelling.

For many business intelligence projects, a well-designed star schema with clear one-to-many relationships and appropriate filter directions provides a strong foundation for building Power BI reports.

Ultimately, the goals of data modelling is simple: organise data in the way that makes analysis accurate, understandable, and easier to maintain

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

Antonina Wambui — Wed, 09 Sep 2026 16:56:45 +0000

Introduction

One thing I have started learning during my journey into data analytics is that data rarely arrives ready for analysis.

After working through Excel as part of the LuxDevHQ Data Science and Analytics, I wanted to put the skills I had learned into practice. For this project, I worked with dataset containing product listing from Jumia and challenged myself to go beyond simply creating charts.

The goal was to take messy e-commerce data, understand what was wrong with it clean and transform it, analyze the patterns, and finally present the result through an interactive Excel dashboard.

The dataset contained 115 product records with information about products, prices, discounts, reviews and ratings. At first glance, it looked fairly simple. However, after inspecting it more closely, I discovered missing values, duplicate records, negative review counts, inconsistent data formats and a product with prices recorded as ranges.

That made the project much more interesting.

Instead of jumping straight into visualization, I followed a complete analytics workflow:
Raw Data → Data Audit → Data Cleaning → Data Transformation → Analysis → Visualization → Dashboard → Insights

This article walks through that process and some of the lessons I learned along the way.

Understanding the Dataset
The original dataset contained six main columns:

Product
Current Price
Old Price
Discount
Review
Ratingd

The Ratingd column was simply a naming error, so I standardized it to Rating.

One One important limitation became clear very early: the dataset did not contain actual sales information.

Because of this, I could not honestly claim that a product with more reviews was necessarily selling more.

Instead, I treated the number of reviews are not the same thing as sales. A product could have more reviews because it has been available for a longer period, for example.

Question I wanted the Data to Answer
With that limitation in mind, I focused my analysis around questions such as:

Do longer discounts appear to attract more customer engagement?
Is there a relationship between product ratings and reviews?
Does product price appear to influence ratings?
Which product price appears to influence ratings?
Are there products that deserve further investigation?
What patterns can be communicated effectively through an Excel dashboard?

Step 1: Auditing the Raw Data
Before cleaning anything, I wanted to understand the condition of the dataset.

The initial audit revealed several interesting problems:

Data Quality Issue	Finding
Total records	115
Original columns	6
Missing Reviews	58
Missing Ratings	58
Exact duplicate rows	3
Repeated product names	6
Negative review values	57
Price ranges	1
Invalid rating range	None
Invalid discount range	None

One of the biggest red flags was the Review column.

There were 57 non-blank review values and all 57 were negative.

That immediately raised a question:
How can a product have a negative number of review?

Obviously, a review count cannot realistically be negative. Since the issue affected every non-blank value rather than only a few records, I treated it as a systematic data-quality problem that needed investigation.

This was a good reminder that data cleaning isn't just about fixing errors; it is about understanding why something looks wrong before changing it.

Step 2: Cleaning the Data
Once I understood the problems, I started cleaning the dataset.
I tried to make each cleaning decision based on the information available rather than simply forcing the data to look perfect.

Correcting Negative Review
For the negative review counts, I created a helper column and used:

=IF(E2="","",ABS(VALUE(E2)))

The ABS () function converts the negative values into positive values while the IF () statement ensures that blank cells remain blank.

After verifying the results, I converted the corrected values to static values.
This gave me realistic positive review counts without turning missing information into fake data.

Handling Missing Values
Both the review and rating columns contained 58 blank cells.
I decided not to replace these blanks with zero.

Why?
Because:

Blank ≠ Zero

A blank review count means the review information was not captured.

A zero means the product actually had zero reviews.

Those two situations carry different meanings.

Replacing all blanks with zero could therefore distort averages, rankings and other calculations.
So, in this case, leaving the missing values untouched was the more honest choice.

Step 3: Dealing With the Price Range
One particularly interesting record belonged to a 1/2/3 Seater Elastic Sofa Cover.

It's prices were recorded as ranges:

Current Price: Ksh 1,620-1980
Old Price: Ksh 2,200-3,200

I considered three possible approaches:

Use the minimum price
Remove the record
Calculate the midpoint

I chose the midpoint approach.

This allowed me to retain the product instead of throwing away an otherwise useful record.

The original price range was retained separately so that the transformation could be traced.

However, this decision also produced an interesting result.

Using the midpoint prices gave me a calculated discount of approximately 33%, while the advertised discount in the dataset was 38%.

Rather than changing one value to make them agree, I kept both.

Step 4: Removing duplicates
The dataset contained three completely identical rows.
Since every field matched, I removed those records as exact duplicates.

However, I also found six products with repeated product names.
I did not automatically remove those.

Why?
Because the repeated product names had differences in things such as price discount or review count. Since Jumia is a marketplace with multiple sellers, these could represent separate listings for similar products.

Removing them simply because the product names matched could therefore introduce another form of data error.

So my rule was:
Exact duplicate → Remove

Same product name but different listing information → Keep

Step 5: Standardizing the Dataset
After dealing with the major data-quality issues, I standardized the remaining fields.

Some of the changes included:

Renaming Ratingd to Rating
Removing "out of 5" from rating values
Converting ratings into numerical values
Converting prices from text into numbers
Standardizing column names
Formatting prices as Kenyan Shillings
Applying consistent number formatting.

The cleaned data was then structured as Excel table so that formulas, PivotTables and charts could work more efficiently.

Creating New Analytical Fields
After cleaning the data, I created additional fields to make the dataset more useful for analysis.

Discount Analysis
I calculated the discount amount by finding the difference between the old and current prices. I also calculated the percentage discount and kept it alongside the original advertised discount.

This allowed me to compare the seller's advertised discount with the discount calculated from the actual prices.

Rating Categories
To make ratings easier to analyze, I grouped them into:

Poor: Below 3
Average: 3-4.5
Excellent: Above 4.5

Ratings between 4.1 and 4.5 were included in the Average category as a documented working assumption.

Discount Categories
Discounts were grouped into:

Low: Below 20%
Medium: 20%- 40%
High: 40%

Price Categories
Instead of choosing price ranges arbitrarily, I used quartiles from the dataset.

Using QUARTILE.INC(), I obtained

Q1: Ksh 493
Q3: Ksh 1,669.50

These thresholds were then used to classify products into Low, Medium and High price categories.

Engagement Strength
Since review count was being used as a proxy foe customer engagement, I used the 75th percentile as the threshold.

The Review Q3 was 13 reviews, so products with 13 or more reviews were classified as having strong engagement.

Data Status and Combination Flags
I also created a Data Status field to identify products as:

Complete
Missing Rating
Missing Review
Missing Both

Finally, I created four combination flags to highlight products requiring further attention:

High discount + Low Rating
High discount + Low Engagement
Many Reviews + Average Rating
Strong Engagement + Excellent Rating

Where the required data was missing, the result was marked "missing" rather than making an assumption.

Analysis Using PivotTables and Excel Functions
With the dataset cleaned and enriched, I moved on to analysis using PivotTables, PivotCharts, formulas, FILTER(), correlation and scatter plots.

I focused on three relationships:

Discount vs. Reviews
Rating vs. Reviews
Current Price vs. Rating

Because Review and Rating contained missing values, I used FILTER () to create helper ranges containing only complete pairs before calculating correlations.

The results were:

Relationship	Pearson r	R²
Discount vs. Reviews	-0.111	0.002
Rating vs. Reviews	0.043	0.002
Current Price vs. Rating	0.110	0.012

All three relationships were very weak.

For example, the -0.111 correlation between discount and reviews is very close to zero, while the R² values show that these variables explained very little of the variation in one another.

This doesn't mean that price, ratings or discounts never influence customer behavior. It simply means that this dataset did not show a strong relationship between them.

Product Ranking
I also created PivotTables to identify:

Top 10 products by Rating
Bottom 10 products by Rating
Top 10 products by Discount

For rating rankings, products without ratings were excluded. Where products has the same rating, review count was used as the tie-breaker.

This gave the rankings a consistent and transparent approach.

Building the Interactive Dashboard
After completing the analysis, I brought the most important findings together into a single-screen interactive dashboard.

The dashboard contains:
KPI Cards

Total Products
Average Price
Average Discount
Average Rating
Total Reviews

Products Performance

Top 10 by Rating
Top 10 by Reviews
Top 10 by Discount

Relationship Analysis

Discount vs. Reviews
Rating vs. Reviews
Price vs. Rating The scatter plots include trendlines and R² values to help interpret the relationship.

Category Analysis
I also included charts showing by the distribution of products by:

Rating Category
Discount Category

Interactive Slicers
To make the dashboard easier to explore, I added slicers for:

Rating Category
Discount Category
Price Category

These allow users to filter the dashboard and explore different groups of products interactively.

Key Findings
1. Bigger Discounts Did Not Mean More Engagement
The correlation between discount and reviews was -0.111, indicating an extremely weak relationship.

In this dataset, products with larger discounts did not necessarily receive more reviews.

2. Price Was Barely Related to Rating
The correlation between current price and rating was 0.110, also a very weak relationship.

This suggests that higher-priced products were not necessarily rated better.

3. Some Highly Discounted Products Still Had Weak Performance
Some products combined high discounts with low ratings or low engagement.

These products may require more than simply another price reduction. Their product description, images, listing quality or customer expectations could be worth investigating.

4. Some Products Had Strong Engagement and Excellent Ratings
I also identified five products with both strong engagement and excellent ratings.

These products could provide useful examples for understanding what appears to work well, although the available data isn't enough to explain exactly why.

Recommendations
Based on the analysis, I would recommend:
Don't rely on discounts alone to drive engagement. Sellers could also improve product images, descriptions and overall listing quality.

Investigate highly discounted products with weak ratings or engagement before offering even larger discounts.

Study high-performing products with strong engagement and excellent ratings to identify patterns worth replicating.

Interpret the results carefully. Correlation shows relationships, but it does not prove that one variable causes another.

Limitations
The analysis had several limitations.

The most important was the lack of sales, revenue, units sold, and listing-age data. Therefore, review count was only used as a proxy for engagement and should not be treated as actual sales performance.

Other limitations included:

One product had a price range that required midpoint estimation.
Many products had missing ratings and reviews.
The rating categories included a working assumption for ratings between 4.1 and 4.5.
The dataset contained only 115 original records.
Correlation does not imply causation.

These limitations are important because they define how far the findings can reasonably be generalized.

What I Learned
The biggest lesson from this project was that data analysis starts before the charts.

What initially looked like a simple dataset turned out to contain several issues that could have affected the analysis if they had gone unnoticed.

I learned to question unusual values, handle missing information carefully, document assumptions, and avoid making changes without understanding the impact.

I also got to experience the complete analytics workflow:

Data Auditing → Cleaning → Transformation → Analysis → Visualization → Dashboard → Insights

Most importantly, this project showed me that Excel can be much more than a spreadsheet. With the right approach, it can be used to turn messy data into meaningful information and communicate insights through an interactive dashboard.

And honestly, seeing a few raw columns turn into a complete analytical dashboard was one of my favorite parts of this project.

I'm really enjoying this data analytics journey.

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

Antonina Wambui — Wed, 09 Sep 2026 09:31:35 +0000

Introduction

When I first started learning data analytics, I thought working with data was mainly about creating charts, finding patterns and presenting insights.
I quickly realised there is an important step that comes before all that: making sure the data is actually ready to be analysed.

Raw datasets can contain missing values, duplicate records, inconsistent text, incorrect data types, and formatting problems. If these issues are ignored, they can affect the results of the analysis and eventually lead to misleading conclusions.

During my Excel training at LuxDevHQ, I was introduced to some of the tools and techniques Excel provides for preparing data for analysis. This included sorting and filtering, formatting, handling missing values, removing duplicates and text functions such as TRIM, PROPER and CONCAT.

For my practice, I worked with a synthetic dataset that allowed me to simulate a real-world data-cleaning task. Rather than jumping into analysis, I focused on understanding the dataset first and gradually transforming it into a cleaner and more consistent version.

In this article, I'll share my process, the Excel tools I used, and some of the lessons I picked up along the way.

1. Starting With the Raw Dataset

The first step in any data analysis project is understanding what you're working with.
I imported my dataset into Excel and took some time to look through the columns and rows before making any changes.

At this stage, I wasn't trying to fix anything yet. I wanted to understand:

What information does each column contain?
Which columns contain numbers?
Which contain text?
Are there dates?
Are there missing values?
Are there values that look inconsistent?
Could any columns have duplicates?

This simple inspection helped me realise that data cleaning shouldn't begin with immediately changing things. You first need to understand what the data represents.

2. Creating a Safe Copy

One thing I learned early was the importance of protecting the original dataset.

Instead of working directly on my original data, I created a duplicate worksheet and used the copy for my cleaning process.
This gave me a backup that I could return to whenever I made a mistake.

It might seem like a small step, but when you're experimenting with Excel for the first time, it's easy to accidentally delete a column, change a value or apply formatting to the wrong range.
Having an untouched copy made the process much safer.

3. Making the Dataset Easier to Work With

Before looking for errors, I first made the spreadsheet easier to read.
I adjusted the column widths using Excel's AutoFit feature so that the values and column headings were visible.

I also converted the dataset into an Excel table.
This was useful because tables make it easier to:

Filter information
Sort records
Navigate through large datasets
Keep formatting consistent
Work with formulas

I then enabled filters on the columns so I could inspect specific categories instead of manually going through every row.

For example, filtering a column allowed me to quickly identify blank cells or check whether the same category had been entered in different ways.

4. Checking Data Types
One of the most interesting things I learned was that how a value looks isn't always the same as how Excel understands it.

A column containing numbers doesn't necessarily mean it should be treated as a numerical field.

For example, an ID such as:

10001

may look like a number, but if it is simply identifying a transaction or customer, there is no reason to calculate an average or total from it.

The same applies to phone numbers.

I therefore checked my columns and made sure they were using appropriate formats.

Some of the common data types I worked with included:

Data	Appropriate Format
Transaction/Customer ID	Text
Names	Text
Phone Numbers	Text
Dates	Date
Quantity	Number
Prices/Revenue	Currency or Number
Categories	Text

This was a good reminder that data types should be based on what the data means, not just what it looks like.

5. Looking for Missing Data
Next, I checked the dataset for missing values.
Missing data is not automatically an error. Sometimes a value is genuinely unavailable.

The important thing is deciding what to do with the missing value based on the context.

For example, if a category such as location was missing, I could use a value such as "Unknown" where appropriate.

However, I wouldn't randomly replace a missing numerical value with zero.

A blank amount and an amount of zero do not necessarily mean the same thing.

This made me realise that data cleaning isn't simply about making every cell look complete. It's about making appropriate decisions about what each value represents.

6. Finding Duplicates
Another important check was identifying duplicate records.

I used Excel's
Data → Remove Duplicates
feature to check whether the dataset contained repeated records.

Even when duplicates aren't found performing the check is still useful.
It confirms that you've considered one of the common problems that can affect analysis.

For example, if the same transaction appeared twice and I calculated total revenue without noticing it the final result could be higher than the actual revenue.

This showed me why data validation should happen before analysis.

7. Cleaning Inconsistent Text
Text data can look simple, but it can create some interesting problems.

For example, Excel may treat these as different values:

Nairobi
nairobi
NAIROBI

To a person, they all appear to mean the same thing. To a computer, however, differences in formatting or extra spaces can affect how values are grouped and analysed.

This is where some of Excel's text functions became useful.

TRIM
I used TRIM to remove unnecessary spaces from text.
=TRIM(A2)

This is particularly useful when data has been copied from another source and contains unwanted space.

PROPER
I also explored PROPER for standardising names.
=PROPER(A2)

For example:

JOHN KAMAU

can become:

John Kamau

CONCAT
Another function I practised was CONCAT,which can be used to join pieces of text together.

=CONCAT(A2,B2)

These functions showed me that Excel isn't only useful for calculations. It can also be used to transform and standardise text.

8. Using Find and Replace
Not every cleaning problem requires a formula.

Excel's Find and Replace feature can be much quicker when dealing with specific inconsitencies.

The shortcut:
Ctrl + H
Opens Find and Replace

For example, if one category had been entered several times incorrectly, I could search for the incorrect version and replace it with the correct one.

This was one of those small Excel features that I initially overlooked but found surprisingly useful during the cleaning process.

9. Helper Columns: A Simple but Useful Technique
Something else I learned was the use of helper columns.

Instead of changing the original values immediately, I could create a temporary column containing a formula such as:
=TRIM(A2)

Then I could fill the formula down the dataset, check the result and, once satisfied, copy the result and use Paste Special → Values.

This allowed me to compare the original values with the cleaned ones before replacing anything.

For a beginner, I found this approach much less intimidating because I could see exactly what the formula was doing.

10. What This Exercise Taught Me
This exercise changed the way I look at spreadsheets.
Before, I thought cleaning data mainly meant removing duplicates and filling blank cells.
Now I understand that it involves several different decisions, including:

Understanding → Checking → Cleaning → Validating → Preparing

I also learned that there isn't always one correct way to clen a dataset.

The right approach depends on:

The type of data
What each column represents
Why values are missing
The type of inconsistency
What the data will eventually be used for

For example, a missing text value might reasonably be labelled "Unknown", while a missing financial value may need further investigation rather than simply being replaced with zero.

11. From Cleaning to Analysis
The biggest lesson for me was that data cleaning is part of data analysis, not something separate from it.

A dashboard may look beautiful, but if the data behind it contains errors, the visualization can still communicate the wrong story.

The process therefore becomes:
Raw Data → Cleaning → Validation → Analysis → Visualization → Insights

Excel can support several of these stages, which makes it a useful starting point for someone learning data analytics.

Conclusion
My first hands-on experience with Excel data cleaning gave me a better understanding of what happens before the charts and dashboards are created.

I learned how to inspect a dataset, protect the original data, work with tables, check data types, identify missing values and duplicates, standardise text and use Excel functions such as TRIM, PROPER, and CONCAT

More importantly, I learned that cleaning data isn't about making a spreadsheet look perfect. It's about making sure the information is consistent, meaningful and reliable enough to support analysis.

I'm still at the beginning of my data analytics journey, but this exercise has given me a stronger foundation in Excel and a better appreciation for the work that happens behind the scenes before we can confidently say:

"Let's analyse the data"

My First GitHub Project

Antonina Wambui — Fri, 21 Aug 2026 12:06:11 +0000

Introduction

When working on a project, you may make many changes to your files. It can become difficult to keep track of what you changed. Git helps you solve this by allowing you to record your changes and keep a history of your work. GitHub provides a place where the project can be stored online.

What is Git?

Git is a tool used to keep track of changes made to files in a project. It helps you have different versions of your work so that you can see how your project has changed over time. This can be useful when working on a project because you can keep track of your progress and go back to an earlier version if needed.

What is GitHub?

GitHub is the online platform where you can store and share that Git project.

What is a Repository?

A (repo) is a place where your project is stored and git keeps track of it's history.

Creating a Local Folder

The first step is to create a folder on my computer where I will keep my project in my files.
This is called a local folder because it is stored in my computer.

For example, I can create a folder called my-first-project. I can then open Git Bash and move into the folderusing the cd command.

mkdir my-first-project
cd my-first-project

The **mkdir** command is used to create a new folder, while the **cd** command is used to move into that folder.

So local folder - is basically my project's home on my computer.
You create folder first, then git comes later when you

bash
git init

so the order is

Create folder
Enter folder
Git init
Git starts tracking the project

Staging with git add

Staging is the process of preparing changes before a commit.

After creating or changing a file, I need to tell git which changes I want to include in my next commit.
This is done by using the git add command.

Example, I can use

git add .

The . means that Git should stage all the changes in the current folder.

Also can stage one specific file by using its name:
Bash
git add README.md

After using git add, the changes are placed in the staging area and are ready to be committed.

**Committing with git commit**
After staging my changes, the next step is to commit them. 
A **commit** is like a saving version or checkpoint of my project. It allows Git to keep a record of the changes I have made.

To create commit, I use the following command

bash
git commit -m "Initial commit"

the -m allows me to add a message describing what I have committed. The message should briefly explain what the changes are
For example
Bash
git commit -m "Add README file"

What is SSH

SSH stands for Secure Shell.
It is a secure way of connecting my computer to git hub. In Git, SSH can be used to connect my local repository to a GitHub repository.
It uses SSH keys to verify the connection between my computer and GitHub.

Once the connection is set up you can use commands such as
Bash
git push
to send your GitHub securely.

Connecting the project to GitHub

After creating a repository on GitHub, I need to connect it to the project on my computer. This allows Git to know where my project should be sent when i want to upload my work.
I can connect my local repository to GitHub by using the git remote add command. Example:


bash
git remote add origin git@github.com:ninadavid4/my-first-project.git
Here, **origin** is the name given to the GitHub repository. The SSH address after origin tells git where the remote repository is located.
I can check if the connection has been added by using 
Bash
git remote -v
This shows the GitHub repository connected to my local project.

Then later, when you use:
Bash 
git push

Git knows where to send your commits.

##Conclusion
In this project, I learned how to take a project from a local folder on my computer and connect it GitHub using Git and SSH. I learned how to create a Git repository, stage changes using `git add`, save them using `git commit`, and connect the project to a remote GitHub repository. I also learned that SSH provides a secure way for my computer to communicate with GitHub.