DEV Community: Stacy Mumbi

From Messy CSV to Boardroom-Ready: Cleaning, Modelling and Visualizing JCars Logistics' Sales Data in Power BI

Stacy Mumbi — Wed, 30 Sep 2026 07:18:57 +0000

1. Project Background and Business Objective

JCars Logistics imports, sells and delivers vehicles to customers across Kenya. Management requested a Power BI solution to understand the company's overall performance and identify areas that may need attention. The analysis covers sales, revenue, costs and profitability, vehicle performance, branch and regional performance, sales representatives, lead sources, payments, deliveries and logistics, returns and cancellations, customer experience and unusual transactions that may need further investigation.

This report explains the journey from the raw dataset to the final Power BI solution. It covers data cleaning, data modelling, DAX measures, dashboard development and the main insights and recommendations from the analysis.

2. Dataset and Grain

The source data was a single raw flat file containing 32 columns and 276 rows. Each row represents one vehicle sold in one sales transaction. This level of detail was kept throughout the project. No aggregation or splitting was needed when creating the fact table.

The columns were grouped into several main areas:

Transaction information such as Order ID and dates
Customer information
Vehicle information
Location information such as Region, County, City and Branch
Sales information such as Sales Rep and Lead Source
Financial information such as prices, costs, discounts, fees and revenue
Operational information such as payment, delivery, returns and customer ratings

Screenshot: Raw dataset showing the original columns and sample records before cleaning.

3. Data Quality Audit: Key Issues Identified

More than ten major data quality issues were identified and resolved during the cleaning process. The most important ones are summarised below. The same cleaning approach was applied consistently across the dataset.

#	Issue Found	Why It's a Problem	How It Was Handled
1	Order IDs were recorded in four different formats (`LC1000`, `LCL-1001`, `ORD1004`)	The same type of identifier had different formats, making it difficult to identify records consistently	The digits were extracted and standardized to `ORD-####`. The original value was kept separately
2	Dates appeared in 5+ formats, including Excel serial numbers such as `45671`, as well as invalid dates such as `31/02/2026` and `April 31 2026`	The dates could not always be read correctly and some of them did not exist on the calendar	Excel serial numbers were converted using the Excel date system. Day-first formatting was used for ambiguous dates. Invalid or unrecognizable dates such as `#DATE!`, "not sure" and impossible calendar dates were changed to null
3	Customer Age contained impossible values such as `0`, `5`, `121` and `-5`	These values are not realistic for the customer information being analysed	Unrealistic ages were changed to null. Negative signs were removed from otherwise realistic ages where they appeared to be data entry errors
4	Monetary columns contained 5 different currencies: KES/KSh, USD ($ and text), EUR, ZAR/R and a corrupted symbol (`?`)	Values from different currencies cannot be directly compared or added together	The currency was identified for each value and converted to KES. Values without a currency label were treated as KES based on the assessment instructions
5	Categorical fields such as Customer Type, Car Make, Car Model, Vehicle Type, Fuel Type, Color, Payment Method/Status, Delivery Status, Returned, Region, County, City and Branch had multiple spelling, casing and abbreviation variations	The same category could appear as different categories in the analysis	The values were standardized into one consistent category using evidence from related fields where necessary
6	Zero values in monetary columns did not always mean the same thing	A KSh 0 selling price for a paid transaction is unlikely to be valid, while KSh 0 revenue for a cancelled transaction can be reasonable	Zero values were assessed separately for each column based on the business context
7	Vehicle Year included `1899`, `2032`, `202A` and `Twenty Twenty`	These values would affect year-based analysis and some were clearly invalid	`202A` was corrected to `2024` based on the Order Date. `Twenty Twenty` was changed to `2020`. `1899` and `2032` were changed to null because there was not enough evidence to recover the correct values
8	Discount values appeared as percentages, decimals, words and included an outlier of `120%`	A discount above 100% is not a realistic business value	Values were standardized to decimal format. Negative discounts and discounts above 50% were treated as unrecoverable errors and changed to null
9	Customer Rating included numbers, `"X out of 5"`, `"X/5"`, `"Excellent"` and invalid values such as `-1` and `6`	Ratings outside the expected range would affect customer satisfaction analysis	Text formats were converted to numbers. `"Excellent"` was mapped to `5` and values outside the 0 to 5 range were changed to null
10	Some locations were labelled as both a "Branch" and a "Yard", such as "Eldoret Branch" and "Eldoret Yard"	A branch and a yard can represent different operational locations rather than spelling variations	The distinction was kept so that branch and yard performance could be analysed separately
11	Some Sales Rep names contained number-for-letter errors such as `"Wanj1ku"` instead of `"Wanjiku"`	These errors created separate and incorrect versions of the same sales representative	The corrupted characters were corrected and matched against the verified list of 10 sales representatives

4. Currency Standardization

All monetary values were converted to Kenya Shillings (KES) so that the report could use one consistent reporting currency. This also followed the assessment instruction that values without a currency label should be treated as KES.

Exchange rates used:

Currency	Rate to KES
USD	129.54
EUR	147.84
ZAR	7.93
GBP	Not applicable, investigated and confirmed absent from the dataset

The ? symbol that appeared before some monetary values was investigated by comparing the affected Unit Selling Price with the Unit Cost in the same row. The values consistently matched USD-level pricing, so the ? symbol was treated as a corrupted USD symbol rather than making an unsupported assumption.

Zero-value business rules:

Unit Selling Price / Unit Cost: zero was treated as missing because a vehicle would not normally have zero acquisition or selling value.
Delivery Fee: zero was accepted because free delivery is possible.
Logistics Cost: zero was treated as missing where DeliveryStatus showed that the vehicle had actually been delivered.
Revenue Recorded: zero was accepted where PaymentStatus was Cancelled or Refunded. In other cases it was treated as missing.

This was the formula format I used to work on the monetary columns with different currencies:

fnMoneyKES = (v as any) as nullable number =>
    let
        raw = Text.Trim(Text.From(v) ?? ""),
        upperRaw = Text.Upper(raw),
        isMissing = raw = "#VALUE!" or raw = "#ERROR" or raw = "-" or raw = ""
                    or upperRaw = "NULL" or upperRaw = "TBD"
                    or Text.Contains(upperRaw, "MISSING") or Text.Contains(upperRaw, "NOT"),
        hasM = Text.EndsWith(upperRaw, "M"),
        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 isMissing or numVal = null then null
        else if hasM then Number.Abs(numVal) * 1000000
        else if currency = "USD" then Number.Abs(numVal) * 129.54
        else if currency = "EUR" then Number.Abs(numVal) * 147.84
        else if currency = "ZAR" then Number.Abs(numVal) * 7.93
        else Number.Abs(numVal),

5. Data Validation Performed

Several validation checks were carried out after the main cleaning process:

Cross-checked currency assumptions against Unit Cost and Unit Selling Price values in the same row before finalizing the conversion logic.
Compared Payment Status and Delivery Status for logical consistency. This identified 14 records where the two fields did not agree. There were 10 records showing "Paid but Delivery Cancelled" and 4 showing "Payment Cancelled but Delivered". These records were kept as they were and flagged for investigation rather than being changed without evidence.
Checked the two blank Vehicle Type records against their Car Model. Each model had a consistent Vehicle Type in the other records, allowing the missing values to be inferred.
Checked Branch and City relationships before creating the location hierarchy. Each branch consistently belonged to one city.
Checked Sales Rep and Branch relationships. Sales representatives worked across multiple branches, which supported creating Sales Rep as a separate dimension.

6. Data Model

I transformed the raw flat file into a star schema consisting of one fact table and eight dimension tables.

Grain of FactSales: one row represents one vehicle sold in one transaction. This is the same grain as the original dataset.

FactSales (Fact Table)

Column	Role
OrderID	Degenerate dimension
OrderDate, DeliveryDate	Relate directly to DimDate
CustomerID	FK → DimCustomer
VehicleID	FK → DimVehicle
LocationID	FK → DimLocation
SalesRepID	FK → DimSalesRep
PaymentID	FK → DimPayment
LeadSourceID	FK → DimLeadSource
DeliveryStatusID	FK → DimDeliveryStatus
Returned	Degenerate attribute
UnitsSold, UnitSellingPrice, UnitCost, DiscountPct, DeliveryFee, LogisticsCost, RevenueRecorded, CustomerRating, ReviewCount	Measures

