DEV Community: Jack Kisutsa

From Dirty Logistics Data to a Management-Ready Power BI Solution

Jack Kisutsa — Sat, 26 Sep 2026 13:01:13 +0000

Introduction

Building a dashboard is only the visible part of a business intelligence project. Before a chart can support a decision, the underlying data must be understood, cleaned, standardised, modelled and validated.

That was the central lesson from my JCars Logistics Power BI project.

The assignment was to develop a complete business intelligence solution for a vehicle sales and logistics company operating through branches in Kenya. The source file contained sales, customers, vehicles, representatives, payment information, delivery activity, logistics cost, returns, ratings and recorded revenue.

It also contained many of the problems analysts meet in real operational data: duplicate identifiers, mixed date formats, invalid calendar dates, inconsistent categories, text inside numeric columns, multiple currencies and financial values that did not always agree.

This article explains how I moved from that raw CSV to a tested Power BI model and an interactive management report.

Understanding the business problem

JCars Logistics needed more than a collection of visuals. Management needed to understand:

How much active revenue and profit the business generated.
Which branches and vehicle types contributed the most value.
Whether deliveries met a reasonable service level.
Where logistics cost and loss-making orders required attention.
Which vehicles experienced high returns.
Whether the source revenue and date fields were reliable enough for decision-making.

I therefore organised the project around a simple analytical flow:

Preserve and profile the source.
Clean and validate the data in Power Query.
Build a dimensional model.
Create explicit DAX measures.
Design an executive page and supporting analytical pages.
Test the entire report.
Convert the analysis into findings and recommendations.

The raw dataset

The CSV contained 276 rows and 32 columns covering the 2025-2026 period.

Each row represented an order record or order line associated with a customer, vehicle configuration, branch, sales representative and delivery outcome. Because Units Sold could exceed one, a row did not necessarily represent one individual vehicle.

The first important modelling decision was not to trust Order ID as a technical key. The column contained blanks, placeholders and repeated identifiers. I added Source Row ID from 1 to 276 to create a stable audit key while preserving the original identifier for business reference.

Profiling the data before cleaning

I reviewed every column before making transformations. This prevented me from applying a data type simply because the column name suggested one.

The audit identified 21 significant issue categories, including:

Order and delivery dates stored as text, Excel serial numbers and invalid calendar dates.
Delivery dates earlier than order dates.
Many spellings and formats for the same customer type, branch and region.
Misspelled sales-representative and vehicle names.
Text, zero, negative and blank values in Units Sold.
Prices and costs stored in KES, USD, EUR and ZAR.
Question-mark prefixes in some financial values.
Discounts represented as percentages, decimals, whole numbers and text.
Ratings written as numbers, fractions and descriptive text.
Return indicators stored as Boolean values, yes/no labels, letters and blanks.
Recorded revenue that was missing, negative, zero or inconsistent with the other order fields.

The audit changed how I approached the project. Rather than trying to force every source value into a clean category, I decided to preserve uncertainty through nulls, Unknown categories and explicit quality statuses.

Structuring the Power Query workflow

I preserved the original query as Raw JCars Data and disabled its load. I then created Stg JCars Data as a reference query for the transformations.

This structure served two purposes:

The source remained unchanged and available for comparison.
Every downstream table could be created from one prepared layer.

I cleaned the data column by column. The main stages were:

Add Source Row ID.
Trim and clean text.
Standardise null markers.
Clean order, customer and location fields.
Map branches, representatives and lead sources.
Standardise vehicle fields.
Parse quantities and discount rates.
Parse and convert monetary values.
Standardise operational status fields.
Parse ratings, review counts and return indicators.
Clean the date fields.
Create calculated and validation columns.
Create the fact and dimension tables.

Cleaning one business area at a time made it easier to verify the effect of each step before moving to the next column.

Solving the date problem

The date fields required the most judgement.

The source included examples such as:

09-Sept-25
Excel serial values such as 45914
31/02/2026
April 31 2026
Ambiguous slash dates such as 01/12/2026

A direct type conversion would either produce errors or interpret some values incorrectly. I used controlled parsing for Excel serials and recognised text formats then documented special decisions at row level.

For example:

09-Sept-25 was parsed as 9 September 2025.
April 31 2026 was corrected to 30 April only when another valid date supported that correction.
31/02/2026 was corrected to 28 February 2026 where a valid related date supported the assumption.
Impossible dates without supporting evidence remained null.
Valid dates with delivery before order were retained and flagged instead of silently changed.

I created Date Validation Status so the report could distinguish:

Valid date sequences.
Missing or invalid order dates.
Missing or invalid delivery dates.
Delivery before order date.

The final model identified 48 orders with date-quality issues. These records remained part of the dataset but invalid chronology did not enter the average delivery-duration calculation.

Converting currencies to KES

The financial columns included KES, USD, EUR, ZAR, unlabeled amounts and question-mark prefixes. Aggregating those values directly would have produced meaningless totals.

I separated currency identification from numeric parsing, then applied fixed project rates:

Currency	KES per unit
KES or unlabeled	1.00
USD	130.00
EUR	150.00
ZAR	7.50

The assignment specifically required unlabeled monetary values to be treated as KES.

The question-mark prefix required an additional assumption. Based on the scale and pattern of the affected amounts, I treated it as USD and documented the decision rather than allowing the values to become null without explanation.

The rates were fixed rounded assumptions so the analysis would be reproducible across the dataset. They were not intended to represent the exact exchange rate on every transaction date.

I referred to the Central Bank of Kenya Foreign Exchange Rates page. CBK publishes indicative daily rates based on average market buying and selling rates. The model therefore creates a common analytical reporting currency but it should not replace transaction-date accounting conversion.

