DEV Community: Dorcas Chebet

Building the JCars logistics Sale Dashboard in Power BI

Dorcas Chebet — Fri, 02 Oct 2026 11:19:34 +0000

Introduction

JCars Logistics sells vehicles out of nine yards around Kenya — Nairobi, Mombasa, Kisumu,
Eldoret, Kakamega and a few others. The business wanted answers to three plain
questions: where do our sales actually come from, what do they earn us, and can we trust vehicles to reach customers on time?
The data I was handed couldn’t answer any of that.It was 273 order rows across 55 columns, and when I checked, only 69 of those rows barely a quarter were clean enough to use.The rest had something wrong with them.

Investigating the data before touching it

I didn’t open the file and start cleaning. First I wanted to know whether I could trust any of it.
In Power Query I switched on Column quality, Column distribution and Column profile so I could see blanks and distinct values in every column at a glance. Then I started poking at the business logic. Does the recorded revenue match what the price, units and discount say
it should be? Do delivery dates come after order dates, like they obviously should? Does each branch stay inside one region?
Those checks surfaced five families of problems.
Identity. 19 rows had no Order ID at all, and the ones that did used five different prefixes ORD, CAR, LCL, LCL-, LC. Without a key you can rely on, you can’t spot duplicates and you can’t build safe relationships.Prices, costs and fees were sitting in four currencies: KES, USD, EUR and ZAR.
When I compared the converted figures against the originals, the implied rates were suspiciously constant (USD 129, EUR 151, ZAR 7.91), which told me someone had applied a fixed booking rate. Some rows had a currency I couldn’t identify, 10 showed negative revenue and 102 flat-out failed my reconciliation check. That check came straight from the
data: Net Sales = Selling Price × Units × (1 − Discount), and the recorded revenue should
equal either that or that plus the delivery fee. 102 rows matched neither.
Time. Dates were stored as long text, like “Wednesday, March 26, 2025”. Once I converted
them, the problems jumped out: 14 orders were apparently delivered before they were
ordered (one of them 290 days before), 23 dates wouldn’t parse at all, and 56 were simply missing.
Categories. The same thing was written several ways. Person and Individual. N.G.O and NGO, Saloon and Sedan, Wire, RTGS and Bank Transfer. Paid and Completed. Every spelling variant quietly splits a total that should have been one number.
Logic. Delivered orders marked “Returned”. Cancelled orders carrying a “Completed”
payment. Discounts as high as 50%. Vehicle years of 2026.

Cleaning and preparation:

what I did, and why For every fix I wrote down three things — what I did, why I did it and what I was assuming when I did it. The assumptions matter more than the clicks.

Steps What I did Why, and what I assumed.

Keys Standardized the Order IDs and added a surrogate key.I assumed the number identifies the order, so LC1000 and LCL1000 are the same order, just typed differently.Types Converted dates, money and flags to proper data types.Text dates break every time-intelligence function Currency Converted everything to KES at the rates the data implied.I assumed those were the business’s own booking rates.Revenue Rebuilt Revenue as
net sales plus delivery fee.The recorded figure failed reconciliation on 102 rows. ORD1009, for instance, had no recorded revenue, but the rule gives 2,920,000 × 0.93 +140,000 = 2,855,600
Dates Kept the impossible date rows but flagged them I assumed the order is real and the date is just wrong — deleting it would throw away a genuine sale Categories Merged the variants with a mapping table Cleaner slicers, and totals that actually add up Missing value State each choice and what it changed Unit economics Added Unit Selling
Price and Unit Cost columns needed them to work out cost and margin per
vehicle
A few of those decisions came back to bite me, and I caught them during validation:
Zeros aren’t blanks. A missing logistics cost that loads as 0 makes an order look more profitable than it really was. That’s not a rounding issue — it’s a wrong answer.
A currency bug hiding in plain sight. One delivery fee read 426.67 sitting right next to a
logistics cost of 74,000. That 426.67 is clearly a USD figure that never got converted
(426.67 × 129 ≈ 55,000 KES)
A duplicated customer ID. After one of the merges I ended up with both Customer ID and dim_customer.Customer ID in the fact table. I kept one and dropped the other.

Modelling the data