Dimension Tables

Table	Primary Key	Columns
DimDate	Date (direct relate, no primary key needed)	Date, Year, Quarter, Month, MonthYear, Day, DayName, WeekdayNumber, IsWeekend
DimCustomer	CustomerID	CustomerName, CustomerType, CustomerAge
DimVehicle	VehicleID	CarMake, CarModel, VehicleType, VehicleYear, FuelType, Transmission, Color
DimLocation	LocationID	Region, County, City, Branch
DimSalesRep	SalesRepID	SalesRep
DimPayment	PaymentID	PaymentMethod, PaymentStatus
DimLeadSource	LeadSourceID	LeadSource
DimDeliveryStatus	DeliveryStatusID	DeliveryStatus

Relationships: all dimensions relate to FactSales as One-to-Many with single-direction cross-filtering. FactSales relates to DimDate twice, using OrderDate as the active relationship and DeliveryDate as the inactive relationship. The DeliveryDate relationship is activated in specific measures using USERELATIONSHIP.

Model View showing FactSales at the centre and the eight dimension tables connected through One-to-Many relationships, including the two DimDate relationships for Order Date and Delivery Date.

Modelling limitation: the dataset does not contain a unique customer identifier. DimCustomer was therefore built from distinct combinations of CustomerName, CustomerType and CustomerAge.I did this by selecting all the columns in it and removing duplicates.

This means that "customer" in this analysis refers to the customer as recorded per transaction rather than a verified master customer record. Two different people with the same name and similar profile be treated as one customer.

I considered this limitation when presenting customer-level findings.

7. Key DAX Measures

The following are some of the main DAX measures I used in the analysis.

Total Revenue = SUM(FactSales[RevenueRecorded])

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

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

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

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

Customer Total Revenue =
CALCULATE([Total Revenue], ALLEXCEPT(FactSales, DimCustomer[CustomerName]))

Examples of the measures I created for the analysis by use of DAX.

Business definition: Gross Profit was defined as Revenue minus (Units Sold times Unit Cost).I excluded Delivery Fee and Logistics Cost because I treated them as operating expenses rather than the direct cost of the vehicle.

Limitation: some Unit Cost and Unit Selling Price values could not be recovered during cleaning and were changed to null. Therefore, Gross Profit and Gross Profit Margin may be affected for transactions where cost or price information was missing.

8. Dashboard and Report Structure

Page	Purpose
1. Executive Dashboard	High-level summary showing KPIs, revenue trend, branch and make comparison, payment mix and a logistics cost attention indicator
2. Vehicle/Branch Performance	Analysis of Make, Model, Vehicle Type and Year performance, branch revenue share and regional drill-down
3. SalesRep/LeadSource Performance	Sales representative performance and lead source value
4. Revenue/Logistics	Payment status and method, delivery performance and logistics cost efficiency
5. TopCustomers	Top 10 customers by revenue and the relationship between revenue and ratings

Interactivity was also added to the report. Region and Car Make slicers are synced across pages. A Region → County → City → Branch drill-down was created within the matrix visual and visuals on each page can interact through cross-filtering.

The Executive Dashboard showing KPI cards, revenue trend, branch and make comparison, payment status mix and logistics cost attention indicator.

How the dashboard looks like when the slicer only applies to Central region.

9. Investigation: Records Requiring Management Attention

14 Payment/Delivery Status inconsistencies: 10 records show Payment Status = Paid while Delivery Status = Cancelled. Another 4 show Payment Status = Cancelled while Delivery Status = Delivered. These records were not corrected because the data does not show which field is inaccurate. They were instead flagged for manual review.
Truck vehicle type shows a -66% gross profit margin: this is the vehicle type with the most negative margin in the dataset. It should be investigated to determine whether the result comes from a genuine pricing or cost issue or from remaining data quality problems.

BMW (-37%) and Isuzu (-31%) show negative margins: both makes show significant negative margins even though their selling prices are comparable with other makes. The Unit Cost data for these makes should therefore be reviewed to check whether currency conversion or missing-value handling affected the results.

Nairobi has the highest average delivery time: Nairobi has an average delivery time of 26.29 days compared with the company-wide average of 15.18 days. Since Nairobi hosts the company's headquarters, this difference is worth investigating to understand what may be causing the longer delivery times.

10. Management Insights

Revenue is concentrated in a small number of vehicle makes and regions. Toyota accounts for the largest share of revenue among the vehicle makes in the dataset. Rift Valley (KSh 381.9M) and Central (KSh 244.6M) also contribute a large share of the total KSh 1.48bn revenue. This means overall performance is strongly connected to demand for Toyota vehicles and performance in these regions.

Customer revenue is concentrated but not extreme.
The top 10 customers out of 276 account for approximately KSh 299.3M, which is around 20% of total revenue. This shows that a meaningful share of revenue comes from a relatively small group of high-value customers.
Some vehicle categories are being sold at negative margins.
SUVs generated the highest revenue among vehicle types at approximately KSh 845M and had a positive margin of 8%. However, Sedans had a -9% margin, Crossovers -6%, Vans -28% and Trucks -66%. Since these are categories with meaningful sales volumes, the results may point to wider pricing or cost issues rather than a few individual transactions. However, these findings should be considered alongside the data quality issues identified during cleaning.
Customer ratings do not show a clear relationship with transaction value.
The scatter plot comparing Customer Rating and Revenue Recorded shows that ratings generally cluster around 3 to 4 across different transaction values. There is no clear upward or downward pattern in the data. Based on this dataset, higher transaction values do not appear to be directly associated with higher customer ratings.

Logistics efficiency varies across branches. Nairobi HQ has the highest Logistics Cost % of Revenue among the branches. Nairobi also has the longest average delivery time at 26.29 days compared with the overall average of 15.18 days. These two findings together suggest that Nairobi's logistics performance deserves further investigation.

Note: Return Rate (31.88%) and Gross Profit Margin (2.53%) were calculated from the cleaned dataset. Both figures should be treated carefully because missing or null values in Unit Cost, Unit Selling Price and Returned may affect the results. Additional source data could therefore change these figures.

11. Management Recommendations

Review pricing and costs for Trucks, Vans, Sedans and Crossovers. These vehicle categories show negative gross margins at meaningful sales volumes. Management should review their selling prices and acquisition costs to determine whether current pricing is covering the cost of the vehicles.
Review the 10 Paid but Cancelled transactions. The 10 records showing Payment Status = Paid and Delivery Status = Cancelled should be reviewed to determine whether customers are owed refunds or whether the delivery status is incorrect.
Investigate Nairobi's delivery times and logistics costs. Nairobi has both a relatively high logistics cost share and the longest average delivery time. Management should investigate the reasons behind this before treating it as normal regional variation. Possible areas to review include traffic, yard-to-customer handoffs, delivery scheduling and available resources.

12. Analyst-Defined Business Questions

In addition to the questions provided in the assessment brief, five additional questions were identified as useful for JCars Logistics management. Three were answered directly by the Power BI analysis.

Does higher customer satisfaction translate into higher-value sales, or are they unrelated? (Answered: Section 10, Insight 4)

Which lead source delivers the best return per lead, and is marketing investment allocated accordingly? (Answered via the Avg Revenue per Lead by Lead Source visual)

Are certain branches carrying disproportionate logistics costs relative to the revenue they generate? (Answered: Section 10, Insight 5)

Does vehicle age affect profitability, suggesting that older stock should be discounted or phased out?
How concentrated is revenue among the top customers, and does this represent a meaningful business risk? (Answered: Section 10, Insight 2)

13. Assumptions and Business Rules Summary

