DEV Community: Jack Kisutsa

Power BI Data Modelling, Relationships and Joins

Jack Kisutsa — Sun, 13 Sep 2026 15:40:38 +0000

Power BI Data Modelling, Relationships and Joins: A Practical Guide

When I began learning Power BI, I naturally focused on dashboards. However, an attractive dashboard can still be slow or misleading when the data behind it is poorly organised. The data model is the foundation: it determines how tables communicate, how filters move and how DAX measures behave.

This article uses a retail-sales example to explain modelling schemas, fact and dimension tables, relationships, filter direction and Power Query joins. It also addresses a common beginner question: if both joins and relationships connect tables, when should each be used?

1. Data modelling in Power BI

Data modelling is the process of organising data into tables and defining logical connections between them. Sales transactions may be stored separately from customers, products, dates and locations. A good model allows a user to select Electronics, Nairobi or 2026 and receive the correct result.

A well-designed model improves accuracy, makes DAX easier to write, reduces duplicated data, improves VertiPaq compression and supports future growth. It also makes the solution easier for another analyst to understand and maintain.

2. Flat table, star schema and snowflake schema

A. Flat table

A flat table stores facts and descriptions together. One Sales table might contain OrderID, Date, CustomerName, ProductName, Category, City, Quantity and SalesAmount.

Advantages: It is quick to build, familiar to Excel users and suitable for a small prototype. No relationships need to be managed.

Disadvantages: Product and customer descriptions repeat for every transaction, increasing table width and storage. Repetition creates inconsistent values and difficult maintenance. Large flat tables are less reusable and can make complex DAX harder to organise.

Best use: Small, one-purpose datasets with limited growth.

B. Star schema

A star schema places a fact table at the centre and connects it directly to dimensions such as Customer, Product, Date and Location.

Advantages: It provides clear filter paths, simpler DAX, less duplication and strong performance. Dimensions can also be reused by several reports.

Disadvantages: It requires deliberate preparation and a clearly defined fact-table grain. Dimensions may contain controlled repetition, such as Category repeated for products in that category.

Best use: Most production Power BI models, especially when data volume and reporting needs will grow.

C. Snowflake schema

A snowflake schema normalises dimensions. Instead of storing Category and Subcategory in DimProduct, it may use DimProduct → DimSubcategory → DimCategory.

Advantages: It reduces repetition within dimensions and can mirror a normalised source database or independently managed hierarchy.

Disadvantages: Extra tables and relationships increase model complexity. Filters cross more paths, fields are harder to find and DAX becomes less intuitive.

Best use: Large or independently governed hierarchies. For ordinary reporting, small snowflaked dimensions are often flattened in Power Query.

Feature	Flat table	Star schema	Snowflake schema
Structure	One wide table	Central fact with dimensions	Dimensions split further
Redundancy	High	Low and controlled	Lowest
Performance at scale	Often weak	Usually strongest	More traversal required
Complexity	Simple initially	Clear and balanced	Highest

3. Fact tables, dimension tables and grain

A fact table records measurable business events—what happened and how much. FactSales may contain DateKey, CustomerID, ProductID, LocationID, OrderID, Quantity, Cost and SalesAmount. It is usually tall and narrow because transactions grow continuously.

A dimension table provides context used for filtering, grouping and labelling. DimProduct may contain ProductName, Brand and Category; DimCustomer may contain CustomerName and Segment; DimDate may contain Month, Quarter and Year. Dimensions answer who, what, when and where, while facts answer how many and how much.

The grain states exactly what one fact row represents. If FactSales has a grain of one product line per order, an order containing three products creates three rows. Grain must remain consistent. Combining order totals with order-line records can double-count revenue.

Table	Typical contents
FactSales	Foreign keys, Quantity, Discount, Cost, SalesAmount
FactOrders	One row per order and order-level measures
FactTransactions	Debit, Credit, Amount and account keys
DimCustomer	Customer name, segment, city
DimProduct	Product name, brand, category
DimDate	Date, month, quarter, year
DimLocation	Branch, city, region, country