I built it as a star schema: one fact table (facts table, 276 rows) with five dimensions around
it — dim_customer, dim_date, dim_location, dim_sale rep and dim_vehicle.
Why a star schema rather than one big flat table?
• Attributes like region, make and rep fall in exactly one place, so when I fix something, I fix it once.
• The relationships are plain one-to-many, which keeps the DAX predictable and the reports quick.
• dim_date is marked as the official date table, so time intelligence works properly.
Delivery Date is a second date column, so it hangs off an inactive relationship and only wakes up inside measures that call USERELATIONSHIP.
One limitation I made a point of writing down: the customer ID seems to track the source
row number, which means a repeat buyer can get counted as several different customers.
There are 181 distinct names across the 273 rows. So read any customer count with that
caveat in mind — it’s an upper bound, not gospel.

The DAX behind the numbers

Each measure exists to answer one specific question. [Swap in your own measures.]
Total Revenue =1.35bn
CALCULATE
Total Sales =SUM('facts table'[sales])
Total= 1.82bn
SUMX(
FILTER('facts table', 'facts table'[Delivery Status] <>
"Cancelled"),
'facts table'[Units Sold] * 'facts table'[Unit Cost] + 'facts
table'[Logistics Cost]
)
Unit Cost is per vehicle, so just summing the column would be nonsense. SUMX multiplies
by units first, row by row.
Gross Profit = [Total Revenue] - [Total Cost]
total gross profit = 454.68m
Gross Margin % = DIVIDE([Gross Profit], [Total Revenue])
Gross Margin = 25%

Designing the report

I built the report around the three questions, one page each.
Executive overview. KPI cards for Total sales, Gross Margin %,total cost and Total revenue recorded.
Sales analysis. Revenue sliced by branch, sales rep, lead source, make and vehicle type,
with slicers for year, region and make. This is the “where does the money come from?”
Operations and delivery. Delivery days by branch, the spread of delivery times, and a table of the orders that ran late.
A few design calls I stuck to:
• A small palette, used consistently — the same colour always means the same thing.
• Bars sorted by value, never alphabetically.
• Drill-through from region down to branch down to the individual order.
• Chart titles that say what you’re looking at, e.g. Revenue, total cost

Insights

• Riftvalley region brought in Ksh.3,044,400 of revenue, while Western managed only Ksh.1,872,800
• online produced the most orders, but walk in had the highest average
order value.
• SUV gave the best gross margin at 14.72%, against 4.22% for Toyota.

Recommendations

Pin each recommendation to an insight above:
• Move marketing spend toward the lead sources with the best margins, not just the
most orders. Volume and profit aren’t the same thing.
• Dig into the slow branches. Delays at [branch] are probably costing repeat business.
• Revisit pricing and discounts on the lowest-margin makes.
• Fix the data at the point of capture. Making currency and Order ID mandatory fields
would have prevented most of what I found — remember that 75% of rows needed
correcting.
what i took away from it
Cleaning ate up most of the time, and that’s exactly where the real analytical decisions got
made. The assumptions I logged turned out to matter as much as any chart.
Checking my own work paid off. Validating the converted values and the filled-in blanks
caught errors that would otherwise have shipped without a sound.
I kept quality visible by holding on to an issues flag, so anyone can filter the report down to
clean records and judge for themselves.
And the big one: missing is not zero. Treat a blank cost or a blank revenue as 0 and you’ve silently poisoned every average built on top of it.
If I did this again, I’d fix the capture process first. Clean inputs mean far less cleaning later.

Conclusion

This project took the JCars Logistics dataset from a raw, inconsistent Excel file to an interactive Power BI report that management can use to make decisions. The process started with investigating and cleaning the data: fixing data types, handling missing values and duplicates, and standardizing text fields. I then shaped it into a star schema, with one facts table linked to the customer, date, location, sales rep and vehicle dimensions. That model made the DAX measures (revenue, gross profit, margin, logistics cost ratio, return rate and time comparisons) accurate and easy to reuse across every visual.

Assignment in power BI

Dorcas Chebet — Tue, 22 Sep 2026 09:16:17 +0000

Data modelling in power Bi

