DEV Community: Stacy Mumbi

Building a Power BI Data Model for ShopKe: Understanding Relationships, Schemas and Joins

Stacy Mumbi — Mon, 14 Sep 2026 04:11:06 +0000

Introduction

Say your working as a data analyst at ShopKe, a fictional Kenyan e-commerce company selling electronics, fashion and home products. The management team wants to understand sales performance, customer purchasing behavior and product profitability.

The company has three datasets namely Customers, Products and Orders. Even though these datasets contain valuable information, they need to be properly organized before creating a Power BI dashboard.

This is where data modelling becomes important. A well designed data model allows Power BI to connect related tables, perform accurate calculations and present meaningful business insights.

In this article, I will explore data modelling approaches, fact and dimension tables, relationships, filter directions and Power Query joins using ShopKe as a practical example for better understanding.

1. Data Modelling in Power BI

Data modelling is the process of organizing data into tables and defining relationships between them so that the data can be analyzed properly.

A good data model is important because it supports:

Accurate reporting and DAX calculations.
Better query performance.
Scalability as data grows.
Easier report development.
Improved readability and maintainability.

For example, ShopKe may store customer information in one table and sales transactions in another. By connecting them using CustomerID, Power BI can analyze sales by customer without repeating customer details in every transaction.

1.1 Flat Table

A flat table stores all information in one table.

OrderID	CustomerName	ProductName	Category	Quantity	SalesAmount
O001	Stacy	Laptop	Electronics	1	75,000
O002	Brian	Mouse	Electronics	2	3,000
O003	Stacy	Shirt	Fashion	1	2,500

Structure

┌──────────────────────────────┐
│          SalesData           │
├──────────────────────────────┤
│ OrderID                      │
│ CustomerName                 │
│ ProductName                  │
│ Category                     │
│ Quantity                     │
│ SalesAmount                  │
└──────────────────────────────┘

Advantages: Simple to understand, easy to import and good for small datasets.

Disadvantages: Repeated information increases redundancy and the table may become difficult to maintain as the business grows.

When to use: Small datasets, quick analysis and simple reporting requirements.

Performance and complexity: It has few relationship complications, but unnecessary columns and repeated values can increase the amount of data stored.

1.2 Star Schema

A star schema contains a central fact table connected directly to multiple dimension tables.

For ShopKe, FactSales stores transactions, while DimCustomer, DimProduct and DimDate provide descriptive information.

Structure

ShopKe data model presented as a star schema

Advantages: Simple to understand, supports readable DAX, reduces unnecessary duplication and well suited for analytical reporting.

Disadvantages: Requires careful relationship design and may involve some repeated descriptive information within dimensions.

When to use: Sales, finance, inventory, customer and other business intelligence projects.

Performance and complexity: A well-designed star schema can support efficient analytical queries and compression while keeping the model relatively easy to navigate.

1.3 Snowflake Schema

A snowflake schema is a type of star schema where dimension tables are further divided into related tables.

For example, ShopKe may separate product information into Product, Subcategory and Category tables.

Structure

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

An example of how a snowflake schema model would look like

Advantages: Reduces repeated descriptive information and can support difficult hierarchies.

Disadvantages: More tables and relationships increase model complexity and may make report development less intuitive.

When to use: Complex organizational or geographical hierarchies, normalized data sources and situations where shared reference tables are useful.

Performance and complexity: It can reduce redundancy, but more relationships may make the model more complicated. In many cases, a star schema is preferred for simplicity.

2. Fact Tables and Dimension Tables

Fact tables

A fact table stores measurable business events. In ShopKe, FactSales contains individual sales transactions.

Example fields include:

SalesID
CustomerID
ProductID
DateKey
Quantity
SalesAmount

The FactSales table

Dimension tables

Dimension tables store descriptive attributes used to analyze facts.

Examples include:

DimCustomer containing customer names and regions.
DimProduct containing product names and categories.
DimDate containing dates, months and years.

Grain of a fact table

The grain describes what one row represents.

For example:

One row in FactSales represents one product line in one customer order.

This is important because an order may contain several product lines. Therefore, counting rows may not equal counting unique orders.

ShopKe's star schema

FactSales connects to the dimensions through keys:

DimCustomer[CustomerID] 1 ─── * FactSales[CustomerID]

DimProduct[ProductID] 1 ─── * FactSales[ProductID]

DimDate[DateKey] 1 ─── * FactSales[DateKey]

DimLocation[LocationID] 1 ─── * FactSales[LocationID]

This allows ShopKe to analyze total sales by product, customer or date.

3. Relationships in Power BI

A relationship connects tables by use of matching columns. Relationships are necessary because business data is often distributed across several tables.

Primary keys and foreign keys

A primary key uniquely identifies a row in a table. A foreign key references a key in another table.

For example, CustomerID is unique in DimCustomer but may repeat in FactSales because one customer can make multiple purchases.

This creates a one to many relationship.

