DEV Community: Wendy Adika

JCars Logistics Business Performance Analysis Using PowerBi

Wendy Adika — Tue, 29 Sep 2026 14:50:29 +0000

Introduction.

JCars Logistics imports, sells, and delivers vehicles to customers across different regions in Kenya.
The overaching goal of this analysis to to help JCars Logistics management understand how business is perforrming and the factors that influence that performance. Data cleaning, analysis, visualization and recommendations will be done through Powerbi.

Understanding the JCars data set.

Before working on the data open the JCars_data.csv on excel to get a glimpse and understand the data you will be working on.

The original dataset has a total of 276 rows(minus the header row) and 32 columns representing the following fields:

Order ID: Unique identifier key for a particular order.
Order Date: Date a particular order was made.
Delivery Date: Date delivery was made.
Customer Name: Name of Purchaser
Customer Type: Category of customer that made the order
Customer Age: Age of customer
Region: The region of purchase
County: County of purchase
City: City of Purchase
Branch: Company Branch
Sales Rep: Sales representative incharge of the Order.
Lead source: Category channels or origin where a potential customer first discovers or hears about the business.
Car Make: The Company that produced the car.
Car Model: The specific name, series, or product line assigned to the vehicle by the manufacturer.
Vehicle Type: category body style of vehicle.
Vehicle Year: Version of vehicle in year form
Fuel Type: Category source of energy the vehicle’s engine consumes to generate power.
Transmission: Mechanical component of a vehicle that transfers power from the engine to the wheels.
Color: The color of your vehicle purchased.
Units Sold: How many vehicles sold.
Unit Selling Price: Original selling price per unit
Unit Cost: Price after discount
Discount: Discount in percentage
Delivery Fee: price customer pays for the final transportation of a vehicle
Logistics Cost: internal expense business pays to move vehicles all the way to the final delivery.
Payment Method: Category means of Payment.
Payment Status: Whether payment is completed of pending
Delivery Status: If vehicle has been delivered
Customer Rating: Customer satisfaction between 0 and 5
Review Count: How many reviews were left
Returned: Whether vehicle was returned
Revenue Recorded: total amount of money business brings in from selling vehicles before any expenses are taken out.

Initial Data Profiling

Rows: 276
Columns: 32
Blank cells: 79 using =countblank(a2:af277) formula.

Data Quality assessment

Text inconsistencies

The same category may appear with different casing or spelling. For example, Toyota is represented as Toyota, toyota, TOYOTA and Toyta. Proper case standardization can fix capitalization, but spelling variants require a mapping table or business rule.

Numeric and currency inconsistencies

Price, cost and revenue fields contain values with KSh, KES, commas, question marks and M suffixes. These should be converted into numeric columns in Power Query. Invalid values should be flagged rather than silently treated as zero.

Discount inconsistencies

Discount values appear as 7%, 0.07, 10%, words and invalid values such as 120% and negative values. Valid discounts should be standardized to one representation and values outside 0–100% should be investigated.

Rating inconsistencies

Ratings appear as both numeric values and strings such as 4.5 out of 5. A clean numeric rating column should be created and validated against the 0–5 range.

Date inconsistencies

Order and delivery dates appear in different formats, including numeric date serials. Power Query should convert them using controlled parsing and validation.

Data cleaning using Power Query on Powerbi

After downloading the JCars_data.csv file, open Powerbi then click on blank report.
On the ribbon click on get data which will display a dropdown menu, then click on the type of data source your data is on which for our case is text/csv, then choose your exact data file.
Once this is displayed click on transform data if it needs cleaning. The option of load is mostly use when data is already cleaned and ready for analysis.

Once you click on transfrom data it will direct you to the power query editor interface.

Cleaning on Power Query

To check column quality click on view tab then check on column quality and profile. directly under your header you will see validity, error and empty percentage checks. These will help validate your data during cleaning.
When a sheet has no headers, you first click on use first row as headers under the transform group under the home tab as shown using the blue arrow.

Column Cleaning

1. Order id column.
As seen earlier on the raw worksheet, our column has inconsistent data format: some are LC1000 others are LCL-1000, ORD1008 and ord1005 hence need for standardization.
Data type needs to be text, in uppercase and without any hyphens, and Blanks to be replaced with Not provided

Solution:

Select the whole Order id column by clicking on the header, then right click to display a dropdown list, click on change type and ensure it is text.
Change column case to upper under the transform option in the dropdown menu once you right click.
Select replace value then replace all hypens with blanks eg when LCL-1OOO once all hyphens are replaced with blanks will be LCL 1000 then trim your column
Replace blanks and missing values with Not Provided.

2. Date columns.
Both the Order date and delivery date have inconcistent formats, with some cells having numbers and diffrent date formats. Another issue could arise in dates like 09/04/2025 where we are not not sure if the date or month come first. We will assume that the numbers in that column represent a date and that all dates 29/03/2016 read as 29th of March 2016.

On the add column tab, select custom column then on the displayed formula workspace add clean order id as the name of the custom column then use this code:

let
    x = Text.Trim(Text.From([Order Date])),

    result =
        if x = "" or Text.Lower(x) = "not provided" then
            null

        else if Value.Is([Order Date], type number) then
            Date.AddDays(#date(1899, 12, 30), Number.From([Order Date]))

        else if Text.Select(x, {"0".."9"}) = x then
            try Date.AddDays(#date(1899, 12, 30), Number.From(x)) otherwise null

        else if Text.Contains(x, "-") and Text.Length(x) >= 10 then
            try Date.FromText(x, "en-US") otherwise
            try Date.FromText(x, "en-GB") otherwise null

        else if Text.Contains(x, "/") then
            let
                parts = Text.Split(x, "/"),
                p1 = try Number.FromText(parts{0}) otherwise null,
                p2 = try Number.FromText(parts{1}) otherwise null,
                p3 = try Number.FromText(parts{2}) otherwise null,

                dateResult =
                    if p1 <> null and p2 <> null and p3 <> null then
                        if p1 > 12 then
                            try #date(p3, p2, p1) otherwise null
                        else if p2 > 12 then
                            try #date(p3, p1, p2) otherwise null
                        else
                            try #date(p3, p2, p1) otherwise null
                    else
                        null
            in
                dateResult

        else
            try Date.FromText(x, "en-US") otherwise
            try Date.FromText(x, "en-GB") otherwise null
in
    result

What this does is it handles:

Excel serial numbers
45671> actual date
DD/MM/YYYY
19/01/2026> 19/01/2026
DD-MMM-YY
26-Mar-25> 26/03/2025
Month-name dates
Feb 15, 2026> 15/02/2026
MM/DD/YYYY
02/21/2025> 21/02/2025
Invalid dates
2026-13-04> null
Missing values
Not Provided> null

The same goes for Delivery date. Use the same code but change column name.
Change the clean columns type to date, then remove the original columns and rename the clean ones once you have ensured that the cleaned columns are correct.
Add Date quality checks that check whether order dates come before delivery dates using this code:

if [Clean Order Date] = null and [Clean Delivery Date] = null then
    "Both Dates Missing"
else if [Clean Order Date] = null then
    "Order Date Missing"
else if [Clean Delivery Date] = null then
    "Delivery Date Missing"
else if [Clean Order Date] <= [Clean Delivery Date] then
    "Valid"
else
    "Invalid - Order After Delivery"

3. Customer Name Column, Sales rep
You can use whichever type of casing to make cleaning faster.
For these columns, Change capitalization to propercase by selecting the column, right click then select capitalize each word, replace blanks and N/A with Not provided. In the column sales rep, we assumed 1 is i hence 1 was replaced with i.
Ensure data type is text.

4. Customer type, Region, County, City, Lead Source, Car Make, Car Model, Vehicle type, Transmission, Delivery status, payment staus, payment mehtod, returned.
Since these are category columns, ensure there are no duplicate categories due to capitalization and spelling inconsistencies by using text cases and replacing similar values with one consistent category name. Also ensure type is text. Replace blanks with Not Provided.

Some of the assumptions and changes made in these columns are:

Customer type: Retail is individual, county government is government, dealer is car dealer and ngo is non profit.
In Region: kisumu region is part of Nyanza region.
Branch: Main yard is nairobi hq
Lead source: Tender is associated with government and corporate tender with private sector/commercial business.
Car Model: BMW x5 is X5
Transmission: AT,A/T> Automatic and MT,M/T> Manual
Fuel type: Gasoline was changed to petrol since they are the same.

5. Customer Age
Ensure Age type is whole number and empties are placed by null.
Min age:5
Max age: 121

6. Vehicle year
Create a custom column then use this code:

let
    x = Text.Trim(Text.Clean(Text.From([Vehicle Year]))),

    result =
        if x = "" or Text.Lower(x) = "null" or Text.Lower(x) = "not provided" then
            null
        else if Text.Lower(x) = "twenty twenty" then
            2020
        else
            try Number.From(x) otherwise null
in
    result

Then change Custom Vehicle Year to Whole Number.For the Power BI dashboard, keep Vehicle Year as a whole number, not Date, because 2020, 2021, etc. are years of manufacture, not dates.

7. Color
As you standardize the column, you notice the GRE category could either be green or grey. To determine which one gre is part of, I assume its probably the one with more counts. To find this, load your file into power query then create two measure that will calculate count of grey and green.
New measure:
Green count =calculate(COUNTROWS(Jcars_data), Jcars_data[Color] = "Green"
Do this for grey as well then compare.

8. Units Sold, Review Count
Replace texts with with corresponding numbers where necessary eg one> 1. Replace symbols (-) with blanks and trim column. Replace blanks with null then change type to whole number.

9. Customer Rating
To make data format consistent in this column, replace out of 5. For unique cells like 4.7/5 you can manually remove the /5. Replace 6 and excellent with 5 as assumption made is that excellent and 6 are 5 as 5 is the highest value in this column. ratings with negatives will be nullk instead of assuming.Replace blanks with null then change type to decimal number.

10. Discount
The discount column has different representations of percentages, that is 7% = 7%
0.07 = 7%
7 = 7%
0.15 = 15%
15 = 15%
ten percent = 10%
No Discount = 0%
120% = invalid because it exceeds 100%
-10 = invalid because it is negative
NULL, unknown, Disc, #ERROR, - = missing/invalid

To clean this, create custom column then use this power query code

let
    x = Text.Lower(Text.Trim(Text.Clean(Text.From([Discount])))),

    // Convert written percentages into numbers
    wordValue =
        if x = "ten percent" or x = "ten" then "10"
        else if x = "fifteen percent" or x = "fifteen" then "15"
        else if x = "twelve percent" or x = "twelve" then "12"
        else if x = "seven percent" or x = "seven" then "7"
        else if x = "five percent" or x = "five" then "5"
        else if x = "three percent" or x = "three" then "3"
        else if x = "two percent" or x = "two" then "2"
        else if x = "twenty percent" or x = "twenty %" or x = "twenty" then "20"
        else if x = "zero percent" or x = "zero" then "0"
        else if x = "fifteen" then "15"
        else x,

    // Treat "No Discount" and "None" as a genuine 0% discount
    noDiscount =
        if x = "no discount" or x = "none" then
            0
        else
            null,

    // Remove the % sign
    cleanedText = Text.Trim(Text.Replace(wordValue, "%", "")),

    // Convert remaining values to numbers
    numericValue =
        if noDiscount <> null then
            noDiscount
        else if cleanedText = ""
            or cleanedText = "null"
            or cleanedText = "unknown"
            or cleanedText = "disc"
            or cleanedText = "#error"
            or cleanedText = "-"
        then
            null
        else
            try Number.FromText(cleanedText) otherwise null,

    // Convert whole-number percentages to decimals
    decimalValue =
        if numericValue = null then
            null
        else if numericValue < 0 then
            null
        else if numericValue > 1 then
            numericValue / 100
        else
            numericValue,

    // Keep only valid discounts between 0% and 100%
    finalValue =
        if decimalValue <> null
            and decimalValue >= 0
            and decimalValue <= 1
        then
            decimalValue
        else
            null
in
    finalValue

It will return the values in decimal number which we will change on Powerbi.
120% was flagged as invalid hence null.

11. Currency columns
For these columns have different curriencies and formats.

Duplicate the original column by rightclicking the column then choosing duplicate column.
Trim and clean the column: Right click>transform> choose trim, then clean.
Change NULL, missing, not available and any blanks to null. A missing amount should remain null not 0.
Create a Currency column BEFORE removing currency labels because there are different currency labels and some unknown ?,- values. Name it currency and use this code:

=  if [#"Delivery Fee - Copy"] = null then null
else if Text.StartsWith(Text.Upper(Text.Trim([#"Delivery Fee - Copy"])), "KSh") then "KES"
else if Text.StartsWith(Text.Upper(Text.Trim([#"Delivery Fee - Copy"])), "KES") then "KES"
else if Text.StartsWith(Text.Upper(Text.Trim([#"Delivery Fee - Copy"])), "$") then "$"
else if Text.StartsWith(Text.Upper(Text.Trim([#"Delivery Fee - Copy"])), "USD") then "$"
else if Text.StartsWith(Text.Upper(Text.Trim([#"Delivery Fee - Copy"])), "R") then "ZAR"
else if Text.StartsWith(Text.Upper(Text.Trim([#"Delivery Fee - Copy"])), "ZAR") then "ZAR"
else if Text.StartsWith(Text.Upper(Text.Trim([#"Delivery Fee - Copy"])), "EUR") then "EUR"
else if Text.StartsWith(Text.Upper(Text.Trim([#"Delivery Fee - Copy"])), "error") then "Unknown"
else if Text.StartsWith(Text.Upper(Text.Trim([#"Delivery Fee - Copy"])), "-") then "Unknown"
else if Text.StartsWith(Text.Trim([#"Delivery Fee - Copy"]), "?") then "Unknown"
else "KES")

The last part say "KES" because your unlabeled values such as 3326000
appear to be Kenyan amounts.

Now remove currency labels from the duplicate unit selling price column by replacing them with nothing.
Remove commas by replacing them with nothing.
To deal with values like 4.2M where M means million, create a custom column for normalized unit selling price then use this code.

if [#"Unit Selling Price - Copy"] = null then null
else if Text.EndsWith(Text.Upper(Text.Trim([#"Unit Selling Price - Copy"])), "M") then
    Number.From(Text.BeforeDelimiter(Text.Upper(Text.Trim([#"Unit Selling Price - Copy"])), "M")) * 1000000
else
    Number.From(Text.Trim([#"Unit Selling Price - Copy"])))

Then Change amount on the normalized column to decimal number.

To change all our currencies to KES, we need to identify currency, order date and exchange rate in those years.
I used to find average exchange rates for years 2025 and 2026 since that is the range on our dates.
We will use order year to determine exchange rates. To extract year from Order date, select order date column, then on the add column tab selct date then year to create a custom year only order date.
This table has the currency details that I will use.

Create a custom column that will now convert our differnt currencies to kes using this code,

if [#"Currency "] = "KES" then [Normalized Custom]
else if [#"Currency "]= "ZAR" and [Year] = 2026 then [Normalized Custom] * 7.89
else if [#"Currency "] = "ZAR" and [Year] = 2025 then [Normalized Custom] * 7.24
else if [#"Currency "] = "EUR" and [Year] = 2026 then [Normalized Custom] * 150.26
else if [#"Currency "] = "EUR" and [Year] = 2025 then [Normalized Custom] * 146.20
else if [#"Currency "] = "$" and [Year] = 2026 then [Normalized Custom] * 129.31
else if [#"Currency "] = "$" and [Year] = 2025 then [Normalized Custom] * 129.31


else
    null)

Now change the column's data type to Decimal Number then you can use Amount_KES as your final currency field in Power BI.

Once all the columns have been cleaned, I will remove duplicates using the order id column since it acts as the primary key for this table by right clicking on the column the selcting remove duplicates.

there were a total of 70 dupes.

Data modelling

For this dataset, the star schema is the recommended structure for the cleaned JCars dataset.A star schema places a fact table at the centre and descriptive dimensions around it. It provides clear filter paths and supports reusable DAX measures.

To create and merging fact and dimension tables:

In Power Query, do not modify this original query directly. Instead, create references from it. Right click on the query name on navigation pane on the left of your screen the click on reference.

Choose of the referenced queries be your fact table and rename it. Since we do not have foreign keys in this table, do not delete any columns from it first. eg Fact_orders
Now create dimension tables using the other referenced queries.
We will use the vehicles data.
Creating dimension table

Create Dim Vehicle by renaming one of the referenced queries.
Keep: Car Make, Car Model, Vehicle Type, Vehicle Year, Fuel Type, Transmission, Color columns.
Remove duplicates
Add Column → Index Column → From 1
Rename Index to Vehicle id

Merging fact and dimnension table

Return to Fact_Orders
Home> Merge Queries
Choose your fact table(fact_orders) as your first table
Select Dim Vehicle
Match the 7 vehicle columns, Ensure the columns are chosen in the exact same order.
Choose Left Outerjoin to display all records in the first table and matching rows in the second.
Expand the merged table
Select Vehicle id

Do the same for the rest of the dimesion table.

Once you are done, goto your facts table and delete descriptive columns that are are in the corresponding dimension table and only remain with the keys for those tables.
eg on fact table remove customer name , customer type and customer age and only remain with customer id.

This is our recommended star schema:

Fact_Orders
The central fact table should represent one clearly defined sales transaction. Candidate measures include Units Sold, Unit Selling Price, Unit Cost, Discount, Delivery Fee, Logistics Cost and cleaned Revenue Recorded.

The grain: one row represents one JCars vehicle sales transaction. If the source later contains multiple vehicle lines per order, the grain must be changed to one row per order line.

Dim_Customer
Contains customer attributes such as Customer Name, Customer Type and Customer Age.

Dim_Vehicle
Contains Car Make, Car Model, Vehicle Type, Vehicle Year, Fuel Type, Transmission and Color.

Dim_Location
Contains Region, County, City and Branch.

Dim_SalesRep
Contains the sales representative and any additional representative attributes added later.

Relationships

To activate these relationships, load your tables to powerbi then select manage realtionships. Power bi automatically creates the relationships for you and shows cardianlity. The normal relationship pattern is dimension-to-fact one-to-many.

DimCustomer 1 ---- * FactSales
DimVehicle  1 ---- * FactSales
DimDate     1 ---- * FactSales
DimBranch   1 ---- * FactSales
DimSalesRep 1 ---- * FactSales

Then ensure status is active so that filtering is done throughout the whole data set.

Filter Direction

Single-direction filtering should normally flow from dimensions to the fact table. If a user selects Vehicle Type = SUV in DimVehicle, the filter propagates to Fact_Orders and restricts the measures to SUV transactions.

For our case we will choose both as our filter direction.

Business Logic and Dax

Create the following measures:

Total Revenue: Create a new measure by clicking new measure on the ribbon when you click home then use the following formula for analysis.

Total Revenue = SUM(Fact_Orders[Revenue_Recorded])

The measure will appear on the data tab on your furthest right as shown using the blue circle.

To display total revenue, click on report view then chose card from the visualizations. Card is marked in pink as shown below.

Drag your measure from the data table where your tables' details are and drop onto the card to display Total revenue.
To ensure the numbers are accurate, ensure you have selected the card, then on the visualization tab select format visual> General> Data Format> Decimal number.

Do the same for the rest.

Gross Sales Value:
Create new measure
Gross Sales Value = SUMX(Fact_Orders, Fact_Orders[Units_Sold] * Fact_Orders[Unit_Selling_Price] )
Total Vehicle cost:
Create a new measure.
Total Vehicle Cost = SUMX( Fact_Orders, Fact_Orders[Units_Sold] * Fact_Orders[Unit_Cost]
Total Delivery Cost:
Create new measure.
Total Delivery Cost = SUM(Fact_Orders[Delivery_Fee])
Total Logistics cost:
Total Logistics Cost =
SUM(Fact_Orders[Logistics_Cost])
Total Operating cost:
Total Operating Cost = [Total Vehicle Cost] + [Total Delivery Cost] + [Total Logistics Cost]
Gross profit:
Gross Profit = [Total Revenue] - [Total Operating Cost]
Profit Margin %
Profit Margin % = DIVIDE([Gross Profit], [Total Revenue], 0)
Format as Percentage.
Revenue per unit
Click on Fact_Orders, create new column then use this formula
Revenue per Unit = DIVIDE([Total Revenue], [Units Sold], 0)
Total Orders
New Measure >
Total Orders = COUNT(Fact_Orders[Order ID])
Total Customers:
Total Customers = DISTINCTCOUNT(Fact_Orders[Customer_Key])

This is preferable to counting customer names because your fact table can contain multiple orders from the same customer.

Average Customer Rating = AVERAGE(Fact_Orders[Customer_Rating])

Delivered Orders:
Delivered Orders = CALCULATE([Total Orders], Fact_Orders[Delivery_Status] = "Delivered")
Delivery Rate %:
Delivery Rate % = DIVIDE([Delivered Orders], [Total Orders], 0)
Cancelled Orders
Cancelled Orders = CALCULATE([Total Orders], Fact_Orders[Delivery_Status] = "Cancelled")
Returned Orders =CALCULATE( [Total Orders], Fact_Orders[Returned] = "True")

Return Rate % = DIVIDE([Returned Orders], [Total Orders], 0)

Paid Orders = CALCULATE( [Total Orders], Fact_Orders[Payment_Status] = "completed" )

Payment Completion Rate % = DIVIDE( [Paid Orders], [Total Orders], 0 )

Dashboard

Create your dashboards on the report view using charts and slicers.
This is how my dashboard looks like.

Findings and Recommendations

Once you are done. Save the Document and Move it Tto your Local Repo folder.

Practical Workflow

Jcars_data.csv
      |
      v
Data Profiling
      |
      v
Power Query Cleaning
      |
      v
Fact + Dimension Tables
      |
      v
Relationships
      |
      v
DAX Measures
      |
      v
Power BI Report

Conclusion

15. Conclusion

The JCars dataset demonstrates that effective Power BI reporting starts with data preparation and modelling rather than visual design. Its 276 records and 32 fields contain enough customer, vehicle, location, transaction and operational information to support a rich analytical model, but the inconsistencies in the raw file must first be addressed.

A star schema provides a clear structure in which FactSales records business events while dimensions provide customer, vehicle, date, branch and sales-representative context. One-to-many relationships provide predictable analytical behaviour. Power Query Merge should be used when data needs to be combined during preparation, whereas model relationships should be used for reusable analytical navigation.

Pushing to Github

Open Git bash.
Run pwd to ensure you are on the right directory.
use cd command to navigate to your directory.
Once on yoour dircetory(Project folder) run ls to ensure all files needed are there.
After confirming this run git status to check the status of your repo.
run git init to initialize your local repo as a Git repo.
run git status again to check state of your repo.- It will show files have not yet started being tracked.
Run git add . to add the whole folder/ current directory.
Write a commit message to commit this change using the git commit -m "Add Jcars"
Go to github and create a new repo> copy the ssh address for that repo.
Go back to git bash and run this command git remote add origin paste the ssh address here.
Run git remote -v to show the connection between the local and remote repos.
Run git push -u origin main
Check git status, then refresh your github repo to ensure the repo has been pushed.

Data Modelling, Relationships & Joins in Power Bi

Wendy Adika — Sun, 13 Sep 2026 11:29:39 +0000

Introduction

Microsoft Power BI is a business intelligence platform used to find insights within an organization's data. It can help connect disparate data sets, transform and clean the data into a data model and create charts or graphs to provide visuals of the data. All of this can be shared with other Power BI users within the organization.
Every Power BI report is only as good as the data model sitting underneath it. Visuals, DAX measures, and refresh performance all trace back to a handful of structural decisions made early on: how tables are shaped, how they relate to one another, and how data is combined before it ever reaches the report canvas. This article works through those decisions using a running sales scenario as a consistent example throughout, before closing with a recommended model design and the reasoning behind it.

i). Data Modelling

A data model, at its core, comes down to how tables relate to one another. Each table is responsible for one piece of the data. An example is sales transactions or product details and clear connections that tie those tables together.

Data Modeling in Power BI is the process of organizing multiple tables and defining relationships between them so that Power BI can efficiently analyze and visualize business data. The main data modelling approaches are flat table, star schema and snowflake schema.

Importance of a well designed data model.

Reporting: It will organize data into familiar business terms using dimensional structures (like fact and dimension tables) rather than rigid source-system formats. It will prevents conflicting metrics across departments by establishing standardized definitions and relationships.
Dax Calculations: It eliminates the need for complex, nested filter manipulation (FILTER, EARLIER) by letting relationships handle context automatically.It also prevents common errors like double-counting or incorrect cross-filtering between unrelated tables.
Performance: It reduces memory consumption and file size by removing unnecessary columns and optimizing data types wich equals to faster load time and efficient storage.
Scalability: It handles expanding data volumes and historical additions without degrading query response times and makes adding new data sources straightforward because you already have a clear conceptual framework for where new information fits.
Maintainability: It allows you to modify or expand reporting requirements locally without breaking existing visuals or downstream dashboards.

1. Flat Table

A flat table stores most or all reporting information in one wide table. For example, a retail dataset could contain OrderID, OrderDate, CustomerID, CustomerName, ProductID, ProductName, Category, Store, Quantity, UnitPrice, Discount, and SalesAmount in every transaction row.

Advantages:

Simple to understand: There is nothing to join or relate since everything is visible in one place.
Fast to build for a one-off, small analysis with no ongoing maintenance need.
Straightforward for basic visuals.

Disadvantages:

Heavy data redundancy: The same customer or product attributes repeat on every transaction row, inflating file size and memory usage.
Update anomalies: if a customer's city changes, every historical row for that customer must be updated, or the data becomes inconsistent.
DAX becomes harder at scale: Measures that should be simple aggregations end up needing extra logic to avoid double-counting repeated attribute values.

When it is apprpriate:
In Power BI, a flat table can work well for a small dataset because there are fewer relationships and less modelling effort. However, as data grows, repeated customer, product, or location attributes increase redundancy and can make maintenance harder. A flat table also does not naturally express the business separation between facts and dimensions.

Performance and complexity implications:
Model complexity is low (one table, no relationships), but runtime performance is often poor at scale: wide flat tables compress less efficiently, and repeated text values consume far more memory than the same values stored once in a dimension table and referenced by a numeric key.

2. Star Schema

A star schema organizes data around a central fact table, connected directly to a set of surrounding dimension tables: one join per dimension, no intermediate tables. Viewed in Power BI's Model View, the fact table sits in the middle with relationship lines radiating outward to each dimension, resembling a star.

The fact table (FactSales) holds transactional measures and foreign keys. Each dimension table (DimCustomer, DimProduct, DimDate, DimStore) is fully denormalized on its own. For example, DimCustomer holds every customer attribute in one table, with no further breakout into separate store or date tables.

Advantages:

Simple, predictable relationship paths: Every dimension is exactly one join away from the fact table, which keeps DAX filter logic intuitive.
Strong VertiPaq compression: Narrow, single-purpose dimension columns with repeated values compress extremely well.
Fast query performance: Fewer joins mean fewer hops for Power BI to resolve when a report applies a filter.
Easy for report authors to navigate: Dimension fields are grouped logically and predictably in the field list.

Disadvantages:

Some redundancy remains inside each denormalized dimension table (e.g., a city name repeated for every customer in that city), though far less than a flat table.
Less “textbook normalized” than a snowflake schema, which can matter in strict data-warehousing contexts outside of BI reporting.

When it is appropriate:
The star schema is the recommended default for the overwhelming majority of Power BI reporting models such as sales analysis, financial reporting, operational dashboards because it balances simplicity, performance, and maintainability better than either alternative.

Performance and complexity implications:
Star schemas are what Power BI's VertiPaq engine and DAX language are optimized for. Model complexity stays low even as more dimensions are added, and performance scales well because each additional dimension is still just one join away from the fact table.

3. Snowflake Schema

A snowflake schema takes a star schema's dimension tables and normalizes them further, splitting each into smaller, related sub-dimension tables. Instead of DimCustomer holding city and country directly, those attributes move into separate DimCity and DimCountry tables, connected in a chain back to DimCustomer.

The fact table still sits at the centre, but some dimensions now require two or more joins to reach. For example, filtering FactSales by Country means passing through DimCustomer > DimCity > DimCountry.

Advantages:

Eliminates redundancy within dimensions: A country name is stored once in DimCountry rather than repeated across every customer row.
Can reduce dimension table size when a dimension has genuinely hierarchical, highly repetitive attributes shared by many members.
Familiar to teams coming from traditional, fully normalized relational data-warehouse design.

Disadvantages:

More relationships to maintain and reason about, increasing model complexity.
Multi-hop filter paths can slow query performance, since Power BI must traverse several relationships to filter the fact table from an outer sub-dimension.
Harder for report authors to navigate because related fields are spread across more tables in the field list.
Diminishing returns in Power BI specifically: VertiPaq's columnar compression already minimizes much of the storage benefit that snowflaking is meant to achieve in traditional row-based databases.

When it is appropriate:
Snowflaking is worth considering when a dimension is very large and its sub-attributes are reused identically across many other dimensions (a shared DimGeography used by both DimCustomer and DimStore, for instance), or when a data source is already normalized upstream, and further denormalizing it into a full star schema is not practical.

Performance and complexity implications:
Snowflake schemas generally increase model complexity and can reduce performance relative to a star schema, because Power BI must resolve longer filter-propagation chains. The storage savings that justify snowflaking in traditional relational databases are largely already achieved by VertiPaq's compression, which weakens the case for snowflaking in most Power BI models.

Schema comparison Table

ii). Fact Table and Dimension Table

Star and snowflake schemas both depend on a clear division of labour between two table types: fact tables, which record business events, and dimension tables, which describe the entities involved in those events.

Fact Tables

A fact table stores measurable, numeric business events including the things a business wants to sum, average, or count. Typical fact tables include FactSales, FactOrders, and FactTransactions. Each row in a fact table generally represents one occurrence of the tracked event (one sale line, one order line, one transaction) and contains:

Foreign keys pointing to related dimension tables (CustomerKey, ProductKey, DateKey, LocationKey).
Measures which are numeric values meant to be aggregated, such as SalesAmount, Quantity, or Discount.

A crucial concept for any fact table is its grain. Grain is the level of detail a single row represents. A fact table with a grain of “one row per order line” is very different from one with a grain of “one row per order,” even if both are called FactSales; the grain determines what can and cannot be calculated correctly from the table. Grain should be decided deliberately and kept consistent. Mixing grains within a single fact table (some rows per line item, others pre-aggregated per order) is a common source of double-counting errors in DAX measures.

Dimensions table

Dimension tables contain descriptive attributes used to group, filter, and explain facts. Common dimensions include DimCustomer, DimProduct, DimDate, and DimLocation.

Measures vs. Descriptive Attributes
The distinction between a measure and a descriptive attribute is really a distinction between what belongs in a fact table and what belongs in a dimension table. SalesAmount is a measure: it is meaningful to sum it across thousands of rows. ProductCategory is a descriptive attribute: summing it makes no sense, but grouping and filtering by it does. Keeping measures and descriptive attributes in separate tables is what makes a star schema efficient. Power BI can compress and filter a column of repeated category labels very differently from a column of continuously varying sale amounts.

Worked Example: FactSales at the Centre of a Star Schema

Consider a retail business tracking online sales. A single central fact table, FactSales, records one row per sale line: a SalesID, the numeric SalesAmount and Quantity, and foreign keys to four dimensions. Each dimension supplies the descriptive context needed to make that fact meaningful in a report:

With this structure, a report can answer “total SalesAmount by Region and Quarter” by summing FactSales while grouping by attributes pulled from DimLocation and DimDate without a single duplicated customer or product name anywhere in the fact table itself.

iii). Relationships in PowerBI

A relationship in the Power BI data model is a defined link between two tables, based on a shared column, that tells Power BI how to combine data from both tables when a report requests it. Relationships are what make a star or snowflake schema function as a single connected model rather than a collection of unrelated tables. Without them, filtering DimProduct to a single category would have no effect on FactSales at all.
The diagram below shows the different types of relationships.

One-to-Many (1:*)

This is the standard, most common relationship in a star schema: one row in a dimension table (the “1” side) can relate to many rows in a fact table (the “*” side). Example: one product in DimProduct appears on many rows in FactSales. Use this whenever a dimension's key is unique and a related table repeats that key for multiple events. It should not be used when the “one” side is not actually unique. If DimProduct itself contains duplicate ProductKey values, Power BI will raise an ambiguity error or produce a many-to-many relationship.

One-to-One (1:1)

Each row in one table relates to exactly one row in the other, with unique keys on both sides, for example, DimEmployee and a supplementary DimEmployeeDetail table both keyed by the same unique EmployeeID. This is uncommon in typical star schemas and is generally used only when splitting one logical entity across two physical tables (for security reasons, or because the data arrives from two separate systems).

Many-to-Many (:)

Both sides of the relationship can contain repeated key values. For example, FactSales and a DimPromotion table where a single sale can qualify for multiple promotions, and a single promotion can apply to multiple sales. Many-to-many relationships should be used carefully and sparingly. They can produce ambiguous or unexpectedly inflated aggregation results, and are usually better resolved with an intermediate bridge table that turns the problem back into two clean one-to-many relationships.

Primary Keys, Foreign Keys, and Uniqueness

Every relationship in Power BI depends on two roles being clearly assigned:

Primary key: A column in a dimension table containing unique values, with no duplicates, that identifies each row (CustomerID in DimCustomer).
Foreign key: A column in another table (typically a fact table) that stores those same key values, but is expected to repeat many times, once for every event associated with that dimension member (CustomerID repeated across every row in FactSales for that customer).

This is precisely why CustomerID contains unique values in DimCustomer but appears many times as a foreign key in FactSales: DimCustomer holds one row per customer, while FactSales holds one row per sale, and any customer can have many sales.

Cardinality, Referential Integrity and Active/Inactive Relationships

Cardinality describes the matching pattern between tables: 1:, 1:1, or *:. Referential integrity means every foreign key value in the fact table has a matching primary key value in the related dimension table: no “orphan” sales pointing to a CustomerID that does not exist in DimCustomer. Power BI handles violations by grouping unmatched rows under a blank member rather than failing outright, but persistent referential-integrity gaps usually indicate an upstream data-quality problem worth fixing at the source.
A single pair of tables can have more than one plausible relationship eg FactSales might have both an OrderDate and a ShipDate, each of which could relate to DimDate. Power BI allows only one active relationship between two tables at a time (the one used automatically by visuals); any additional relationship must be marked inactive and invoked explicitly in DAX using the USERELATIONSHIP function when a calculation specifically needs the alternate date.

iv). Filter Direction

Filter direction determines which way a selection made on one table propagates to affect another, related table. This is set per relationship and is one of the most consequential yet most commonly misunderstood settings in a Power BI model.
The diagram below shows the two filtering directions:

Single-Direction Filtering

In single-direction filtering, a selection on the “1” side of a relationship (a dimension table) filters the “*” side (the fact table), but not the reverse. For example, selecting “Electronics” in DimProduct filters FactSales down to electronics sales but a selection made directly on FactSales does not filter DimProduct back. This is the default behaviour for one-to-many relationships in Power BI, and it correctly matches how most star schema reporting is meant to work: dimensions drive filters on facts, not the other way around.

Bidirectional Filtering

Bidirectional (both-direction) filtering allows the filter to propagate both ways: a selection on DimProduct still filters FactSales, but a selection or calculation touching FactSales can now also filter back onto DimProduct. This is occasionally necessary, for example, to make a slicer built from a fact table correctly limit an unrelated dimension in a many-to-many scenario but it should be applied carefully and only when needed.

The main risks of overusing bidirectional filtering are:

Ambiguous filter paths: When multiple bidirectional relationships create more than one possible route between two tables, Power BI may be unable to resolve which path should apply, or may apply one path unexpectedly.
Unnecessary model complexity: Bidirectional relationships are harder to reason about and debug, since the effect of a single filter can now ripple through the model in ways that are not immediately obvious from looking at a single relationship line.
Performance cost: Resolving filters in both directions requires more computation than a single, predictable direction.

The general guidance is to leave relationships single-directional by default and enable bidirectional filtering only on a specific relationship, for a specific, well-understood reason but never as a default troubleshooting step when a filter “isn't working.”

v) Joins in Power Query

A join (called a Merge Query in Power Query) combines rows from two tables based on matching values in one or more key columns. It is a similar in concept to a SQL join, but performed during data loading and transformation, before the data ever reaches the Power BI data model. Power Query's Merge Queries dialog supports six join kinds, illustrated below using a Customers table and an Orders table.
See figure below.

Left Outer Join

Definition: keeps every row from the left table (Customers) and attaches matching columns from the right table (Orders) where a match exists; unmatched left rows are kept with nulls in the right-hand columns.
Retained: all left rows, plus matched right rows.
Example: Customers = {Amina, John, Wanjiru}; Orders = {Amina–Order1, John–Order2}. A left outer join on Customer returns Amina with Order1, John with Order2, and Wanjiru with a null order. Wanjiru is not dropped even though she has no order.

Right Outer Join

Definition: the mirror image of a left outer join. It keeps every row from the right table (Orders) and attaches matching columns from the left table (Customers) where a match exists.
Retained: all right rows, plus matched left rows.
Example: using the same tables, if Orders additionally contains an order with no matching customer record (a deleted or missing customer), a right outer join still returns that order, with null customer details attached.

Full Outer Join

Definition: keeps every row from both tables, matching where possible and filling with nulls on whichever side has no match.
Retained: all rows from both Customers and Orders.
Example: the result includes Amina and John with their orders, Wanjiru with a null order, and any order with no matching customer, with null customer details or nothing from either table is dropped.

Inner Join

Definition: keeps only rows where a match exists in both tables.
Retained: only matched rows, which is the intersection.
Example: only Amina and John appear in the result, since only they have matching orders; Wanjiru (no order) and any unmatched order are excluded entirely.

Left Anti Join

Definition: keeps only rows from the left table (Customers) that have no match in the right table (Orders); no columns from the right table are attached, since by definition there is nothing to attach.
Retained: unmatched left rows only.
Example: the result is just Wanjiru and is useful for a “customers who have never placed an order” report.

Right Anti Join

Definition: the mirror image of a left anti join. It keeps only rows from the right table (Orders) that have no match in the left table (Customers).
Retained: unmatched right rows only.
Example: the result is only the order with no matching customer record. It is useful for finding orphaned transactions that point to a missing or deleted customer.

Join summary table

vi). Power Query Join vs Power BI Relationships.

Merging tables in Power Query and creating a relationship in the Power BI data model can look superficially similar because both connect two tables on a matching key but they operate at different stages of the workflow and have very different consequences for the resulting model.

A merge is the right choice when data genuinely needs to be flattened once upstream for example, resolving a lookup value from a small reference table into a single column before the query is loaded, where creating a full dimension table would be overkill. A relationship is the right choice for connecting a fact table to its dimensions, because it preserves each table's independent structure, avoids duplicating dimension attributes across every fact row, and lets Power BI's filter-propagation engine do the work of applying filters across tables at query time rather than at load time.
Excessive merging pulls a model back toward the flat-table pattern discussed in Section 1: descriptive attributes get copied into transactional rows, redundancy increases, and the model loses the clean separation between facts and dimensions that makes a star schema efficient. Keeping fact and dimension tables separate, connected by relationships rather than merged into one wide table is preferable in a BI model because it keeps redundancy low, keeps each table's grain clean and unambiguous, and lets a single dimension table (like DimDate) be reused across multiple fact tables without being duplicated into each one.

vii). Recommended Power BI Model

For a typical business intelligence project like sales, operations, or financial reporting against a fact table connected to a handful of descriptive dimensions(a star schema), connected with single-direction, one-to-many relationships, is the recommended default design.

Why a star schema over a flat table
A flat table may look simpler at first glance, but it fails on almost every criterion that matters as a report grows: redundancy inflates file size, compression suffers, and DAX measures that look simple can silently double-count values once a single wide table mixes transactional and descriptive data. A star schema solves all three problems by separating facts from dimensions from the outset.

Why a star schema over a snowflake schema
A snowflake schema's main benefit is reduced redundancy within dimensions. It is only marginally useful in Power BI, because VertiPaq's columnar compression already stores repeated values efficiently. What a snowflake schema does add is more relationships and longer filter-propagation paths, which increase model complexity and can slow down report performance without a corresponding benefit. Snowflaking is only worth the added complexity when a dimension is genuinely very large and its sub-attributes are shared identically across multiple other dimensions.

Relationships and filter direction
Within the recommended star schema, relationships should be one-to-many between each dimension and the fact table, with the dimension on the “1” side. Filter direction should default to single-direction (dimension > fact), which matches how star schema reporting is meant to work and avoids the ambiguous filter paths that bidirectional relationships can introduce. Bidirectional filtering should be reserved for specific, well justified cases such as a genuine many-to-many scenario resolved through a bridge table rather than applied broadly

Conclusion

In short, build fact and dimension tables separately, connect them with one-to-many relationships kept single-directional by default, use Power Query merges sparingly and only for genuine upstream flattening needs, and reach for a snowflake schema only when a specific, large, shared dimension clearly justifies the added complexity. This combination gives the best balance of performance, simplicity, and long-term maintainability for the great majority of Power BI reporting projects.

Building a Product Analytics Dashboard from E-Commerce Data: A Jumia Case Study

Wendy Adika — Sun, 06 Sep 2026 15:48:44 +0000

1. Introduction

E-commerce sites collect huge amounts of listing data, but it's rarely ready to analyze right out of the box. Prices show up as messy text instead of numbers, review counts sometimes don't make logical sense, and ratings are missing more often than you'd expect. This article walks through the full process of turning raw Jumia product listings into a working, interactive Excel dashboard including the data problems I ran into, how I cleaned them, the formulas used to build new analytical fields, and what the data ultimately showed. The goal isn't just to show the final result, but to explain why each decision was made, so the same approach can be reused on similar marketplace data.

The main question behind this project was easy to ask but harder to answer well: does discounting actually help sellers on Jumia, and if so, how? More specifically: do bigger discounts lead to more customer engagement (reviews), or better perceived quality (ratings)? To answer that, the analysis moved through four stages: getting the raw data in, cleaning and validating it, engineering useful features, and building a dashboard that non-technical people could actually use.

2. Dataset

The raw dataset consists of 115 rows and six columns as per the Raw_Data worksheet in the Jumia_product_dashboard.xlsl file. Below are screenshots of the Raw_Data worksheet and field descriptions.

This dataset is just a single snapshot in time;it doesn't include sales numbers, how old each listing is, or who the seller is. That limits what we can actually claim. The analysis can show that things are related, not that one causes the other.

3. Data Quality Assessment

Before any transformation, the raw data was profiled systematically. This produced a data quality audit with the following findings:

345 price cells were text, not numbers. Prices had things like currency symbols, commas, or extra spaces so Excel couldn't do math on them until we cleaned them up.
58 blank cells in both Review and Ratings. These weren't zero they just meant no review or rating existed yet. If they were treated as zero, it would have made every average look much worse than it really was.
57 review counts were negative. A review count can't be negative in real life, so this was likely an error from scraping or exporting the data.
*57 discount values didn't make sense *. Discounts should always be between 0% and 100%, so these were probably also export errors.
3 rows were exact duplicates. If left in, they would have been counted twice in every total and average.
2 prices were written as a range (like "100–200") instead of one number, which formulas can't use directly.
Column headers weren't consistent with spelling mistakes. This seems minor, but it causes real problems once formulas depend on typing the header name exactly right.

4. Data Cleaning

Before cleaning, I copied the data from Raw_Data sheet to Cleaned_Data sheet and converted the data range to an Excel Table with Ctrl+T and gave it the name tblProducts.

Removed the 3 duplicate rows. They added no new information and would have thrown off every total. 112 Products remained.
Renamed the column headers. so they were consistent. This also made them safe to use as table references (like tblproducts[Current Price]) instead of plain cell ranges.
Formatted prices column.Removed KSh, commas, and extra spaces, then convert to a number using this formula. =VALUE(SUBSTITUTE(SUBSTITUTE(B2,"KSh",""),",","")) Format cleaned values as KSh #,##0.00
Turned the price ranges into a midpoint number. This keeps the row usable without guessing at an exact figure. I used the average function to do this.
Changed disount column data type. Remove %, convert to a number, divide by 100 if necessary, and format as Percentage using the formula below. =VALUE(SUBSTITUTE(D2,"%",""))/100
Converted negative review counts to whole numbers. Since a negative review count isn't real, it was treated as an error and corrected it rather than deleting the row using the formula below. =IF(E2="","",ABS(VALUE(E2)))
Formatted Ratings column. Removed out of 5, converted to a decimal, and leave genuinely missing ratings blank using this formula. =IF(F2="","",VALUE(SUBSTITUTE(F2," out of 5","")))
Kept missing Review and Ratings cells as "Missing," not zero. This was the most important cleaning decision in the whole project.
Added checks for Price, Ratings and discounts column using the formulas shown.

5. Excel formulas and enrichment fields.

Once the numbers were clean, new columns were added to turn raw numbers into useful, easy to read categories.

Discount Amount:
=[@[Old Price]]-[@[Current Price]]

Rating Category (fixed thresholds, since a star rating means the same thing everywhere):
=IF([@Ratings]="","Missing",IF([@Ratings]<3,"Poor",IF([@Ratings]<=4.5,"Average","Excellent")))

Discount Category Chosen to match how discount tiers are usually talked about in retail.
=IF([@Discount]="","Missing",IF([@Discount]<20%,"Low Discount",IF([@Discount]<=40%,"Medium Discount","High Discount")))

Price Category used the dataset's own quartiles that were calculated in the analysis sheet instead of fixed numbers, since "expensive" or "cheap" depends on what's actually in this dataset:

Price_Q1 = QUARTILE.INC(tblproducts[Current Price], 1)
Price_Q3 = QUARTILE.INC(tblproducts[Current Price], 3)

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

Customer Engagement used the same quartile approach, splitting products into Low, Steady, and Strong engagement.
=IF([@Review]="","Missing",IF([@Review]<=Review_Q1,"Low Engagement",IF([@Review]<=Review_Q3,"Steady Engagement","Strong Engagement")))

Review Category
=IF([@Review]="","Missing",IF([@Review]<=Review_Q1,"Low Reviews",IF([@Review]<=Review_Q3,"Steady Reviews","Many Reviews")))

Finally, three simple QC check columns were added so problems would show up immediately if new data was ever added. Price, Ratings and Disounts.

6. PivotTable and analysis workflow;

After cleaning, 112 products remained. Summary numbers were calculated directly from the cleaned table.
These were the formulas used:

Total products =ROWS(tblProducts[Product])
Average current price =AVERAGE(tblProducts[Current Price])
Average old price =AVERAGE(tblProducts[Old Price])
Average discount =AVERAGE(tblProducts[Discount])
Average rating =AVERAGE(tblProducts[Rating])
Total reviews =SUM(tblProducts[Review])
Most expensive price =MAX(tblProducts[Current Price])
Least expensive price =MIN(tblProducts[Current Price])

Below is a screenshot of the summary.

The Xlookup function was used to find the excat most and least expensive product.
=XLOOKUP(MAX(tblproducts[Current Price]),tblproducts[Current Price],tblproducts[Product])

Correlation was also tested between the following fields using this formulas:

*Discount (x) versus review *(y); -0.136822724
As disount goes up the number of reviews tend to slightly go down but because the value is very close to zero the linear relationship is non-existent. That's worth pausing on, because it goes against the common assumption that bigger discounts automatically mean more customer engagement.

Ratings (x) versus review (y); 0.057209035
As the product rating increases, the number of reviews tends to increase very slightly.Because the strength of this relationship is so close to zero there is no meaningful linear relationship between the two variables, hence a product's rating does not predict how many reviews it will receive and vice versa.

Current price (x) versus rating (y); 0.110090213
As the current price of a product increases, its rating tends to increase slightly.The relationship is highly minimal. Because the value is so close to zero, price explains only a tiny fraction of the variation in product ratings. You cannot reliably use a product's price to predict its consumer satisfaction rating.

There are also three line charts for each correlation analysis.

From there, PivotTables were built on top of the cleaned data. PivotTables act as a middle layer. They make the charts faster to load and let the slicers filter several charts at once without slowing the whole workbook down.
The following are the tables created:
top 5 and bottom 5 products by rating;
top 10 products by discount;
top 10 products by reviews;
top 10 products by rating;
products with high discounts but low ratings;
products with high discounts but low engagement; and
products with many reviews but average ratings.

7. Dashboard Design and Slicer Connections

The dashboard was built so a non-technical person like a category manager could explore the data without ever touching a formula.

Below are screenshots of the Dashboard.

The top part of the dashboard is the dashboard title: Product Performance and Pricing
The furthest left side of the board are the slicers; Price category, Rating Category and Discount Category. All are connected to every PivotTable behind the charts, so clicking one option filters all the visuals on the sheet at once. Connecting both slicers to all five PivotTables (instead of just one chart each) is what makes the dashboard feel interactive rather than static: a single click updates the whole picture at once. You do this by right clicking on each slicer then reporting connections and choose all tables.

Eight charts:

Top 10 products by ratings
Top 10 products by review
Top 10 products by Discounts
Discount vs Reviews: does a bigger discount mean more reviews?
Rating vs Review: how does review count change across different rating scores?
Price vs Ratings: do more expensive products get rated higher?
Rating Mix: what share of products fall into each rating category, including "Missing"?
Discount Mix: how many products sit in each discount tier?

Insights
The insight text box contains findings and recommendations.

8. Key Findings

Bigger discounts bring more reviews, but not better ratings. High and Medium Discount products get far more total reviews than Low Discount ones, but the correlation check shows this isn't a strong, reliable pattern at the individual product level.
Most products sit in the High Discount group (about 63 products, versus 24 in Low Discount)The catalog leans heavily toward deep discounting.
Higher priced products get slightly better ratings (about 4.1 vs. 3.75 for the cheapest tier), a small but consistent gap.
A clear "Missing" ratings group exists, separate from "Poor": these are simply unrated products, not confirmed bad ones.
Discount size barely predicts review count (correlation ≈ −0.14) something else is driving engagement more than the discount itself.

9. Business Recommendations

Test smaller discounts on Low Discount products to see if reviews still grow without hurting ratings.
Check a sample of High Discount listings to see if they're real deals or just aging stock being cleared out.
Improve photos and descriptions on Low Price listings, where ratings tend to be a bit lower.
Focus review-request campaigns on "Missing" rated products before worrying about the smaller "Poor" group.
Don't rely on discount depth alone to grow reviews because the data shows it's a weak lever on its own.

10. Limitations and Lessons Learned

This dataset has no sales numbers, no listing age, and no seller identity hence "more reviews" can't be read as "more sales," and older listings can't be told apart from genuinely more popular ones. With only 112 cleaned rows, smaller groups (like Low Discount, at about 24 products) carry more uncertainty than larger ones. And because this is a single snapshot, none of these patterns should be treated as fixed facts but starting points for real tests, like controlled discount experiments, not final answers.

The biggest lesson from this project wasn't about charts or formatting. It was the decision to keep missing ratings labeled as "Missing" instead of quietly treating them as zero or dropping them. That one choice is what let the dashboard tell the difference between a product nobody has rated yet and one people genuinely dislike, a distinction that changes what a seller should actually do next.

10.Links

Github Repo
Dashboard file

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

Wendy Adika — Thu, 27 Aug 2026 18:14:07 +0000

What is Excel?

Excel is a spreadsheet program that enables users to capture, organize, calculate and visualize data. Excel is part of Microsoft office and is available for Windows and Mac. Operating systems that can't access excel like Linux will first require installation of a virtual Machine(A software based computer inside another physical computer) through a cloud-service provider like Microsoft Azure, Google Cloud Platform(GCP) or Amazon Web Services(AWS).

How Organizations Use Excel.

Collection and storage of data.
Data cleaning.
Data analysis and visualization.
Performance reporting.
Accounting.
Administrative and project management.

Excel User Interface and Components.

Interface.

When you open excel on your laptop, this is what you essentially see:

Components of Excel.

Ribbon: A row of tabs that helps you locate and navigate commands. The tabs include Home, Insert, Page Layout, Formulas, Data, Review, View and Help.
Quick Access Toolbar: Contains icons for save, undo and redo. Marked blue in the image below is the quick access toolbar.
Formula bar: Where contents of a selected cell are displayed. The formul bar is circled red in the image below.
Worksheet: A set of rows and columns.
Column: A vertical line of cells labelled in Alphabetical Order(Column A, Column B,C...)
Row: Horizontal line of cells labelled in numerical order( Row 1, Row 2,3...)
Cell: Intersection of a row and column where raw data is captured. It is labelled as column_row(A1, B12)
- Worksheet tabs: Tabs at the bottom (Original, Cleaned, Sheet2) that switch between spreadsheets within a workbook
Workbook- A collection of multiple spreadsheets/worksheets that create an excel file.
Status bar: Displays workbook status and quick calculation information. Refer on what it looks like using the image below.

Data Entry and Editing.

Entering data:

To enter data into a cell, click on any cell then add values. Columns store fields and rows store records. Click enter to move to the next row or the arrow keys to move to whichever cell you want.
See example below:

Data Types:

Some commonly used data types that can be found on the home tab under number group include:

General: No specific format
Text: Words(Gloria)
Number:2
Dates: 02/12/2026
Currency: $280

Data types are also accessible when you select a cell/cells then right click and choose format cells on the dropdown list that appears.

Data formatting in excel:

All data formatting commands are found in the home tab.

To Format text: Click on home tab then under font group select whatever format you desire. Some format options include Bold(B), Italics(I), Underline(U). You can also change Font type, Font color and Font size.
To Align cells> Check alignment group.Some alignment options include aligning to either the right, left, center top or bottom, merging cells(combining cells) and wrappint text(moving words to a new line when they reach a margin instead of overflowing.
Normally, when the data type is text, the cell contents are aligned to the left. Numerical data is usually aligned to the right.
To Format numbers: Check the Number group which contains actions like number types and choose your data type from the dropdown list.
Borders and fill colors: Select your cell range then go to font group and click on the diagram that looks like a window pane. See borders in the image below marked in blue.

Saving a Workbook:

Different ways to save your workbook:

Click on file then click on save as. Select the directory/folder you want it save in.(eg.Desktop) then write your file name. Make sure the file type is excel workbook/ .xlsx.
You can also click on the save icon at the quicktool bar at the top left part of the screen.
To save time, use the save shortcut by clicking ctrl and S.

Data Sorting and Filtering.

Sorting:

Sorting means arranging data in a specific order. You can sort text in alphabetical order(A-Z) or (Z-A), numerical values from largest to smallest and vice versa and date from oldest to newest and vice versa.
Sorting helps to make data easier to read, search and manage.
To sort:

Select column or range of cells you want to sort.
Go to home tab then sort and filter under the editing group.
Choose what type of sorting you desire. You can also custom sort. Then click ok to run that command.

Filtering:

Filtering hides cells that don't meet a certain criteria. It only displays the cells that do. The filer button is at the same place where the sort is. Check the sorting diagram above.
To filter:

Click anywhere on your data set.
Click on sort&filter on the hometab in the editing group, then click filter.
A dropdown arrow will appear on the column you want the filter.
Click on the arrow and choose your filter option. Click ok.

There are three types of filter options:

Text Filter: Contains, Begins With, Ends With.....
Number Filter: Greater Than, Less Than......
Date Filter: Before, After, Between.....

Data Validation:

Data validation restricts the type of data that can be keyed into a cell in a particular field/column.

Select range of cells you want to validate.
Go to data tab then click on data validation
On the dialog box, select the criteria you want to use.

An example is when you want your column to only accept K, L and M, you can click on list then add the 3 values. No other value that is not the three will be accepted in that particular column. See demonstration below.

Any attempt to key in any value that is not in the criteria will bring an error message as shown in the illustration above.

Data Cleaning.

Data cleaning is the process of finding and fixing errors, missing values and removing duplicates in a dataset to make it more accurate and ready for analysis.

Before cleaning your data, you can perform this actions to make navigation easier:

Autofit: When values in cells are cut off showing ###errors, you can first select all data on your workspace using the short cut C*TRL+A* then autofit by clicking on the home tab, then on the cells group click format, then click autofit width length as shown below.

2.Freezing Panes:
Freezing panes helps to lock specific rows or columns so that they remain visible as you scroll. This makes it easier to work on the data especially when dealing with a large data set.
Click on the view tab the freeze panes the choose what you intend to freeze, then click ok.

Removing Duplicates:

To remove duplicates,

Select your complete dataset by clicking the ctrl+A shortcut.
Go to the Data tab and select remove duplicates. See illustration below.
In the dialog box, check the columns to consider. Often it is wiser to check all. Then click ok.

Number Formatting:

Number formatting changes how numerical values appear on a spreadsheet without changing the actual data stored in the cell.
To format number,
You can select cells then right click to return a dropdown box with a format cells option.

Select cells then under the home tab go to number group and click on number format bar. These data types are used to show prices(currency), percentages(percentages/%)and dates.

Conditional Formatting:

Conditional Formatting is used to highlight cells that follow a rule that you have set, making it easier to search for a particular value and also spot trends.
To conditional format:

Select cells.
Go to home bar, under styles group click on conditional formatting.
Choose the rule you wish to apply. Click ok
Enter the value you want to highlight then click ok.

Text Functions

Text functions can be used to clean data by addressing issues such as extra spaces, inconsistent text formats, unwanted symbols, or multiple text strings combined in a single cell.
Below are the different text functions:

UPPER-Changes all text in selected cell to upper case- Example: If cell k5 contents is "Grace", when you use the upper function =UPPER(K5) and run it GRACE will be displayed.
LOWER-Changes all text in selected cell to lower case- Example: If cell k5 contents is "Grace", when you use the lower function =LOWER(K5) and run it grace will be displayed.
PROPER-Capitalizes the first letter of each word in a cell.If cell k5 contents is "grace kamau" when you use the proper function =PROPER(K5) then "Grcae Kamau" will be displayed.
TRIM-Removes extra spaces within text. If you =TRIM( " Grace Kamau " then "Grace Kamau" will be displayed.
LEFT-Extracts leftmost characters. If you =LEFT("Grace,2) then "Gr" will be displayed. Similar to the RIGHT function where if you =RIGHT("Grace",2) it will display "ce".
MID-Extracts characters from the middle. if you =MID("Grace", 1) then "a" is displayed.
LEN-Calculates length of text. If you =LEN("Grace") then "5" is displayed.
FIND-Finds position of a substring. If you =FIND("a","Grace") the "3" is displayed.
CONCAT-Combine two texts from different cells into one. If Grace(k5) and Kamau(L5) are in different cells you can =CONCAT(K2," ",L2) then "Grace Kamau" will be displayed. the " " on the function is for when you want a single space between the two texts.
SUBSTITUTE-Replaces a text within a string. If you =SUBSTITUTE("Grace kamau"," kamau"," Mutua") it will display "Grace Mutua".

Other functions include

Aggregate functions: SUM, PRODUCT, POWER, MODE, MEDIAN, MAX, MIN, SQRT etc. Statistical functions: COUNT, COUNTA, COUNTBLANK etc. Conditional: COUNTIF,SUMIF....etc. Date and Time: TODAY,NOW,DAY,MONTH,YEAR,DATEDIF,NETWORKDAYS etc.

Formulas.

A formula is an expression in a cell that performs calculations. It must always start with the = sign.
It can include

Cell references(M20)
Operators(*,+)
Numbers(45)
Functions(SUM) An example: =78+22 which will return 100

Conclusion

This article has explained the basics of Mircosoft excel including components and the different ways to you can use it to clean data(Sorting and filtering, Data validation, removing duplicates, number formatting). Moreover, you can practice on ai generated unclean date and explore other articles to deepen your skills.

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

Wendy Adika — Thu, 20 Aug 2026 12:20:47 +0000

This tutorial guides on how to push your local projects to Github using Git and SSH.

The prerequisite steps are:

Installed and configured Git using your user name and email.
Set up your Github account.
Generated and added your public ssh key to your Github.
Tested your ssh connected and make sure it is successful.
Created a local project with Readme file, data ,scripts and notebooks where applicable.

Step 1: Project Initialization With Git

Open gitbash terminal.

Before initialization, ensure your location(which is at the end of the path) is the project folder you intend to push.
Run:
pwd which stands for print working directory. Working directory is the folder you are currently in.
You can move to intended directory using the cd command.
To initialize the folder as a Git repo(repository) run:
git init

This will appear:

Also check for git's hidden files by running: ls -la You should see .git among files. .git folder acts as the git memory.

Step 2: Check Status

To check the current status of your working directory and staging area run:
git status

Because it is a new project, the files are untracked.

Step 3: Add

To prepare our project files for the next commit you run:
git add .

Step 4: Commit

To save any changes to your project files run:
git commit -m "----"
Inside the quotes, write a message that explains what was saved.
See example:

Always ensure that the branch is main.

Step 5: Pushing to Github

Create a New Github Repo

Sign in to your github account.
Create a new repo- should be empty.
Fill in new repo form then click on create(Repo name, description and choosing either public or private, the rest you can skip).
Click on ssh then copy ssh address.

Go Back To Gitbash

Ensure location is still project folder.
To link your local git repository to your github repository run: git remote add origin **the_copied_ssh_address**
To confirm that Git can send and receive information from the correct github repo run: git remote -v .
To push your project run: git push -u origin main This means that git has sent any commited changes to your github repository.
Confirm your project has been pushed to github by refreshing the page.