Data modelling is setting up tables, relationships, calculations and access for analysis scenarios.This helps so that data can be analyzed correctly and efficiently.
for example, a sales department can have information about,

-Order ID

-Order date

-Customer Name

-City

-Product

-Category

-Amount

instead of putting everything into one large table ,the data can be organized into related tables.
importance of a well designed model
1.Reporting and analytics
it helps in data consistency by standardizing definitions and metrics across different systems so everyone uses the same number.it ensures filters and slicers automatically propagate across related visuals without missing or duplicating records.
2.DAX calculations
3.It prevents incorrect results when writting time-intelligence or cross table measures.
4.it also simplifies code syntax.
Performance
5.A well-designed data model reduce unnecessary data duplication and improve power BI's ability to process queries efficiently.
6.Scalability.It accommodate additional products, customers transactions and other data without the need of the entire structure to be redesigned.
7.maintability.It makes it easier for another analyst to understand and modify the report.

Data modelling approaches

1.Flat table
it is a single ,wide table that contains all your data,e.g Order ID,Order date,Customer name,City,Product category and amount
Example

2.star schema
Definition: One central fact table surrounded by dimension tables, each joined directly to the fact table. It looks like a star.
Example,
dim_doctors
|
|
dim_patients --- Visits_fact --- dim_department
|
|
dim_diagnosis
|
dim_procedure
|
dim_wards
Advantages
• Simple, intuitive and the layout Power BI is optimized for.
• Fast: one hop from dimension to fact and efficient compression.
• Easy DAX and easy filtering.
• Easy for report authors to understand.
Advantages
• Dimensions are denormalised, so some repetition remains (for example, category name repeated per product).
• Requires upfront design and transformation effort.
When appropriate: Almost all analytical models. It is Microsoft's recommended default.
Performance/complexity: Best balance. Few relationships, short filter paths, low comp

3.Snowflake schema approach
It is a star schema where dimensions are normalised into further related tables, so dimensions have their own sub-dimensions.

advantages
• Less redundancy, so easier to maintain hierarchies (Category → Product).
• Mirrors normalised source databases.

Disadvantages

Easy to set up, with no need to handle any connections or relationships.
Fine for quick, one-off exploration.

Disadvantages

-Heavy repetition of words many times, which makes the files bigger and the system slower.
-Updating anomalies: when you rename a category, you have to change many rows.
-It's difficult to create accurate time-based analysis without having a proper date table.
Comparison
1.Feature flat table star schema and snowflake schema
Tables can have one fact along with several dimensions, or they can include a fact along with dimensions and sub-dimensions.
Redundancy High Moderate Low
Performance in Power BI is poor when handling large data sets, but it's best when dealing with smaller data. It’s good, but slightly slower than the best.
Model complexity Lowest Low Highest
DAX simplicity Awkward Easy Moderate
Recommended? Only small or ad hoc, yes (default), only when justified.
2.Fact table and Dimension Table

A fact table keeps track of business events along with the numerical outcomes of those events. It typically contains:

Foreign keys to dimensions (DateKey, ProductKey, CustomerKey)
Numeric measures: Quantity, SalesAmount, Discount, Cost
Sometimes degenerate dimensions such as an OrderNumber

Fact tables are long because they have many rows, but they are not wide since they only have a few columns. Examples: FactSales, FactOrders, FactTransactions.
Dimension tables

A dimension table holds descriptive information that helps in filtering, grouping, and labeling the facts. It has a special key called the primary key and also includes text and category columns. Dimension tables are not very long, having fewer rows, but they have many columns that provide detailed descriptions.

DimCustomer: CustomerKey, Name, Gender, Segment, Join Date
DimProduct: ProductKey, Product Name, Category, Brand, Unit Price.
DimDate: DateKey, Date, Month, Quarter, Year, Weekday
DimLocation: LocationKey, City, County, Country
Measures versus attributes
Measures are numerical outcomes from business events that you combine by adding, averaging, or counting.
Attributes are descriptive fields that you can use to break down or analyze your data, such as Category, City, or Month.

Grain
The grain refers to what is represented by one row in the fact table. Examples:

"One row for each product in each order line" (FactSales).
"One row per order" (FactOrders)
One entry for each account every day, which is a daily balance summary.