Monetary values without a stated currency were treated as Kenya Shillings based on the assessment instructions.
Exchange rates used were USD 129.54, EUR 147.84 and ZAR 7.93 to KES.
Gross Profit = Revenue minus (Units times Unit Cost). Delivery Fee and Logistics Cost were treated as separate operating expenses.
"County Government" was standardized under "Government" because the dataset did not provide enough information to distinguish between different levels of government.
"Retail" was kept as its own Customer Type category rather than being combined with "Individual."
Branch and Yard designations for the same city were kept as separate locations.
Ambiguous dates were interpreted using the day-first UK/Kenya convention.
Ratings outside 0 to 5 and discounts below 0% or above 50% were treated as unreliable and changed to null rather than being manually corrected.
DimCustomer was created using Name + Type + Age because the source data did not contain a unique customer identifier. Customer-level findings should therefore be interpreted with this limitation in mind.

14. Challenges Encountered and Learning

The biggest challenge in this project was the number and variety of data quality issues spread across the dataset.

Many cleaning decisions could not be made by simply applying the same rule to every column. Instead, related fields had to be compared to understand what the data was actually showing.

For example, Unit Cost and Unit Selling Price were compared when resolving unclear currencies. Car Model was compared with Vehicle Type when filling missing Vehicle Type values. Payment Status was also compared with Delivery Status to identify records that needed further investigation.

For me this project solidified the importance of making data cleaning decisions based on evidence wherever possible. When the available information was not enough to confidently correct a value, the value was left as null or flagged for investigation instead of making an unsupported assumption.

The process also showed that data cleaning is not just about making a dataset look neat. Every cleaning decision can affect the final analysis and the business conclusions drawn from it. Clearly documenting these decisions is therefore an important part of producing a reliable Power BI solution.

Github Repository Link

Power BI: Data Modelling, Relationships and Joins

Stacy Mumbi — Thu, 24 Sep 2026 19:15:04 +0000

Introduction

When I first started working with Power BI, I thought my main job was simply to import a dataset, clean it, create a few charts and build a dashboard. I did know there was more to all that until I discovered the term data modelling.

There were new terms such as tables, relationships, cardinality, joins, keys and something called Model View.At first, it felt confusing. But once I understood how tables connect, Power BI started making much more sense.

In this article, I will explain data modelling, relationships and joins in Power BI using a simple e-commerce example.

1. What is Data Modelling?

Data modelling is basically the process of organising your tables and defining how they connect to each other.Instead of putting every piece of information into one big table, we can separate the information into dimension tables.

For example, imagine an online shop.

We can have tables like:

Customers

CustomerID	CustomerName	County
C001	Stacy	Nairobi
C002	Brian	Kiambu
C003	Aisha	Mombasa

Products

ProductID	ProductName	Category
P001	Laptop	Electronics
P002	Headphones	Electronics
P003	Sneakers	Fashion

Sales

SaleID	CustomerID	ProductID	Quantity	Sales
S001	C001	P001	1	65000
S002	C002	P002	2	5000
S003	C001	P003	1	4500
S004	C003	P001	1	65000

We can see that the Sales table doesn't contain the customer's name, county, product name or category.Instead, it contains CustomerID and ProductID.These IDs allow Power BI to connect the tables.

2. What is a Key?

A key is a column that helps us identify and connect records.

For example:

CustomerID identifies a customer.
ProductID identifies a product.

In the Customers table:

CustomerID	CustomerName
C001	Stacy
C002	Brian
C003	Aisha

Each CustomerID appears once.But in the Sales table, the same CustomerID can appear many times:

SaleID	CustomerID	Sales
S001	C001	65000
S003	C001	4500

This gives us a one-to-many relationship.

3. Understanding Relationships

A relationship tells Power BI:

"These two tables are connected through this column."

For example:

Customers[CustomerID] -> Sales[CustomerID]
This tells Power BI which customer made each sale.

Relationships also allow filters to propagate from one table to another. This is what makes it possible to use fields from different tables together in a report.

The model would look something like:

        Customers
       CustomerID
            |
            | 1
            |
            | *
          Sales
       CustomerID
       ProductID
            |
            | *
            |
            | 1
         Products
        ProductID

This is a very common structure in Power BI.

The 1 represents the side where the value is unique.
The * represents the side where the value can appear multiple times.

4. One-to-Many Relationships

The most common relationship we all encounter is the One-to-Many (1:*)

For example:
One customer can make many purchases.

Customers Sales

C001 ──────────────── S001
  │
  └────────────────── S003

C002 ──────────────── S002

Customer C001 appears once in the Customers table but appears multiple times in the Sales table.

Therefore:

Customers = One
Sales = Many

Power BI calls this the relationship cardinality.

5. Why Do Relationships Matter?

If we create a dashboard containing:

Total Sales
Sales by County
Sales by Product Category
Sales by Customer

The Sales table contains the actual transaction amounts.But the county is stored in the Customers table.The category is stored in the Products table.
The relationships tell Power BI how the tables are connected so that it will be able to know which category or county belongs to each sale.

For example:

Customers
   ↓
CustomerID
   ↓
Sales
   ↑
ProductID
   ↑
Products

Because these relationships exist, we can use:Customers[County]together with Sales[Sales] enabling Power BI to calculate sales by county.

6. Creating a Relationship in Power BI

In Power BI Desktop, go to Model View.You will see your tables displayed visually.You can then connect matching columns, such as:
Customers[CustomerID] to Sales[CustomerID]

Power BI can sometimes detect relationships automatically when tables are loaded, but you can also create or edit them manually through Manage relationships.

When creating a relationship, you may meet terms such as:

Cardinality

Examples include:

One-to-many
One-to-one
Many-to-many

Cross-filter direction

This determines how filters move between related tables.For a beginner-friendly model, keeping relationships simple and using a clear model structure is usually easier to understand and maintain.

7. What Are Joins?

This is another important concept:

Joins.

A join combines information from two tables based on a matching column.
For example, if we have:

Customers

CustomerID	CustomerName
C001	Stacy
C002	Brian
C003	Aisha

Sales

SaleID	CustomerID	Sales
S001	C001	65000
S002	C002	5000
S003	C001	4500

We can join the tables using CustomerID.

The result could look like:

SaleID	CustomerID	CustomerName	Sales
S001	C001	Stacy	65000
S002	C002	Brian	5000
S003	C001	Stacy	4500

Now the customer's name has been brought into the Sales table.

8. What's the Difference between Joins and Relationships?

This was one of the things that confused me when I started.Although joins and relationships connect tables, they are not exactly the same thing.

A Join

A join combines tables during the data preparation stage.In Power BI, this is commonly done in Power Query using Merge Queries.

A Relationship

A relationship connects tables in the data model.The tables remain separate, but Power BI understands how they are related.

So to remember simply:

Join = combine the data.
Relationship = connect the tables.

9. Common Join Types

When using Merge Queries in Power Query, you'll see several types of joins .

Inner Join

This returns only rows where there is a match in both tables.
For example:

Table A	Table B
C001	C001
C002	C002
C003	C004

An inner join would return:

C001
C002

The unmatched values are excluded.

Left Outer Join
This keeps all rows from the left table and brings in matching information from the right table.

For example:

Customers	Customer Details
C001	C001
C002	C002
C003

A left join keeps:

C001
C002
C003

Even though C003 has no match in the second table.This is one of the most commonly useful joins when you want to preserve your main dataset.

Right Outer Join

This is the opposite of a left join.It keeps all rows from the right table and matching rows from the left table.

Full Outer Join

A full outer join keeps all rows from both tables.It is useful when you want to identify both matching and unmatched records.

10. Where Do Joins Happen in Power BI?

Joins are created in Power Query.

Power Query can be transformed through:

Home -> Transform data

Then, in Power Query:

Home -> Merge Queries

You select:

The first table
The second table
The matching column(s)
The type of join

After the merge, you can expand the new column to bring the required fields into your table.

Power Query supports several join kinds, including inner, left outer, right outer, full outer, left anti, right anti, left semi and right semi joins.

11. So Should Everything be Joined?

No.
This is where data modelling becomes important.You don't necessarily need to combine every table into one giant table.A well-designed Power BI model often keeps tables separate and connects them using relationships.

For example:

                Customers
                    |
                    |
                    ↓
Products -> Sales <- Date

Here, Sales is the central table containing transactions, while Customers, Products and Date provide additional information for analysing those transactions.

This type of structure is closely related to a star schema, which is recommended as a useful modelling approach for Power BI.

12. A Simple Example in a Dashboard