Relationship cardinalities

One to Many (1:*)

One row in a dimension relates to many rows in a fact table.

Example:

DimCustomer[CustomerID] 1 ─── * FactSales[CustomerID]

This is the most common relationship in a star schema.

One to One (1:1)

Each row in one table matches at most one row in another table.

Example:

Employee[EmployeeID] 1 ─── 1 EmployeeDetails[EmployeeID]

It is appropriate when two tables contain separate information about the same entity and both sides have unique keys.

Many to Many (:)

Multiple rows in one table can relate to multiple rows in another table.

Example:

Students * ─── * Courses

One student can take many courses and one course can have many students.

A bridge table can be used for a clearer design as shown:

DimCustomer 1 ─── * BridgeCustomerSegment * ─── 1 DimSegment
Referential integrity and unique values

Referential integrity means foreign keys should correspond to valid keys in the related dimension, where applicable.

For example, every CustomerID in FactSales should also exist in DimCustomer. Missing or unmatched keys may indicate data quality problems.

Active and inactive relationships

An active relationship is used automatically for filter propagation.

An inactive relationship is not used automatically but can be activated in a DAX measure.

For example, FactSales may have OrderDate and ShipDate. DimDate can have an active relationship with OrderDate and an inactive relationship with ShipDate.

4. Filter Direction

Filter direction determines how filters move between related tables.

Single-direction filtering

Filters flow in one direction, mostly from dimensions to facts.

DimProduct ───────────> FactSales

If a user selects Electronics in a product slicer, Power BI filters FactSales to show sales for electronics products.

Single-direction filtering is recommended for star schemas because it provides predictable behavior and reduces ambiguity.

Bidirectional filtering

Filters flow in both directions.

DimProduct <──────────> FactSales

This may be useful in specific scenarios, such as certain bridge-table models.

However, bidirectional filtering should be used carefully because it can create ambiguous filter paths, unexpected results and unnecessary model complexity.

For ShopKe, I would use single-direction filtering by default and only introduce bidirectional filtering when there is a clear business requirement.

5. Joins in Power Query

A join combines rows from two tables using matching columns. In Power Query, this is performed using Merge Queries.

If ShopKe had the following tables:

DimCustomers

CustomerID	CustomerName
C001	Stacy
C002	Brian
C003	Aisha

DimOrders

OrderID	CustomerID	Amount
O001	C001	75,000
O002	C001	4,500
O003	C002	3,000
O004	C004	2,000

C003 has no order, while C004 appears in Orders but not Customers.

5.1 Left Outer Join

Returns all rows from the left table and matching rows from the right table.

Example: Customers LEFT JOIN Orders.

CustomerID	CustomerName	OrderID	Amount
C001	Stacy	O001	75,000
C001	Stacy	O002	4,500
C002	Brian	O003	3,000
C003	Aisha	NULL	NULL

Use: Finding customers who have not placed orders while retaining all customers.

5.2 Right Outer Join

Returns all rows from the right table and matching rows from the left table.

Example: Customers RIGHT JOIN Orders.

CustomerID	CustomerName	OrderID	Amount
C001	Stacy	O001	75,000
C001	Stacy	O002	4,500
C002	Brian	O003	3,000
C004	NULL	O004	2,000

Use: Identifying orders without matching customer records.

5.3 Full Outer Join

Returns all rows from both tables, including unmatched records.

Expected unmatched records:

CustomerID	CustomerName	OrderID	Amount
C001	Stacy	O001	75,000
C001	Stacy	O002	4,500
C002	Brian	O003	3,000
C003	Aisha	NULL	NULL

Matching records are also retained.

Use: Data reconciliation and identifying missing records in either table.

5.4 Inner Join

Returns only rows with matching values in both tables.

Expected output:

CustomerID	CustomerName	OrderID	Amount
C001	Stacy	O001	75,000
C001	Stacy	O002	4,500
C002	Brian	O003	3,000

Use: Keeping only customers who have matching orders.

5.5 Left Anti Join

Returns rows from the left table that have no matching rows in the right table.

Example: Customers LEFT ANTI JOIN Orders.

CustomerID	CustomerName
C003	Aisha

Use: Finding customers who have never placed an order.

5.6 Right Anti Join

Returns rows from the right table that have no matching rows in the left table.

Example: Customers RIGHT ANTI JOIN Orders.

OrderID	CustomerID	Amount
O004	C004	2,000

Use: Identifying orders with missing customer records.

In Power Query, a merge may create a nested table column. For joins that return matching records, the column can be expanded to display the required fields.

6. Power Query Joins vs Power BI Relationships

Even though both connect tables, they serve different purposes.

Feature	Power Query Merge	Power BI Relationship
Stage	Data preparation	Data modelling
Purpose	Combine data	Connect tables
Physically combines columns?	Yes, in the merged query result	No
Filter propagation	Not as a model relationship	Yes
Common use	Bring ProductCategory into a table	Connect DimProduct to FactSales

