From Raw Data to Business Decisions: Cleaning and Modeling JCars Logistics Sales Data in Power BI

In today's data-driven world, making sense of raw data is crucial for effective business decisions. This topic is especially relevant now as businesses increasingly rely on data analytics to guide their strategies.

Understanding the Dataset

The dataset we are discussing comes from JCars Logistics, a company that imports and sells vehicles across Kenya. It consists of 276 rows and 32 columns, with each row representing a vehicle sales order. This includes details about the customer, the vehicle, and the transaction date, among other things.

What Was Actually Wrong With the Data

Upon initial inspection, the dataset revealed several issues:

• Order ID: Different formats and duplicates.
• Date Fields: Mixed formats, including text and Excel serial numbers.
• Categorical Columns: Inconsistent capitalization, abbreviations, and spelling errors.
• Numeric Fields: Text mixed with numbers and impossible values.
• Currency Fields: Various formats including KSh, KES, and USD.

These issues made the data unreliable for analysis.

Cleaning the Data

Step 1: Load the Data

First, I loaded the raw dataset into Power Query as a staging query called Sales_Raw. I then duplicated it into a working query named Sales to keep the original data safe.

Step 2: Fix the Order IDs

For the Order ID, I standardized the formats and resolved any duplicates. This was crucial because Order ID needs to be unique for each transaction.

Step 3: Clean the Dates

Next, I focused on cleaning the date fields. This involved:

• Converting Excel serial numbers to readable dates.
• Standardizing the formats to a single style.
• Handling invalid dates, such as impossible dates like 2026-13-04.

Step 4: Standardize Categorical Data

For categorical columns, I created a mapping formula to correct inconsistencies. For example, I merged different spellings and abbreviations into a single standard term. This ensured that similar entries were grouped correctly.

Step 5: Normalize Numeric Fields

I converted text representations of numbers into actual numeric values. This included handling entries like "two" and "3 cars" for the Units Sold field.

Step 6: Standardize Currency Formats

All monetary values were converted to a single currency format (KES). I established a logic to handle various formats, ensuring that comparisons and calculations could be accurately performed.

Data Modeling

After cleaning the data, I built a star schema. This involved creating a central fact table for sales and related dimension tables for customers, vehicles, and transactions. This structure helps in efficient data analysis.

DAX Measures

I then created several DAX (Data Analysis Expressions) measures to calculate key metrics:

• Total Revenue
• Total Units Sold
• Gross Profit These measures provide reliable insights into the business performance.

The Dashboard So Far

With the cleaned data and DAX measures in place, I developed an initial dashboard in Power BI. Here are some key findings:

• Total revenue reached 1.31 billion KES from 452 units sold.
• Gross profit stood at approximately 430.37 million KES.
• SUVs accounted for 59% of total sales revenue, highlighting a market trend.
• Toyota led in sales, significantly outperforming other brands.

What's Still Left

While the cleaning and initial dashboard are complete, further work is needed. This includes adding more interactivity, detailed report pages, and additional DAX measures to answer specific business questions.

Conclusion

Transforming raw data into a clean and usable format is essential for making informed business decisions. The process involves careful cleaning, modeling, and visualization to ensure that the data tells a clear story.

Merits

• Improved data accuracy and reliability.
• Enhanced decision-making capabilities through clear insights.
• Efficient data analysis using a structured model.

Demerits

• Time-consuming process to clean and standardize data.
• Requires familiarity with tools like Power BI and DAX.
• Potential for oversight if not carefully managed.

Caution

This article is meant for educational purposes. Any placeholder values must be replaced with actual data, and readers should verify the information against the original source before relying on it.

Frequently asked questions

• What is Power BI? — Power BI is a business analytics tool that helps visualize data and share insights across an organization.
• Why is data cleaning important? — Data cleaning ensures accuracy and reliability in analysis, leading to better business decisions.
• What are DAX measures? — DAX measures are calculations used in Power BI to analyze data dynamically.
• How can I standardize data formats? — You can standardize data formats by using mapping formulas and functions in Power Query.
• What is a star schema? — A star schema is a type of database structure that organizes data into fact and dimension tables for efficient querying.
• What challenges might I face in data cleaning? — Common challenges include inconsistent formats, duplicates, and missing values.

Tags

#data #powerbi #analytics #datacleaning #businessintelligence #DAX #datamodeling #dashboard #dataanalysis #datascience