DEV Community: Alex Majale

From Messy Transactions to Business Insights: My JCars Power BI Project

Alex Majale — Tue, 29 Sep 2026 13:51:48 +0000

When I started working on the JCars dataset, my first instinct was to think about the dashboard.

Which KPIs should I create?
Which visualizations should I use?
How should the report look?

But I quickly realised that the dashboard was not the difficult part.

The difficult part was understanding whether the data underneath it could actually be trusted.

That changed how I approached the entire project.

The Dataset

The JCars dataset contained 276 transaction records and 46 columns, covering areas such as customers, vehicles, locations, sales, payments, delivery and costs.

My goal was to turn the raw transactional data into a Power BI report that could help answer questions around:

Sales performance
Revenue and profitability
Vehicle performance
Customer and sales channels
Delivery performance
Operational costs
Returns and cancellations

But before answering those questions, I needed to understand what each row actually represented.

Understanding the grain

I established that one row represented one transaction/order record.

That sounds straightforward, but it became important later when I started investigating duplicate Order IDs.

The First Problem: Order IDs Were Not Always Unique

One of the first things I noticed was that some Order IDs appeared more than once.

The duplicated IDs included:

ord1020
CAR1086
ord1174

At first, deleting duplicates might seem like the obvious solution.

But I decided to investigate them instead.

The duplicate records contained differences in other attributes such as customer information, location and customer type.

That raised an important question:

Were these actually duplicate records, or were they separate transaction records sharing the same source Order ID?

Without a separate transaction identifier, I could not confidently assume that the records were duplicates.

So instead of deleting them, I retained the records and created a unique Transaction Key for each fact-row.

The original Order ID was retained as a business/source reference.

This was one of the biggest lessons from the project:

A duplicate value is not automatically a duplicate record.

Sometimes the right response to a data-quality problem is not to delete data, but to investigate what the data is actually telling you.

Investigating Before Cleaning

I used Excel extensively during the investigation stage.

This allowed me to look at the dataset column by column and identify patterns before transforming anything.

I found inconsistencies across several fields, including:

Customer Type
Region
County
City
Branch
Lead Source
Car Make
Fuel Type
Transmission
Vehicle Year
Discount
Prices and costs
Delivery dates
Delivery status

There were also values such as blanks, N/A, NULL, inconsistent capitalisation, spelling variations and values that required further investigation.

For example, vehicle makes contained variations such as Toyota, TOYOTA and other misspelled variants.

The important distinction for me was between standardising a genuine variation and assuming two different values represented the same real-world entity.

That distinction became particularly important for fields such as sales representatives, where names like Faith and Faith Achieng appeared.

I did not automatically merge these because the dataset did not provide enough evidence to prove they represented the same person.

Cleaning and Transformation

After investigating the raw data, I moved into Power Query.

The goal was not simply to make the dataset look cleaner.

The goal was to make the data more consistent, usable and analytically reliable while preserving the underlying transaction records.

The cleaning process included:

Standardising categorical values
Correcting inconsistent text formats
Handling missing and placeholder values
Converting columns to appropriate data types
Validating numerical fields
Investigating date anomalies
Creating calculated fields required for analysis
Checking the results after transformation

After the cleaning process, the final fact table retained all 276 records, with zero technical Power Query errors.

Dates Needed Special Attention

Dates were one of the more complicated areas of the dataset.

The source contained a mixture of valid dates, blanks, placeholder values, Excel serial numbers and invalid dates.

I also found delivery records where the calculated delivery period was negative.

Four examples were:

LCL-1080
CAR1219
ord1229
LCL1236

Instead of silently removing these records, I treated them as data-quality considerations that needed to remain visible during analysis.

That approach matters because cleaning data does not mean pretending that problematic records never existed.

Building the Data Model

Once the data was prepared, I moved from cleaning into modelling.

Rather than keeping everything in one large table, I created a star-schema structure around a central Fact Sales table.

The model included dimensions for:

Customer
Vehicle
Location
Sales
Payment
Delivery
Date

The fact table contained the transactional measures and keys needed for analysis.

I also created a dedicated Date table covering 1 January 2025 to 15 July 2026.

The Order Date relationship was active, while Delivery Date was kept inactive so that delivery-related analysis could use it when specifically required.

This was another point where the project moved beyond simply creating visuals.

The model structure determines how reliably those visuals answer questions.

Adding Business Measures with DAX

With the model in place, I created measures for the main business questions.

Some of the measures included:

Total Revenue
Total Units Sold
Total Cost
Total Profit
Profit Margin
Average Delivery Days
Average Discount
Total Delivery Fees
Total Logistics Cost
Revenue Per Unit
Total Transactions
Average Units per Transaction
Profit per Transaction

One example was Total Cost, calculated from units sold and unit cost rather than simply summing a pre-existing total.

This allowed the report to analyse profitability at a more meaningful level.

Turning the Model Into a Report

The final Power BI report was organised into three pages.

1. Executive Overview

This page focused on the overall performance of the business.

The main KPIs included:

$1.273B Total Revenue
$455.46M Total Profit
36% Profit Margin
452 Units Sold
276 Transactions
16.76 Average Delivery Days

The purpose was to give a decision-maker a quick view of the overall situation before going deeper.

2. Sales & Profitability

This page explored sales volume, revenue and profitability across dimensions such as vehicle make, location and lead source.

One example from the analysis was Toyota, which generated approximately $540.8M in revenue from 137 units.

At the same time, not every high-revenue category performed equally from a profitability perspective.

For example, BMW generated approximately $50.9M in revenue but had a negative margin of around 2% in this dataset.

That creates a business question rather than simply being a number on a chart:

What is driving the negative profitability?

Potential areas for further investigation would include acquisition cost, selling price, discounts and the underlying transaction records.

3. Delivery & Operations

The third page focused on delivery performance and operational costs.

The dataset contained several delivery statuses, including:

Delivered
In Transit
Cancelled
At Yard
Delayed
Held

The report allowed delivery performance and delivery-related costs to be examined alongside those statuses.

For example, records marked as Held had an average delivery period of approximately 25 days, compared with approximately 15.4 days for Delivered records.

Again, the dashboard does not automatically explain why this happens.

It provides a starting point for asking the next business question.

What I Learned

The biggest lesson from this project was that Power BI is not just about building dashboards.

The dashboard is the visible part.

A large part of the actual analytical work happens before the first visual is created.

I learned to ask questions such as:

What does one row represent?

Can I trust this identifier to be unique?

Is this really a duplicate, or just a repeated value?

Should these two values actually be merged?

What does this missing value mean?

Is this date valid?

Does this relationship make business sense?

What assumptions am I making?

Those questions changed the way I approached the project.

Final Takeaway

I went from looking at a 46-column dataset and asking “What visual should I build?” to asking:

“What does this data actually allow me to conclude?”

That shift has probably been the most valuable part of the project.

Power BI helped me turn the data into something visual and interactive.

But the real work was learning how to question the data before trusting the numbers.

And that is the part of data analytics I want to keep developing.