Calculating and validating revenue

The dataset already contained Revenue Recorded but I did not assume it was correct. I calculated revenue independently in Power Query:

Calculated Revenue KES =
Units Sold × Unit Selling Price KES × (1 − Discount Rate)
+ Delivery Fee KES

I then compared calculated and recorded revenue and created three validation outcomes:

Match
Mismatch
Cannot Validate

The result was one of the most important findings in the project:

116 records matched.
123 records mismatched.
37 records could not be validated.
The match rate among validateable records was 48.54%.

Instead of hiding the discrepancy, I made it visible on the Customer and Data Quality page. A dashboard can communicate both business performance and the reliability of the data used to calculate it.

Defining transaction status

The source contained payment, delivery and return information that could produce different interpretations of whether an order should count as active revenue.

I created Final Transaction Status with four mutually exclusive categories:

Active Sale: 153 orders.
Returned: 73 orders.
Cancelled: 42 orders.
Refunded: 8 orders.

Active commercial KPIs use only Active Sale records. This prevents returned, cancelled and refunded transactions from inflating active revenue and profit.

The Returned Orders measure is deliberately different from the final transaction-status count. It uses the cleaned returned flag, resulting in 88 returned orders among 256 records with a known return status. That distinction is documented because the two fields answer different questions.

Building the data model

The final Power BI model uses a compact star schema:

FactSalesOrders: 276 rows.
DimDate: 730 dates covering 2025 and 2026.
DimBranch: 8 standard branches.
DimSalesRep: 10 representatives.
DimVehicle: 35 vehicle combinations.
_Measures: explicit DAX measures.

The model uses many-to-one relationships and single-direction filtering from dimensions to the fact table.

The active date relationship connects FactSalesOrders[Order Date] to DimDate[Date]. A second inactive relationship connects Delivery Date to the same dimension. Delivery-date measures activate that relationship using USERELATIONSHIP.

This allowed one date table to support both sales analysis and delivery analysis without creating ambiguous filter paths.

Creating the DAX layer

I used explicit measures so the business logic remained visible and reusable.

Active Orders =
CALCULATE(
    [Total Orders],
    FactSalesOrders[Final Transaction Status] = "Active Sale"
)

Active Sales Revenue KES =
CALCULATE(
    SUM(FactSalesOrders[Calculated Revenue KES]),
    FactSalesOrders[Final Transaction Status] = "Active Sale"
)

On-Time Delivery Rate =
DIVIDE(
    [On-Time Delivered Orders],
    [Delivered Orders with Valid Duration]
)

Return Rate =
DIVIDE(
    [Returned Orders],
    [Orders with Known Return Status]
)

Orders by Delivery Date =
CALCULATE(
    [Total Orders],
    USERELATIONSHIP(
        FactSalesOrders[Delivery Date],
        DimDate[Date]
    )
)

Other measures covered gross order revenue, active cost, gross profit, gross margin, average order value, logistics cost, discounts, cancellations, refunds, loss-making orders, revenue year to date and data-quality counts.

One practical DAX lesson was that CALCULATE changes filter context. It does not replace multiplication, subtraction or addition. Row-level financial calculations remained in Power Query, while measures used CALCULATE, SUMX, DIVIDE and time-intelligence functions to respond dynamically to report filters.

Designing the report

The final report contains five visible pages and two hidden support pages.

Executive Overview

The first page provides the management summary. It shows active revenue, gross profit, gross margin, active orders, on-time delivery and average customer rating. Supporting visuals compare branch revenue, monthly revenue and margin, transaction status and vehicle profitability.

Sales and Profitability

This page focuses on revenue, cost, profit, margin, loss-making orders, sales representatives, discounts, customer types and vehicle economics.

Operations and Logistics

The logistics page analyses delivered orders, average delivery days, the on-time rate, delivery status, branch delivery efficiency and logistics cost by vehicle type.

Customer and Data Quality

This page combines customer ratings and returns with revenue-validation and date-quality results. I included data quality in the business report because management should understand the confidence limits around the KPIs.

Insights and Recommendations

The final visible page connects the analysis to decisions through business questions, findings, insights and actions.

The hidden support pages are:

Branch Details, which receives drill-through context from branch visuals.
Vehicle Tooltip, which displays vehicle revenue, profit, margin and return rate on hover.

Year, Region, Branch and Vehicle Type slicers are synchronised across the main analytical pages. A page navigator provides consistent movement through the report.

What the analysis found

With all slicers cleared, the final baseline results were:

276 total orders.
153 active sales orders.
Approximately KES 905.0M active sales revenue.
Approximately KES 804.7M active sales cost.
Approximately KES 99.2M active gross profit.
11.65% active gross margin.
11 loss-making active orders.
113 on-time orders among 123 delivered orders with valid duration.
91.87% on-time delivery rate.
88 returned orders among 256 known return statuses.
34.38% return rate.

The branch analysis showed that Thika Yard led active revenue at approximately KES 171M. Kakamega Yard followed at KES 149M and Athi River Yard at KES 144M. Nairobi HQ recorded approximately KES 54M.

SUVs dominated the vehicle portfolio, producing approximately KES 510M in active revenue and KES 60M in gross profit. That makes SUVs the most important commercial category but it also creates product-concentration risk.

The overall delivery result was strong, but branch performance was uneven. Thika and Kisumu achieved 100% on-time performance. Nairobi HQ recorded approximately 75% and an average delivery duration near 45 days.

Pickup and Hatchback had the highest return rates at approximately 42.86% and 42.42%. The dataset cannot prove why those returns occurred, so the result should trigger investigation by model, supplier, complaint and delivery handling.

Recommendations

I converted the findings into five management recommendations.