Example

If ShopKe needs to bring ProductCategory into FactSales, Power Query Merge can combine the columns.However, if DimProduct already contains ProductCategory, keeping it separate and creating a relationship is better.

Too much merging can create a wide table with repeated customer and product information. This may cause increased redundancy and the model being harder to maintain.Keeping fact and dimension tables separate supports a clearer and more scalable star schema.

7. Recommended Power BI Model for ShopKe

For ShopKe, I would recommend a star schema with FactSales connected to DimCustomer, DimProduct and DimDate.

I would use one-to-many relationships from the dimensions to the fact table and single-direction filtering by default.

Why?

Performance : Separating facts and dimensions can support efficient analytical queries.
DAX simplicity : Measures such as Total Sales can be written directly against FactSales.
Readability : Each table has a clear purpose.
Scalability : New transactions, customers and products can be added without redesigning the entire model.
Maintainability : Descriptive information can be updated in dimensions.
Report development : Users can easily combine dimension attributes with fact measures.

Example measures:

Total Sales =
SUM(FactSales[SalesAmount])
Total Orders =
DISTINCTCOUNT(FactSales[OrderID])
Average Order Value =
DIVIDE([Total Sales], [Total Orders])

I would use bridge tables for genuine many-to-many scenarios and inactive relationships for alternative date analysis when necessary.

Conclusion

Data modelling is the foundation of a reliable Power BI report. It determines how data is organized, how tables interact and how accurately business questions can be answered.

As much as flat tables are simple and snowflake schemas can support complex structures, a star schema is most likely the most suitable choice for a business intelligence project.

Through the ShopKe example, we have seen how fact and dimension tables work together, how relationships control filter propagation and how Power Query joins prepare data for analysis.

The most important lesson is that proper Power BI development is not only about creating visuals. It is about designing a model that is accurate, understandable, scalable and useful for decision-making.

A well designed data model turns disconnected data into meaningful business insights.

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

Stacy Mumbi — Sat, 05 Sep 2026 13:13:23 +0000

Introduction

After completing Excel's coursework this week courtesy of the LuxDevHQ Data Science, Analytics and AI Programme, I decided to put what I had learnt into practice by analyzing Jumia product data. I challenged myself to turn the raw data into something useful for analysis and decision making.

The dataset contained 115 product listings with information such as prices, discounts, reviews and ratings. However, it was far from ready for data analysis. There were missing values, duplicate records, negative review counts, inconsistent formatting and even one product whose price was given as a range.

So, this project ended up being about much more than creating a dashboard. I first had to understand the data, identify the issues, make reasonable cleaning decisions, document those decisions and then use the cleaned data to build an interactive Excel dashboard.

In this article, I'll walk through that process, from the initial data-quality audit to the final dashboard and the insights I found from the analysis.

The Dataset and Business Questions

The dataset, Excel_jumia_dataset.csv, contained six fields originally:

Product
Current price
old price
Discount
Review
Ratingd

The last column, Ratingd, was misspelled in the original dataset and was later corrected to Rating.

One of the first things I noticed was that the dataset didn't contain actual sales, revenue or units sold data. Because of this, I couldn't use sales as a measure of product performance.

Instead, I used review count as a proxy for customer engagement throughout the project.

This was important to take note of because having more reviews doesn't necessarily mean that a product has higher sales. A product that has been listed for a longer period, for example, may simply have had more time to accumulate reviews.

With that in mind, I focused on the following questions:

Are bigger discounts associated with more reviews?
Do highly rated products receive more engagement?
Does product price have any relationship with rating?
Which products stand out based on ratings, reviews or discounts?
Which products might need a different pricing, marketing or listing strategy?

Data-Quality Audit

Before changing anything, I wanted to understand exactly what I was working with.

The initial audit showed:

115 rows and 6 columns
58 blank Review values
58 blank Rating values
3 exact duplicate rows
6 repeated product names with different prices, discounts, or review counts
57 negative Review values
1 row where both Current price and Old price were given as ranges
Ratings were within the expected 0–5 range
Discounts were within 0–100%
No Current Price was higher than the corresponding Old Price

One thing that I immediately realised: all 57 non-blank Review values were negative.

That was unusual. Since a review count cannot be negative realistically, I treated this as a likely systematic scraping or formatting issue rather than assuming the values represented something meaningful.

If only a few values had been negative, I would have been more cautious about changing them. But because every non-blank review value had the same issue, the pattern strongly suggested a consistent data-quality problem.

The original dataset as imported into the Raw_data sheet. The negative Review values and misspelled Ratingd header are visible.

Cleaning the Data and Why I Made These Decisions

Cleaning the dataset was one of the most important parts of the project. I didn't want to change values simply because they looked inconvenient. For each major cleaning step, I considered what the data was actually telling me and how the decision would affect the analysis.

1. Fixing the Negative Review Counts

There were 57 negative Review values and all non-blank review values had this issue.

