DEV Community: Elvis

Transforming Messy Logistics Data into an Executive Power BI Dashboard: The JCars Project

Elvis — Tue, 29 Sep 2026 18:29:10 +0000

In the world of data analytics, you rarely get a perfect dataset. Real-world business data is often messy, filled with manual entry errors, mixed currencies, and flawed calculations.

Recently, I took on a project for JCars Logistics to build an end-to-end Business Intelligence solution. The goal was to take a highly inconsistent raw flat-file and transform it into an interactive, reliable Power BI executive dashboard. Here is a technical walkthrough of my process from ETL to Data Modeling and DAX.

(You can view the complete project files and documentation in my (https://github.com/dev-elvismuoka/End-to-End-Power-BI-Reporting-JCars)).

1. ETL & Data Cleaning with Power Query

The initial dataset (Jcars_data.csv) required heavy cleaning before any analysis could begin. Using Power Query, I executed the following transformations:

Standardizing Text & Dates: Fixed typos, trimmed invisible spaces, and converted Excel serial numbers (e.g., 46066) into standard Date formats.
Cleaning Percentages: Converted text-based string percentages (e.g., "7%") into usable decimal formats (0.07).
Currency Standardization (M-Code): The most complex ETL challenge was a mix of currencies (USD, ZAR, EUR) and text suffixes ("M" for millions) in the financial columns. I wrote a custom Power Query M formula to extract the numeric values, handle missing/error text, and apply static exchange rates to convert everything into a unified Kenya Shillings (KES) baseline.

2. Data Validation: Trusting the Math, Not the System

A critical part of data engineering is validation. The raw dataset contained a Revenue Recorded column. Instead of blindly accepting it, I created a custom column to calculate the True Revenue based on the formula: (Unit Price * (1 - Discount)) * Units Sold.

The Finding: The recorded revenue contained severe discrepancies, missing discounts, and negative error values. This validated the decision to abandon the raw column and rely on explicit DAX measures moving forward.

3. Analytical Data Modeling (Star Schema)

To optimize the Power BI engine and avoid implicit aggregation issues, I broke the flat file down into a Star Schema.

Created Dim_Location and Dim_Vehicle by referencing the main query and removing duplicates to create unique primary keys.
Retained the cleaned main query as Fact_Sales.
Established One-to-Many (1:*) relationships with single cross-filter directions between the dimensions and the fact table.

4. Business Logic with DAX

I created a dedicated _Measures table to house explicit DAX calculations, preventing the model from relying on implicit aggregations. Key measures included:

Total Sales Revenue (using SUMX for row-by-row iteration)
Total Costs (Base Cost + Logistics + Delivery)
Gross Profit & Gross Profit Margin (using DIVIDE to prevent errors)

5. Visualization & Interactive Drill-Through

The final product consists of a two-page interactive report:

The Executive Summary (Page 1): A high-level view featuring a KPI ribbon, revenue/profit trendlines over time, and location performance.
Detailed Logistics Analysis (Page 2): A deep-dive page utilizing Power BI's Drill-through feature. An executive can right-click a specific branch on Page 1 and instantly teleport to Page 2 to see the vehicle-specific logistics costs and customer satisfaction ratings for only that branch.

Key Business Insights

Through this model, I was able to deliver actionable insights to JCars management:

Heavy vehicle types severely erode gross profit margins due to disproportionate logistics costs.
Relying on manual Point-of-Sale revenue entry is causing massive accounting discrepancies; the CRM needs automated calculations.
The company is highly exposed to currency volatility and should standardize regional price lists to a unified base currency.

*Feel free to check out the underlying code and data model on my GitHub.

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

Elvis — Sun, 13 Sep 2026 12:18:11 +0000

Data typically arrives in Power BI from various sources, and is often in unorganized or inconsistent formats. Data modelling is just the process of cleaning this data and then logically linking it together in such a way that your reports can run quickly and your DAX formula functions. A good model makes your life easier, a bad model will make your Power BI app slow and confusing!

This is a summary of key concepts that you should understand when creating a Power BI model.

1. Data Modelling Approaches

The order of tables is important. You can accomplish this in three ways:

Flat Table: This is a bit like a huge Excel spreadsheet with all sales, customer information, product information and so on stuffed into one large table.

Pros: Easy to view for a rapid scan.

Cons: Lot of repeating data, huge file size and poor performance in Power BI.

You can create a diagram of a One Massive Table with 50 columns.A diagram of a One Massive Table with 50 columns can be created.

Star Schema

: The optimal configuration in Power BI. You have one numbers table, in the middle, with descriptive tables that reference the numbers. It resembles a star.

Cons: Can be very slow when using many different data sources that don't share the same data model.

Cons: It needs a little work up front to correctly partition your data.

Diagram:

Snowflake Schema: Just like a star, but with even more tables such as a product table pointing to a separate category table.

Cons: Offers a little less storage space.

Pros and Cons: Creates a messy model and makes the Power BI tooling slower due to the number of hops it needs to make to filter data.

2. Fact Tables vs Dimension Tables

In order to create a Star Schema, you must divide your tables into two categories: Facts and Dimensions.

Numbers are stored in Fact Tables. Business Events are stored in Fact Tables. They monitor and follow up what has occurred. A fact table's "grain" is how much detail it contains, for example, one row represents one transaction.

Contains: Numbers, dates, and ID keys.

In fact, there are several examples of fact tables, including FactSales , which is a table that contains information about all transactions ,and FactOrders.

Dimension Tables: These are used to hold information about the facts. They give you the who, what, where and when.

The description will include the following: Contains: Text, names, categories, descriptions.

Examples: DimCustomer (names of customers), DimProduct (names of items), DimDate.

Let us take an example of a business which sells spare parts for motorcycles. The FactSales table just logs that on Tuesday, Customer #105 bought Product #50 for KES 2,000. To find out that Customer #105 is John and Product #50 is a brake pad, Power BI looks up those IDs in the DimCustomer and DimProduct tables.

3. Relationships in Power BI

Relationships are simply wires that link your tables together, allowing them to communicate with one another. If they're not in there, the total sales amount won't be filtered by clicking on a customer's name on a dashboard.

One to Many (1:*): Standard and best type. In the DimCustomer table, there can be many Sales in the FactSales table.

One-to-One (1:1): Rare. In Table A, each row corresponds to exactly one row in Table B (one employee to one ID badge).

Many-to-Many (:): Disorderly and typically not done. A large number of students enrolled in a variety of classes. Can cause Power BI to be confused and to generate incorrect totals.

Key concepts here:

Ability to have a unique ID in a dimension table (e.g., CustomerID). No duplicates allowed.

The same ID used in the fact table (Foreign Key). It is repeated because a customer has the ability to purchase items multiple times.

Active vs Inactive: More than one line can be between two tables (such as Order Date and Ship Date) but only one of them can be solid (Active). The other are dotted (Inactive) and require special DAX to get them active.

4. Filter Direction

Filters are passed down the wires when tables are connected.

Single-direction filtering: The dimension table filters the data in one direction only, that is, from the Dimension table to the Fact table. If you filter DimProduct by "Helmets", the dimension will go down and filter FactSales to just show helmet sales. This is the most secure and quickest way.

Both/Bidirectional filtering: Arrows flow in both directions. What's more, the fact table can be used to filter the dimension table. Unless there is a compelling need to use this, you should do your best to avoid using it as it creates “ambiguous paths” and slows down your entire report.

6. Separates in Power Query (Dividing)

Data can be transformed in Power Query prior to it being loaded into the Power BI Model. In some cases, it's necessary to physically join two tables. This is called Merging.

Let's say we have an Orders table and a Customers table.

Left Outer Join: All the Orders are retrieved and only Customer details are retrieved when there is a match.

Right Outer Join: - Retains all Customers; Only orders when they match.

Full Outer Join: Returns all the data from both tables, matching them when they have a matching field, but leaving the values empty when they do not.

Inner Join: Only returns the rows with a match in both tables. If there is no matching customer, then the order will be dropped.

Left Anti Join: Only return the Orders which do not have a corresponding Customer.

Right Anti Join: Remains only the Customers that have not ordered anything.

Expected Output Example (Inner Join): When you Inner Join Orders to Customers, the output table will only contain orders that had a Customer ID on their Order Line item, and not those that were a cash order.

Power Query Joins vs Power BI Relationships
They can be easily confused, but they occur at different times and do different things:

Power Query Merge (Joins): This occurs prior to the data being loaded. It physically "mashes" columns from two tables into a wider table. It will make files larger and refreshing slower.

Power BI Relationships: This occurs once the data has been loaded. The tables remain completely independent in the Model view and are only connected by a virtual wire.

When to use which? Always use tables separately and use Relationship 90% of the time. It's better for BI. Use the Power Query Merge only if you have to get some messy data into a single dimension table before loading it.

7. Recommended Power BI Model

In my opinion, the Star Schema is a very good solution for a typical business intelligence project.

The way it should be set up is:

Data Model: Star Schema (1 fact table as center surrounded by multiple dimension tables).

Relationships: Strictly One-to-Many (1:*).

Filter Direction: Only Single direction from Dimensions to Fact table.

Why? The Star Schema has the "Goldilocks" condition.

Performance: The underlying engine of Power BI (VertiPaq) is actually optimised to read Star Schemas very quickly.

Writing DAX on a Star Schema is easy, DAX Simplicity. No more bidirectional filters or peculiar many to many errors.

Maintainability: When the business adds a new product category or a new store branch, you simply update the dimension table for that category without destroying some big, big flat file. It maintains your model clean, scalable and easy to read.

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

Elvis — Sun, 23 Aug 2026 00:11:26 +0000

I thought that when i join Lux Dev i would jump straight into building complex data pipelines and getting to understand kafka, kafka sounds like a really cool name, but if there's one thing I'm realizing quickly, it's that before you can orchestrate complex data pipelines or deploy web scrapers, you have to master the absolute basics of version control.

This week, I was working on setting up a new local project, a health records analysis and pushing it to GitHub entirely through the command line.

If you're just starting out with version control, here is exactly how I took a project from a completely blank folder on my desktop to a live repository on GitHub, including testing SSH keys.

Setting Up the Local Project

First, I needed a place for my project to live. I opened my bash terminal, navigated to my Desktop using he cdcommand, and created the main project folder along with a sub-folder for the data named Data.

cd Desktop
mkdir -p Kenya_Hospital_Health_Records_Project/Data
cd Kenya_Hospital_Health_Records_Project

With the directories created, I copied and pasted my Kenya_Hospital_Health_Records_Project.csv data set we were given in class into the Data folder.

Writing the README via Terminal

Instead of opening a text editor, I decided to build out my README.md right from the command line using echo command. The >operator adds new text the file, while >> adds text to the already creaed line.

echo "# KENYA HEALTH RECORDS ANALYSIS" > README.md
echo "## Project Overview" >> README.md
echo "This project analyses health records of a hospital" >> README.md

I also added a quick list of tools and challenges using the same method and used the cat README.md command to print the contents of the file directly in the terminal to confirm that everything looked right.

Initializing and Staging

Now it was time to turned this folder into a tracked Git repository.

git init

Running git status showed that my Data/ folder and README.md were untracked. To stage them for my first commit, I used add command tell Git to add everything in the current directory:

git add .

Committing the Code

With the files staged, I wrote a commit message to remind myself in future what the code was about.

git commit -m "Kenye Hospital Data Project"

Connecting to GitHub and Testing SSH

At this point, I needed to link my local repo to the empty GitHub repository I had just created.

git remote add origin https://github.com/dev-elvismuoka/Git-Commands-Class.git

I confirmed that the SSH for secure authentication was working before trying to push:

ssh -T git@github.com

The response was: Hi dev-elvismuoka! You've successfully authenticated, but GitHub does not provide shell access.

The Final Push

Finally, it was time for me to push my local main branch to the origin remote on GitHub. The -u command to make sure that for future updates, I can just type git push without typing out the branch name every time.

git push -u origin main

And just like that, the project was live.