Define the grain first. Every measure and foreign key has to be accurate at that level of detail. Combining different types of grain values (such as order-level and line-level) in the same table leads to counting the same items twice.
FactSales

| Sales ID |Date key |Customer key | Product key |Location key | Quantity |Sales amount|
|---------|-----------|-------------|------------|--------------|
|1 |20260105 | 11 |501 |3 |

|1 |65000
|2 |20260105 |11 |502 |3 |
|2 |3000
|3 |20260106 |12 |501 |4 |
|1 |65000 |

Each foreign key refers to a dimension where the key is unique. Choosing "Mombasa" in the DimLocation or "Tech" in the DimProduct narrows down the FactSales data to only the rows that match those selections. This is the star schema presented in Section 1.

Relationships in Power BI What is a relationship?

A relationship connects two tables by using a column in each, which helps Power BI understand how they are linked and allows it to pass filters from one table to the other. Without relationships, data from different tables can't be connected: if you select "Nairobi" in a slicer, it won't change the sales totals because Power BI won't know which rows of data belong to Nairobi.

Keys, uniqueness and integrity

Primary key (PK): A column that uniquely identifies every row in a table, like CustomerKey in DimCustomer.
A foreign key is a column in one table that points to the primary key of another table, like the CustomerKey in the FactSales table.
Unique values: The "one" side of a relationship should have distinct, non-empty values.
Cardinality refers to the type of relationship between columns, such as one-to-many or other similar types.
Referential integrity means that every value in a foreign key of the fact table must be found in the corresponding dimension table. If FactSales includes CustomerKey 99 but DimCustomer does not, those rows appear under a (Blank) category in visuals, making reports misleading.

Why is CustomerID unique in DimCustomer but appears multiple times in FactSales: DimCustomer has one row for each customer, meaning each person is represented only once. FactSales records one entry for each time a customer makes a purchase, and a customer can make multiple purchases. CustomerID 11 is listed once in the DimCustomer table but appears many times in the FactSales table. That is exactly a one-to-many relationship.

_One-to-many (1:*)

How it works: A single row on the "one" side connects to multiple rows on the "many" side. Filters move from the single side to the multiple side.

Example: DimProduct (1) → FactSales (*).

DimProduct (1) ─────────────► (*) FactSales
ProductKey PK ProductKey FK

Use it: This is the standard, default relationship in star schemas. Use it whenever a dimension describes a fact.
Avoid it: When the "one" column has duplicate entries. Power BI will then force a many-to-many.

One-to-one (1:1)

How it works: Each row in one table can match only one row in the other table, and both columns are unique. Filters propagate in both directions.

Employees (EmployeeID) and EmployeeSensitiveDetails (EmployeeID), such as salary or national ID, are kept in a separate table for security.

Employees (1) ◄────────────► (1) EmployeeDetails
EmployeeID PK EmployeeID PK

Use it: Occasionally, such as when dividing a table for security reasons or when two sources refer to the same entity.
Usually, it's better to combine the two tables into one using Power Query, which makes the model easier to work with.

Many-to-many (:)

How it works: Neither column is unique, so there may be multiple rows that match on each side. Power BI doesn't require a unique key, but the results can vary based on how filters work and may be difficult to predict.

Students and Courses, where a student enrolls in multiple courses and a course is attended by multiple students. Another example is budget data at the category level connected to sales data at the product level.

Students () ◄────────► () Enrollments?

The recommended design is a bridge table:

DimStudent has a one-to-many relationship with BridgeEnrollment, which in turn has a one-to-many relationship with DimCourse.

Use it: Only when a bridge table isn't practical, or when you need to compare tables that have different levels of detail, like budget data grouped by month versus sales data grouped by day.
Avoid it: As a shortcut to skip cleaning your data. It causes ambiguity and unexpected totals.

Active and inactive relationships

At any given time, only one active connection can exist between two tables, and this is shown as a solid line in the Model view. Additional ones are inactive (dotted).

FactSales has OrderDate and ShipDate, both connecting to DimDate[Date]. OrderDate is the active relationship. To calculate sales by ship date, turn on the other one in DAX:

DAX
Sales by Ship Date =
CALCULATE (
SUM ( FactSales[SalesAmount] ),
USERELATIONSHIP ( FactSales[ShipDate], DimDate[Date] )
)

Filter Direction How filters propagate

Filters travel along relationships. In a standard star schema, when you apply a filter to a dimension, that filter is passed down to the fact table.

Single-direction filtering

Filters only move from the "one" side to the "many" side.
A user chooses the category "Tech" in the DimProduct slicer.

DimProduct is filtered to FactSales, and the Total Sales shows only Tech sales.
The slicer filters DimProduct to Tech products.
That filters FactSales to include only the rows where the ProductKey matches the specified products.
SUM(FactSales[SalesAmount]) returns Tech sales only.

This is the default and preferred behaviour.

Both (bidirectional) filtering

Filters work both ways: a filter applied to the fact table also affects the dimension.

DimProduct ◄──filter──► FactSales

Legitimate use: For instance, a slicer on DimProduct that displays only products with actual sales, or many-to-many relationships handled through a bridge table.

How to use it carefully
Ambiguous filter paths: When multiple dimensions are linked to a single fact table, bidirectional filters can lead to different paths connecting two tables. Power BI might not allow the relationship to be activated or could cause unexpected outcomes.
Unexpected results happen when filters affect the entire model, meaning a slicer applied to one dimension can also filter another.
Performance cost: Using filters can make calculations slower because they require more processing work.
The model becomes more difficult to understand and fix.

Performance/complexity: Lowest modelling complexity but poor scalability. Memory usage increases rapidly when text columns are repeated.

Star schema

Definition: There is one main fact table with dimension tables around it, and each dimension table is connected directly to the fact table. It looks like a star.

Best practice is to keep everything set to one direction by default, and only turn on two-way filtering when it's actually needed. Often the same outcome can be reached more safely in DAX by using CROSSFILTER() within a single measure.

Joins in Power Query A join combines rows from two tables by matching them on a common column. In Power Query, you perform this action by going to the Home tab and selecting Merge Queries, or choosing Merge Queries as New. You pick two tables, choose the matching column in each one, select the type of join you want, and then expand the joined table to include its columns. Example data

Customers table
|Customer ID| Name |City
|
|-----------|---------|-----
-|C1 | Brian |Nairobi
|
|C2 | Amos | Mombasa

|C3 |Dorcas |Kisumu

|C4 |Cynthia |Nakuru

Orders

OrderID CustomerID Amount
O101 C1 5,000
O102 C1 2,500
O103 C3 8,000
O104 C5 1,200

Note: Brian (C2) and David (C4) have no orders, and order O104 belongs to C5, who is not in Customers. Merges always start by using Customers as the first table on the left and Orders as the second table on the right, pairing them based on the CustomerID.

Left Outer Join

How it works: It keeps all the rows from the left table and only includes the matching rows from the right table.
Records kept: All customers and corresponding order information are retained. Unmatched customers get nulls.

Right Outer Join

How it works: It keeps all the rows from the right table and only the matching rows from the left.
Records kept: Every order along with the corresponding customer information.

CustomerID Name City OrderID Amount
C1 Amina Nairobi O101 5,000
C1 Amina Nairobi O102 2,500
C3 Cynthia Kisumu O103 8,000
null null null O104 1,200

Use case: Display all orders and mark those that don't have a corresponding customer record.

Full Outer Join

How it works: It keeps all the rows from both tables and matches them where possible.
Records are kept: all information is stored; missing data is represented by nulls where there is no matching information.

CustomerID Name City OrderID Amount
C1 Amina Nairobi O101 5,000
C1 Amina Nairobi O102 2,500
C2 Brian Mombasa null null
C3 Cynthia Kisumu O103 8,000
C4 David Nakuru null null
null null null O104 1,200

Use case: Combining two systems to identify all the matches and differences in a single outcome.
Inner Join

How it works: It keeps only the rows that are the same in both tables.
Records kept: Only customers who have placed orders, and orders that are linked to customers.

CustomerID Name City OrderID Amount
C1 Amina Nairobi O101 5,000
C1 Amina Nairobi O102 2,500
C3 Cynthia Kisumu O103 8,000

Use case: "Analyze customers who are currently active and have made a purchase."