Let's say our dashboard has a slicer for:

County

We select:

Nairobi

The Customers table contains the county information.

Because Customers is related to Sales through CustomerID, the filter can flow to the Sales table.

Power BI can then update our visual to show something like:

Total Sales in Nairobi
KSh 69,500

We did not have to filter every sale ourselves.The relationship did the work behind the scenes.That's the beauty of data modelling.

13. A Few Things to Check Before Creating Relationships

Before connecting two tables, check the columns you're using.

1. Make sure the data types match

For example:

CustomerID should not be Text in one table and Whole Number in another.
It is recommended that the columns used in a relationship have compatible data types.

2. Check for duplicates

The "one" side of a one-to-many relationship needs unique values.
For example, CustomerID should appear once in the Customers table.

3. Check for missing values

If Sales contains a CustomerID that doesn't exist in Customers, you may have an unmatched record.

4. Choose the correct relationship

Don't automatically choose many-to-many just because Power BI allows it.
Many-to-many relationships can be useful, but they require more careful modelling and are sometimes handled using a bridging table.

14. My Simple Mental Model

After learning all of this, this is how I think about it:

Power Query
This is where I clean, merge, change data types, remove errors and prepare tables.

Model View
"Let me tell Power BI how my tables are connected."This is where relationships come in.

Report View
"Now let me analyse and visualise the data."
The three work together:

       POWER QUERY
            ↓
   Clean & Transform
            ↓
      DATA MODEL
            ↓
   Connect Tables
            ↓
       REPORT
            ↓
   Analyse & Visualise

15. Final Takeaway

Data modelling can sound complicated at first, but the basic idea is simple.

Relationships connect tables inside your Power BI model.
Joins combine data based on matching values, usually during data preparation in Power Query.

Understanding the difference is important because it helps you decide whether you should:

Merge two tables
Keep them separate
Create a relationship
Use a one-to-many relationship
Use another relationship type
Or rethink the structure of your model

Honestly, once relationships start making sense, Power BI becomes much less intimidating. You stop seeing a bunch of random tables and start seeing a connected data model.

That is when Power BI starts to feel like a real analytical tool rather than just a dashboard-making app.

Building a Power BI Data Model for ShopKe: Understanding Relationships, Schemas and Joins

Stacy Mumbi — Mon, 14 Sep 2026 04:11:06 +0000

Introduction

Say your working as a data analyst at ShopKe, a fictional Kenyan e-commerce company selling electronics, fashion and home products. The management team wants to understand sales performance, customer purchasing behavior and product profitability.

The company has three datasets namely Customers, Products and Orders. Even though these datasets contain valuable information, they need to be properly organized before creating a Power BI dashboard.

This is where data modelling becomes important. A well designed data model allows Power BI to connect related tables, perform accurate calculations and present meaningful business insights.

In this article, I will explore data modelling approaches, fact and dimension tables, relationships, filter directions and Power Query joins using ShopKe as a practical example for better understanding.

1. Data Modelling in Power BI

Data modelling is the process of organizing data into tables and defining relationships between them so that the data can be analyzed properly.

A good data model is important because it supports:

Accurate reporting and DAX calculations.
Better query performance.
Scalability as data grows.
Easier report development.
Improved readability and maintainability.

For example, ShopKe may store customer information in one table and sales transactions in another. By connecting them using CustomerID, Power BI can analyze sales by customer without repeating customer details in every transaction.

1.1 Flat Table

A flat table stores all information in one table.

OrderID	CustomerName	ProductName	Category	Quantity	SalesAmount
O001	Stacy	Laptop	Electronics	1	75,000
O002	Brian	Mouse	Electronics	2	3,000
O003	Stacy	Shirt	Fashion	1	2,500

Structure

┌──────────────────────────────┐
│          SalesData           │
├──────────────────────────────┤
│ OrderID                      │
│ CustomerName                 │
│ ProductName                  │
│ Category                     │
│ Quantity                     │
│ SalesAmount                  │
└──────────────────────────────┘

Advantages: Simple to understand, easy to import and good for small datasets.

Disadvantages: Repeated information increases redundancy and the table may become difficult to maintain as the business grows.

When to use: Small datasets, quick analysis and simple reporting requirements.

Performance and complexity: It has few relationship complications, but unnecessary columns and repeated values can increase the amount of data stored.

1.2 Star Schema

A star schema contains a central fact table connected directly to multiple dimension tables.

For ShopKe, FactSales stores transactions, while DimCustomer, DimProduct and DimDate provide descriptive information.

Structure

ShopKe data model presented as a star schema

Advantages: Simple to understand, supports readable DAX, reduces unnecessary duplication and well suited for analytical reporting.

Disadvantages: Requires careful relationship design and may involve some repeated descriptive information within dimensions.

When to use: Sales, finance, inventory, customer and other business intelligence projects.

Performance and complexity: A well-designed star schema can support efficient analytical queries and compression while keeping the model relatively easy to navigate.

1.3 Snowflake Schema

A snowflake schema is a type of star schema where dimension tables are further divided into related tables.

For example, ShopKe may separate product information into Product, Subcategory and Category tables.

Structure

                    ┌──────────────┐
                    │  DimCategory │
                    └──────┬───────┘
                           │ 1
                           │ *
                    ┌──────▼───────┐
                    │DimSubcategory│
                    └──────┬───────┘
                           │ 1
                           │ *
┌─────────────┐     ┌──────▼───────┐
│  FactSales  │ *──1│  DimProduct  │
└─────────────┘     └──────────────┘

An example of how a snowflake schema model would look like

Advantages: Reduces repeated descriptive information and can support difficult hierarchies.

Disadvantages: More tables and relationships increase model complexity and may make report development less intuitive.

When to use: Complex organizational or geographical hierarchies, normalized data sources and situations where shared reference tables are useful.

Performance and complexity: It can reduce redundancy, but more relationships may make the model more complicated. In many cases, a star schema is preferred for simplicity.

2. Fact Tables and Dimension Tables

Fact tables

A fact table stores measurable business events. In ShopKe, FactSales contains individual sales transactions.

Example fields include:

SalesID
CustomerID
ProductID
DateKey
Quantity
SalesAmount

The FactSales table

Dimension tables

Dimension tables store descriptive attributes used to analyze facts.

Examples include:

DimCustomer containing customer names and regions.
DimProduct containing product names and categories.
DimDate containing dates, months and years.

Grain of a fact table

The grain describes what one row represents.

For example:

One row in FactSales represents one product line in one customer order.

This is important because an order may contain several product lines. Therefore, counting rows may not equal counting unique orders.

ShopKe's star schema

FactSales connects to the dimensions through keys:

DimCustomer[CustomerID] 1 ─── * FactSales[CustomerID]

DimProduct[ProductID] 1 ─── * FactSales[ProductID]

DimDate[DateKey] 1 ─── * FactSales[DateKey]

DimLocation[LocationID] 1 ─── * FactSales[LocationID]

This allows ShopKe to analyze total sales by product, customer or date.

3. Relationships in Power BI

A relationship connects tables by use of matching columns. Relationships are necessary because business data is often distributed across several tables.

Primary keys and foreign keys

A primary key uniquely identifies a row in a table. A foreign key references a key in another table.

For example, CustomerID is unique in DimCustomer but may repeat in FactSales because one customer can make multiple purchases.

This creates a one to many relationship.

Relationship cardinalities

One to Many (1:*)

One row in a dimension relates to many rows in a fact table.

Example:

DimCustomer[CustomerID] 1 ─── * FactSales[CustomerID]

This is the most common relationship in a star schema.

One to One (1:1)

Each row in one table matches at most one row in another table.

Example:

Employee[EmployeeID] 1 ─── 1 EmployeeDetails[EmployeeID]

It is appropriate when two tables contain separate information about the same entity and both sides have unique keys.

Many to Many (:)

Multiple rows in one table can relate to multiple rows in another table.

Example:

Students * ─── * Courses

One student can take many courses and one course can have many students.

A bridge table can be used for a clearer design as shown:

DimCustomer 1 ─── * BridgeCustomerSegment * ─── 1 DimSegment
Referential integrity and unique values

Referential integrity means foreign keys should correspond to valid keys in the related dimension, where applicable.