Github: [(https://github.com/majalealex-ux/JCars-Sales-Performance-Analysis/tree/main)]

Understanding Data Modelling, Relationships and Joins in Power BI

Alex Majale — Mon, 14 Sep 2026 12:07:59 +0000

Introduction

Data does not always come in a structure that is ready for analysis.
For example, sales information may be stored separately from customer details, product information, dates and locations.Before creating reports, visuals or doing calculations in Power BI, it is important to understand how these different tables should be organised and connected.

Data Modeling

Data modelling is the process of organising data into a structure that makes it easier to analyse and report on.
A well-designed data model helps Power BI understand how information from different tables is connected, allowing filters, calculations and reports to work correctly. The way a model is designed can also affect the performance, scalability and maintainability of a Power BI report.

Flat Table

A flat table is a simple way of organising data where most or all the information is stored in one table.
This is common in spreadsheet tools such as Microsoft Excel, where a dataset may contain multiple types of information in the same table.
For example, in an e-commerce business, one table could contain information such as the Order ID, date, customer name, customer email, address, product name, product category, unit price, quantity and total sales amount. Each row may represent a transaction or an individual product within a transaction.
While this structure can be easy to understand and work with, problems can start to appear as the data grows.
Customer and product information may be repeated across many rows. For example, a customer who has placed several orders may have their name, email and address repeated for every transaction.
This repetition increases the size of the dataset and can make it more difficult to maintain.
If customer or product information needs to be updated, the same change may need to be made in multiple records.
As the dataset becomes larger, a flat table can also become less efficient and more difficult to manage compared to a properly structured data model.

Order Id	Date	Customer name	Customer email	Address	Product name	Product Category	Unit Price	Quantity	Total Sales Amount
S001	25/08/2005	Joseph Kamau	josephk@email.com	Nairobi	Mouse	Electronics	Kes 250	10	Kes 2500
S002	26/08/2005	Mitchell Faraja	mitchellf@email.com	Mombasa	Laptop	Electronics	Kes 20000	2	Kes 40000
S003	27/08/2005	Joseph Kamau	josephk@email.com	Kisumu	Keyboard	Electronics	Kes 500	5	2500

Advantages

Easy to understand initially
Simple for very small datasets
No relationships required
Quick to create

Disadvantages

Repeated data
Larger storage requirements
Difficult to maintain
Less scalable
Can create unnecessary redundancy

The Limitations of a Flat Table

Consider an e-commerce dataset where sales transactions, customer information and product details are all stored in one large worksheet.
If a customer named Joseph Kamau buys ten different products, his name, email address and address may be repeated across all ten records.

This creates duplication of information. It can also create problems when information needs to be updated. For example, if Joseph changes his address and only some of his records are updated, the dataset wil contain different addresses for the same customer. This can affect the accuracy and consistency of the data.

As the dataset grows, storing everything in one wide table can also make analysis more difficult and less efficient. The same customer and product information may be repeated thousands or even millions of times. Calculations such as counting unique customers or analysing average sales per customer may therefore require Power BI to process a large amount of repeated information.

One way to solve this is by separating the data into different tables based on the type of information being stored. Instead of storing Joseph's details in every sales record, his information can be stored once in a Customers table. The Sales table would then contain a CustomerID that connects each sale to the correct customer.

The same approach can be used for products. Product information can be stored in a separate Products table, while the Sales table contains the ProductID, quantity purchased and transaction details. This reduces unnecessary repetition and creates a more organised structure where customer and product information can be managed separately from sales transactions.

Customers Table

CustomerID	CustomerName	Email	Address
C001	Joseph Kamau	josephk@email.com	Nairobi
C002	Mitchell Faraja	mitchellf@email.com	Mombasa

Products Table

ProductID	ProductName	Category
P101	Laptop	Electronics
P102	Mouse	Electronics
P103	Keyboard	Electronics

Sales Table

OrderID	Date	CustomerID	ProductID	Quantity	Unit Price
S001	25/08/2005	C001	P102	10	250
S002	26/08/2005	C002	P101	2	20000
S003	27/08/2005	C001	P103	5	500

Once the data has been separated into different tables, the next step is to understand the role of each table. Not every table in a data model serves the same purpose. In the example, the Customers table contains information about customers, the Products table contains information about products, while the Sales table records the actual transactions that take place.

This leads to two important types of tables used in analytical data models: fact tables and dimension tables.

A fact table records business events or transactions that the organisation wants to analyse. In the example, the Sales table can be treated as the fact table because each row represents a sale. It contains information such as the transaction date, CustomerID, ProductID, quantity and unit price.

For example:

OrderID	Date	CustomerID	ProductID	Quantity	Unit Price
S001	25/08/2005	C001	P102	10	250
S002	26/08/2005	C002	P101	2	20000
S003	27/08/2005	C001	P103	5	500

The Customers and Products tables, on the other hand, provide descriptive information about the entities involved in those sales. These are known as dimension tables.

The Customers table contain:

CustomerID	CustomerName	Email	Address
C001	Joseph Kamau	josephk@email.com	Nairobi
C002	Mitchell Faraja	mitchellf@email.com	Mombasa

The Products table could contain:

ProductID	ProductName	Category
P101	Laptop	Electronics
P102	Mouse	Electronics
P103	Keyboard	Electronics

The main difference is that the fact table tells us what happened, while the dimension tables provide the context needed to understand and analyse what happened. For example, FactSales can tell us that a sale of KES 2500 occurred, while DimCustomer tells us who made the purchase and DimProduct tells us what was purchased.

This separation also helps reduce unnecessary duplication. Instead of storing Joseph's name, email and location in every sales record, the Sales table only needs to store her CustomerID. The same applies to products, where the Sales table stores ProductID instead of repeating the product name and category.

The tables can then be connected using keys. CustomerID is the primary key in DimCustomer and acts as a foreign key in FactSales. Similarly, ProductID is the primary key in DimProduct and a foreign key in FactSales.

Grain

Grain describes the level of detail represented by each row in a fact table. In simple terms, it answers the question: what exactly does one row represent?

For example, in an e-commerce FactSales table, the grain could be defined as one row representing one product sold as part of a sales transaction.

Using this grain, a single order containing three different products would result in three rows in the fact table:

Order O001

Laptop      → 20000
Mouse       → 250
Keyboard    → 500

Each row represents a specific product within that transaction, allowing measures such as quantity sold and sales amount to be analysed at the product, customer or transaction level.

Defining the grain before building a fact table is important because it ensures that the data is stored at a consistent level of detail. It also helps prevent incorrect calculations, such as counting transactions multiple times or combining data that is recorded at different levels.

The grain does not have to be the same for every fact table. For example, one fact table could record individual sales transactions, while another could record daily inventory levels. What matters is that the grain of each fact table is clearly defined and consistently maintained.

Primary Keys and Foreign Keys

After separating the data into different tables, the next question is how those tables will be connected. This is where primary keys and foreign keys become important.

A primary key is a column that uniquely identifies each record in a table. For example, in a DimCustomer table, CustomerID can be used as the primary key because each customer should have their own unique CustomerID.

For example:

CustomerID	CustomerName	Email
C001	Joseph Kamau	josephk@email.com
C002	Mitchell Faraja	mitchellf@email.com

In the example, CustomerID is unique for every customer. The same CustomerID should not identify two different customers in the dimension table.

A foreign key is a column in another table that refers to the primary key. In the FactSales table, CustomerID acts as a foreign key because it connects each sale to the customer who made the purchase.

For example:

SalesID	CustomerID	ProductID	SalesAmount
S001	C001	P102	250
S002	C002	P101	20000
S003	C001	P103	500

Here, C001 appears several times because Joseph has made multiple purchases. This is different from the DimCustomer table, where C001 appears only once.

This creates an important pattern in Power BI: the primary key is normally found on the one side of a relationship, while the foreign key appears on the many side. In this example, one customer can have many sales.

DimCustomer                    FactSales

CustomerID                     CustomerID
   C001  ─────────────────────── C001
   C002  ─────────────────────── C001
   C003                         C002
                                C001

The keys therefore provide the link between the descriptive information in the dimension table and the transactions stored in the fact table.

Cardinality

After identifying the keys that connect tables, the next thing to understand is cardinality. Cardinality describes how many records in one table can be related to records in another table.

There are three main types of cardinality that are commonly used in Power BI: one-to-many, one-to-one and many-to-many.

One-to-Many (1:*)

One-to-many is the most common relationship in a Power BI data model. It means that one record in one table can be related to many records in another table.

For example, one customer can make multiple purchases. In this case, DimCustomer is on the one side and FactSales is on the many side.

DimCustomer                     FactSales

CustomerKey                     CustomerKey
C001  ────────────────────────▶  C001
C002  ────────────────────────▶  C001
C003  ────────────────────────▶  C002
                                C001

The CustomerKey appears only once for each customer in DimCustomer, but the same CustomerKey can appear many times in FactSales as the customer makes different purchases.

Filters can also flow from the dimension table to the fact table. For example, if a report user selects Nairobi from DimCustomer[Address], Power BI can use the relationship to filter the relevant records in FactSales.

One-to-One (1:1)

A one-to-one relationship means that each record in one table is related to only one record in another table.

For example, an organisation might have an Employee table and a separate EmployeeConfidentialInformation table. Each employee would have one corresponding record in each table.

Employee                    EmployeeConfidential

EmployeeID                  EmployeeID
E001  ───────────────────▶  E001
E002  ───────────────────▶  E002
E003  ───────────────────▶  E003

Many-to-Many (:)

A many-to-many relationship occurs when multiple records in one table can be related to multiple records in another table.

For example, a student can take several courses, while each course can have many students.

Students                     Courses

Student A ────────────────▶  Course 1
Student A ────────────────▶  Course 2
Student B ────────────────▶  Course 1
Student B ────────────────▶  Course 3

Active and Inactive Relationships

Power BI relationships can be either active or inactive. An active relationship is the relationship Power BI uses by default when filters and calculations are applied between two related tables. An inactive relationship exists in the model but is not used automatically.

A common example occurs when a fact table contains more than one date that needs to be analysed. For example, a FactSales table may contain both OrderDate and ShipDate, while the model has a DimDate table.

                 ┌──────────────┐
                 │   DimDate    │
                 └──────┬───────┘
                        │
                 Active │
                        ▼
                  OrderDate
                 ┌──────────────┐
                 │  FactSales   │
                 └──────────────┘
                        ▲
              Inactive │
                        │
                   ShipDate

The OrderDate relationship could be active, meaning it is used automatically when a date filter is applied. The relationship between DimDate and ShipDate can remain inactive because both relationships cannot normally be active between the same two tables at the same time.

This does not mean the inactive relationship is useless. It can be used when a particular analysis requires it. For example, a report may normally analyse sales based on the date an order was placed, but a separate measure may need to analyse sales based on the date the order was shipped.

DAX provides the USERELATIONSHIP() function to temporarily use an inactive relationship within a calculation. This allows the same DimDate table to support different date-based analyses without creating unnecessary duplicate tables.

For example, the model could use the active OrderDate relationship for normal sales analysis, while a separate measure uses the inactive ShipDate relationship when analysing shipped orders.

Active and inactive relationships are therefore useful when a fact table contains multiple fields that could relate to the same dimension.

The Star Schema

A star schema is a data model where a central fact table is connected directly to several dimension tables. The fact table sits at the centre, while the dimension tables surround it, creating a structure that looks like a star.

For example, in an e-commerce model, the FactSales table could contain information about sales transactions such as SalesAmount, Quantity, CustomerKey, ProductKey and DateKey. It can then be connected directly to dimension tables such as DimCustomer, DimProduct and DimDate.

                    ┌─────────────────┐
                    │   DimCustomer   │
                    └────────┬────────┘
                             │ 1
                             │
                             │ *
┌─────────────────┐     ┌────▼────────────┐     ┌─────────────────┐
│     DimDate     │ 1 * │    FactSales    │ * 1 │   DimProduct    │
└─────────────────┘─────►│                 │◄────└─────────────────┘
                         └─────────────────┘

The relationships are normally one-to-many (1:*), with the dimension table on the one side and the fact table on the many side. For example, one customer can have many sales transactions, so DimCustomer is on the one side while FactSales is on the many side.

Importance of a Star Schema

Simpler analysis:
Dimensions provide the descriptive information used to filter and group data, while the fact table contains the numbers and events being analysed. For example, a user can select a product category from DimProduct and analyse the corresponding sales from FactSales.

Better report performance:
A well-structured star schema reduces unnecessary relationships and keeps the model relatively simple. This can help Power BI process queries and DAX calculations more efficiently, especially as the amount of data increases.

Easier to use:
Report developers can easily identify where different types of information belong. Customer attributes are in DimCustomer, product attributes are in DimProduct, dates are in DimDate, and transaction measures are in FactSales.

Easier to maintain:
Because each table has a clear purpose, changes to the model are easier to manage. New descriptive attributes can be added to the appropriate dimension without unnecessarily changing the structure of the fact table.

Works well with DAX and filtering:
The clear separation between dimensions and facts makes it easier to create measures and understand how filters affect the results. For example, selecting a specific product category can filter the related sales records through the relationship between DimProduct and FactSales.

The Snowflake Schema

A snowflake schema is a variation of the star schema where dimension tables are further divided into smaller related tables. This process is known as normalization.

For example, instead of keeping the product category and sub-category information directly inside DimProduct, they can be separated into their own tables. DimProduct would connect to DimSubCategory, which would then connect to DimCategory.

┌─────────────────┐
│   DimCategory   │
└────────┬────────┘
         │ 1
         │
         │ *
┌────────▼────────┐
│ DimSubCategory  │
└────────┬────────┘
         │ 1
         │
         │ *
┌────────▼────────┐
│   DimProduct    │
└────────┬────────┘
         │ 1
         │
         │ *
┌────────▼────────┐
│    FactSales    │
└─────────────────┘

Using the e-commerce example, DimProduct might contain ProductID, ProductName and SubCategoryID. DimSubCategory would contain SubCategoryID, SubCategoryName and CategoryID, while DimCategory would contain CategoryID and CategoryName.

This structure reduces repeated descriptive information. For example, instead of storing the same category information across many product records, the category can be stored once in DimCategory.

However, this also means that the model contains more tables and relationships. If a report needs to analyse sales by category, the filter may need to move through several tables:

DimCategory → DimSubCategory → DimProduct → FactSales

Advantages of a Snowflake Schema

Reduces redundancy: Related information can be stored once instead of being repeated across a dimension.
More structured dimensions: Large dimensions can be divided into smaller logical tables.
Can be useful for complex data: Some organisations may already have highly normalized source systems that naturally produce this structure.

Limitations of a Snowflake Schema

More relationships: Splitting dimensions creates additional relationships that Power BI has to manage.
More complex reporting: Users may need to work across several tables to access related attributes.
More complicated filtering: Filters may have to pass through multiple tables before reaching the fact table.
More difficult to maintain: As the number of tables and relationships increases, understanding and troubleshooting the model can become harder.

Cross-Filter Direction

After establishing a relationship between tables, the next thing to consider is how filters should move between those tables. In Power BI, this is controlled by the cross-filter direction of a relationship.

There are two main options: Single direction and Both directions (bidirectional).

Single Direction

Single-direction filtering is the default and is the most common approach in a star schema. The filter normally flows from the one side of the relationship to the many side.

For example:

DimCustomer  ───────────────►  FactSales
     1                              *

If a user selects Nairobi from DimCustomer, Power BI can use that filter to show only the sales associated with customers from Nairobi in FactSales.

However, the filter does not automatically travel in the opposite direction. Filtering FactSales does not change the available values in DimCustomer.

This direction is usually preferred because it makes the model easier to understand and keeps filter behaviour predictable.

Both Directions

With Both selected, filters can move in both directions between the related tables.

DimCustomer  ◄──────────────►  FactSales
     1                              *

For example, filtering FactSales could also affect the values available in DimCustomer.

Power Query Joins

Power Query provides tools for preparing and transforming data before it is loaded into the Power BI data model. One of these tools is Merge Queries, which allows data from two tables to be combined based on a common column.

For example, suppose an organisation has a Sales table containing CustomerID, but the customer's name and location are stored in a separate Customers table.

Customers
┌────────────┬──────────────┬───────────┐
│ CustomerID │ CustomerName │ City      │
├────────────┼──────────────┼───────────┤
│ C001 │ Joseph Kamau │ Nairobi│
│ C002│ Mitchell Faraja │ Mombasa│ 
│ C003 │ Joseph Kamau      │ Kisumu    │
└────────────┴──────────────┴───────────┘

Sales
┌─────────┬────────────┬─────────────┐
│ SalesID │ CustomerID │ SalesAmount │
├─────────┼────────────┼─────────────┤
│ S001    │ C001       │ 2500         │
│ S002    │ C002       │ 40000         │
│ S003    │ C001       │ 2500         │
└─────────┴────────────┴─────────────┘

The two tables have a common CustomerID column. In Power Query, this column can be used as the matching column when merging the tables.

After the merge, information from the Customers table can be brought into the Sales table:

Sales + Customers
        ↓
      Merge
        ↓
┌─────────┬────────────┬──────────────┬───────────┬─────────────┐
│ SalesID │ CustomerID │ CustomerName │ City      │ SalesAmount │
├─────────┼────────────┼──────────────┼───────────┼─────────────┤
│ S001    │ C001       │ Joseph Kamau       │ Nairobi   │ 2500        │
│ S002    │ C002       │ Mitchell Faraja        │ Mombasa   │ 40000         │
│ S003    │ C001       │ Joseph Kamau       │ Kisumu   │ 2500          │
└─────────┴────────────┴──────────────┴───────────┴─────────────┘

The important point is that Merge Queries physically brings data from one table into another during the Power Query transformation stage. It is therefore different from creating a relationship in the Power BI model.

When performing a merge, Power Query requires a column or columns that can be used to match records between the two tables. In this example, CustomerID is the matching column.

Power Query provides several types of joins that determine which records from the two tables are retained in the merged result. These include Left Outer, Right Outer, Full Outer, Inner, Left Anti and Right Anti joins.

Understanding what each join keeps is important because choosing the wrong join can result in missing records or unexpected data in the final table.

Left Outer Join

A Left Outer Join keeps all the records from the first, or left, table and brings in matching records from the second, or right, table. If a record in the left table does not have a match in the right table, the record is still retained, but the columns brought from the right table will contain blank or null values.

For example, suppose the Sales table is the left table and Customers is the right table.

Sales                         Customers
┌─────────┬────────────┐      ┌────────────┬──────────────┐
│ SalesID │ CustomerID │      │ CustomerID │ CustomerName │
├─────────┼────────────┤      ├────────────┼──────────────┤
│ S001    │ C001       │      │ C001       │ Joseph Kamau       │
│ S002    │ C002       │      │ C002       │ Mitchell Faraja       │
│ S003    │ C003       │      │ C003       │ Joseph Kamau      │
│ S004    │ C004       │      └────────────┴──────────────┘
└─────────┴────────────┘

If we merge these tables using CustomerID and select Left Outer, every record from Sales is retained.

Result

┌─────────┬────────────┬──────────────┐
│ SalesID │ CustomerID │ CustomerName │
├─────────┼────────────┼──────────────┤
│ S001    │ C001       │ Joseph Kamau       │
│ S002    │ C002       │ Mitchell Faraja        │
│ S003    │ C003       │ Joseph Kamau       │
│ S004    │ C004       │ null         │
└─────────┴────────────┴──────────────┘

S004 is retained even though C004 does not exist in the Customers table. Because there is no matching customer record, CustomerName is returned as null.

This makes a Left Outer Join useful when the left table is the main dataset that must be preserved, while information from another table is being added where a match exists.

For example, an analyst could use a Left Outer Join to keep every sales transaction while adding customer names, locations or other customer attributes from a separate table.

In simple terms:

Left Outer = keep everything from the left table + matching records from the right table.

Right Outer Join

A Right Outer Join keeps all records from the second, or right, table and brings in matching records from the first, or left, table. If a record exists in the right table but has no matching record in the left table, it is still retained, while the columns from the left table contain blank or null values.

Using the same Sales and Customers tables:

Sales                         Customers
┌─────────┬────────────┐      ┌────────────┬──────────────┐
│ SalesID │ CustomerID │      │ CustomerID │ CustomerName │
├─────────┼────────────┤      ├────────────┼──────────────┤
│ S001    │ C001       │      │ C001       │ Joseph Kamau       │
│ S002    │ C002       │      │ C002       │ Mitchell Faraja       │
│ S003    │ C003       │      │ C003       │ Joseph Kamau      │
└─────────┴────────────┘      │ C004       │ David        │
                              └────────────┴──────────────┘

If we merge the tables using CustomerID and select Right Outer, every record from Customers is retained.

Result

┌─────────┬────────────┬──────────────┐
│ SalesID │ CustomerID │ CustomerName │
├─────────┼────────────┼──────────────┤
│ S001    │ C001       │ Joseph Kamau       │
│ S002    │ C002       │ Mitchell Faraja       │
│ S003    │ C003       │ Joseph Kamau       │
│ null    │ C004       │ David        │
└─────────┴────────────┴──────────────┘

C004 is retained because it exists in the right-hand Customers table, even though there is no corresponding sales record. Since there is no matching record in Sales, the SalesID value is null.

A Right Outer Join can therefore be useful when the right table is the table whose records must all be preserved.

In practice, the same result could often be achieved by switching the order of the tables and using a Left Outer Join. Therefore, the important concept is not the word "right" itself, but understanding which table you want to preserve.

In simple terms:

Right Outer = keep everything from the right table + matching records from the left table.

Full Outer Join

A Full Outer Join keeps all records from both the left and right tables. Where a matching value exists in both tables, the records are combined. Where a record exists in only one table, it is still retained and the columns from the other table contain blank or null values.

Using the same Sales and Customers tables:

Sales                         Customers
┌─────────┬────────────┐      ┌────────────┬──────────────┐
│ SalesID │ CustomerID │      │ CustomerID │ CustomerName │
├─────────┼────────────┤      ├────────────┼──────────────┤
│ S001    │ C001       │      │ C001       │ Joseph Kamau       │
│ S002    │ C002       │      │ C002       │ Mitchell Faraja       │
│ S003    │ C003       │      │ C003       │ Joseph Kamau       │
│ S004    │ C005       │      │ C004       │ David        │
└─────────┴────────────┘      └────────────┴──────────────┘

If we merge the tables using CustomerID and select Full Outer, every record from both tables is retained.

Result

┌─────────┬────────────┬──────────────┐
│ SalesID │ CustomerID │ CustomerName │
├─────────┼────────────┼──────────────┤
│ S001    │ C001       │ Joseph Kamau      │
│ S002    │ C002       │ Mitchell Faraja        │
│ S003    │ C003       │ Joseph Kamau       │
│ S004    │ C005       │ null         │
│ null    │ C004       │ David        │
└─────────┴────────────┴──────────────┘

Here, C001, C002 and C003 exist in both tables, so their information is combined. C005 exists only in Sales, while C004 exists only in Customers. Both are still retained because a Full Outer Join does not discard unmatched records.

A Full Outer Join can therefore be useful when the goal is to identify all records across two datasets, including records that do not have a match.

For example, an analyst could use it to compare two datasets and identify customers that appear in one system but not the other.

In simple terms:

Full Outer = keep everything from both tables, whether there is a match or not.

Inner Join

An Inner Join keeps only the records that have a matching value in both tables. Any record that does not have a match is excluded from the merged result.

Using the same Sales and Customers tables:

Sales                         Customers
┌─────────┬────────────┐      ┌────────────┬──────────────┐
│ SalesID │ CustomerID │      │ CustomerID │ CustomerName │
├─────────┼────────────┤      ├────────────┼──────────────┤
│ S001    │ C001       │      │ C001       │ Joseph Kamau       │
│ S002    │ C002       │      │ C002       │ Mitchell Faraja        │
│ S003    │ C003       │      │ C003       │ Joseph Kamau      │
│ S004    │ C005       │      │ C004       │ David        │
└─────────┴────────────┘      └────────────┴──────────────┘

If the tables are merged using CustomerID with an Inner Join, only customers that appear in both tables are retained.

Result

┌─────────┬────────────┬──────────────┐
│ SalesID │ CustomerID │ CustomerName │
├─────────┼────────────┼──────────────┤
│ S001    │ C001       │ Joseph Kamau       │
│ S002    │ C002       │ Mitchell Faraja        │
│ S003    │ C003       │ Joseph Kamau       │
└─────────┴────────────┴──────────────┘

C005 is removed because it exists in Sales but not in Customers. Similarly, C004 is not included because it exists in Customers but has no matching record in Sales.

An Inner Join is useful when the analysis should only include records that exist in both datasets. For example, an analyst may want to analyse only sales transactions that can be matched to a valid customer record.

In simple terms:

Inner Join = keep only the records that match in both tables.

Left Anti Join

A Left Anti Join returns only the records from the left table that do not have a matching record in the right table.

Using the same example:

Sales                         Customers
┌─────────┬────────────┐      ┌────────────┬──────────────┐
│ SalesID │ CustomerID │      │ CustomerID │ CustomerName │
├─────────┼────────────┤      ├────────────┼──────────────┤
│ S001    │ C001       │      │ C001       │ Joseph Kamau      │
│ S002    │ C002       │      │ C002       │ Mitchell Faraja       │
│ S003    │ C003       │      │ C003       │ Joseph Kamau       │
│ S004    │ C005       │      │ C004       │ David        │
└─────────┴────────────┘      └────────────┴──────────────┘

When Sales is the left table, a Left Anti Join checks which CustomerID values in Sales cannot be found in Customers.

Result

┌─────────┬────────────┐
│ SalesID │ CustomerID │
├─────────┼────────────┤
│ S004    │ C005       │
└─────────┴────────────┘

C005 is returned because it exists in Sales but does not exist in Customers.

This type of join is particularly useful for data-quality checks. For example, it can help an analyst identify sales transactions that refer to customer IDs that are missing from the customer master table.

In simple terms:

Left Anti = records in the left table that have NO match in the right table.

Right Anti Join

A Right Anti Join returns only the records from the right table that do not have a matching record in the left table.

Using the same tables, Customers is the right table. The join checks which customers do not appear in the Sales table.

Result

┌────────────┬──────────────┐
│ CustomerID │ CustomerName │
├────────────┼──────────────┤
│ C004       │ David        │
└────────────┴──────────────┘

C004 is returned because it exists in Customers but has no corresponding record in Sales.

A Right Anti Join can therefore be useful for identifying records that exist in one dataset but are missing from another. For example, an analyst could use it to find registered customers who have not yet appeared in a sales dataset.

In simple terms:

Right Anti = records in the right table that have NO match in the left table.

Power Query Joins vs Power BI Relationships

Power Query joins and Power BI relationships can both involve matching columns between tables, but they serve different purposes and happen at different stages of the Power BI workflow.

The main difference is that a Power Query merge combines data, while a Power BI relationship connects tables without physically combining them.

Power Query Joins

A Power Query merge is performed during the data preparation and transformation stage. When two tables are merged, columns from one table can be brought into another based on matching values.

For example, if the Sales table contains CustomerID but does not contain the customer's name, Power Query can merge it with the Customers table using CustomerID and bring CustomerName into the resulting table.

Sales + Customers
       │
       │  Merge using CustomerID
       ▼
Combined table
       │
       ├── SalesID
       ├── CustomerID
       ├── CustomerName
       └── SalesAmount

The result is a physically combined table containing columns from both sources.

Power BI Relationships

A relationship is created after the data has been loaded into the Power BI model. Instead of combining the tables, Power BI keeps them separate and creates a connection between them using related columns.

For example:

DimCustomer                 FactSales
┌──────────────┐            ┌──────────────┐
│ CustomerID   │  1      *  │ CustomerID   │
│ CustomerName │───────────►│ SalesAmount  │
│ City         │            │ Quantity     │
└──────────────┘            └──────────────┘

The tables remain separate, but the relationship allows filters from DimCustomer to affect the sales data in FactSales.

This is one of the main reasons relationships are important in a star schema. Customer information does not need to be copied into every sales transaction. Instead, the model connects the two tables through CustomerID.

Key Differences

Power Query Merge	Power BI Relationship
Happens during data preparation	Happens in the data model
Physically combines columns from tables	Keeps tables separate
Produces a new/expanded query result	Creates a logical connection between tables
Used to transform and prepare data	Used for analysis, filtering and calculations
Can increase the width of a table	Keeps fact and dimension tables separate
Uses join types such as Inner and Left Outer	Uses relationship properties such as cardinality and filter direction

When Should You Use a Merge?

A merge is useful when information from two sources genuinely needs to become part of the same table.

For example, an analyst may merge a product lookup table into a dataset during preparation when the product attributes are needed directly in the resulting table.

However, merging every table into one large dataset is not always a good approach. Excessive merging can create wide tables, increase duplication and make the data model harder to maintain.

When Should You Use a Relationship?

A relationship is generally preferable when the tables represent different types of information that should remain separate.

For example:

DimCustomer ───────► FactSales ◄─────── DimProduct
     1                    *                   1

Here, customer and product information belongs in their respective dimension tables, while sales transactions belong in the fact table. Relationships allow these tables to work together during analysis without physically combining them.

This structure supports the star schema discussed earlier and allows the model to remain organised as more data is added.

Why Not Merge Everything?

It may seem simpler to combine all the data into one large table, but doing so can introduce unnecessary repetition.

For example, if the same customer makes 1,000 purchases, merging all of the customer's descriptive information into every sales record would repeat that information 1,000 times.

Keeping the customer information in DimCustomer and connecting it to FactSales through a relationship avoids this unnecessary duplication.

Therefore, the choice between a merge and a relationship depends on when and why the data needs to be combined:

Use Power Query merges to prepare and transform data. Use Power BI relationships to connect separate tables for analysis.

Recommended Power BI Model

Based on the concepts discussed above, a star schema with one-to-many relationships and single-direction filtering is generally the most suitable model for Power BI reporting and analytical workloads.

A typical e-commerce model could be structured as follows:

                         ┌─────────────────┐
                         │     DimDate     │
                         └────────┬────────┘
                                  │ 1
                                  │
                                  │ *
┌─────────────────┐        ┌─────▼───────────┐        ┌─────────────────┐
│  DimCustomer    │ 1    * │    FactSales    │ *    1 │   DimProduct    │
├─────────────────┤────────►│                 │◄────────├─────────────────┤
│ CustomerKey     │         │ CustomerKey     │         │ ProductKey      │
│ CustomerName    │         │ ProductKey      │         │ ProductName     │
│ City            │         │ DateKey         │         │ Category        │
│ Country         │         │ Quantity        │         │ Brand           │
└─────────────────┘         │ Unit Price     │         └─────────────────┘
                            └─────────────────┘

In this model, FactSales sits at the centre and contains the business transactions and numeric values being analysed. The dimension tables provide the descriptive context used to filter and group those transactions.

Why Use a Star Schema?

Simplicity:
Each dimension connects directly to the fact table, making the model easier to understand and navigate. Report developers can quickly identify where customer, product and date information is stored.

Performance:
A simpler relationship structure reduces unnecessary paths between tables and can allow Power BI to process analytical queries more efficiently. This becomes increasingly important as the amount of data grows.

Simpler DAX:
A clear separation between fact and dimension tables makes it easier to write and understand measures. For example, a measure calculating total sales can operate on the sales values in FactSales, while dimensions provide the context in which those sales are analysed.

Effective filter propagation:
Single-direction relationships allow filters to normally flow from dimensions to the fact table. For example, selecting a product category in DimProduct filters the corresponding records in FactSales.

Reduced unnecessary duplication:
Customer and product attributes are stored in their respective dimension tables rather than being repeated throughout every sales transaction. This keeps the fact table focused on the events and measurements being analysed.

Readability and maintainability:
Each table has a clear purpose. If a new customer attribute is required, it can be added to DimCustomer, while a new sales measure can be added to FactSales. This makes the model easier to maintain as reporting requirements change.

Scalability:
A star schema can accommodate additional dimensions as the reporting requirements grow. For example, a DimLocation or DimSalesperson table can be added and connected directly to FactSales without redesigning the entire model.

Recommended Relationship Design

For this model, the preferred relationship pattern would be one-to-many (1:*), with each dimension on the one side and the fact table on the many side.

The relationships would also normally use single-direction filtering, allowing filters to flow from the dimensions towards the fact table.

DimCustomer ──►
DimProduct  ──► FactSales
DimDate     ──►

This approach provides predictable filter behaviour and avoids introducing unnecessary bidirectional relationships that could create ambiguous filter paths.

Why Not Use a Snowflake Schema?

A snowflake schema can be appropriate in some situations, particularly where dimensions naturally contain several levels of hierarchy. However, for many Power BI reporting models, the additional tables and relationships can make the model more complicated than necessary.

For the e-commerce example used throughout this article, keeping product attributes such as category and brand within DimProduct provides a simpler structure than creating separate tables for each level of the product hierarchy.

Therefore, the recommended model is a star schema with one-to-many relationships and predominantly single-direction filtering. It provides a practical balance between performance, simplicity, usability, scalability and maintainability.

Comparison of Flat Table, Star Schema and Snowflake Schema

The three modelling approaches discussed above can be compared based on their structure, complexity, performance, flexibility and suitability for Power BI reporting.

Aspect	Flat Table	Star Schema	Snowflake Schema
Structure	Most data stored in one table	One central fact table connected directly to dimensions	Fact table connected to dimensions that may be further split into related tables
Data redundancy	High, because descriptive information may be repeated	Lower, because descriptive data is stored in dimensions	Generally lower because dimensions are more normalized
Number of tables	Usually one large table	Fact table + dimension tables	Fact table + multiple related dimension/hierarchy tables
Model complexity	Simple at first, but can become difficult to manage as data grows	Relatively simple and easy to understand	More complex because of additional tables and relationships
Relationships	Few or none within the model	Mainly one-to-many relationships between dimensions and facts	More relationships because dimensions may connect to other dimension tables
Filter propagation	Limited need for relationship-based filtering	Clear and predictable from dimensions to fact	Can involve more relationship paths
DAX and analysis	Can become harder to manage as the table grows	Generally simpler because the model has a clear structure	Can require more complex relationships and calculations
Scalability	Less suitable as data and reporting requirements grow	Highly suitable for growing analytical models	Can scale, but complexity increases with additional normalized tables
Maintainability	Changes may affect a large table	Easier because each table has a clear purpose	More difficult because changes may involve several related tables
Typical use	Small datasets, simple analysis, spreadsheets	Business intelligence and Power BI reporting	Complex or highly normalized data structures
Recommended for Power BI reporting?	Usually not for larger analytical models	Yes generally preferred	Useful in specific scenarios

From the comparison, the star schema provides a strong balance between simplicity, performance, scalability and maintainability. A flat table may be convenient when working with a small dataset, but repeated information and increasing table size can make it less suitable for a larger analytical model.

A snowflake schema can reduce redundancy and may be useful when dealing with complex hierarchies or highly normalized source data. However, it introduces additional tables and relationships that can make the model harder to understand and maintain.

For the e-commerce example used throughout this article, the star schema is therefore the preferred approach because it keeps the fact table focused on transactions while dimensions provide the descriptive information needed for analysis.

Conclusion

Data modelling is an important part of building effective Power BI reports because it determines how data is organised, connected and used for analysis. A well-designed model makes it easier to create reliable calculations, apply filters correctly and maintain the report as the amount of data and reporting requirements increase.

The comparison between flat tables, star schemas and snowflake schemas shows that each approach has its place. A flat table can be useful for simple datasets, while a snowflake schema can be appropriate when dealing with more complex or highly normalized data structures. However, for most Power BI analytical and reporting scenarios, a star schema provides a practical balance between simplicity, performance, scalability and maintainability.

Understanding the difference between fact and dimension tables is also important when building this type of model. Fact tables contain the events and measurements being analysed, while dimension tables provide the descriptive context used to filter and group those events. Defining the grain of the fact table and using appropriate primary and foreign keys helps maintain a consistent and reliable model.

Relationships then connect these tables and allow filters to move through the model. In a typical star schema, one-to-many relationships with single-direction filtering provide a clear and predictable structure. Active and inactive relationships can also be used when different date or analytical perspectives are required.

Power Query joins serve a different purpose. They are useful during the data preparation stage when information from different sources needs to be combined, filtered or compared. Power BI relationships, on the other hand, allow separate tables to work together during analysis without physically combining their data.

Overall, effective Power BI modelling is not simply about creating relationships between tables. It is about designing a structure that reflects the business data clearly and allows that data to be analysed efficiently. For the e-commerce scenario used throughout this article, a star schema with one-to-many relationships and predominantly single-direction filtering provides the most suitable foundation for building a clear, scalable and maintainable Power BI reporting model.

Building an Interactive Excel Dashboard for E-commerce Product Analysis: A Case Study of Jumia Products

Alex Majale — Sun, 06 Sep 2026 12:02:19 +0000

If you've ever wondered what actually happens between "I have a spreadsheet full of messy data" and "I have a dashboard I'd be proud to show someone," this article walks through that whole journey, step by step, using a real example.

1. Project Introduction and Objective

Online stores discount things constantly. But here's a question that's easy to assume the answer to and actually get wrong: Does a bigger discount get you more customer interest?
The goal wasn't just "make some charts." It was to build something a manager could actually open and trust because every number on it either comes from a live formula or is clearly an assumption.

2. Dataset and Business Questions

The raw data is : 116 rows, 6 columns.

Column	What it holds
Product	The product
Current price	The price you'd actually pay
old price	The original price before the discount
Discount	The advertised discount
Review	Number of customer reviews
Ratingd	rating

Business questions:

whether larger discounts are associated with more reviews
whether highly rated products attract stronger engagement
whether price and rating move together
which products perform best based on ratings and reviews
which products may need a different pricing or marketing strategy.

3. Initial Data-Quality Audit

I audited the raw data for problems. Here's what I actually found:

58 rows had no value in Review or Ratingd at all.
58 rows had negative numbers in the Review column. Reviews are a count of people, so a negative count is impossible.
3 rows were exact duplicates.
6 different product names appeared more than once, but with different prices.
1 row had a price range instead of a single value.
Prices were stored as text, with a currency prefix.
Ratings were stored as text.

4. Cleaning and Preparation Decisions

Every cleaning decision went into a documented "data dictionary"

Issue	Rows affected	Decision	Reason
Negative review counts	58 rows	Converted to positive	A review count can't be negative
Exact duplicate rows	3	Removed	They were duplicates
Duplicate product names, different prices	6	Kept as separate rows	interpreted as different types of the same product
One price stored as a range	1	Replaced with the midpoint	A single number was required for any calculation to work
Prices stored as text with currency symbols	113	Converted to numbers	Can't calculate on text
Ratings stored as `"4.5 out of 5"`	58	Extracted the numeric `4.5`	Text can't be calculated

5. Excel Formulas and Enrichment Fields

This is the part that turns "a cleaned spreadsheet" into "a dataset you can actually analyze."
Breaking down what these do:

Rating Status =IF(OR(F2<0,F2>5,ISBLANK(F2)),"Check rating","OK")
Rating must be between 0 and 5. If it isn't (or it's blank), this flags it instead of silently trusting the data.
Calculated Discount =IFERROR((C2-B2)/C2,"")
Instead of trusting the advertised discount, I recalculated it independently from the old and new prices.
Discount Check =IF(OR(D2="",K2=""),"Missing",IF(ABS(D2-K2)>2%,"Check Discount","OK")) compares the advertised discount to the calculated one, and flags anything more than 2 percentage points apart.
Rating Category =IF(F2="","Missing",IF(F2<3,"Poor",IF(F2<=4.5,"Average","Excellent"))) Rating into "Poor" (below 3), "Average" (3–4.5), or "Excellent" (above 4.5), so I can group and count products instead of comparing 113 individual decimals.

I used the same pattern for Discount Category (Low/Medium/High) =IF(D2="","Missing",IF(D2<20%,"Low Discount",IF(D2<=40%,"Medium Discount","High Discount"))) and Price Category (Low/Medium/High) =IF(B2="","Missing",IF(B2<=Price_Q1,"Low Price",IF(B2<=Price_Q3,"Medium Price","High Price"))) , the price Categories are based on the dataset's own quartiles, calculated with:

Price_Q1 = QUARTILE.INC(tblProducts[Current Price], 1)   → KSh 493
Price_Q3 = QUARTILE.INC(tblProducts[Current Price], 3)   → KSh 1,669.50
Review_Q3 = QUARTILE.INC(tblProducts[Review], 3)         → 14 reviews

I stored these as named ranges so every formula in the workbook could reference Price_Q1 instead of a hardcoded number.
Engagement flags =IF(E2="","Missing",IF(E2>=Review_Q3,"Strong Engagement","Below Threshold")) so that i can group them with the dataset review threshold.

Finally, I layered on a few compound flags that combine two conditions at once:

High Discount + Low Rating =IF(OR(D2="",E2=""),"Missing",IF(AND(D2>40%,F2<3),"Flag","")) discount over 40% and rating under 3
High Discount + Low Engagement =IF(OR(D2="",E2=""),"Missing",IF(AND(D2>40%,E2<Review_Q3),"Flag","")) discount over 40% and reviews below the 75th percentile
Many Reviews + Average Rating =IF(AND(E2>=Review_Q3,F2>=3,F2<=4.5),"Flag","") heavily reviewed but only "Average," not "Excellent"

6. PivotTable and Analysis Workflow

With every product tagged and categorized, PivotTables became genuinely simple.

Here's one of the PivotTables:

PivotTable feeding a chart:

I built seven of these PivotTable/PivotChart pairs in total — rating mix, discount mix, price vs. rating, engagement vs. discount, and three "Top 10" rankings (by rating, by reviews, by discount).

Alongside the PivotTables, I ran descriptive statistics and correlation checks directly on the Cleaned Data table using AVERAGE, SUM, MAX/MIN, and CORREL.

7. Dashboard Design and Slicer Connections

Everything comes together on a single dashboard sheet: a title bar, five KPI cards across the top, and nine charts.

8. Key Findings

Here's the "Business Insights" sheet I built to summarize what the data actually showed

In plain language:

The catalog is aggressively discounted. 55.4% of products (62 of 112) are discounted more than 40%.
But bigger discounts don't buy more attention. Products discounted 20–40% average 15.3 reviews each more than the heavily-discounted group's 11.1 average, and clearly more than the lightly-discounted group's 9.5. The correlation between discount size and review count across the whole catalog is essentially flat (r ≈ -0.14).
Rating and popularity are basically unrelated. A product's star rating barely correlates with how many people reviewed it (r ≈ 0.06).
Price has only a weak relationship with rating. Higher-priced products rate slightly better on average (4.08 vs. 3.64 for the cheapest band), but the relationship is too weak to call a real trend (r ≈ 0.11).
One product is a real warning sign: a cordless vacuum cleaner has the most reviews in the entire dataset (69) but only a 2.8-star rating.

9. Business Recommendations

Based on what the data actually supports:

Don't assume "discount deeper" means "sell more." Test the 20–40% discount band deliberately it's where engagement was strongest in this dataset.
Audit high-visibility, low-rating listings first. They're seen by the most people, so any quality or accuracy issue there does the most damage.
Invest in listing quality over price cuts. Since price alone barely predicts rating, better photos, descriptions, and accurate specs may do more for perception than another 5% off.
Chase more reviews on unrated listings, not fewer discounts. Half the catalog (55 of 112 products) has no rating or review at all.

10. Limitations and Lessons Learned

Being upfront about what this analysis can't tell you is just as important as the findings themselves:

A review count isn't a sales figure. Engagement ≠ conversion, and nothing here should be read as a profit or revenue claim.
One number was a judgment call, not a fact: the single price-range row that I replaced with a midpoint.

Cleaning and documentation take longer than the "real" analysis, and that's normal. Most of the time on this workbook went into deciding what a negative review count means, what to do with a price range, and how to label the 55 products with no rating.

11. Links

[https://github.com/majalealex-ux/Jumia-Product-Performance-Dashboard]

Getting Started with Excel for Data Analytics: From Basics to Data Cleaning

Alex Majale — Sat, 29 Aug 2026 05:06:33 +0000

Introduction

We interact with data almost every day. But having data is one thing, and having clean, usable data is another. This is where Excel comes in. It can be a good starting point for cleaning, organising, and analysing data.
Excel is an electronic spreadsheet program that allows users to organize, calculate, format, and analyze data.
Data cleaning is the process of identifying and correcting errors, missing values, duplicates, and inconsistent formats in raw data sets.

Basics of Excel

The first thing you'll see once you open Excel is rows, columns, and cells.
A row runs horizontally and is labeled with numbers (1, 2, 3), while a column runs vertically and is labeled with letters (A, B, C)
A cell is the individual rectangular box formed by the intersection of a vertical column and a horizontal row.
These rows, columns, and cells are contained within a worksheet.
One or more worksheets form a workbook.
The image below contains an example of an Excel worksheet with its features;

Inside the worksheet, there is basic information I need to know, such as;

Sorting which is the process of rearranging your rows of data into a specific order based on the values in one or more columns. This includes arranging text from A to Z or Z to A, arranging numbers from smallest to largest or largest to smallest, and arranging dates and times from oldest to newest or newest to oldest.
Filtering a tool that hides rows you do not want to see so you can focus only on the data that matches your rules.
Basic formulas, where I learned that every formula has to begin with an equals sign (=). These formulas include addition, such as =1+1 or =A1+B1, and subtraction, such as =2-1 or =A1-B1.
Formatting which is the process of changing the visual appearance of data in a spreadsheet to make it easier to read and understand without changing the actual data values.

Data Cleaning

I learnt some basic data cleaning techniques such as;
Removing duplicates
Handling missing values
Removing extra spaces with TRIM()
Standardizing text with UPPER(), LOWER(),PROPER()
Fixing inconsistent dates

To understand how data cleaning works in Excel, I used a small example containing employee records. At first, the data looked normal, but a closer look revealed some errors.

Employee Name	Department	Location	Hire Date
john kamau	Finance	Nairobi	12/01/2024
Jane Wanjiku	HR	nairobi	15/01/2024
John Kamau	Finance	Nairobi	12/13/2024
brian Otieno	IT	NAIROBI	18/01/2024
Mary Akinyi	HR	Kisumu	20/01/2024

1. Removing duplicates

The first issue I noticed was that John Kamau appeared twice with the same information. Duplicate records can affect analysis by making some results appear higher than they actually are.

I used Excel's Conditional formatting feature to identify and remove the repeated record.

2. Standardising text

The Location column also contained different versions of the same location:

Nairobi
nairobi
NAIROBI

Although they refer to the same place, Excel can treat them as different values when analysing the data.

I standardised the entries so that they followed the same format.

3. Checking dates

The Hire Date column also needs attention. Dates may appear in different formats depending on how the data was entered. I checked that the values were recognised as actual dates and then applied a consistent date format. Such as correcting the 12/13/2024 to 12/01/2024.

4. Fixing the Word format

The employee name format is in improper format with words such as john kamau in lower cases instead of proper cases. So i used the formula =PROPER(A2) to change the format to John Kamau

Conclusion

I initially thought cleaning data meant simply removing blanks and duplicates. I learned that consistency is equally important, especially with dates, categories, and text.

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

Alex Majale — Thu, 20 Aug 2026 10:09:58 +0000

Introduction

When I first heard the words Git and GitHub, I actually thought that git was the short form of GitHub, only to realise that they are two different things.
Git is free, open-source version control software used by programmers to track code changes and collaborate. Git is a version control system that runs on my computer. GitHub, on the other hand, is a cloud-based web platform where computer programmers store, share, and work together on software code.
This article explains how I created a local repository and pushed the project to GitHub using Git and SSH.

What Requirements I Needed

A GitHub account
Git installed on the Computer
An SSH Key configured with GitHub
Visual Studio Code installed on the Computer

How I Did It

I started by opening GitBash on my desktop, then typed ls to see what my desktop contained.
The ls command means list, and it is used to list the contents of a directory.
Next, I created a folder called My First Project.
using mkdir "My First Project"
mkdirmeans make directory and is used to create a new folder.
I then used cd "My First Project" to enter the folder.
cd means change directory and is used to open folders.
While in the My First Project folder, I created another folder called Data.
Using mkdir Data
Then I created a README file using touch README.md
touch is used to create an empty file
So to add information to the README file I used
echo "# My First Project"> README.md
echo means to output or display text, data, or variable values directly to a screen, terminal, or log file.
The # is used to show that it is a heading.
I then generated practice data and copied it into the Data folder
I then used echo "This is practice data on Hardware sales">> README.md
This time I used >> so that I could not overwrite the first line in the README file.
I then used cat README.md to see the contents of the README file.
cat is a standard command used to read, print, combine, and create text files directly from the terminal.
I then confirmed the contents of the Data folder using ls Data
I keyed in git init
git init stands for "Git Initialize," and it is the command used to create a brand-new, empty Git repository.
Then git status
git status shows the current state of your repository's working directory and staging area. It tells you which files have been changed, which ones are prepared for your next save point (commit), and which ones Git is completely ignoring.
Then ran git add .
git add is the command used to save your code changes into a temporary preparation zone.
Then I ran git commit -m "Add My First Project"
git commit is a save point, or snapshot, of your project's files at a specific moment in time.
I then ran git log
git log is a command-line tool used to view the history of changes (commits) made to a code repository.
Then git remote add origin[SSH link]
The SSH link is found on GitHub, where you copy it from the repository you created.
Then I ran git remote -v
Finally, I ran git push -u origin main
git push uploads your local repository commits to a remote repository.
After this, the project was uploaded to my GitHub.

Conclusion

I was able to learn about the difference between git and GitHub, and along the way I managed to understand the process of pushing a project from a local repository to an online repository using Git, GitHub, and SSH.