JCars Logistics Business Performance Analysis Using PowerBi

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

1. 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.
   

2. 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.