For example, every CustomerID in FactSales should also exist in DimCustomer. Missing or unmatched keys may indicate data quality problems.

Active and inactive relationships

An active relationship is used automatically for filter propagation.

An inactive relationship is not used automatically but can be activated in a DAX measure.

For example, FactSales may have OrderDate and ShipDate. DimDate can have an active relationship with OrderDate and an inactive relationship with ShipDate.

4. Filter Direction

Filter direction determines how filters move between related tables.

Single-direction filtering

Filters flow in one direction, mostly from dimensions to facts.

DimProduct ───────────> FactSales

If a user selects Electronics in a product slicer, Power BI filters FactSales to show sales for electronics products.

Single-direction filtering is recommended for star schemas because it provides predictable behavior and reduces ambiguity.

Bidirectional filtering

Filters flow in both directions.

DimProduct <──────────> FactSales

This may be useful in specific scenarios, such as certain bridge-table models.

However, bidirectional filtering should be used carefully because it can create ambiguous filter paths, unexpected results and unnecessary model complexity.

For ShopKe, I would use single-direction filtering by default and only introduce bidirectional filtering when there is a clear business requirement.

5. Joins in Power Query

A join combines rows from two tables using matching columns. In Power Query, this is performed using Merge Queries.

If ShopKe had the following tables:

DimCustomers

CustomerID	CustomerName
C001	Stacy
C002	Brian
C003	Aisha

DimOrders

OrderID	CustomerID	Amount
O001	C001	75,000
O002	C001	4,500
O003	C002	3,000
O004	C004	2,000

C003 has no order, while C004 appears in Orders but not Customers.

5.1 Left Outer Join

Returns all rows from the left table and matching rows from the right table.

Example: Customers LEFT JOIN Orders.

CustomerID	CustomerName	OrderID	Amount
C001	Stacy	O001	75,000
C001	Stacy	O002	4,500
C002	Brian	O003	3,000
C003	Aisha	NULL	NULL

Use: Finding customers who have not placed orders while retaining all customers.

5.2 Right Outer Join

Returns all rows from the right table and matching rows from the left table.

Example: Customers RIGHT JOIN Orders.

CustomerID	CustomerName	OrderID	Amount
C001	Stacy	O001	75,000
C001	Stacy	O002	4,500
C002	Brian	O003	3,000
C004	NULL	O004	2,000

Use: Identifying orders without matching customer records.

5.3 Full Outer Join

Returns all rows from both tables, including unmatched records.

Expected unmatched records:

CustomerID	CustomerName	OrderID	Amount
C001	Stacy	O001	75,000
C001	Stacy	O002	4,500
C002	Brian	O003	3,000
C003	Aisha	NULL	NULL

Matching records are also retained.

Use: Data reconciliation and identifying missing records in either table.

5.4 Inner Join

Returns only rows with matching values in both tables.

Expected output:

CustomerID	CustomerName	OrderID	Amount
C001	Stacy	O001	75,000
C001	Stacy	O002	4,500
C002	Brian	O003	3,000

Use: Keeping only customers who have matching orders.

5.5 Left Anti Join

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

Example: Customers LEFT ANTI JOIN Orders.

CustomerID	CustomerName
C003	Aisha

Use: Finding customers who have never placed an order.

5.6 Right Anti Join

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

Example: Customers RIGHT ANTI JOIN Orders.

OrderID	CustomerID	Amount
O004	C004	2,000

Use: Identifying orders with missing customer records.

In Power Query, a merge may create a nested table column. For joins that return matching records, the column can be expanded to display the required fields.

6. Power Query Joins vs Power BI Relationships

Even though both connect tables, they serve different purposes.

Feature	Power Query Merge	Power BI Relationship
Stage	Data preparation	Data modelling
Purpose	Combine data	Connect tables
Physically combines columns?	Yes, in the merged query result	No
Filter propagation	Not as a model relationship	Yes
Common use	Bring ProductCategory into a table	Connect DimProduct to FactSales

Example

If ShopKe needs to bring ProductCategory into FactSales, Power Query Merge can combine the columns.However, if DimProduct already contains ProductCategory, keeping it separate and creating a relationship is better.

Too much merging can create a wide table with repeated customer and product information. This may cause increased redundancy and the model being harder to maintain.Keeping fact and dimension tables separate supports a clearer and more scalable star schema.

7. Recommended Power BI Model for ShopKe

For ShopKe, I would recommend a star schema with FactSales connected to DimCustomer, DimProduct and DimDate.

I would use one-to-many relationships from the dimensions to the fact table and single-direction filtering by default.

Why?

Performance : Separating facts and dimensions can support efficient analytical queries.
DAX simplicity : Measures such as Total Sales can be written directly against FactSales.
Readability : Each table has a clear purpose.
Scalability : New transactions, customers and products can be added without redesigning the entire model.
Maintainability : Descriptive information can be updated in dimensions.
Report development : Users can easily combine dimension attributes with fact measures.

Example measures:

Total Sales =
SUM(FactSales[SalesAmount])
Total Orders =
DISTINCTCOUNT(FactSales[OrderID])
Average Order Value =
DIVIDE([Total Sales], [Total Orders])

I would use bridge tables for genuine many-to-many scenarios and inactive relationships for alternative date analysis when necessary.

Conclusion

Data modelling is the foundation of a reliable Power BI report. It determines how data is organized, how tables interact and how accurately business questions can be answered.

As much as flat tables are simple and snowflake schemas can support complex structures, a star schema is most likely the most suitable choice for a business intelligence project.

Through the ShopKe example, we have seen how fact and dimension tables work together, how relationships control filter propagation and how Power Query joins prepare data for analysis.

The most important lesson is that proper Power BI development is not only about creating visuals. It is about designing a model that is accurate, understandable, scalable and useful for decision-making.

A well designed data model turns disconnected data into meaningful business insights.

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

Stacy Mumbi — Sat, 05 Sep 2026 13:13:23 +0000

Introduction

After completing Excel's coursework this week courtesy of the LuxDevHQ Data Science, Analytics and AI Programme, I decided to put what I had learnt into practice by analyzing Jumia product data. I challenged myself to turn the raw data into something useful for analysis and decision making.

The dataset contained 115 product listings with information such as prices, discounts, reviews and ratings. However, it was far from ready for data analysis. There were missing values, duplicate records, negative review counts, inconsistent formatting and even one product whose price was given as a range.

So, this project ended up being about much more than creating a dashboard. I first had to understand the data, identify the issues, make reasonable cleaning decisions, document those decisions and then use the cleaned data to build an interactive Excel dashboard.

In this article, I'll walk through that process, from the initial data-quality audit to the final dashboard and the insights I found from the analysis.

The Dataset and Business Questions

The dataset, Excel_jumia_dataset.csv, contained six fields originally:

Product
Current price
old price
Discount
Review
Ratingd

The last column, Ratingd, was misspelled in the original dataset and was later corrected to Rating.

One of the first things I noticed was that the dataset didn't contain actual sales, revenue or units sold data. Because of this, I couldn't use sales as a measure of product performance.

Instead, I used review count as a proxy for customer engagement throughout the project.

This was important to take note of because having more reviews doesn't necessarily mean that a product has higher sales. A product that has been listed for a longer period, for example, may simply have had more time to accumulate reviews.

With that in mind, I focused on the following questions:

Are bigger discounts associated with more reviews?
Do highly rated products receive more engagement?
Does product price have any relationship with rating?
Which products stand out based on ratings, reviews or discounts?
Which products might need a different pricing, marketing or listing strategy?

Data-Quality Audit

Before changing anything, I wanted to understand exactly what I was working with.

The initial audit showed:

115 rows and 6 columns
58 blank Review values
58 blank Rating values
3 exact duplicate rows
6 repeated product names with different prices, discounts, or review counts
57 negative Review values
1 row where both Current price and Old price were given as ranges
Ratings were within the expected 0–5 range
Discounts were within 0–100%
No Current Price was higher than the corresponding Old Price

One thing that I immediately realised: all 57 non-blank Review values were negative.

That was unusual. Since a review count cannot be negative realistically, I treated this as a likely systematic scraping or formatting issue rather than assuming the values represented something meaningful.