I created a helper column and used the following formula:

=IF(E2="","",ABS(VALUE(E2)))

This converted the negative values into positive review counts while keeping genuinely blank cells blank.

After checking the results, I pasted the corrected values as values so that the cleaned numbers were no longer dependent on the formula.

2. Handling Missing Reviews and Ratings

Both Review and Rating contained 58 blank values.

I intentionally left these cells blank.

A blank Review means that the review count wasn't captured. A zero means that the product had zero reviews. These are two completely different situations.

Replacing the blanks with zero would therefore have affected averages, rankings and other calculations in ways that weren't supported by the original data.

3. Handling the Price Range

One product, a 1/2/3 Seater Elastic Sofa Cover, had its prices listed as ranges:

Current price: KSh 1,620–1,980
Old price: KSh 2,200–3,200

I had three options to choose from:

Use the lower value
Remove the row
Use the midpoint

I chose the midpoint.

Using the lower bound would ignore part of the information provided, while removing the row would mean losing a useful product just because its price was formatted differently.

The original range was retained elsewhere so that the decision could still be traced back to the source data.

There was also an interesting consequence of this decision. When I calculated the discount using the midpoint prices, I got 33%, while the seller's advertised discount was 38%.

Rather than replacing the advertised value, I kept both values. The difference is documented as a result of using an estimated midpoint rather than an exact price.

4. Removing Duplicates

The dataset contained three rows that were completely identical across all six original fields. I removed these as they were exact duplicates.

However, there were also six cases where the same product name appeared more than once but the prices, discounts or review counts were different.I kept these records.

Because Jumia is a marketplace with multiple sellers, the repeated product names could represent different seller listings rather than duplicate records. Removing them without evidence would have meant making an assumption that the data didn't support.

5. Fixing Text and Formatting Issues

I also made other formatting corrections:

Removed "out of 5" from the Rating values
Converted Rating into a numeric decimal
Changed Ratingd to Rating
Standardized old price to Old Price
Converted prices from text into numeric currency values
Formatted prices as KSh #,##0.00

The cleaned tblProducts table showing corrected data types, standardized fields and additional analytical columns.

All of these cleaning decisions were documented in the Data Dictionary sheet so that the process could be reviewed and traced back to the original data.

Adding Enrichment Fields

Once the basic cleaning was complete, I created additional fields to make the dataset more useful for analysis.

Discount Amount and Calculated Discount

I calculated the difference between the Old Price and Current Price to get the Discount Amount.I also calculated the corresponding percentage discount and kept it alongside the original advertised Discount.
This made it possible to compare the advertised discount with the discount calculated from the prices.

Rating Category

I grouped products into three rating categories:

Poor: < 3
Average: 3–4.5
Excellent: > 4.5

The original brief left ratings from 4.1 to 4.5 without a category. I therefore followed the suggested option of including this range under Average and documented this as a working assumption.

Discount Category

Discounts were grouped into:

Low: < 20%
Medium: 20–40%
High: > 40%

Price Category

Rather than guessing price cutoffs, I used the data itself to determine the categories.

I calculated the first and third quartiles of Current Price using

QUARTILE.INC()

The resulting thresholds were:

Price Q1: KSh 493
Price Q3: KSh 1,669.50

I then used these thresholds to classify products into Low, Medium and High price categories.

Engagement Strength

I also wanted a data-driven way to identify products with relatively high engagement.

I used the 75th percentile of Review count as the threshold:

Review Q3: 13 reviews

Products with 13 or more reviews were therefore classified as having Strong Engagement.

Data Status

Because the dataset contained many missing ratings and reviews, I added a Data Status field showing whether each product was:

Complete
Missing rating
Missing review
Missing both

This made it easier to understand the level of data coverage behind the analysis.

Combination Flags

Finally, I created four flags to identify products with interesting combinations:

High discount + low rating
High discount + low engagement
Many reviews + average rating
Strong engagement + excellent rating

Whenever one of the required values was missing, the formula returned "Missing" rather than making an assumption.

Example of an Excel formula used to classify products into Rating Categories.

PivotTables and Analysis Workflow

With the data cleaned and enriched, I moved on to the analysis.
One of the main things I wanted to investigate was whether price, discount, rating and review count were related.

I focused on three relationships:

Discount vs. Reviews
Rating vs. Reviews
Current Price vs. Rating

Because Review and Rating contained missing values, I didn't want blank cells to interfere with the correlation analysis.

I therefore created helper ranges using FILTER() to return only rows where both variables in each pair were available. I then used these complete pairs for the correlation calculations and scatter plots.

The results were:

Relationship	Pearson r	R²
Discount vs. Reviews	-0.111	0.012
Rating vs. Reviews	0.043	0.002
Current Price vs. Rating	0.110	0.012

All three relationships were very weak.

For example, the correlation between Discount and Reviews was -0.111, which is very close to zero.

The R² values were also extremely small, meaning that each variable explained very little of the variation in the other variable.