1. Protect profitability

Review the 11 loss-making active orders and introduce minimum-margin controls and approval thresholds for unusually high discounts.

2. Improve delivery performance

Conduct operational reviews at Nairobi HQ, Mombasa Port Yard and Athi River Yard. The review should separate routing issues, carrier performance, workload and data-entry delays.

3. Reduce returns

Analyse Pickup and Hatchback returns by model, customer complaint, supplier and delivery handling before changing procurement or inventory decisions.

4. Strengthen data controls

Make order date, delivery date, currency and revenue fields mandatory. Add validation rules before records enter reporting systems so impossible dates and malformed financial values are rejected early.

5. Manage concentration risk

Continue monitoring profitable SUV growth while expanding profitable Sedan, Pickup and Crossover opportunities where the evidence supports them.

Testing the completed report

Before finalising the project, I performed a complete quality-assurance review:

Refreshed the model and confirmed that no Power Query errors appeared.
Reconciled baseline measures with all slicers cleared.
Tested the Year, Region, Branch and Vehicle Type slicers.
Tested page navigation.
Tested the Branch Details drill-through page.
Tested the Vehicle Tooltip on multiple vehicle categories.
Tested cross-filtering between visuals.
Reviewed visual, page and report filters.
Checked for truncated titles, unintended blanks, overlapping visuals and inconsistent formats.
Confirmed that support pages remained hidden.
Exported and reviewed the final report PDF.

All final checks passed.

Lessons from the project

The project reinforced several practical lessons.

First, data cleaning is not only technical. Correcting an impossible date requires business reasoning, supporting evidence and documentation. When the evidence is insufficient, retaining null is often better than inventing certainty.

Second, calculated and recorded financial values should be reconciled. A mismatch is not an inconvenience to remove; it can be one of the most important findings in the analysis.

Third, Power Query and DAX solve different problems. Power Query prepared row-level fields and classifications. DAX created filter-responsive measures for the semantic model.

Fourth, interactivity should help answer a question. The branch drill-through and vehicle tooltip were useful because they allowed deeper investigation without overcrowding the main pages.

Finally, the report became more useful when data quality was treated as part of business performance. Management needs to know both what the numbers say and how reliable the underlying records are.

Conclusion

The final JCars Logistics solution is more than a dashboard. It is a documented analytical system that preserves the source, exposes assumptions, validates financial values, separates active from non-active transactions and connects operational evidence to management action.

The project demonstrates the complete Power BI workflow: profiling, Power Query transformation, dimensional modelling, DAX, visual design, interactivity, validation and business communication.

The strongest outcome was not simply producing a set of KPIs. It was building a report in which those KPIs could be traced back to documented rules, cleaning decisions and validation results.

Power BI Data Modelling, Relationships and Joins

Jack Kisutsa — Sun, 13 Sep 2026 15:40:38 +0000

Power BI Data Modelling, Relationships and Joins: A Practical Guide

When I began learning Power BI, I naturally focused on dashboards. However, an attractive dashboard can still be slow or misleading when the data behind it is poorly organised. The data model is the foundation: it determines how tables communicate, how filters move and how DAX measures behave.

This article uses a retail-sales example to explain modelling schemas, fact and dimension tables, relationships, filter direction and Power Query joins. It also addresses a common beginner question: if both joins and relationships connect tables, when should each be used?

1. Data modelling in Power BI

Data modelling is the process of organising data into tables and defining logical connections between them. Sales transactions may be stored separately from customers, products, dates and locations. A good model allows a user to select Electronics, Nairobi or 2026 and receive the correct result.

A well-designed model improves accuracy, makes DAX easier to write, reduces duplicated data, improves VertiPaq compression and supports future growth. It also makes the solution easier for another analyst to understand and maintain.

2. Flat table, star schema and snowflake schema

A. Flat table

A flat table stores facts and descriptions together. One Sales table might contain OrderID, Date, CustomerName, ProductName, Category, City, Quantity and SalesAmount.

Advantages: It is quick to build, familiar to Excel users and suitable for a small prototype. No relationships need to be managed.

Disadvantages: Product and customer descriptions repeat for every transaction, increasing table width and storage. Repetition creates inconsistent values and difficult maintenance. Large flat tables are less reusable and can make complex DAX harder to organise.

Best use: Small, one-purpose datasets with limited growth.

B. Star schema

A star schema places a fact table at the centre and connects it directly to dimensions such as Customer, Product, Date and Location.

Advantages: It provides clear filter paths, simpler DAX, less duplication and strong performance. Dimensions can also be reused by several reports.

Disadvantages: It requires deliberate preparation and a clearly defined fact-table grain. Dimensions may contain controlled repetition, such as Category repeated for products in that category.

Best use: Most production Power BI models, especially when data volume and reporting needs will grow.

C. Snowflake schema

A snowflake schema normalises dimensions. Instead of storing Category and Subcategory in DimProduct, it may use DimProduct → DimSubcategory → DimCategory.

Advantages: It reduces repetition within dimensions and can mirror a normalised source database or independently managed hierarchy.

Disadvantages: Extra tables and relationships increase model complexity. Filters cross more paths, fields are harder to find and DAX becomes less intuitive.

Best use: Large or independently governed hierarchies. For ordinary reporting, small snowflaked dimensions are often flattened in Power Query.

Feature	Flat table	Star schema	Snowflake schema
Structure	One wide table	Central fact with dimensions	Dimensions split further
Redundancy	High	Low and controlled	Lowest
Performance at scale	Often weak	Usually strongest	More traversal required
Complexity	Simple initially	Clear and balanced	Highest

3. Fact tables, dimension tables and grain