For example, Total Sales = SUM(FactSales[SalesAmount]) can be analysed by any related dimension without copying its descriptive columns into FactSales.

4. Relationships in Power BI

A relationship is a logical connection between columns in two loaded tables. It allows filters and calculations to work across tables without physically combining them.

A primary key uniquely identifies a dimension row. DimCustomer[CustomerID] should contain C001 once.
A foreign key is the matching fact column; FactSales[CustomerID] may contain C001 many times because that customer can purchase repeatedly. Related columns need compatible data types.

Referential integrity means each foreign key has a matching dimension key. If FactSales contains C999 but DimCustomer does not, Power BI may group the transaction under a blank member. Anti joins can help detect such orphan records.

One-to-many (1:*)
One unique dimension value matches many fact rows. One product appears once in DimProduct but can appear in thousands of sales. This is the standard star-schema relationship.
One-to-one (1:1)
Each key occurs once in both tables—for example, one Employee row matched to one EmployeeSecurity row. It can separate sensitive fields, although merging the tables may be simpler when both describe the same entity at the same grain.
Many-to-many (:)
Keys repeat on both sides. Many students take many courses. A direct many-to-many relationship can make totals difficult to interpret, so a bridge table containing unique StudentID–CourseID pairs is often safer. It connects to Students and Courses through two one-to-many relationships.

An active relationship appears as a solid line and operates automatically. An inactive relationship is dotted and must be activated in a measure. If FactSales has OrderDateKey and ShipDateKey linked to DimDate, Order Date may be active while Ship Date is inactive:

Sales by Ship Date =
CALCULATE(
    [Total Sales],
    USERELATIONSHIP(FactSales[ShipDateKey], DimDate[DateKey])
)

5. Filter direction

With single-direction filtering, a filter normally travels from the one-side dimension to the many-side fact. Selecting Electronics in DimProduct identifies the relevant ProductIDs, filters FactSales and recalculates Total Sales. This behaviour is predictable and is the recommended default.

Bidirectional filtering allows filters to travel both ways. It can help in carefully designed bridge-table scenarios, but it should not be enabled simply to make a visual work. Multiple routes between tables can create ambiguous filter paths, unexpected totals, more query work and difficult debugging.

6. Joins in Power Query

A join matches rows from two tables using a common column. In Power Query, select Home → Merge Queries, choose both tables and matching columns, choose a join kind then expand the required columns.

Customers (left)

CustomerID	Name
C1	Amina
C2	Brian
C3	Carol

Orders (right)

OrderID	CustomerID	Amount
O10	C1	500
O11	C1	300
O12	C2	700
O13	C4	200

C3 has no order, while O13 refers to a missing customer. This reveals how each join treats unmatched records.

Left outer join

Keeps every customer and matching orders. Amina appears twice; Carol remains with null order values.

CustomerID	Name	OrderID	Amount
C1	Amina	O10	500
C1	Amina	O11	300
C2	Brian	O12	700
C3	Carol	null	null

Use it when Customers is the master list and no customer should disappear.

Right outer join

Keeps every order and matching customers. O13 remains with a null customer name.

CustomerID	Name	OrderID	Amount
C1	Amina	O10	500
C1	Amina	O11	300
C2	Brian	O12	700
C4	null	O13	200

Use it when every transaction must remain. Reversing the tables and using Left Outer gives the same logic.

Full outer join

Keeps all matches and unmatched rows from both tables. The output contains the three valid order matches, C3 with null order fields and O13/C4 with a null name. It is useful for reconciliation, although it may create many nulls.

Inner join

Keeps only keys found in both tables: C1–O10, C1–O11 and C2–O12. C3 and O13 are removed. Use it when analysis requires valid matches only, but check row counts because unmatched data is silently excluded.

Left anti join

Keeps only left-table rows without a right-table match. The result is C3–Carol. It can identify customers who have never purchased, missing submissions or unused master records.

