DEV Community: PRUDENCE KORIR

End-to-End Power BI Architecture: Transforming JCars Logistics from Dirty Flat Data to Executive Decision Support

PRUDENCE KORIR — Sat, 26 Sep 2026 14:17:21 +0000

Transforming Dirty Automotive Logistics Data into Executive Decision Support in Power BI

Building business intelligence dashboards for messy, real-world data requires far more than dropping charts onto a canvas. It demands rigorous exploratory auditing, robust Power Query ETL pipelines, structured DAX modeling, and executive-level storytelling.

In this project, I engineered an end-to-end Power BI reporting solution for JCars, a multi-branch automotive dealership and logistics network. Here is how I took an uncleaned transactional flat file and transformed it into a decision-support platform tracking KES 1.905B in revenue across 276 customer orders.

1. The Challenge: Uncleaned Flat File Data

The source data (Jcars_data.csv) captured transaction-level vehicle sales, delivery tracking, and financing data. However, the raw data presented major data quality challenges:

Mixed Currencies: Monetary columns contained mixed string prefixes (USD, KES) alongside bare numbers.
Inconsistent Categorical Text: Branch locations, vehicle makes, and delivery statuses suffered from varied casing (toyota, TOYOTA) and split synonyms (Delivered, completed, Done).
Locale-Sensitive Dates: Conflicting DD/MM/YYYY and MM/DD/YYYY formats caused parse errors and nulls.
Data Governance Traps: Dispatched units showing delivery timestamps prior to order placement dates, and cancelled orders retaining "Paid" settlement statuses.

2. Power Query ETL & Data Standardization

To ensure the downstream semantic model remained performative and reliable, all cleaning logic was addressed upfront:

Currency Normalization

All values were standardized to Kenya Shillings (KES). Text extraction logic stripped currency prefixes, applied an exchange rate baseline of 1 USD = 130 KES, and cast columns to native currency data types:

Final KES = Raw Numeric Value * Exchange Rate

Date Engine Resiliency

To resolve locale parsing errors across operating systems, I implemented locale-aware date parsing with conditional fallbacks, preserving chronological continuity across all order and delivery timelines.

Categorical Bucketing

Using DAX and M transformations, over a dozen fragmented delivery labels were mapped into four unified operational states:

Delivered (encompassing "Delivered", "Completed", "Done")
In Transit (encompassing "On the way", "At the yard", "Dispatched")
Cancelled
Returned

3. Dimensional Modeling & DAX Architecture

Rather than computing ad-hoc aggregations inside visual fields, I established a dedicated _Measures table housing normalized DAX logic:

// 1. Top-Line Turnover
Total Orders = DISTINCTCOUNT(Jcars_data[Order ID])
Total Units Sold = SUM(Jcars_data[Units Sold])
Total Revenue = SUMX(Jcars_data, Jcars_data[Units Sold] * Jcars_data[Unit Selling Price])

// 2. Cost & Profitability Mechanics
Total Cost = SUMX(Jcars_data, (Jcars_data[Units Sold] * Jcars_data[Unit Cost]) + Jcars_data[Clean_Logistics_Cost])
Gross Profit = [Total Revenue] - [Total Cost]
Gross Profit Margin = DIVIDE([Gross Profit], [Total Revenue], 0)

// 3. Operational Friction
Return Rate = 
DIVIDE(
    CALCULATE([Total Units Sold], Jcars_data[Clean_Delivery_Status] = "Returned"),
    [Total Units Sold],
    0
)

Cancellation Rate = 
DIVIDE(
    CALCULATE([Total Orders], Jcars_data[Clean_Delivery_Status] = "Cancelled"),
    [Total Orders],
    0
)

Core Benchmark Results:

Total Revenue: KES 1,904,596,846.00

Total Gross Profit: KES 393,327,843.00

Gross Profit Margin: 20.65%

Units Sold: 466 Units across 276 Orders

Return Rate: 31.88%

Cancellation Rate: 13.41%

4. The 3-Page Executive Dashboard

The report was organized into three purpose-built views with persistent button navigation:
1. Executive Overview: 6 high-level KPI cards, revenue ranking by vehicle make, branch turnover distribution, and monthly sales trends.

2. Vehicle Performance: A hierarchical drill-down matrix (Make, Model), vehicle body type share, and a Margin % vs. Return Rate scatter chart with reference quadrants separating star performers from high-risk liabilities.

3. Operations & Logistics: Detailed operational metrics comparing order delivery statuses, branch order volumes, and a clustered column chart evaluating Collected Delivery Fees vs. Actual Logistics Costs.

5. Key Strategic Takeaways

The High-End Return Paradox: While luxury brands like BMW drive healthy unit margins, they exhibit an alarming 55.56% return rate. Recommending mandatory pre-delivery mechanical inspections (PDI) before customer dispatch.

Logistics Subsidy Leakage: Upcountry and high-volume branches frequently incur logistical costs that exceed the flat delivery fees billed to customers. Introducing dynamic, distance-based freight pricing will protect operating margins.

Pre-Delivery Churn: Extended fulfillment delays account for a 13.41% cancellation rate, indicating an urgent need to automate inventory release workflows at regional holding yards.

Conclusion

A successful analytics project connects low-level data transformation directly to executive decision-making. By pairing resilient ETL processes with intentional visual design, this dashboard turns fragmented logistics records into actionable operational clarity.

Making Sense of Power BI: Data Modelling, Relationships, and Joins

PRUDENCE KORIR — Mon, 14 Sep 2026 15:25:41 +0000

When raw data hits Power BI, it’s rarely clean or ready for reporting. Data modeling is the process of structuring and connecting your tables so your reports load fast, your DAX measures calculate accurately, and your visuals actually make sense.