A fact table records measurable business events—what happened and how much. FactSales may contain DateKey, CustomerID, ProductID, LocationID, OrderID, Quantity, Cost and SalesAmount. It is usually tall and narrow because transactions grow continuously.

A dimension table provides context used for filtering, grouping and labelling. DimProduct may contain ProductName, Brand and Category; DimCustomer may contain CustomerName and Segment; DimDate may contain Month, Quarter and Year. Dimensions answer who, what, when and where, while facts answer how many and how much.

The grain states exactly what one fact row represents. If FactSales has a grain of one product line per order, an order containing three products creates three rows. Grain must remain consistent. Combining order totals with order-line records can double-count revenue.

Table	Typical contents
FactSales	Foreign keys, Quantity, Discount, Cost, SalesAmount
FactOrders	One row per order and order-level measures
FactTransactions	Debit, Credit, Amount and account keys
DimCustomer	Customer name, segment, city
DimProduct	Product name, brand, category
DimDate	Date, month, quarter, year
DimLocation	Branch, city, region, country

For example, Total Sales = SUM(FactSales[SalesAmount]) can be analysed by any related dimension without copying its descriptive columns into FactSales.

4. Relationships in Power BI

A relationship is a logical connection between columns in two loaded tables. It allows filters and calculations to work across tables without physically combining them.

A primary key uniquely identifies a dimension row. DimCustomer[CustomerID] should contain C001 once.
A foreign key is the matching fact column; FactSales[CustomerID] may contain C001 many times because that customer can purchase repeatedly. Related columns need compatible data types.

Referential integrity means each foreign key has a matching dimension key. If FactSales contains C999 but DimCustomer does not, Power BI may group the transaction under a blank member. Anti joins can help detect such orphan records.

One-to-many (1:*)
One unique dimension value matches many fact rows. One product appears once in DimProduct but can appear in thousands of sales. This is the standard star-schema relationship.
One-to-one (1:1)
Each key occurs once in both tables—for example, one Employee row matched to one EmployeeSecurity row. It can separate sensitive fields, although merging the tables may be simpler when both describe the same entity at the same grain.
Many-to-many (:)
Keys repeat on both sides. Many students take many courses. A direct many-to-many relationship can make totals difficult to interpret, so a bridge table containing unique StudentID–CourseID pairs is often safer. It connects to Students and Courses through two one-to-many relationships.

An active relationship appears as a solid line and operates automatically. An inactive relationship is dotted and must be activated in a measure. If FactSales has OrderDateKey and ShipDateKey linked to DimDate, Order Date may be active while Ship Date is inactive:

Sales by Ship Date =
CALCULATE(
    [Total Sales],
    USERELATIONSHIP(FactSales[ShipDateKey], DimDate[DateKey])
)

5. Filter direction

With single-direction filtering, a filter normally travels from the one-side dimension to the many-side fact. Selecting Electronics in DimProduct identifies the relevant ProductIDs, filters FactSales and recalculates Total Sales. This behaviour is predictable and is the recommended default.

Bidirectional filtering allows filters to travel both ways. It can help in carefully designed bridge-table scenarios, but it should not be enabled simply to make a visual work. Multiple routes between tables can create ambiguous filter paths, unexpected totals, more query work and difficult debugging.

6. Joins in Power Query

A join matches rows from two tables using a common column. In Power Query, select Home → Merge Queries, choose both tables and matching columns, choose a join kind then expand the required columns.

Customers (left)

CustomerID	Name
C1	Amina
C2	Brian
C3	Carol

Orders (right)

OrderID	CustomerID	Amount
O10	C1	500
O11	C1	300
O12	C2	700
O13	C4	200

C3 has no order, while O13 refers to a missing customer. This reveals how each join treats unmatched records.

Left outer join

Keeps every customer and matching orders. Amina appears twice; Carol remains with null order values.

CustomerID	Name	OrderID	Amount
C1	Amina	O10	500
C1	Amina	O11	300
C2	Brian	O12	700
C3	Carol	null	null

Use it when Customers is the master list and no customer should disappear.

Right outer join

Keeps every order and matching customers. O13 remains with a null customer name.

CustomerID	Name	OrderID	Amount
C1	Amina	O10	500
C1	Amina	O11	300
C2	Brian	O12	700
C4	null	O13	200

Use it when every transaction must remain. Reversing the tables and using Left Outer gives the same logic.

Full outer join

Keeps all matches and unmatched rows from both tables. The output contains the three valid order matches, C3 with null order fields and O13/C4 with a null name. It is useful for reconciliation, although it may create many nulls.

Inner join

Keeps only keys found in both tables: C1–O10, C1–O11 and C2–O12. C3 and O13 are removed. Use it when analysis requires valid matches only, but check row counts because unmatched data is silently excluded.

Left anti join

Keeps only left-table rows without a right-table match. The result is C3–Carol. It can identify customers who have never purchased, missing submissions or unused master records.

Right anti join

Keeps only right-table rows without a left-table match. The result is O13–C4–200. It is valuable for identifying orphan transactions or broken foreign keys.

7. Power Query merge versus model relationship

Question	Power Query merge	Model relationship
Stage	Data preparation before loading	Model view after loading
Effect	Physically adds matching data to a query	Logically connects separate tables
Purpose	Enrichment, consolidation, reconciliation	Filtering and analysis
Structure	Can create a wider table	Preserves facts and dimensions

Suppose FactSales has one million rows and DimProduct contains ProductName and Category. Merging repeats those descriptions across many transactions. A relationship stores each product once and uses ProductID to filter FactSales. This is generally cleaner and more efficient.

A merge is appropriate when creating a dimension from several sources, adding a small lookup required before loading, consolidating datasets or using anti joins for quality checks. Excessive merging can recreate a wide flat table and weaken the model.