Left Anti Join

How it works: It keeps the rows from the left table that do not have a matching row in the right table.
Records retained: Customers with no orders.

CustomerID Name City
C2 Brian Mombasa
C4 David Nakuru

Use case: Identifying customers who have not placed any orders yet in order to run a marketing campaign.

Right Anti Join

How it works: It keeps the rows from the right table that do not have a matching row in the left table.
Records retained: Orders with no matching customer.

OrderID CustomerID Amount
O104 C5 1,200

Use case: Identifying records that are no longer linked to any other data, which is a data quality check to ensure that all references between data sets are correct and complete.

Summary
Join type Keeps
Left Outer All left + matching right
Right Outer All right + matching left
Full Outer Everything from both
Inner Matching rows only
Left Anti Left rows with no match
Right Anti Right rows with no match

Power Query Joins vs Power BI Relationships

Both link tables together, but they function in different steps and serve different reasons.

Aspect Power Query join (Merge) Power BI relationship
When it happens, it can occur during data loading or refreshing (ETL stage) or when a report or query is run.
What it does is physically combines tables into a new table or adds columns. It keeps tables separate and links them logically.
Result: One wider table (rows may multiply or shrink) Filters propagate between tables; no data merged.
Join types include six kinds such as left, inner, anti, and others. Cardinality includes ratios like 1 to many, 1 to 1, and so on. Filter direction is also considered.
Impact on model size can lead to larger size and extra copies. It is efficient because each entity is stored only once.
Aggregation values are set at the row level. Dynamic: measures update based on the filter context.
Best for data preparation: adding more information, fixing errors, making data easier to use, handling complex structures, and finding records that don't match. Analytical modeling: breaking down data into different categories to analyze it effectively.

Rule of thumb:

Use Power Query joins to clean and structure your data: simplify a snowflaked dimension, add more information to a table using a lookup column, or find rows that don’t have matches with an anti join.
Use relationships to analyze data by linking a fact table to dimensions, allowing measures to react to slicers and filters.

Merge the DimProduct and DimCategory tables in Power Query to create a single DimProduct table, which flattens the snowflake schema. Link the DimProduct table to the FactSales table using a one-to-many relationship. Combining FactSales with all dimensions into a single flat table is possible, but it reduces the performance, flexibility, and ease of maintenance that a star schema provides.

Conclusion

A good Power BI model keeps facts like events and measurements separate from dimensions that provide descriptive details. It connects them with the right one-to-many relationships and uses filters that only go in one direction by default. The star schema is the preferred approach since it is efficient, straightforward, and works well with DAX. Power Query joins help organize the data and also show any issues with data quality by using anti joins, while relationships allow the final model to answer business questions in a flexible way.

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

Dorcas Chebet — Wed, 09 Sep 2026 11:06:44 +0000

Introduction

The case study is about jumia products which has generated large amount of data that include a certain number of products, prices, discounts, ratings and customer review .It contains information on 113 products .It also include their current prices, old prices, discount percentage s,number of customer review and ratings .Informations help the business to know the product performance and identify areas that requires attention.
The project involves transforming raw data into a structured dataset ,performing explolatory analysis and presenting the results through an interactive dashboard.

Project Objective.

The main project objective of this project is to analyze data from jumia.
the specific objectives are to analyze products by comparing current prices with the old prices.

-To identify the discount offered in different products

To identify products with high,medium and low discounts

To determine whether highly rated products receive more customer reviews

-To calculate descriptive statistics such as average,current price,average old price,average discount,total discount,total reviews and number of products.

which project generated more reviews

To use excel formulas to organize, categorise and interpret the Jumia product data.

Dataset and Business Queastions

The questions guiding the analysis were:

Does a higher discount correspond to more reviews?

2.Do highly rated products receive more reviews?

3.Is there a relationship between current price and ratings?

4.Which products have the highest rating?

5.Which products have the lowest discounts?

6.Which products have the highest number of reviews?

7.Are there products with high discount but relatively weak ratings or engagements?
The questions helped determine which calculations,pivot tables and visualization were needed.

Initial Data-Quality Audit