Right anti join

Keeps only right-table rows without a left-table match. The result is O13–C4–200. It is valuable for identifying orphan transactions or broken foreign keys.

7. Power Query merge versus model relationship

Question	Power Query merge	Model relationship
Stage	Data preparation before loading	Model view after loading
Effect	Physically adds matching data to a query	Logically connects separate tables
Purpose	Enrichment, consolidation, reconciliation	Filtering and analysis
Structure	Can create a wider table	Preserves facts and dimensions

Suppose FactSales has one million rows and DimProduct contains ProductName and Category. Merging repeats those descriptions across many transactions. A relationship stores each product once and uses ProductID to filter FactSales. This is generally cleaner and more efficient.

A merge is appropriate when creating a dimension from several sources, adding a small lookup required before loading, consolidating datasets or using anti joins for quality checks. Excessive merging can recreate a wide flat table and weaken the model.

8. Recommended model

For a typical BI project, I would use a star schema. I would define the grain first, create dimensions with unique keys and keep transactional measures in fact tables. Relationships would normally be one-to-many with single-direction filtering from dimensions to facts.

I would use inactive relationships for alternative dates, bridge tables for genuine many-to-many scenarios and bidirectional filtering only when the business requirement and path are clear. Power Query merges would prepare data rather than flatten the entire model.

This design improves compression, query performance, DAX simplicity, readability, scalability and maintenance. Most importantly, every table has a clear responsibility. Dashboards are what users see but a trustworthy model is what makes their answers correct.

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

Jack Kisutsa — Sun, 06 Sep 2026 00:10:57 +0000

1. Project Introduction and Objective

In this project, I used Microsoft Excel and Power Query to clean and analyze a Jumia product dataset and then built an interactive dashboard to summarize pricing, discounts, ratings and customer engagement.

The main objective was to turn a small raw e-commerce dataset into useful business information. I wanted the final dashboard to answer practical questions such as:

Do products with higher discounts receive more customer engagement?
Do higher priced products have better ratings?
Is there a relationship between product rating and number of reviews?
Which products have the highest review engagement?
Which products may require further investigation because they have high discounts but low ratings?

The project also gave me practical experience in data cleaning, excel formulas, PivotTables, PivotCharts, slicers, correlation analysis and dashboard design.

2. Dataset and Business Questions

The original dataset contained 115 rows and 6 columns:

Product
Current price
Old price
Discount
Review
Rating

The dataset was small but it contained several realistic data quality problems. This made it useful for me to practice the complete analytics process rather than going directly to visualization.

I structured the workbook into the following sheets:

Raw_Data
Cleaned_Data
Analysis
Pivot_Tables
Dashboard
Data_Dictionary

As we have always been taught in class,I kept the Raw_Data sheet unchanged so that I always have a copy of the original source data.

3. Initial Data-Quality Audit

Before cleaning the data, I profiled the dataset in Power Query
using Column Quality, Column Distribution and Column Profile.

The audit identified several issues:

Data-quality check	Result
Original rows	115
Original columns	6
Blank Review values	58
Blank Rating values	58
Populated Review values stored as negative numbers	57
Current Price ranges	1
Old Price ranges	1
Exact duplicate rows removed	3
Discount values outside 0 to 100%	0
Rating values outside 0 to 5 after cleaning	0

One unusual issue was the Review field. All populated review counts were negative, even though a review count cannot logically be negative.
I therefore treated the negative sign as a data collection or scraping issue rather than a genuine business value.

The Rating field also required cleaning because values were stored as text such as:

4.5 out of 5
rather than as numeric ratings.

This audit was important because it prevented incorrect calculations later in the project.

4. Cleaning and Preparation Decisions

I performed the main cleaning steps in Power Query.

Product names

I applied Trim and Clean to remove unnecessary spaces and non-printable characters. Repeated product names were not automatically deleted because two rows with the same product name are not necessarily duplicates.

Current and old prices