8. Recommended model

For a typical BI project, I would use a star schema. I would define the grain first, create dimensions with unique keys and keep transactional measures in fact tables. Relationships would normally be one-to-many with single-direction filtering from dimensions to facts.

I would use inactive relationships for alternative dates, bridge tables for genuine many-to-many scenarios and bidirectional filtering only when the business requirement and path are clear. Power Query merges would prepare data rather than flatten the entire model.

This design improves compression, query performance, DAX simplicity, readability, scalability and maintenance. Most importantly, every table has a clear responsibility. Dashboards are what users see but a trustworthy model is what makes their answers correct.

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

Jack Kisutsa — Sun, 06 Sep 2026 00:10:57 +0000

1. Project Introduction and Objective

In this project, I used Microsoft Excel and Power Query to clean and analyze a Jumia product dataset and then built an interactive dashboard to summarize pricing, discounts, ratings and customer engagement.

The main objective was to turn a small raw e-commerce dataset into useful business information. I wanted the final dashboard to answer practical questions such as:

Do products with higher discounts receive more customer engagement?
Do higher priced products have better ratings?
Is there a relationship between product rating and number of reviews?
Which products have the highest review engagement?
Which products may require further investigation because they have high discounts but low ratings?

The project also gave me practical experience in data cleaning, excel formulas, PivotTables, PivotCharts, slicers, correlation analysis and dashboard design.

2. Dataset and Business Questions

The original dataset contained 115 rows and 6 columns:

Product
Current price
Old price
Discount
Review
Rating

The dataset was small but it contained several realistic data quality problems. This made it useful for me to practice the complete analytics process rather than going directly to visualization.

I structured the workbook into the following sheets:

Raw_Data
Cleaned_Data
Analysis
Pivot_Tables
Dashboard
Data_Dictionary

As we have always been taught in class,I kept the Raw_Data sheet unchanged so that I always have a copy of the original source data.

3. Initial Data-Quality Audit

Before cleaning the data, I profiled the dataset in Power Query
using Column Quality, Column Distribution and Column Profile.

The audit identified several issues:

Data-quality check	Result
Original rows	115
Original columns	6
Blank Review values	58
Blank Rating values	58
Populated Review values stored as negative numbers	57
Current Price ranges	1
Old Price ranges	1
Exact duplicate rows removed	3
Discount values outside 0 to 100%	0
Rating values outside 0 to 5 after cleaning	0

One unusual issue was the Review field. All populated review counts were negative, even though a review count cannot logically be negative.
I therefore treated the negative sign as a data collection or scraping issue rather than a genuine business value.

The Rating field also required cleaning because values were stored as text such as:

4.5 out of 5
rather than as numeric ratings.

This audit was important because it prevented incorrect calculations later in the project.

4. Cleaning and Preparation Decisions

I performed the main cleaning steps in Power Query.

Product names

I applied Trim and Clean to remove unnecessary spaces and non-printable characters. Repeated product names were not automatically deleted because two rows with the same product name are not necessarily duplicates.

Current and old prices

The price fields contained KSh and commas so I removed the currency text and converted the fields to numeric values.

I notieced that two records contained price ranges rather than single prices:

Current Price: KSh 1,620 - KSh 1,980
Old Price: KSh 2,200 - KSh 3,200

I used the midpoint of each range:
Current Price midpoint = KSh 1,800
Old Price midpoint = KSh 2,700

This decision was documented in the Data Dictionary.

Discount

The Discount field was already interpreted correctly as a percentage.
The cleaned values ranged from 1% to 64%, so I did not divide the field by 100 or remove its percentage data type.

Reviews

The populated Review values were negative. I used Absolute Value in Power Query to convert them to valid positive counts.

The missing reviews remained blank. I deliberately did not convert missing reviews to zero because a blank means that the review information is unavailable, while zero would mean that the product definitely had no reviews.

Ratings

I renamed Ratingd to Rating, removed the text out of 5 and
converted the result to a decimal number.

The final valid ratings ranged from 2.0 to 5.0.

Duplicate records

I checked duplicates using the analytical fields:

Product
Current Price
Old Price
Discount
Review
Rating

Three redundant duplicate rows were removed, reducing the dataset from 115 to 112 products/records.

After cleaning:

112 records remained.
57 had complete Rating and Review information.
55 were missing both Rating and Review.
There were 0 Power Query errors.

5. Excel Formulas and Enrichment Fields

After cleaning the source fields in Power Query, I used Excel formulas to create analytical categories and flags. The cleaned Excel table was named tblProducts.

Discount Amount

I calculated the difference between Old Price and Current Price:

=[@[Old Price]]-[@[Current Price]]

Rating Category

I grouped ratings into three categories while keeping missing values separate:

=IF([@Rating]="","Missing",IF([@Rating]<3,"Poor",IF([@Rating]<=4.5,"Average","Excellent")))

The categories were:

Poor: below 3
Average: 3 to 4.5
Excellent: above 4.5
Missing: no rating available

Discount Category

I grouped discounts into Low, Medium, and High:

=IF([@Discount]="","Missing",IF([@Discount]<20%,"Low Discount",IF([@Discount]<=40%,"Medium Discount","High Discount")))

The thresholds were:

Low Discount: below 20%
Medium Discount: 20%--40%
High Discount: above 40%

Price Category

I calculated the first and third quartiles of Current Price:

=QUARTILE.INC(tblProducts[Current Price],1)
Result: **KSh 493**

=QUARTILE.INC(tblProducts[Current Price],3)
Result: **KSh 1,669.50**

I then categorized each product:

=IF([@[Current Price]]<=Price_Q1,"Low Price",IF([@[Current Price]]<=Price_Q3,"Medium Price","High Price"))

Engagement Flag

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

=QUARTILE.INC(tblProducts[Review],3)

The result was 14 reviews.

The engagement formula was:

=IF([@Review]="","Missing",IF([@Review]>=Review_P75,"High Engagement","Low Engagement"))

Therefore:

High Engagement: 14 reviews or more
Low Engagement: fewer than 14 reviews
Missing: no Review value

Performance Flags

I created three Yes/No flags:

High Rating + High Engagement

=IF(AND([@[Rating Category]]="Excellent",[@[Engagement Flag]]="High Engagement"),"Yes","No")

High Discount + Low Rating

=IF(AND([@[Discount Category]]="High Discount",[@[Rating Category]]="Poor"),"Yes","No")

High Discount + High Rating

=IF(AND([@[Discount Category]]="High Discount",[@[Rating Category]]="Excellent"),"Yes","No")

These fields helped turn individual rows into business segments that could be summarized using PivotTables.

6. PivotTable and Analysis Workflow

I used both formulas and PivotTables for analysis.
The overall cleaned dataset had:

112 products
Average Current Price: KSh 1,186.89
Average Discount: 37%
Average Rating: 3.89
Average Reviews: 13
Discount analysis

Discount Analysis

The Discount Category PivotTable produced:

Discount Category	Products	Average Discount	Average Reviews
Low Discount	19	8%	10
Medium Discount	31	31%	15
High Discount	62	48%	11

Medium-discount products recorded the highest average review count.

Price analysis

Price Category	Products	Avg Current Price	Avg Discount	Avg Reviews	Avg Rating
Low Price	28	KSh 284.75	48%	17	3.64
Medium Price	56	KSh 1,073.84	36%	10	3.88
High Price	28	KSh 2,315.14	27%	13	4.08

Low-priced products had the highest average review engagement while high-priced products had the highest average rating.

Rating and engagement

Among the 57 products with complete Review and Rating data:

Rating Category	High Engagement	Low Engagement	Total
Poor	4	8	12
Average	6	20	26
Total	16	41	57

I also calculated the Pearson correlation between Rating and Review:

=CORREL(tblProducts[Rating],tblProducts[Review])

The result was approximately 0.06, showing almost no linear relationship between rating and review count.

Performance flags

The analysis identified:

6 High Rating + High Engagement products
10 High Discount + Low Rating products
8 High Discount + High Rating products

Because these are independent flags, the counts should not simply be added together and interpreted as unique products.

7. Dashboard Design and Slicer Connections

After completing the analysis, I created a one-page dashboard designed to provide a quick summary of product performance.

The dashboard included five KPI cards:

Total Products: 112
Average Price: KSh 1,186.89
Average Discount: 37%
Average Rating: 3.89
Total Customer Reviews: 723

I created nine charts:

Top 10 Products by Ratings
Top 10 Products by Review Count
Top 10 Products by Discount Percentage
Discount vs Reviews
Rating vs Reviews
Price vs Rating
Rating mix
Discount mix
Engagement by Discount

The first two charts summarize category-level patterns. The scatter plotshows the weak relationship between ratings and reviews while the Top 10 chart identifies products with the strongest observed review
engagement.

Rating vs Review scatter plot

The scatter plot used Rating on the X-axis and Review Count on the Y-axis. I added a linear trendline and displayed the R-squared value.

The trendline is almost flat and the chart shows R² ≈ 0.0033, which supports the correlation result of approximately 0.06.

Top 10 products by review count

A horizontal bar chart was used because product names were long and easier to read in this format.

Review count was treated as an engagement indicator, not as evidence of sales.

Slicers

I added three slicers:

Rating Category
Price Category
Discount Category

The slicers were connected to compatible PivotTables through Report Connections/PivotTable Connections. This allows a user to select a category and dynamically explore different product segments.

The scatter plot was created as a normal Excel XY chart rather than a PivotChart, so it remains an overall view and is not controlled directly by the PivotTable slicers.

Final dashboard

The dashboard was arranged so that the viewer sees the KPIs first, followed by the charts, slicers and business insights.

8. Key Findings

Finding 1: Medium discounts recorded the strongest average review engagement

Evidence: Medium-discount products averaged 15 reviews, compared with 11 for high-discount products and 10 for low-discount products.

Meaning: The deepest discounts were not associated with the highest review engagement in this dataset.

Action: Sellers could test medium and high discount bands and
compare engagement before assuming that deeper discounting produces better product response.

Caveat: Review count is an engagement proxy. The dataset does not include sales or listing age, so this relationship should not be interpreted as causal.

Finding 2: Average rating increased across price categories

Evidence: Average rating increased from 3.64 for low-priced products, to 3.88 for medium-priced products and 4.08 for high-priced products.

Meaning: Higher-priced products in this dataset tended to have stronger average customer ratings.

Action: Sellers could investigate the product quality, features,brands or listing characteristics associated with highly rated high-priced products.

Caveat: This is an association. The analysis does not prove that increasing a product's price would improve its rating.

Finding 3: Rating and review engagement had almost no linear relationship

Evidence: The correlation between Rating and Review was
approximately r = 0.06, with R² ≈ 0.0033.

Meaning: A highly rated product does not necessarily receive a high number of reviews.

Action: Sellers should consider both rating quality and review engagement when evaluating product performance rather than relying on rating alone.

Caveat: Only 57 products had complete Rating and Review
information and review count is not a direct measure of sales.

Finding 4: Performance flags identified both opportunities and risks

Evidence: The analysis found 6 high-rating/high-engagement products, 10 high-discount/low-rating products and 8 high-discount/high-rating products.

