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(usingSUMXfor row-by-row iteration) -
Total Costs(Base Cost + Logistics + Delivery) -
Gross Profit&Gross Profit Margin(usingDIVIDEto 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.
Top comments (0)