The price fields contained KSh and commas so I removed the currency text and converted the fields to numeric values.

I notieced that two records contained price ranges rather than single prices:

Current Price: KSh 1,620 - KSh 1,980
Old Price: KSh 2,200 - KSh 3,200

I used the midpoint of each range:
Current Price midpoint = KSh 1,800
Old Price midpoint = KSh 2,700

This decision was documented in the Data Dictionary.

Discount

The Discount field was already interpreted correctly as a percentage.
The cleaned values ranged from 1% to 64%, so I did not divide the field by 100 or remove its percentage data type.

Reviews

The populated Review values were negative. I used Absolute Value in Power Query to convert them to valid positive counts.

The missing reviews remained blank. I deliberately did not convert missing reviews to zero because a blank means that the review information is unavailable, while zero would mean that the product definitely had no reviews.

Ratings

I renamed Ratingd to Rating, removed the text out of 5 and
converted the result to a decimal number.

The final valid ratings ranged from 2.0 to 5.0.

Duplicate records

I checked duplicates using the analytical fields:

Product
Current Price
Old Price
Discount
Review
Rating

Three redundant duplicate rows were removed, reducing the dataset from 115 to 112 products/records.

After cleaning:

112 records remained.
57 had complete Rating and Review information.
55 were missing both Rating and Review.
There were 0 Power Query errors.

5. Excel Formulas and Enrichment Fields

After cleaning the source fields in Power Query, I used Excel formulas to create analytical categories and flags. The cleaned Excel table was named tblProducts.

Discount Amount

I calculated the difference between Old Price and Current Price:

=[@[Old Price]]-[@[Current Price]]

Rating Category

I grouped ratings into three categories while keeping missing values separate:

=IF([@Rating]="","Missing",IF([@Rating]<3,"Poor",IF([@Rating]<=4.5,"Average","Excellent")))

The categories were:

Poor: below 3
Average: 3 to 4.5
Excellent: above 4.5
Missing: no rating available

Discount Category

I grouped discounts into Low, Medium, and High:

=IF([@Discount]="","Missing",IF([@Discount]<20%,"Low Discount",IF([@Discount]<=40%,"Medium Discount","High Discount")))

The thresholds were:

Low Discount: below 20%
Medium Discount: 20%--40%
High Discount: above 40%

Price Category

I calculated the first and third quartiles of Current Price:

=QUARTILE.INC(tblProducts[Current Price],1)
Result: **KSh 493**

=QUARTILE.INC(tblProducts[Current Price],3)
Result: **KSh 1,669.50**

I then categorized each product:

=IF([@[Current Price]]<=Price_Q1,"Low Price",IF([@[Current Price]]<=Price_Q3,"Medium Price","High Price"))

Engagement Flag

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

=QUARTILE.INC(tblProducts[Review],3)

The result was 14 reviews.

The engagement formula was:

=IF([@Review]="","Missing",IF([@Review]>=Review_P75,"High Engagement","Low Engagement"))

Therefore:

High Engagement: 14 reviews or more
Low Engagement: fewer than 14 reviews
Missing: no Review value

Performance Flags

I created three Yes/No flags:

High Rating + High Engagement

=IF(AND([@[Rating Category]]="Excellent",[@[Engagement Flag]]="High Engagement"),"Yes","No")

High Discount + Low Rating

=IF(AND([@[Discount Category]]="High Discount",[@[Rating Category]]="Poor"),"Yes","No")

High Discount + High Rating

=IF(AND([@[Discount Category]]="High Discount",[@[Rating Category]]="Excellent"),"Yes","No")

These fields helped turn individual rows into business segments that could be summarized using PivotTables.

6. PivotTable and Analysis Workflow

I used both formulas and PivotTables for analysis.
The overall cleaned dataset had:

112 products
Average Current Price: KSh 1,186.89
Average Discount: 37%
Average Rating: 3.89
Average Reviews: 13
Discount analysis

Discount Analysis

The Discount Category PivotTable produced:

Discount Category	Products	Average Discount	Average Reviews
Low Discount	19	8%	10
Medium Discount	31	31%	15
High Discount	62	48%	11

Medium-discount products recorded the highest average review count.

Price analysis

Price Category	Products	Avg Current Price	Avg Discount	Avg Reviews	Avg Rating
Low Price	28	KSh 284.75	48%	17	3.64
Medium Price	56	KSh 1,073.84	36%	10	3.88
High Price	28	KSh 2,315.14	27%	13	4.08

Low-priced products had the highest average review engagement while high-priced products had the highest average rating.

Rating and engagement

Among the 57 products with complete Review and Rating data:

Rating Category	High Engagement	Low Engagement	Total
Poor	4	8	12
Average	6	20	26
Total	16	41	57

I also calculated the Pearson correlation between Rating and Review:

=CORREL(tblProducts[Rating],tblProducts[Review])

The result was approximately 0.06, showing almost no linear relationship between rating and review count.

Performance flags

The analysis identified:

6 High Rating + High Engagement products
10 High Discount + Low Rating products
8 High Discount + High Rating products

Because these are independent flags, the counts should not simply be added together and interpreted as unique products.

7. Dashboard Design and Slicer Connections

After completing the analysis, I created a one-page dashboard designed to provide a quick summary of product performance.

The dashboard included five KPI cards:

Total Products: 112
Average Price: KSh 1,186.89
Average Discount: 37%
Average Rating: 3.89
Total Customer Reviews: 723

I created nine charts:

Top 10 Products by Ratings
Top 10 Products by Review Count
Top 10 Products by Discount Percentage
Discount vs Reviews
Rating vs Reviews
Price vs Rating
Rating mix
Discount mix
Engagement by Discount

The first two charts summarize category-level patterns. The scatter plotshows the weak relationship between ratings and reviews while the Top 10 chart identifies products with the strongest observed review
engagement.

Rating vs Review scatter plot

The scatter plot used Rating on the X-axis and Review Count on the Y-axis. I added a linear trendline and displayed the R-squared value.

The trendline is almost flat and the chart shows R² ≈ 0.0033, which supports the correlation result of approximately 0.06.

Top 10 products by review count

A horizontal bar chart was used because product names were long and easier to read in this format.

Review count was treated as an engagement indicator, not as evidence of sales.

Slicers

I added three slicers:

Rating Category
Price Category
Discount Category

The slicers were connected to compatible PivotTables through Report Connections/PivotTable Connections. This allows a user to select a category and dynamically explore different product segments.

The scatter plot was created as a normal Excel XY chart rather than a PivotChart, so it remains an overall view and is not controlled directly by the PivotTable slicers.

Final dashboard

The dashboard was arranged so that the viewer sees the KPIs first, followed by the charts, slicers and business insights.

8. Key Findings

Finding 1: Medium discounts recorded the strongest average review engagement

Evidence: Medium-discount products averaged 15 reviews, compared with 11 for high-discount products and 10 for low-discount products.

Meaning: The deepest discounts were not associated with the highest review engagement in this dataset.

Action: Sellers could test medium and high discount bands and
compare engagement before assuming that deeper discounting produces better product response.

Caveat: Review count is an engagement proxy. The dataset does not include sales or listing age, so this relationship should not be interpreted as causal.

Finding 2: Average rating increased across price categories

Evidence: Average rating increased from 3.64 for low-priced products, to 3.88 for medium-priced products and 4.08 for high-priced products.

Meaning: Higher-priced products in this dataset tended to have stronger average customer ratings.

Action: Sellers could investigate the product quality, features,brands or listing characteristics associated with highly rated high-priced products.

Caveat: This is an association. The analysis does not prove that increasing a product's price would improve its rating.

Finding 3: Rating and review engagement had almost no linear relationship

Evidence: The correlation between Rating and Review was
approximately r = 0.06, with R² ≈ 0.0033.

Meaning: A highly rated product does not necessarily receive a high number of reviews.