Meaning: Some products appear to be strong engagement candidates, while others may require investigation because heavy discounting is occurring alongside weak ratings.

Action: Sellers could investigate the 6 high-rating/high-engagement products for promotional opportunities and review quality or listing issues among the 10 high-discount/low-rating products before increasing promotional support.

Caveat: These flags are screening tools. The dataset does not
contain revenue, units sold, margin, advertising spend, or profitability data, so the flagged products cannot automatically be classified as commercial winners or failures.

9. Business Recommendations

Based on the completed analysis, I would recommend the following
actions:

Test discount bands rather than automatically increasing discounts. Medium-discount products had higher average review engagement than high-discount products.
Investigate highly rated, higher-priced products. Understanding what these products have in common may provide useful lessons for product positioning and listing quality.
Use rating and engagement together. The weak correlation shows that a high rating alone does not guarantee strong review activity.
Review high-discount/low-rating products. These products may require investigation into quality, expectations, listing content, or promotional strategy.
Study high-rating/high-engagement products. These products can be investigated further as possible candidates for targeted visibility or promotional tests.

These recommendations are deliberately limited to what the dataset supports.

10. Limitations and Lessons Learned

Limitations
The dataset had substantial missing feedback data. Out of 112 cleaned records, only 57 had both Rating and Review information. This reduces the amount of data available for analyses involving customer feedback.

The project is also observational. Patterns such as higher ratings among high-priced products or stronger engagement among medium-discount products show association, not causation.

Lessons learned

This project reinforced several lessons for me.

First, data cleaning is part of analysis, not a separate optional step. If I had used the original negative Review values or text-based ratings directly, the final analysis would have been misleading.

Second, I learned the importance of documenting assumptions. Converting price ranges to midpoints, using 14 reviews as the high-engagement threshold and defining category boundaries all affect the results and therefore need to be transparent.

Third, you can use PivotTables to summarize data and also create an interactive dashboard. I also found out that PivotCharts rely on the PivotTables they get their data from. This means you can create separate PivotTables from the same data source for your charts. That way, a chart on your dashboard won't accidentally update a detailed analytical PivotTable.

Fourth, a dashboard should not contain every calculation. The Analysis and Pivot_Tables sheets can hold detailed work while the Dashboard should focus on the KPIs, charts, filters and insights that help a user understand the data quickly.

Finally, I learned to be careful with business language. A correlation does not prove causation, reviews do not equal sales and a flagged product is a starting point for investigation rather than a final business conclusion.

Conclusion

This project took a Jumia product dataset through the complete Excel analytics workflow: audit, cleaning, enrichment, analysis,visualization and business interpretation.

Starting with 115 raw records, I used Power Query to resolve price formatting, rating text, negative review values, missing information and duplicate records. The final cleaned dataset contained 112 records. I then used Excel formulas to create price, discount, rating, engagement and performance categories before summarizing the results through PivotTables and charts.

The final interactive dashboard provides a concise view of product pricing, discounts, ratings and customer engagement while still recognizing the limitations of the available data.

For me, the main lesson was that building a useful dashboard is not only about creating attractive charts. The quality of the final dashboard depends on the decisions made before visualization: how the data is cleaned, how metrics are defined, how assumptions are documented and how carefully the findings are interpreted.

Getting Started With Excel For Data Analytics: From Basics To Data Cleaning

Jack Kisutsa — Sun, 30 Aug 2026 15:30:38 +0000

Introduction

Excel is a pretty straightforward tool for anyone starting out with data. You can input, organize, calculate and look at information without needing to write any code. However, just having data in Excel doesn't automatically make it usable. Real-world datasets often have gaps, repeated information, spelling mistakes, and other small issues that need fixing before you can really use the data.

For this task, I worked with a file named HR_Dataset_Dirty.xlsx. It had 876 employee records with details like Employee ID, Name, Department, Salary, Hire Date, Age, Gender and Performance Score. This article explains the basic Excel features I learned and how I used them to clean up this data, making it ready for analysis.

Understanding the Basics

A file in Excel is called a workbook and each sheet within it is a worksheet. A worksheet is made up of rows, numbered down the side, and columns, labeled across the top. The point where a row and column intersect is a cell; for instance, cell A2 might contain an employee's ID number.

Usually, the very first row of a dataset lists the column titles, explaining what kind of information is in each column below. In my dataset, some columns contained numbers (like Salary and Age), some had dates (like Hire Date), and others had text categories (like Department and Gender). Excel handles these types differently. You can perform calculations like sums or averages on numbers, but if a number is accidentally saved as text, those calculations won't work correctly.

Before I made any changes, my first step was to look over the raw data so I could get a sense of what I was dealing with.

A section of the raw dataset, with blank cells highlighted in red and duplicate Employee IDs highlighted in yellow.

Spotting the Problems

Looking through the raw data, I noticed a few problems right away:

Some cells were blank — missing an age, a salary or a gender.
Some Employee IDs appeared more than once meaning the same employee had been entered twice.
The same category was spelled differently in different rows — for example, "HR," "Hr," and "Human Resources" all meant the same department, but Excel would treat them as three separate values.
Some dates didn't make sense, like a hire date of 2020/13/05 — there's no 13th month.

I used Excel's Conditional Formatting (Home tab → Conditional Formatting) to highlight these problems directly on the sheet, so I could see exactly which cells needed attention before touching anything.

Handling Missing Values

Instead of guessing what a missing value should be, I used Excel's Filter feature to check each column for blanks. For example, filtering the Salary column showed me exactly which employees had no salary recorded.

We were told in class that it's important not to just replace every blank with a zero — a missing salary doesn't mean the employee earns nothing, it just means the information wasn't captured. I left genuinely missing values as blank rather than guessing a number.