Before cleaning i identified the problems before making the changes or cleaning the dataset inorder to understand whether the data was complete ,consistent ,accurate and suitable for analysis.
For the jumia project the audit included:

1.Identifying if there was duplicate
2.Identify if there was missing values in important field such as price ,rating ,discount or reviews.
3.incorrect values :values that do not add up, such as current price being the same as the old price while a discount is still recorded.
4.Data type errors :to ensure prices are stored as numbers ,discounts as percentages/numbers, ratings as numbers and products names as text.

Excel techniques ,formulas and analysis performed.

Calculating the Discount amount

I calculated the discount amount by subtracting the current price from the old price.
formula:
=old price-current price
use of IF statements to classify products into different categories based on their ratings, discounts and prices.
I classified into categories such as:

Excellent
Average
Poor
Missing
Discounts were categorized as:
High discount
Medium discount
Low discount

Analysis performed

Rating analysis I analyzed and group product ratings into categories to determine which products have excellent, average, poor or missing ratings to help evaluate overall customer satisfaction.
Discount analysis I analyzed and categorized into high, medium and low discounts .I also compared the old prices with the current price to determine the amount customers could save.
Trend analysis The project was used to examine relationships between: -Discount percentage and number of customer review -Product rating and number of customer reviews -Product price and product rating. The analysis indicates that a higher discount percentage did not result in more customer reviews and highly rated products did not necessarily receive more reviews. #Dashboard creation process The final stage of the project was to transform the analysis into an interactive dashboard .Top most of the Dashboard was the heading written in bold. The key performance indicators were

Total products
Average Current price KSh 1,097
Average Discount 48%
Average rating 5 -Total reviews 723 Beneath this KPIs the slicers used were:

price category-it categorized data into High price ,Low price and Medium price. Discount category- it categorized data into High discount ,Low discount and medium discount. Rating Category-Categorized data into average, excellent ,poor and missing for missing values.

key insights and business recommendation.

1.Higher discounts do not necessarily lead to higher customer engagement .Products with higher discount percentages doo not consistently receive more customer review.
2.Customer reviews vary across products
3.Discounting should not be the only strategy
4.Products performance should consider both ratings and reviews.
5.Highly rated products do not necessarily receive more reviews.it does not guarantee strong customer engagement.

Recommendations

The sellers should focus on customer satisfaction by improving quality and customer service to maintain good ratings.
the sellers should use data to make pricing decisions
Review products with low engagement.
Use target discounts .Strategically on products where they are likely to improve sales or engagement.

Screenshot of original data, cleaned data ,pivot tables and dashboards

The Original Data

2.Cleaned data

3.Pivot tables

3.Dashboard

ASSIGNMENT WEEK 2

Dorcas Chebet — Sun, 30 Aug 2026 07:49:35 +0000

Getting Started with Excel For Data Analysis

Introduction

Excel is one of the tools commonly used for working data .It is used to enter, organize ,clean, calculate and analyze information using spreadsheets .learning excel gives good foundation on how data is handled before other advanced tools such as python ,SQL and Power BI are used.
In data analysis ,raw data can have errors, duplicates, missing values or unorganized information.

Excel Basics

Dataset- It is a collection of related information organized into rows and columns .Example,

Employee ID	Name	Department	Age	Salary
1102	Marion	Finance	28	45000
1103	Peter	IT	30	70000
1104	June	HR	34	60000
1105	Irene	Finance	32	80000
1106	Dennis	IT	29	65000

Explanation
_column-is a verticle line of data that runs straight from top to bottom
_row-it is a horizontal line of data that runs from left to right.
_cell-it is an intersection of a row and column
_Header-The name of a column.
entering data into Excel
for the case of our dataset, it is entered as,
A1:Employees ID
B2:Name
C1:Department
D1:Age
E1:Salary
It is important to organize data rather than putting two different pieces of information in the same column. For example:
Employee ID
1102,Marion,finance
instead;
Employee ID Name Department
1102 Marion Finance
Hence the data will be easier to analyze.

Formatting in Excel

Formatting in excel involves,

Bold headings
Number of formatting
Alignment
Adjusting column width
Dates
Borders
Percentages
Currency
formatting makes easy to interpret data but it doesn't clean the data.

Formulas applied in Excel.