Action: Sellers should consider both rating quality and review engagement when evaluating product performance rather than relying on rating alone.

Caveat: Only 57 products had complete Rating and Review
information and review count is not a direct measure of sales.

Finding 4: Performance flags identified both opportunities and risks

Evidence: The analysis found 6 high-rating/high-engagement products, 10 high-discount/low-rating products and 8 high-discount/high-rating products.

Meaning: Some products appear to be strong engagement candidates, while others may require investigation because heavy discounting is occurring alongside weak ratings.

Action: Sellers could investigate the 6 high-rating/high-engagement products for promotional opportunities and review quality or listing issues among the 10 high-discount/low-rating products before increasing promotional support.

Caveat: These flags are screening tools. The dataset does not
contain revenue, units sold, margin, advertising spend, or profitability data, so the flagged products cannot automatically be classified as commercial winners or failures.

9. Business Recommendations

Based on the completed analysis, I would recommend the following
actions:

Test discount bands rather than automatically increasing discounts. Medium-discount products had higher average review engagement than high-discount products.
Investigate highly rated, higher-priced products. Understanding what these products have in common may provide useful lessons for product positioning and listing quality.
Use rating and engagement together. The weak correlation shows that a high rating alone does not guarantee strong review activity.
Review high-discount/low-rating products. These products may require investigation into quality, expectations, listing content, or promotional strategy.
Study high-rating/high-engagement products. These products can be investigated further as possible candidates for targeted visibility or promotional tests.

These recommendations are deliberately limited to what the dataset supports.

10. Limitations and Lessons Learned

Limitations
The dataset had substantial missing feedback data. Out of 112 cleaned records, only 57 had both Rating and Review information. This reduces the amount of data available for analyses involving customer feedback.

The project is also observational. Patterns such as higher ratings among high-priced products or stronger engagement among medium-discount products show association, not causation.

Lessons learned

This project reinforced several lessons for me.

First, data cleaning is part of analysis, not a separate optional step. If I had used the original negative Review values or text-based ratings directly, the final analysis would have been misleading.

Second, I learned the importance of documenting assumptions. Converting price ranges to midpoints, using 14 reviews as the high-engagement threshold and defining category boundaries all affect the results and therefore need to be transparent.

Third, you can use PivotTables to summarize data and also create an interactive dashboard. I also found out that PivotCharts rely on the PivotTables they get their data from. This means you can create separate PivotTables from the same data source for your charts. That way, a chart on your dashboard won't accidentally update a detailed analytical PivotTable.

Fourth, a dashboard should not contain every calculation. The Analysis and Pivot_Tables sheets can hold detailed work while the Dashboard should focus on the KPIs, charts, filters and insights that help a user understand the data quickly.

Finally, I learned to be careful with business language. A correlation does not prove causation, reviews do not equal sales and a flagged product is a starting point for investigation rather than a final business conclusion.

Conclusion

This project took a Jumia product dataset through the complete Excel analytics workflow: audit, cleaning, enrichment, analysis,visualization and business interpretation.

Starting with 115 raw records, I used Power Query to resolve price formatting, rating text, negative review values, missing information and duplicate records. The final cleaned dataset contained 112 records. I then used Excel formulas to create price, discount, rating, engagement and performance categories before summarizing the results through PivotTables and charts.

The final interactive dashboard provides a concise view of product pricing, discounts, ratings and customer engagement while still recognizing the limitations of the available data.

For me, the main lesson was that building a useful dashboard is not only about creating attractive charts. The quality of the final dashboard depends on the decisions made before visualization: how the data is cleaned, how metrics are defined, how assumptions are documented and how carefully the findings are interpreted.

Getting Started With Excel For Data Analytics: From Basics To Data Cleaning

Jack Kisutsa — Sun, 30 Aug 2026 15:30:38 +0000

Introduction