Removing Duplicates

I looked for employee IDs that showed up more than once because each ID should be unique. Some, like 10407 and 10870 appeared twice and had the same details.

Once I made sure these were actual duplicates and not two different employees with similar IDs, I used Excel's Remove Duplicates feature from the Data tab to get rid of them. This process removed 23 duplicate rows from the data.

Cleaning Text

When i cheched the data some text entries had too many spaces or the capitalization was all over the place; for instance, some names were just in lowercase. Excel has a couple of easy functions for fixing this:

TRIM() removes extra spaces from text.
PROPER() changes text to proper case, so "grace karanja" becomes "Grace Karanja."

I used these on the Name and Location columns to get everything looking the same.

Standardizing Categories

I also noticed that the Department column had the same problem. There were several spellings for the same department like "Markting" and "Marketing," or "HR" and "Human Resources." Since these functions don't fix spelling on their own, I mapped each incorrect spelling to its correct version, so that every version of a department name became one standard value.

Cleaning formulas correcting names, departments, and other text fields, with anything still needing a manual check flagged in red.

Fixing Dates and Numbers

I also had to check the Hire Date field too. The goal was to make sure each entry was a valid date and to flag any that weren't, like the incorrect 2020/13/05 instead of trying to guess what they should have been.

The Age field had a similar problem. One entry was the word "thirty" instead of the number 30. Since Excel can't use text in calculations, I flagged entries like this so they could be reviewed, rather than letting them cause errors in later calculations.

Preparing the Clean Dataset

Once every column had been checked and corrected, I copied the results into a new sheet as plain values, so the final table didn't depend on the raw data anymore. I also added a dropdown list to the Department column (Data tab → Data Validation) so that only the correct department names can be entered from now on.

The final cleaned dataset — 852 unique employees remain after removing duplicates, with a Department dropdown to keep future entries consistent.

A Quick Summary

With the data cleaned, I used COUNTIF() and AVERAGEIF() to summarise employees by department and built a simple chart from that summary to see the results at a glance.

A quick summary of headcount and average salary by department, built from the cleaned data.

Conclusion

I learned from this exercise that before you even get to charts or dashboards, you first need to make sure the data itself is reliable. Things like missing information, duplicate entries, different spellings for the same thing or incorrect dates can all mess up your analysis if you don't find and fix them early on.

Turning a messy raw dataset into a clean and consistent one really showed me how crucial this initial step is. It also showed me that some basic tools in Excel like Conditional Formatting, Filter, Remove Duplicates, TRIM, PROPER and Data Validation are all you really need to do it.

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

Jack Kisutsa — Sun, 23 Aug 2026 09:41:03 +0000

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

Introduction

This week I learned how to use Git, Git Bash, GitHub and setting up SSH Keys. Before this, I knew about GitHub but I did not really understand how a project moves from a folder on my computer to GitHub.

A simple way I now understand the relationship is:

Git manages the history of my project while GitHub provides an online home for the project.

In this article, I will explain the steps I followed to create a simple project locally and push it to GitHub.

Creating My Project

I started by creating a folder for my project using Git Bash.

mkdir Kenya-Hospital-Records-Analysis
cd Kenya-Hospital-Records-Analysis

Inside the folder, I created a README.md file and a folder called data.

My project looked like this:

Kenya-Hospital-Records-Analysis/
├── README.md
└── data/

The README.md file is where I can explain what my project is about while the data folder can be used to store datasets.

In the data folder i uploaded an excel file called Kenyan_Hospital_Health_Records

How to use Git

The next step was to make Git start tracking my project. I did this using:

git init

I then used:

git status

This helped me see which files Git was tracking and which files had not yet been added.

To add my files, I used:

git add .

I then saved the changes to Git using a commit:

git commit -m "Initial commit"

One thing I learned is that a commit is like saving a checkpoint of my project. The commit message helps explain what changes I made.

Connecting Git to GitHub while generating SSH Keys

To push my local project to GitHub, I needed a secure way for my computer to communicate with my GitHub account.

I used SSH (Secure Shell).

I first generated an SSH key on my computer and then added the public key to my GitHub account.

An SSH key normally consists of two parts:

Private key – stays securely on my computer.
Public key – can be added to GitHub.

One important lesson was that the private key should never be shared.

After configuring my SSH key, I tested the connection using:

ssh -T git@github.com

A successful authentication confirmed that my computer could communicate securely with GitHub.

Connecting to GitHub Using SSH Keys

I also learned how to connect Git on my computer to my GitHub account using an SSH key.

After creating the SSH key, I added the public key to my GitHub account. I could then test whether the connection was working using:

ssh -T git@github.com

This was interesting because I learned that SSH allows my computer to communicate securely with GitHub without entering my GitHub password every time I push my work.

Pushing My Project to GitHub

After creating a repository on GitHub, I connected my local project to it.

git remote add origin git@github.com:username/Kenya-Hospital-Records-Analysis.git

I then pushed my project to GitHub using:

git push -u origin main

After refreshing my GitHub repository, I could see the files I had created on my computer now appearing on GitHub.

What I Learned

The biggest thing I learned from this exercise is that Git and GitHub are different but work together.

Git helps me track changes to my project on my computer, while GitHub allows me to store and share the project online.

I also now understand the basic Git workflow better:

Create or change files
        ↓
git add
        ↓
git commit
        ↓
git push
        ↓
GitHub

At first, the commands looked confusing but after using them practically, I started to understand what each command was doing.

Conclusion

This was my first time taking a project from a local folder on my computer and pushing it to GitHub using Git and SSH.

I am still learning Git and GitHub but I can now create a project, track my files, make commits and push my work to GitHub.