Scatter plots for Discount vs. Reviews, Rating vs. Reviews, and Current Price vs. Rating, each with a linear trendline, equation and R² value.

Product Rankings

I also created PivotTables to identify:

Top 10 products by Rating
Bottom 10 products by Rating
Top 10 products by Reviews
Top 10 products by Discount

I also used the combination flags to identify products that met the four additional conditions.

For rating-based rankings, products with missing ratings were excluded.
Where products had the same rating, I used review count as the tie-breaker. For review-count ties, rating was used as the tie-breaker.

This made the ranking rules explicit rather than leaving the ordering to chance.

_PivotTables used to identify the highest and lowest-performing products across ratings and discounts.

Building the Interactive Dashboard

After completing the analysis, I brought the most useful pieces together into a single-screen dashboard.

I wanted the dashboard to answer the main questions quickly without requiring someone to scroll through multiple sheets.

The dashboard includes:

KPI Cards
- Total Products
- Average Price
- Average Discount
- Average Rating
- Total Reviews
Product Ranking Charts
- Top 10 by Rating
- Top 10 by Reviews
- Top 10 by Discount
Relationship Analysis
- Discount vs. Reviews
- Rating vs. Reviews
- Price vs. Rating

Each scatter plot includes a trendline, equation and R² value.

Category Analysis
- Rating Mix
- Discount Mix
Interactive Slicers The dashboard has slicers for:
- Rating Category
- Discount Category
- Price Category

These slicers are connected to the compatible PivotTables and charts, allowing the dashboard to update when a user filters the data.

The completed single-screen dashboard combining KPIs, product rankings, relationship analysis, category charts and interactive slicers.

Key Findings

After cleaning and analyzing the data, a few findings stood out.

1. Bigger Discounts Didn't Necessarily Mean More Engagement

The correlation between Discount and Review count was -0.111.

That's really close to zero.

So in this dataset, products with larger discounts did not necessarily have more reviews.

2. Price and Rating Were Barely Related

The correlation between Current Price and Rating was 0.110.
This is also a very weak relationship.

In this dataset, highly rated products were not always more expensive or cheaper than products with lower ratings.

3. Some Heavily Discounted Products Still Had Weak Performance

Some products combined a high discount with either a low rating or low engagement.

These products caught my attention because offering a bigger discount may not solve the underlying problem.

They could instead be worth investigating from a listing-quality perspective, such as product descriptions, images, presentation or customer expectations.

4. Some Products Had Both Strong Engagement and Excellent Ratings

There were also 5 products that stood out for having both strong engagement and excellent ratings.They could provide useful examples of what appears to be working well.

Even though the dataset doesn't contain enough information to explain why these products perform well, they would be good choices for further investigation.

Business Recommendations

Based on the patterns in the data, I would recommend:

Not to rely on discounts alone to drive engagement. Since discount and review count were almost not related, sellers could test other approaches such as improving product descriptions, images and overall listing quality.
Investigating heavily discounted products with low ratings or engagement. Instead of immediately offering an even bigger discount, it may be better to first understand why the product isn't attracting positive customer feedback.
Studying products with strong engagement and excellent ratings. These products could provide useful examples for understanding what works well across listings.
Using data carefully when making decisions. The results show patterns in this dataset, but they don't prove that changing one variable will cause another to change.

Limitations

This project also has limitations like any other.

The biggest one is that the dataset doesn't contain sales, revenue, units sold or listing-age data. Because of this, review count is only a proxy for engagement and cannot be treated as a measure of sales performance.
There is also one product whose price had to be estimated using the midpoint of a range rather than an exact value.
The Rating Category assumption for ratings between 4.1 and 4.5. I grouped these under "Average" based on the suggested option in the project brief, but this was just a working assumption .
The correlations in this analysis describe relationships between variables. Correlation does not imply causation.

What I Learned

One of my biggest lessons from this project was that data analysis starts before the charts.

Initially, the dataset looked small and straightforward. But once I started auditing it, I found several issues that could have easily affected the final results if I had jumped straight into analysis.

The negative review counts were a good example. If I hadn't investigated that pattern, I could have ended up presenting misleading engagement figures.

I also learned the importance of documenting assumptions. For example, rather than silently changing the price-range row or filling missing values with zeros, I recorded what I did and why.

Most importantly, this project helped me see Excel as more than just a spreadsheet tool. I was able to use it for the full analytics process that is data auditing, cleaning, transformation, analysis, visualization and communication , all within one workbook.

And probably the biggest lesson was that a good dashboard isn't just about making something look nice. The real value comes from making sure that the numbers behind it are trustworthy and that the story you're telling is actually supported by the data.

I am honestly still in awe of how such a seemingly small dataset could yield so many other columns and useful insights after proper cleaning and analysis.
From just a few original columns, I was able to create additional fields, uncover patterns, build meaningful visualizations and turn the data into a dashboard that tells a story.