If only a few values had been negative, I would have been more cautious about changing them. But because every non-blank review value had the same issue, the pattern strongly suggested a consistent data-quality problem.

The original dataset as imported into the Raw_data sheet. The negative Review values and misspelled Ratingd header are visible.

Cleaning the Data and Why I Made These Decisions

Cleaning the dataset was one of the most important parts of the project. I didn't want to change values simply because they looked inconvenient. For each major cleaning step, I considered what the data was actually telling me and how the decision would affect the analysis.

1. Fixing the Negative Review Counts

There were 57 negative Review values and all non-blank review values had this issue.

I created a helper column and used the following formula:

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

This converted the negative values into positive review counts while keeping genuinely blank cells blank.

After checking the results, I pasted the corrected values as values so that the cleaned numbers were no longer dependent on the formula.

2. Handling Missing Reviews and Ratings

Both Review and Rating contained 58 blank values.

I intentionally left these cells blank.

A blank Review means that the review count wasn't captured. A zero means that the product had zero reviews. These are two completely different situations.

Replacing the blanks with zero would therefore have affected averages, rankings and other calculations in ways that weren't supported by the original data.

3. Handling the Price Range

One product, a 1/2/3 Seater Elastic Sofa Cover, had its prices listed as ranges:

Current price: KSh 1,620–1,980
Old price: KSh 2,200–3,200

I had three options to choose from:

Use the lower value
Remove the row
Use the midpoint

I chose the midpoint.

Using the lower bound would ignore part of the information provided, while removing the row would mean losing a useful product just because its price was formatted differently.

The original range was retained elsewhere so that the decision could still be traced back to the source data.

There was also an interesting consequence of this decision. When I calculated the discount using the midpoint prices, I got 33%, while the seller's advertised discount was 38%.

Rather than replacing the advertised value, I kept both values. The difference is documented as a result of using an estimated midpoint rather than an exact price.

4. Removing Duplicates

The dataset contained three rows that were completely identical across all six original fields. I removed these as they were exact duplicates.

However, there were also six cases where the same product name appeared more than once but the prices, discounts or review counts were different.I kept these records.

Because Jumia is a marketplace with multiple sellers, the repeated product names could represent different seller listings rather than duplicate records. Removing them without evidence would have meant making an assumption that the data didn't support.

5. Fixing Text and Formatting Issues

I also made other formatting corrections:

Removed "out of 5" from the Rating values
Converted Rating into a numeric decimal
Changed Ratingd to Rating
Standardized old price to Old Price
Converted prices from text into numeric currency values
Formatted prices as KSh #,##0.00

The cleaned tblProducts table showing corrected data types, standardized fields and additional analytical columns.

All of these cleaning decisions were documented in the Data Dictionary sheet so that the process could be reviewed and traced back to the original data.

Adding Enrichment Fields

Once the basic cleaning was complete, I created additional fields to make the dataset more useful for analysis.

Discount Amount and Calculated Discount

I calculated the difference between the Old Price and Current Price to get the Discount Amount.I also calculated the corresponding percentage discount and kept it alongside the original advertised Discount.
This made it possible to compare the advertised discount with the discount calculated from the prices.

Rating Category

I grouped products into three rating categories:

Poor: < 3
Average: 3–4.5
Excellent: > 4.5

The original brief left ratings from 4.1 to 4.5 without a category. I therefore followed the suggested option of including this range under Average and documented this as a working assumption.

Discount Category

Discounts were grouped into:

Low: < 20%
Medium: 20–40%
High: > 40%

Price Category

Rather than guessing price cutoffs, I used the data itself to determine the categories.

I calculated the first and third quartiles of Current Price using

QUARTILE.INC()

The resulting thresholds were:

Price Q1: KSh 493
Price Q3: KSh 1,669.50

I then used these thresholds to classify products into Low, Medium and High price categories.

Engagement Strength

I also wanted a data-driven way to identify products with relatively high engagement.

I used the 75th percentile of Review count as the threshold:

Review Q3: 13 reviews

Products with 13 or more reviews were therefore classified as having Strong Engagement.

Data Status

Because the dataset contained many missing ratings and reviews, I added a Data Status field showing whether each product was:

Complete
Missing rating
Missing review
Missing both

This made it easier to understand the level of data coverage behind the analysis.

Combination Flags

Finally, I created four flags to identify products with interesting combinations:

High discount + low rating
High discount + low engagement
Many reviews + average rating
Strong engagement + excellent rating

Whenever one of the required values was missing, the formula returned "Missing" rather than making an assumption.

Example of an Excel formula used to classify products into Rating Categories.

PivotTables and Analysis Workflow

With the data cleaned and enriched, I moved on to the analysis.
One of the main things I wanted to investigate was whether price, discount, rating and review count were related.

I focused on three relationships:

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

Because Review and Rating contained missing values, I didn't want blank cells to interfere with the correlation analysis.

I therefore created helper ranges using FILTER() to return only rows where both variables in each pair were available. I then used these complete pairs for the correlation calculations and scatter plots.

The results were:

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

All three relationships were very weak.

For example, the correlation between Discount and Reviews was -0.111, which is very close to zero.

The R² values were also extremely small, meaning that each variable explained very little of the variation in the other variable.

Scatter plots for Discount vs. Reviews, Rating vs. Reviews, and Current Price vs. Rating, each with a linear trendline, equation and R² value.

Product Rankings

I also created PivotTables to identify:

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

I also used the combination flags to identify products that met the four additional conditions.

For rating-based rankings, products with missing ratings were excluded.
Where products had the same rating, I used review count as the tie-breaker. For review-count ties, rating was used as the tie-breaker.

This made the ranking rules explicit rather than leaving the ordering to chance.

_PivotTables used to identify the highest and lowest-performing products across ratings and discounts.

Building the Interactive Dashboard

After completing the analysis, I brought the most useful pieces together into a single-screen dashboard.

I wanted the dashboard to answer the main questions quickly without requiring someone to scroll through multiple sheets.

The dashboard includes:

KPI Cards
- Total Products
- Average Price
- Average Discount
- Average Rating
- Total Reviews
Product Ranking Charts
- Top 10 by Rating
- Top 10 by Reviews
- Top 10 by Discount
Relationship Analysis
- Discount vs. Reviews
- Rating vs. Reviews
- Price vs. Rating

Each scatter plot includes a trendline, equation and R² value.

Category Analysis
- Rating Mix
- Discount Mix
Interactive Slicers The dashboard has slicers for:
- Rating Category
- Discount Category
- Price Category

These slicers are connected to the compatible PivotTables and charts, allowing the dashboard to update when a user filters the data.

The completed single-screen dashboard combining KPIs, product rankings, relationship analysis, category charts and interactive slicers.

Key Findings

After cleaning and analyzing the data, a few findings stood out.

1. Bigger Discounts Didn't Necessarily Mean More Engagement

The correlation between Discount and Review count was -0.111.

That's really close to zero.

So in this dataset, products with larger discounts did not necessarily have more reviews.

2. Price and Rating Were Barely Related

The correlation between Current Price and Rating was 0.110.
This is also a very weak relationship.

In this dataset, highly rated products were not always more expensive or cheaper than products with lower ratings.

3. Some Heavily Discounted Products Still Had Weak Performance

Some products combined a high discount with either a low rating or low engagement.

These products caught my attention because offering a bigger discount may not solve the underlying problem.

They could instead be worth investigating from a listing-quality perspective, such as product descriptions, images, presentation or customer expectations.

4. Some Products Had Both Strong Engagement and Excellent Ratings

There were also 5 products that stood out for having both strong engagement and excellent ratings.They could provide useful examples of what appears to be working well.

Even though the dataset doesn't contain enough information to explain why these products perform well, they would be good choices for further investigation.

Business Recommendations

Based on the patterns in the data, I would recommend:

Not to rely on discounts alone to drive engagement. Since discount and review count were almost not related, sellers could test other approaches such as improving product descriptions, images and overall listing quality.
Investigating heavily discounted products with low ratings or engagement. Instead of immediately offering an even bigger discount, it may be better to first understand why the product isn't attracting positive customer feedback.
Studying products with strong engagement and excellent ratings. These products could provide useful examples for understanding what works well across listings.
Using data carefully when making decisions. The results show patterns in this dataset, but they don't prove that changing one variable will cause another to change.