Excel is a pretty straightforward tool for anyone starting out with data. You can input, organize, calculate and look at information without needing to write any code. However, just having data in Excel doesn't automatically make it usable. Real-world datasets often have gaps, repeated information, spelling mistakes, and other small issues that need fixing before you can really use the data.

For this task, I worked with a file named HR_Dataset_Dirty.xlsx. It had 876 employee records with details like Employee ID, Name, Department, Salary, Hire Date, Age, Gender and Performance Score. This article explains the basic Excel features I learned and how I used them to clean up this data, making it ready for analysis.

Understanding the Basics

A file in Excel is called a workbook and each sheet within it is a worksheet. A worksheet is made up of rows, numbered down the side, and columns, labeled across the top. The point where a row and column intersect is a cell; for instance, cell A2 might contain an employee's ID number.

Usually, the very first row of a dataset lists the column titles, explaining what kind of information is in each column below. In my dataset, some columns contained numbers (like Salary and Age), some had dates (like Hire Date), and others had text categories (like Department and Gender). Excel handles these types differently. You can perform calculations like sums or averages on numbers, but if a number is accidentally saved as text, those calculations won't work correctly.

Before I made any changes, my first step was to look over the raw data so I could get a sense of what I was dealing with.

A section of the raw dataset, with blank cells highlighted in red and duplicate Employee IDs highlighted in yellow.

Spotting the Problems

Looking through the raw data, I noticed a few problems right away:

Some cells were blank — missing an age, a salary or a gender.
Some Employee IDs appeared more than once meaning the same employee had been entered twice.
The same category was spelled differently in different rows — for example, "HR," "Hr," and "Human Resources" all meant the same department, but Excel would treat them as three separate values.
Some dates didn't make sense, like a hire date of 2020/13/05 — there's no 13th month.

I used Excel's Conditional Formatting (Home tab → Conditional Formatting) to highlight these problems directly on the sheet, so I could see exactly which cells needed attention before touching anything.

Handling Missing Values

Instead of guessing what a missing value should be, I used Excel's Filter feature to check each column for blanks. For example, filtering the Salary column showed me exactly which employees had no salary recorded.

We were told in class that it's important not to just replace every blank with a zero — a missing salary doesn't mean the employee earns nothing, it just means the information wasn't captured. I left genuinely missing values as blank rather than guessing a number.

Removing Duplicates

I looked for employee IDs that showed up more than once because each ID should be unique. Some, like 10407 and 10870 appeared twice and had the same details.

Once I made sure these were actual duplicates and not two different employees with similar IDs, I used Excel's Remove Duplicates feature from the Data tab to get rid of them. This process removed 23 duplicate rows from the data.

Cleaning Text

When i cheched the data some text entries had too many spaces or the capitalization was all over the place; for instance, some names were just in lowercase. Excel has a couple of easy functions for fixing this:

TRIM() removes extra spaces from text.
PROPER() changes text to proper case, so "grace karanja" becomes "Grace Karanja."

I used these on the Name and Location columns to get everything looking the same.

Standardizing Categories

I also noticed that the Department column had the same problem. There were several spellings for the same department like "Markting" and "Marketing," or "HR" and "Human Resources." Since these functions don't fix spelling on their own, I mapped each incorrect spelling to its correct version, so that every version of a department name became one standard value.

Cleaning formulas correcting names, departments, and other text fields, with anything still needing a manual check flagged in red.

Fixing Dates and Numbers

I also had to check the Hire Date field too. The goal was to make sure each entry was a valid date and to flag any that weren't, like the incorrect 2020/13/05 instead of trying to guess what they should have been.

The Age field had a similar problem. One entry was the word "thirty" instead of the number 30. Since Excel can't use text in calculations, I flagged entries like this so they could be reviewed, rather than letting them cause errors in later calculations.

Preparing the Clean Dataset

Once every column had been checked and corrected, I copied the results into a new sheet as plain values, so the final table didn't depend on the raw data anymore. I also added a dropdown list to the Department column (Data tab → Data Validation) so that only the correct department names can be entered from now on.