How I love being a data analyst!

Project Files

The complete project is available here:

GitHub Repository: Jumia_Product_Performance_Dashboard
Dashboard Worksheet: Available in the repository under dashboard
Raw Dataset: Available under data

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

Stacy Mumbi — Sun, 30 Aug 2026 05:47:14 +0000

Introduction

Data is definitely everywhere because almost all the information we receive everyday can be collected and analysed data, but having a dataset is only the beginning of the data analytics process. Before analysing data and drawing conclusions from it, it is important to make sure that the data is accurate, consistent, complete and in the correct format through a process called data cleaning.

During Week 3 of my Data Science and Analytics training at LuxDev Academy, I was introduced to the basics of Microsoft Excel and how it can be used to prepare data for analysis. I used to view Excel as just an application that is used to view spreadsheets, but this week I learnt that it has many useful features for working with real-world datasets. These include sorting, filtering, formatting, removing duplicates, finding and replacing values and using functions such as TRIM, PROPER and CONCAT which are known as Text Functions.

For practice, I decided to work on a synthetic Kenyan M-Pesa transaction dataset generated using Mockaroo. The dataset contains 193 transaction records and includes information such as transaction IDs, transaction dates, sender and receiver names, transaction types, transaction amounts, transaction status, counties and transaction fees.

Even though the dataset was synthetic and small, it contained several inconsistencies that provided an opportunity to practise the data-cleaning concepts I had learned. In this article, I will walk through how I did the cleaning, from importing the dataset into Excel to identifying and correcting formatting, missing values, duplicates and inconsistencies.

1. Generating and Importing the Dataset

I first generated the M-Pesa transactions dataset using Mockaroo and downloaded it as a CSV(Comma Separated Values) file. I chose an M-Pesa dataset because mobile money transactions are really relevant to the Kenyan context as millions of people transact money everyday using M-Pesa and it can contain different types of information that would be useful for data analysis.

After downloading the CSV file, I opened Microsoft Excel. Instead of opening the file directly through the File section, I practised importing the data through the Data option on the ribbon at the top of the blank workbook: 'Data' then 'Get Data' then 'From File' then 'From Text/CSV'. Excel then showed a preview of the data before loading it into the workbook.

It was a very important first step before beginning any cleaning.

This is how the dataset looked like after loading and before I started cleaning.

2. Making a Duplicate Sheet Before Cleaning

Before making any changes to the data, I duplicated the worksheet by right-clicking the sheet tab by copying the whole dataset by first using keyboard shortcut Ctrl+A to select the whole dataset,then Ctrl+C to copy, then added a new sheet and then right-clicked in the top-left cell to paste the dataset . This gave me an untouched version of the original dataset to refer back to, while I carried out all my cleaning on the duplicate sheet.
If I made a mistake while cleaning, I still had the original data intact rather than needing to re-download the dataset or opening it from my file explorer.

Creating a backup copy of the sheet so the original data stayed untouched.

3. Formatting, Sorting and Checking for Duplicates

Once the dataset was loaded, I used 'Home' then 'Format' then 'AutoFit Column Width' so that all the values were completely visible, which made it easier to see the data during cleaning.

I then checked the dataset for duplicate records using 'Data' then 'Data Tools' then 'Remove Duplicates', selecting all columns so that only exact duplicate rows would be removed. Excel returned that no duplicates were found. Even though none were present, the step was important because checking that a potential problem does not exist is as much a part of data cleaning as fixing one that does.

I also applied filters via 'Home' then 'Sort & Filter' then 'Filter', which added drop-down buttons to each column header. This made it much faster to look at individual columns. For example, checking 'County' for blanks or checking 'Transaction Status' and 'Transaction Type' for inconsistent entries without having to scroll through every column.

The Home ribbon tools I used for formatting and creating the drop-downs.

4. Checking and Correcting Data Types

I then went column by column checking whether Excel had interpreted each one correctly, since a value can look correct visually while having the wrong data type.

Transaction ID - changed from Number to Text, since it is an identifier rather than a value that can be calculated or aggregated.
Transaction Date - changed from General to a proper Date format (year-month-day), making it possible to sort and support analysis later.
Sender Name / Receiver Name - already correctly identified as text.
Sender Phone - converted to Text, since phone numbers are identifiers not quantities that can be aggregated.
Transaction Type / Transaction Status - already identified as text, but needed further cleaning for consistency.
Transaction Amount / Transaction Fee - formatted from Number to Currency (KES), since these represent monetary values and needed to clearly show that.

5. Handling Missing Values

Using the drop-downs I had applied earlier, I checked each column for blanks. I found missing values in County, as well as in the numeric fields Transaction Amount and Transaction Fee.

I did not treat all missing values the same way. For the text column, County, I replaced blanks with "Unknown". For the numerical fields, I left the blanks as they were rather than filling in a made-up value as it would have added incorrect information to the dataset rather than fixing it.