Getting your data model right early saves hours of DAX troubleshooting down the road. Here is a breakdown of the core concepts you need to build robust Power BI reports.

1. Data Modeling Architecture

How you arrange your tables determines how efficient your report will be.

Flat Table Approach: Combining everything into a single, massive spreadsheet-style table.

Pros: Quick to set up for a fast sanity check.

Cons: Tons of redundant data, massive file sizes, and terrible DAX performance.

Star Schema (Best Practice): Placing a central fact table (containing numerical metrics) surrounded by descriptive lookup tables.

Pros: Blazing-fast engine performance, clean DAX, and clear reporting paths.

Cons: Requires upfront work to break down flat tables.

Snowflake Schema: Similar to a Star Schema, but dimension tables branch out into further sub-dimension tables (e.g., a Product table linking to a SubCategory table, which links to a Category table).

Pros: Slightly reduces data storage redundancy.

Cons: Creates a complex model, makes DAX harder to write, and slows down report filtering.

2. Fact Tables vs. Dimension Tables

Building a Star Schema requires dividing your data into two distinct table types:

Fact Tables

What they are: Log files of business events or transaction records.

Contents: Numeric measurements, monetary values, timestamps, and foreign keys.

Examples: FactSales, FactInventoryLogs, FactOrders.

Grain: Defines what a single row represents (e.g., one row per line item on a customer receipt).

Dimension Tables

What they are: Context tables that provide descriptive details for your metrics.

Contents: Text, attributes, names, dates, and categories.

Examples: DimCustomer, DimProduct, DimStore, DimDate.

Example: In an electronics store, FactSales logs that on Friday, CustomerID #402 bought ProductID #88 for $1,200. Power BI uses DimCustomer to see that #402 is Sarah, and DimProduct to look up that #88 is a 4K Monitor.

3. Relationships & Directionality

Relationships serve as the connectors between tables so filters apply across visuals.

One-to-Many (1:*): The standard relationship pattern. One unique ID in a Dimension table connects to multiple entries in a Fact table.

One-to-One (1:1): Rare. Used mainly when splitting large tables for privacy or optimization.

Many-to-Many (:): Complex and risky. Can cause ambiguous filter behavior and inaccurate total calculations.

Key Mechanics:

Keys: Dimension tables use a Primary Key (strictly unique values). Fact tables use a Foreign Key (repeated values).
Active vs. Inactive: Only one active relationship (solid line) can exist between two tables at a time. Alternate dates (like OrderDate vs. ShipDate) require inactive relationships (dotted lines) activated via DAX (USERELATIONSHIP).
Filter Direction: Stick to single-direction filtering (Dimension $\rightarrow$ Fact). Avoid bi-directional filtering unless absolutely necessary, as it introduces circular paths and degrades performance.

4. Power Query Joins vs. Power BI Relationships

It is common to confuse Power Query Merges with Data Model Relationships, but they run at completely different stages:

Feature	Power Query Merges (Joins)	Power BI Data Model Relationships
When it runs	During data transformation (before load).	After data is loaded into memory.
How it works	Combines columns physically into one wider table.	Keeps tables separate, connected via virtual relationships.
Best Used For	Consolidation of messy dimension tables.	90% of your reporting and metric modeling.

Power Query Join Types:

Inner Join: Keeps only matching rows from both tables.

Left Outer Join: Keeps all rows from the primary table, adding matching details from the secondary table.

Left Anti Join: Keeps only rows from the primary table that have no match in the secondary table (great for identifying unassigned IDs).

For standard analytics, target a Star Schema with One-to-Many (1:*) relationships and Single Directional filters. Power BI’s engine is engineered specifically for this structure, giving you fast load times, concise DAX, and scalable reports.

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

PRUDENCE KORIR — Sun, 23 Aug 2026 02:50:10 +0000

When I first walked through the gates of LuxDevHq, my first thought was "endless possibilities". However, I was intimidated. I did not think I would measure up. But when I attended my first class, I learned that building something great starts with a great foundation and at LuxDevHq, that means mastering all the basics.

In the last week, I worked on setting up a local project on my desktop which I then pushed to a live repository on GitHub using the command line.

I was working with a "Kenya_Hospital_Health_Records" csv dataset which was to be stored in a sub-folder called "data" within the main folder on the desktop which is named after the file.

These are the steps I took:

1. Local Project Set Up

Open the bash terminal and navigate to the desktop using the cd command and create the main folder along with the sub-folder.

cd Desktop
mkdir -p Kenya_Hospital_Health_Records_Project/Data
cd Kenya_Hospital_Health_Records_Project

Copy and paste the file into the sub-folder.

Creating the README.md File

echo "# KENYA HEALTH RECORDS ANALYSIS" > README.md
echo "##Project Overview" >> README.md
echo "This is a project analysis for a hospital health records" >> README.md
cat README.md

> operator adds new text to the file.
>> operator adds text to already written text.
cat README.md prints the contents of the file for review.
nano README.md edits the file.

2. Creating a Tracked Git Repository

git init creates and empty repository.
git status checks the status of the repository.
git add . adds everything to the current repository.
git commit -m "Kenya Hospital Records Project the commit message helps to remember whatever it entails.
git remote add origin https://github.com/prudencejeronokorir-code/Git-Commands-Class.git this is the SSH key that links the local repository to the empty GitHub repository.
ssh -T git@github.com to confirm that the SSH key works
git push -u origin main pushes the local main branch to the remote origin on GitHub
*-u command helps avoid typing out the branch name upon every update.

And with that you are done!!!