A formula directs on how to perform a calculation .For example when you enter
=60+85
Excel calculates as 145
To calculate average salary the formula is
=AVERAGE(range criteria)
=AVERAGE(D2:D5)
To calculate the highest salary
=MAX(D2:D5)
To calculate the lowest salary
=MIN(D1:D5)
To count the number of employees
=COUNTA(A1:A5)

Sorting

sorting is organizing data in a particular order.
Example :Employees can be sort according to their departments,
Names Departments
Marion Finance
Peter IT
June HR
Irene Finance
Dennis IT
This sorted data enables the analyst to quickly identify an employee which department the employees comes from.

Filtering of data

Filtering hides data that does not meet a particular condition.
For example, since our dataset contains employees from different departments, we can filter the column to only show employees in IT departments ,filtering does not delete the other records .It only hides since it does not match the selected condition.

Data cleaning

It is the process of finding and correcting errors in a dataset.
Common errors in a dataset involves,
Missing values
Duplicate values
Inconsistent information
In data cleaning ,excel functions are:
UPPER
=UPPER(A2) It helps changes the text to uppercase
LOWER
=lOWER it help changes the text to lowercase
TRIM
=TRIM(A2) It help removes unnecessary spaces from text.

Preparation of data for analysis.

It is the process of cleaning, organizing raw data to be accurately ready for analysist avoid misleading results.
The analyst should ensure that the dataset is well structured, complete ,consistent ,accurate and identify missing values.

GitHub Repository

You can find my Excel data analysis project on GitHub:

[view my GitHub Repository

ASSIGNMENT WEEK 1

Dorcas Chebet — Sun, 23 Aug 2026 11:37:33 +0000

Understanding the Git workflow.

Introduction

Git is a system that is used incase one needs to track their projects or check if there was changes or need to get back to the initial project.
Git is important since it simplify the work of re-writting the project over again by keeping track on the initial project through tracking the changes in the file or checking on what has changed or under certain changes.
A complete work flow involves,

working directory
staging area
commit
push

working directory.

It is a folder in your laptop or computer where you work on your project .Working directory is where you:
create files
edit files
delete files
add data and make changes to your project.
The commands that are used in the working directory include,
pwd-it shows your current location
ls_it is used for listing files and a folder.
cd folder-it moves into a folder.
cd..-it is used if one needs to move back to the previous folder.
mkdir-it creates a new file folder.
touch file-wit creates a new empty folder.
git status-command used in tracking a folder.
git add-moves changes to staging area.

staging area.

It is where you put the changes you have chosen to save in your next commit in git.

The commands that are used in staging area:

git add- stage all the changes
git status- check what is staged
git diff--staged -sees what is exactly staged
git restore--staged-it removes a file from staging
git commit -m -it ensure you run git add before committing

Commit

It is a back up of the changes that have been placed in the staging area.
Good commit messages can tell what happened.

Push

Git push is a command that takes the commits made in a computer and send them to a repository such as Github.It is the final stage in working directory that sends the project to remote or local repository.

assignment

Dorcas Chebet — Thu, 20 Aug 2026 10:15:21 +0000

assinment week 1 article submission

DEV Community: Dorcas Chebet

Building the JCars logistics Sale Dashboard in Power BI

Introduction

Investigating the data before touching it

Cleaning and preparation:

Steps What I did Why, and what I assumed.

Modelling the data

The DAX behind the numbers

Designing the report

Insights

Recommendations

Conclusion

Assignment in power BI

Data modelling in power Bi

Data modelling approaches

Keys, uniqueness and integrity

Conclusion

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

Introduction

Project Objective.

Dataset and Business Queastions

Initial Data-Quality Audit

Excel techniques ,formulas and analysis performed.

Calculating the Discount amount

Analysis performed

ASSIGNMENT WEEK 2

Getting Started with Excel For Data Analysis

Introduction

Excel Basics

Formatting in Excel

Formulas applied in Excel.

Sorting

Filtering of data

Data cleaning

Preparation of data for analysis.

GitHub Repository

ASSIGNMENT WEEK 1

Understanding the Git workflow.

Introduction

working directory.

staging area.

The commands that are used in staging area:

Commit

Push

assignment