This taught me that how you handle a missing value should depend on what the column represents and not a single default rule applied everywhere.

6. Standardising Text with Key Functions

Several columns had inconsistent capitalisation or extra spacing, so this was where most of my actual cleaning work happened:

Find and Replace (Ctrl + H) - used to correct inconsistent entries in Transaction Type (e.g different casings of "Pay Bill") and Transaction Status (e.g "failed" vs "Failed").
TRIM - used in a helper column to remove hidden extra spaces from text values that looked correct but weren't matching due to leading spaces.
PROPER - used in a helper column to standardise capitalisation in Sender Name and Receiver Name (e.g turning "MUTUA KARANJA" into "Mutua Karanja").
CONCAT - used to restore the leading zero on Kenyan phone numbers after removing apostrophes and the country-code had left some numbers starting with "1" instead of "0". The formula I used was =CONCAT("0",D2).

For each of these, I applied the formula to the first cell in a row, used AutoFill to apply it down the column, then used Paste Special for Values only to convert the formula results into permanent values in a new column before deleting the helper columns. This kept my final dataset clean while preserving the original column structure.

For cleaning the sender name column I created a helper column and used the above formulas to clean it.

7. What I Learned From the Cleaning Process

Working on this dataset helped me understand that data cleaning is more than removing duplicates and deleting or filling blank cells.

This is a brief summary of what I learnt:

The importance of understanding the meaning of each column before deciding how to clean it. A phone number consists of digits, but treating it as numeric wouldn't make sense because there's no meaningful total or average of phone numbers. Also, a Transaction ID may contain only numbers but should be treated as text because it's an identifier, not a value to calculate.
Missing values should be handled according to the type and meaning of the data i.e "Unknown" for text fields where appropriate and left blank for numeric fields instead of guessing.
Sorting and filtering are really important tools as they help you find out what's actually in a column before you touch it. In addition, creating a copy of the original sheet before cleaning meant I could correct mistakes without risking the original data.
Excel usually offers more than one way to solve the same problem like in the case of PROPER versus Find and Replace and choosing between them often comes down to the type of inconsistency and personal preference.

Conclusion

My first practical data-cleaning project using Excel gave me a much better understanding of how raw data can be prepared for analysis.

I started with a synthetic Kenyan M-Pesa transactions dataset generated using Mockaroo and imported it into Excel. Before making any changes, I duplicated the sheet to preserve the original data, then worked through the dataset column by column correcting data types, formatting monetary fields as KES currency, converting dates into the correct format, treating identifiers like Transaction ID and phone numbers as text, handling missing values carefully and standardising text using TRIM, PROPER, Find and Replace and CONCAT.

The dataset after cleaning now with consistent formatting and standardized text.

The dataset was not too messy, but that actually made it easier for my first practice exercise. It allowed me to focus on understanding the purpose of each Excel tool rather than becoming overwhelmed by a very complicated dataset.

I learnt that good data cleaning requires both technical Excel skills and an understanding of the data itself. Excel provides the tools, but I still need to make informed decisions about how those tools should be used.

As I continue learning data analytics, I hope to build on these skills and become more confident in using Excel for larger and more complex datasets. Week 3 has given me a strong foundation and I now have a better understanding of how important data cleaning is before carrying out analysis and visualisation.

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

Stacy Mumbi — Sat, 22 Aug 2026 17:59:57 +0000

Introduction

It's only been two weeks since I enrolled in the LuxDev Data Science, Analytics and AI program yet I've already learnt so much!
One of the first things I've been able to learn is how to take a project from a local folder in my PC and publish it to GitHub using Git and SSH.
For someone who is still building their skills in data and tech,terms like repositories,commits,staging,remotes and SSH keys were new to me at first. Working on my first project helped me understand these commands and also what happens behind the scenes.

So for a start,I decided to document my experience of taking my first GitHub project from a local folder to GitHub using Git and SSH.

What are Git and GitHub?

Interestingly, I learnt that Git and GitHub are not the same thing. This is why understanding what each of them means became a very important step in my project.

Git

Git is a version control system which runs in my PC and helps me keep track of changes made to files in a project.
As I work,I might change my code,update my analysis,add new files or fix mistakes. Git lets me keep track of the changes I make over time instead of having multiple edited copies of the same file. Each saved version is called a commit.

GitHub

GitHub is an online platform where repositories from Git can be stored and shared.
Git does not need to work with GitHub as I can use it in my PC without uploading my project elsewhere.
However, GitHub becomes useful when I want to:

Keep a back up of my projects online
Show potential employers what I can do
Share my work
Collaborate with others
Build a portfolio
Contribute to open-source projects

In a nutshell, this is the journey the local folder in my PC took to reach Github:

Project folder in my PC
         `
Track changes with Git
         `