The final cleaned dataset — 852 unique employees remain after removing duplicates, with a Department dropdown to keep future entries consistent.

A Quick Summary

With the data cleaned, I used COUNTIF() and AVERAGEIF() to summarise employees by department and built a simple chart from that summary to see the results at a glance.

A quick summary of headcount and average salary by department, built from the cleaned data.

Conclusion

I learned from this exercise that before you even get to charts or dashboards, you first need to make sure the data itself is reliable. Things like missing information, duplicate entries, different spellings for the same thing or incorrect dates can all mess up your analysis if you don't find and fix them early on.

Turning a messy raw dataset into a clean and consistent one really showed me how crucial this initial step is. It also showed me that some basic tools in Excel like Conditional Formatting, Filter, Remove Duplicates, TRIM, PROPER and Data Validation are all you really need to do it.

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

Jack Kisutsa — Sun, 23 Aug 2026 09:41:03 +0000

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

Introduction

This week I learned how to use Git, Git Bash, GitHub and setting up SSH Keys. Before this, I knew about GitHub but I did not really understand how a project moves from a folder on my computer to GitHub.

A simple way I now understand the relationship is:

Git manages the history of my project while GitHub provides an online home for the project.

In this article, I will explain the steps I followed to create a simple project locally and push it to GitHub.

Creating My Project

I started by creating a folder for my project using Git Bash.

mkdir Kenya-Hospital-Records-Analysis
cd Kenya-Hospital-Records-Analysis

Inside the folder, I created a README.md file and a folder called data.

My project looked like this:

Kenya-Hospital-Records-Analysis/
├── README.md
└── data/

The README.md file is where I can explain what my project is about while the data folder can be used to store datasets.

In the data folder i uploaded an excel file called Kenyan_Hospital_Health_Records

How to use Git

The next step was to make Git start tracking my project. I did this using:

git init

I then used:

git status

This helped me see which files Git was tracking and which files had not yet been added.

To add my files, I used:

git add .

I then saved the changes to Git using a commit:

git commit -m "Initial commit"

One thing I learned is that a commit is like saving a checkpoint of my project. The commit message helps explain what changes I made.

Connecting Git to GitHub while generating SSH Keys

To push my local project to GitHub, I needed a secure way for my computer to communicate with my GitHub account.

I used SSH (Secure Shell).

I first generated an SSH key on my computer and then added the public key to my GitHub account.

An SSH key normally consists of two parts:

Private key – stays securely on my computer.
Public key – can be added to GitHub.

One important lesson was that the private key should never be shared.

After configuring my SSH key, I tested the connection using:

ssh -T git@github.com

A successful authentication confirmed that my computer could communicate securely with GitHub.

Connecting to GitHub Using SSH Keys

I also learned how to connect Git on my computer to my GitHub account using an SSH key.

After creating the SSH key, I added the public key to my GitHub account. I could then test whether the connection was working using:

ssh -T git@github.com

This was interesting because I learned that SSH allows my computer to communicate securely with GitHub without entering my GitHub password every time I push my work.

Pushing My Project to GitHub

After creating a repository on GitHub, I connected my local project to it.

git remote add origin git@github.com:username/Kenya-Hospital-Records-Analysis.git

I then pushed my project to GitHub using:

git push -u origin main

After refreshing my GitHub repository, I could see the files I had created on my computer now appearing on GitHub.

What I Learned

The biggest thing I learned from this exercise is that Git and GitHub are different but work together.

Git helps me track changes to my project on my computer, while GitHub allows me to store and share the project online.

I also now understand the basic Git workflow better:

Create or change files
        ↓
git add
        ↓
git commit
        ↓
git push
        ↓
GitHub

At first, the commands looked confusing but after using them practically, I started to understand what each command was doing.

Conclusion

This was my first time taking a project from a local folder on my computer and pushing it to GitHub using Git and SSH.

I am still learning Git and GitHub but I can now create a project, track my files, make commits and push my work to GitHub.