Limitations

This project also has limitations like any other.

The biggest one is that the dataset doesn't contain sales, revenue, units sold or listing-age data. Because of this, review count is only a proxy for engagement and cannot be treated as a measure of sales performance.
There is also one product whose price had to be estimated using the midpoint of a range rather than an exact value.
The Rating Category assumption for ratings between 4.1 and 4.5. I grouped these under "Average" based on the suggested option in the project brief, but this was just a working assumption .
The correlations in this analysis describe relationships between variables. Correlation does not imply causation.

What I Learned

One of my biggest lessons from this project was that data analysis starts before the charts.

Initially, the dataset looked small and straightforward. But once I started auditing it, I found several issues that could have easily affected the final results if I had jumped straight into analysis.

The negative review counts were a good example. If I hadn't investigated that pattern, I could have ended up presenting misleading engagement figures.

I also learned the importance of documenting assumptions. For example, rather than silently changing the price-range row or filling missing values with zeros, I recorded what I did and why.

Most importantly, this project helped me see Excel as more than just a spreadsheet tool. I was able to use it for the full analytics process that is data auditing, cleaning, transformation, analysis, visualization and communication , all within one workbook.

And probably the biggest lesson was that a good dashboard isn't just about making something look nice. The real value comes from making sure that the numbers behind it are trustworthy and that the story you're telling is actually supported by the data.

I am honestly still in awe of how such a seemingly small dataset could yield so many other columns and useful insights after proper cleaning and analysis.
From just a few original columns, I was able to create additional fields, uncover patterns, build meaningful visualizations and turn the data into a dashboard that tells a story.

How I love being a data analyst!

Project Files

The complete project is available here:

GitHub Repository: Jumia_Product_Performance_Dashboard
Dashboard Worksheet: Available in the repository under dashboard
Raw Dataset: Available under data

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

Stacy Mumbi — Sun, 30 Aug 2026 05:47:14 +0000

Introduction

Data is definitely everywhere because almost all the information we receive everyday can be collected and analysed data, but having a dataset is only the beginning of the data analytics process. Before analysing data and drawing conclusions from it, it is important to make sure that the data is accurate, consistent, complete and in the correct format through a process called data cleaning.

During Week 3 of my Data Science and Analytics training at LuxDev Academy, I was introduced to the basics of Microsoft Excel and how it can be used to prepare data for analysis. I used to view Excel as just an application that is used to view spreadsheets, but this week I learnt that it has many useful features for working with real-world datasets. These include sorting, filtering, formatting, removing duplicates, finding and replacing values and using functions such as TRIM, PROPER and CONCAT which are known as Text Functions.

For practice, I decided to work on a synthetic Kenyan M-Pesa transaction dataset generated using Mockaroo. The dataset contains 193 transaction records and includes information such as transaction IDs, transaction dates, sender and receiver names, transaction types, transaction amounts, transaction status, counties and transaction fees.

Even though the dataset was synthetic and small, it contained several inconsistencies that provided an opportunity to practise the data-cleaning concepts I had learned. In this article, I will walk through how I did the cleaning, from importing the dataset into Excel to identifying and correcting formatting, missing values, duplicates and inconsistencies.

1. Generating and Importing the Dataset

I first generated the M-Pesa transactions dataset using Mockaroo and downloaded it as a CSV(Comma Separated Values) file. I chose an M-Pesa dataset because mobile money transactions are really relevant to the Kenyan context as millions of people transact money everyday using M-Pesa and it can contain different types of information that would be useful for data analysis.

After downloading the CSV file, I opened Microsoft Excel. Instead of opening the file directly through the File section, I practised importing the data through the Data option on the ribbon at the top of the blank workbook: 'Data' then 'Get Data' then 'From File' then 'From Text/CSV'. Excel then showed a preview of the data before loading it into the workbook.

It was a very important first step before beginning any cleaning.

This is how the dataset looked like after loading and before I started cleaning.

2. Making a Duplicate Sheet Before Cleaning

Before making any changes to the data, I duplicated the worksheet by right-clicking the sheet tab by copying the whole dataset by first using keyboard shortcut Ctrl+A to select the whole dataset,then Ctrl+C to copy, then added a new sheet and then right-clicked in the top-left cell to paste the dataset . This gave me an untouched version of the original dataset to refer back to, while I carried out all my cleaning on the duplicate sheet.
If I made a mistake while cleaning, I still had the original data intact rather than needing to re-download the dataset or opening it from my file explorer.

Creating a backup copy of the sheet so the original data stayed untouched.

3. Formatting, Sorting and Checking for Duplicates

Once the dataset was loaded, I used 'Home' then 'Format' then 'AutoFit Column Width' so that all the values were completely visible, which made it easier to see the data during cleaning.

I then checked the dataset for duplicate records using 'Data' then 'Data Tools' then 'Remove Duplicates', selecting all columns so that only exact duplicate rows would be removed. Excel returned that no duplicates were found. Even though none were present, the step was important because checking that a potential problem does not exist is as much a part of data cleaning as fixing one that does.

I also applied filters via 'Home' then 'Sort & Filter' then 'Filter', which added drop-down buttons to each column header. This made it much faster to look at individual columns. For example, checking 'County' for blanks or checking 'Transaction Status' and 'Transaction Type' for inconsistent entries without having to scroll through every column.

The Home ribbon tools I used for formatting and creating the drop-downs.

4. Checking and Correcting Data Types

I then went column by column checking whether Excel had interpreted each one correctly, since a value can look correct visually while having the wrong data type.

Transaction ID - changed from Number to Text, since it is an identifier rather than a value that can be calculated or aggregated.
Transaction Date - changed from General to a proper Date format (year-month-day), making it possible to sort and support analysis later.
Sender Name / Receiver Name - already correctly identified as text.
Sender Phone - converted to Text, since phone numbers are identifiers not quantities that can be aggregated.
Transaction Type / Transaction Status - already identified as text, but needed further cleaning for consistency.
Transaction Amount / Transaction Fee - formatted from Number to Currency (KES), since these represent monetary values and needed to clearly show that.

5. Handling Missing Values

Using the drop-downs I had applied earlier, I checked each column for blanks. I found missing values in County, as well as in the numeric fields Transaction Amount and Transaction Fee.

I did not treat all missing values the same way. For the text column, County, I replaced blanks with "Unknown". For the numerical fields, I left the blanks as they were rather than filling in a made-up value as it would have added incorrect information to the dataset rather than fixing it.

This taught me that how you handle a missing value should depend on what the column represents and not a single default rule applied everywhere.

6. Standardising Text with Key Functions

Several columns had inconsistent capitalisation or extra spacing, so this was where most of my actual cleaning work happened:

Find and Replace (Ctrl + H) - used to correct inconsistent entries in Transaction Type (e.g different casings of "Pay Bill") and Transaction Status (e.g "failed" vs "Failed").
TRIM - used in a helper column to remove hidden extra spaces from text values that looked correct but weren't matching due to leading spaces.
PROPER - used in a helper column to standardise capitalisation in Sender Name and Receiver Name (e.g turning "MUTUA KARANJA" into "Mutua Karanja").
CONCAT - used to restore the leading zero on Kenyan phone numbers after removing apostrophes and the country-code had left some numbers starting with "1" instead of "0". The formula I used was =CONCAT("0",D2).

For each of these, I applied the formula to the first cell in a row, used AutoFill to apply it down the column, then used Paste Special for Values only to convert the formula results into permanent values in a new column before deleting the helper columns. This kept my final dataset clean while preserving the original column structure.

For cleaning the sender name column I created a helper column and used the above formulas to clean it.

7. What I Learned From the Cleaning Process

Working on this dataset helped me understand that data cleaning is more than removing duplicates and deleting or filling blank cells.

This is a brief summary of what I learnt:

The importance of understanding the meaning of each column before deciding how to clean it. A phone number consists of digits, but treating it as numeric wouldn't make sense because there's no meaningful total or average of phone numbers. Also, a Transaction ID may contain only numbers but should be treated as text because it's an identifier, not a value to calculate.
Missing values should be handled according to the type and meaning of the data i.e "Unknown" for text fields where appropriate and left blank for numeric fields instead of guessing.
Sorting and filtering are really important tools as they help you find out what's actually in a column before you touch it. In addition, creating a copy of the original sheet before cleaning meant I could correct mistakes without risking the original data.
Excel usually offers more than one way to solve the same problem like in the case of PROPER versus Find and Replace and choosing between them often comes down to the type of inconsistency and personal preference.

Conclusion

My first practical data-cleaning project using Excel gave me a much better understanding of how raw data can be prepared for analysis.

I started with a synthetic Kenyan M-Pesa transactions dataset generated using Mockaroo and imported it into Excel. Before making any changes, I duplicated the sheet to preserve the original data, then worked through the dataset column by column correcting data types, formatting monetary fields as KES currency, converting dates into the correct format, treating identifiers like Transaction ID and phone numbers as text, handling missing values carefully and standardising text using TRIM, PROPER, Find and Replace and CONCAT.

The dataset after cleaning now with consistent formatting and standardized text.

The dataset was not too messy, but that actually made it easier for my first practice exercise. It allowed me to focus on understanding the purpose of each Excel tool rather than becoming overwhelmed by a very complicated dataset.

I learnt that good data cleaning requires both technical Excel skills and an understanding of the data itself. Excel provides the tools, but I still need to make informed decisions about how those tools should be used.

As I continue learning data analytics, I hope to build on these skills and become more confident in using Excel for larger and more complex datasets. Week 3 has given me a strong foundation and I now have a better understanding of how important data cleaning is before carrying out analysis and visualisation.

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

Stacy Mumbi — Sat, 22 Aug 2026 17:59:57 +0000

Introduction

It's only been two weeks since I enrolled in the LuxDev Data Science, Analytics and AI program yet I've already learnt so much!
One of the first things I've been able to learn is how to take a project from a local folder in my PC and publish it to GitHub using Git and SSH.
For someone who is still building their skills in data and tech,terms like repositories,commits,staging,remotes and SSH keys were new to me at first. Working on my first project helped me understand these commands and also what happens behind the scenes.

So for a start,I decided to document my experience of taking my first GitHub project from a local folder to GitHub using Git and SSH.

What are Git and GitHub?

Interestingly, I learnt that Git and GitHub are not the same thing. This is why understanding what each of them means became a very important step in my project.

Git

Git is a version control system which runs in my PC and helps me keep track of changes made to files in a project.
As I work,I might change my code,update my analysis,add new files or fix mistakes. Git lets me keep track of the changes I make over time instead of having multiple edited copies of the same file. Each saved version is called a commit.

GitHub

GitHub is an online platform where repositories from Git can be stored and shared.
Git does not need to work with GitHub as I can use it in my PC without uploading my project elsewhere.
However, GitHub becomes useful when I want to:

Keep a back up of my projects online
Show potential employers what I can do
Share my work
Collaborate with others
Build a portfolio
Contribute to open-source projects

In a nutshell, this is the journey the local folder in my PC took to reach Github:

Project folder in my PC
         `
Track changes with Git
         `
Commit changes
         `
Push to GitHub

Step 1: Create the Project Files

I first created my project folder in my PC's file explorer and named it Kenya_Health_Records_Analysis.
Then I opened Git Bash and navigated to it using these commands:

cd Desktop
cd Kenya_Health_Records_Analysis

cd(change directory) is a command that tells Git to enter a file or folder in simple terms.
I then proceeded to add a data and README.md file in the project folder:

mkdir data
touch README.md

mkdir(make directory) is used to create a new file or folder from the Git Bash terminal but notice I did not use it to create the README.md file because touch is used to create files with extensions like .md, .py or .ipynb .
I then copied my Kenya_Health_Records.csv dataset and pasted it into the newly created data file in my file explorer.
To confirm this in Git Bash I ran the ls command which lists the contents of a file or folder.
This is what I could see:

ls data
Kenya_Health_Records.csv

Then I went ahead to briefly describe the project in the README.me using the echo command:

echo "# Kenya_Health_Records_Analysis" >README.md
echo " Using Excel to analyze Kenya health records to uncover,trends,patterns and insights in healthcare data." >>README.md

echo prints text while >directs the text into the README.md file however > can replace what is already in the README.md file that is why in the next line I used >> which adds content below what is already there.
To view the full README.md I used the cat command (which displays the contents of a file in the terminal) and ran cat README.md which retrieved:

# Kenya_Health_Records_Analysis
Using Excel to analyze Kenya health records to uncover,trends,patterns and insights in healthcare data.

Step 2: Turning the Folder into a Git Repository

I then ran:

git init

This command initializes a new Git repository and Git begins to manage the project. It also added a new hidden .git file in the project folder.

Step 3: Checking the Status of My Project

One of the most useful Git commands is:

git status

This tells me what Git can see or what is happening inside my repo. At first it told me that both the data and README.md were untracked files meaning that Git had not been told to include them in the project history or to commit.

Step 4: Staging the Files

To stage both files I used:

git add .

The dot tells git to add everything in the current directory.
I then checked the status again using:

git status

The files appeared under the changes to be committed.

Step 5: Creating My First Commit

I then created my first commit:

git commit -m "Add Kenya Health Record Analysis"

The -m allows me to provide a message that explains what I changed.
Hence a commit message can explain what happened at different points in time in the project's history if written clearly.
I then ran:

git log --oneline

This retrieved my unique commit ID and my commit message.

Step 6: Creating a GitHub Repository

After creating the project locally, I went to GitHub and created a new repository and named it the same way I had in my File Explorer:

Kenya_Health_Records_Analysis

This would become an online version of my local Git repository. I also learnt that even though I created the repo and named it the same way,it does not automatically upload my local files.
I still needed to connect my local repo to the GitHub repo and push the changes and this where an SSH Key comes in.

Step 7: Defining an SSH Key

To me, it sounded like such a complex tech term but it was actually just a simple concept.
SSH(Secure Shell) provides a secure way for my PC to connect with my GitHub account.
So after creating the repo on GitHub I was given two link options to connect to it;HTTPS and SSH.

Step 8: Connecting my Local Repository to GitHub

Since I then had both my local Git and GitHub respositories, I needed to connect them using a remote.
A remote is a reference to another repository hosted online.
I then copied the SSH key for my GitHub repository and ran this in Git:

git remote add origin git@github.com:SMumbi5144/Kenya_Health_Analysis.git

origin is the name given to the remote repository.
The SSH address:

git@github.com:SMumbi5144/Kenya_Health_Records_Analysis.git

tells Git where the remote repo is.
To check if the two repos had connected I ran:

git remote -v

Step 9: Pushing the Project to GitHub

Once I had confirmed the two had connected, I pushed my local repo to GitHub by running:

git push -u origin main

git push - tells Git to upload my commits to a remote repository.
origin - refers to the GitHub repository I had added as my remote.
-u - sets an upstream relationship between my local main branch and the remote main branch.

Step 10: Seeing My Project on GitHub

This was the most exciting part of the process. For a beginner like me, it was quite the journey.
I refreshed my GitHub and I could then see my local project files online.
I could therefore view the project online,share it with others and continue working on it.

What I learnt to Avoid

We all make mistakes as we learn and this project was no exception.
Therefore, in order to successfully upload my local files to my GitHub repository I learnt that I should:

Not forget to initialize Git using git init.
Not forget to stage files using git add . else any changes I make will not be part of my next commit.
Never assume commit means upload, to send to GitHub I ought to use git push.
Avoid using unclear commit messages since they will not help me understand the project history easily.

Conclusion

My first project taught me more than just a few commands. It taught me patience and the importance of paying attention to detail and being systematic since a command will only work efficiently if the correct command is used. These are very important qualities for a data professional to develop so as to enhance the quality of their work.

I also learnt some cool concepts such as how a version control system works, how an SSH Key can connect my PC to GitHub and how I can manage and share my projects.

As I continue developing my skills at and outside LuxDev Academy, GitHub will help me document my projects and build a portfolio that shows what I can do.

This first project was the beginning of my journey towards becoming a better data professional.