Commit changes
         `
Push to GitHub

Step 1: Create the Project Files

I first created my project folder in my PC's file explorer and named it Kenya_Health_Records_Analysis.
Then I opened Git Bash and navigated to it using these commands:

cd Desktop
cd Kenya_Health_Records_Analysis

cd(change directory) is a command that tells Git to enter a file or folder in simple terms.
I then proceeded to add a data and README.md file in the project folder:

mkdir data
touch README.md

mkdir(make directory) is used to create a new file or folder from the Git Bash terminal but notice I did not use it to create the README.md file because touch is used to create files with extensions like .md, .py or .ipynb .
I then copied my Kenya_Health_Records.csv dataset and pasted it into the newly created data file in my file explorer.
To confirm this in Git Bash I ran the ls command which lists the contents of a file or folder.
This is what I could see:

ls data
Kenya_Health_Records.csv

Then I went ahead to briefly describe the project in the README.me using the echo command:

echo "# Kenya_Health_Records_Analysis" >README.md
echo " Using Excel to analyze Kenya health records to uncover,trends,patterns and insights in healthcare data." >>README.md

echo prints text while >directs the text into the README.md file however > can replace what is already in the README.md file that is why in the next line I used >> which adds content below what is already there.
To view the full README.md I used the cat command (which displays the contents of a file in the terminal) and ran cat README.md which retrieved:

# Kenya_Health_Records_Analysis
Using Excel to analyze Kenya health records to uncover,trends,patterns and insights in healthcare data.

Step 2: Turning the Folder into a Git Repository

I then ran:

git init

This command initializes a new Git repository and Git begins to manage the project. It also added a new hidden .git file in the project folder.

Step 3: Checking the Status of My Project

One of the most useful Git commands is:

git status

This tells me what Git can see or what is happening inside my repo. At first it told me that both the data and README.md were untracked files meaning that Git had not been told to include them in the project history or to commit.

Step 4: Staging the Files

To stage both files I used:

git add .

The dot tells git to add everything in the current directory.
I then checked the status again using:

git status

The files appeared under the changes to be committed.

Step 5: Creating My First Commit

I then created my first commit:

git commit -m "Add Kenya Health Record Analysis"

The -m allows me to provide a message that explains what I changed.
Hence a commit message can explain what happened at different points in time in the project's history if written clearly.
I then ran:

git log --oneline

This retrieved my unique commit ID and my commit message.

Step 6: Creating a GitHub Repository

After creating the project locally, I went to GitHub and created a new repository and named it the same way I had in my File Explorer:

Kenya_Health_Records_Analysis

This would become an online version of my local Git repository. I also learnt that even though I created the repo and named it the same way,it does not automatically upload my local files.
I still needed to connect my local repo to the GitHub repo and push the changes and this where an SSH Key comes in.

Step 7: Defining an SSH Key

To me, it sounded like such a complex tech term but it was actually just a simple concept.
SSH(Secure Shell) provides a secure way for my PC to connect with my GitHub account.
So after creating the repo on GitHub I was given two link options to connect to it;HTTPS and SSH.

Step 8: Connecting my Local Repository to GitHub

Since I then had both my local Git and GitHub respositories, I needed to connect them using a remote.
A remote is a reference to another repository hosted online.
I then copied the SSH key for my GitHub repository and ran this in Git:

git remote add origin git@github.com:SMumbi5144/Kenya_Health_Analysis.git

origin is the name given to the remote repository.
The SSH address:

git@github.com:SMumbi5144/Kenya_Health_Records_Analysis.git

tells Git where the remote repo is.
To check if the two repos had connected I ran:

git remote -v

Step 9: Pushing the Project to GitHub

Once I had confirmed the two had connected, I pushed my local repo to GitHub by running:

git push -u origin main

git push - tells Git to upload my commits to a remote repository.
origin - refers to the GitHub repository I had added as my remote.
-u - sets an upstream relationship between my local main branch and the remote main branch.

Step 10: Seeing My Project on GitHub

This was the most exciting part of the process. For a beginner like me, it was quite the journey.
I refreshed my GitHub and I could then see my local project files online.
I could therefore view the project online,share it with others and continue working on it.

What I learnt to Avoid

We all make mistakes as we learn and this project was no exception.
Therefore, in order to successfully upload my local files to my GitHub repository I learnt that I should:

Not forget to initialize Git using git init.
Not forget to stage files using git add . else any changes I make will not be part of my next commit.
Never assume commit means upload, to send to GitHub I ought to use git push.
Avoid using unclear commit messages since they will not help me understand the project history easily.

Conclusion

My first project taught me more than just a few commands. It taught me patience and the importance of paying attention to detail and being systematic since a command will only work efficiently if the correct command is used. These are very important qualities for a data professional to develop so as to enhance the quality of their work.

I also learnt some cool concepts such as how a version control system works, how an SSH Key can connect my PC to GitHub and how I can manage and share my projects.

As I continue developing my skills at and outside LuxDev Academy, GitHub will help me document my projects and build a portfolio that shows what I can do.

This first project was the beginning of my journey towards becoming a better data professional.