DEV Community: Brian Mugo

From Messy Rows to Management Decisions: Building a Power BI Solution for JCars Logistics

Brian Mugo — Sun, 27 Sep 2026 13:21:26 +0000

JCars Logistics sells and delivers vehicles across Kenya. Someone handed me one flat CSV, 276 rows and 32 columns, and asked for three things: an executive dashboard, a detailed report, and recommendations backed by the data.
The dataset had been deliberately messed up. No column could be trusted at face value, and that turned out to be the actual point of the exercise.
This is the story of how it went, in the order it actually happened: understand, audit, clean, validate, model, calculate, visualise, and investigate.

Figuring out what a row even means

Before touching anything, I had to answer one question: what does a single row represent?
It turned out to be one sales order, not one vehicle. Units sold ranged from 1 to 5 on a single row, which means a customer could buy several units of the same car in one transaction, and the file never broke that down into individual line items.
That single fact shaped a lot of later decisions. COUNTROWS tells you how many orders happened. SUM(Units Sold) tells you how many cars were sold. Mix those two up, and every number downstream is quietly wrong.

Auditing before touching anything

I went column by column looking for missing values, inconsistent formatting, mismatched categories, and business rule violations. A few findings jumped out right away:
Order ID, the supposed primary key, had 19 blanks and 3 duplicates. Sales Rep names appeared 30 different ways for only 10 actual people (double spaces, first name only entries, you name it). Monetary columns mixed four currencies (KES, USD, EUR, ZAR), sometimes with symbols, sometimes with codes, sometimes with nothing at all. Dates arrived in at least seven distinct formats, including Excel serial numbers, and one outright invalid value (2026-13-04) that showed up identically on five unrelated orders.

Currency

The assessment gave one rule for free: unmarked values are KES. Everything else had to be converted using a documented, fixed set of rates (USD = 130, EUR = 150, ZAR = 7.5 KES).
Rather than trust the conversion blindly, I validated it. For every foreign currency price, I checked whether Units × Price × (1 − Discount) + Fee matched the row's own recorded revenue. 13 of 17 tied out within half a percent, which was solid enough evidence that both the rates and my currency detection were reasonable.
That same validation technique caught something I did not expect: two cells that were created by my own cleaning logic. A raw unit cost of "0.0M" came out the other side as a flat 1,000,000, not because of anything in the data, but because a multiplication step defaulted to the exchange rate itself when the input was blank. Same story for a logistics cost that became 8 instead of roughly 44,000, because the currency strip logic handled "R" but never handled "ZAR". Both got fixed once the pattern was understood. Both are the kind of bug that's invisible unless you specifically go looking for it.

Reconstructing what looked unrecoverable

19 missing order IDs looked like a dead end. Then I noticed something about the raw file: valid IDs ran in strict sequence, 1000 plus row position, and this held for 254 of 257 of them. That meant every missing ID, and even the 3 wrong duplicates, could be rebuilt with confidence from row order alone.
There was one condition for this to work. Row order had to survive untouched until the ID was generated, which meant adding an Index column immediately after headers were promoted, before a later sort step in the query would have destroyed it.
Dates got a similar treatment. Ambiguous DD/MM vs MM/DD values were resolved not by guessing, but by testing which reading produced a plausible delivery lag (0 to 60 days) against the paired date. Where neither reading worked, I did not force one. Those rows got flagged for review instead.

Deciding what not to fix

Not every irregularity has a defensible fix. Two selling prices sat at roughly 10 times the normal price for their model. One delivery fee was 5 times the next highest genuine value. I had no independent way to confirm what the correct values should have been, so instead of guessing, I left them as recorded and built a flag column to mark them for exclusion from price sensitive analysis.
The same approach was applied to a handful of negative discounts that had been silently stripped of their sign during cleaning. One of them reconciled cleanly against its own recorded revenue once the negative sign was restored. The rest did not reconcile under either sign, so they stayed flagged rather than corrected.
This turned into a single Conflict Flag column threading through the whole model. Every unresolved judgement call lives in one place, so later DAX measures can deliberately include or exclude those rows instead of silently inheriting whatever the raw data happened to say.

Validation

Cleaning data does not make it correct. I tested every order's recorded revenue against what its own components implied. 140 of 202 testable rows tied out within half a per cent.
The remaining 62 did not, and they failed in some clean, explainable patterns: exactly half, exactly double, exactly 10% off. That points to a systematic error somewhere rather than 62 unrelated mistakes, but not one I could pin down from the data alone. So that uncertainty is now a documented, flagged part of the model. Not something quietly resolved one way or the other.

Modelling

The temptation with a flat file is to build a flat report. Instead, I split it into a proper star schema: one fact table (Fact_Orders, at order grain) and eight dimension tables (Customer, Sales Rep, Vehicle, Branch, Payment, Delivery Status, Lead Source, Date).
The test I used for every column was simple: does this stay the same when I filter down to one entity, or does it vary?
Unit cost looked like it might belong on the vehicle dimension until I checked. Every single Prado TX order in the dataset had a different unit cost. That is not a vehicle attribute; that is a transaction-level fact, and it stayed on Fact_Orders. The same check ruled out a "Costs" dimension and a "Feedback" dimension entirely.
Dates got special handling: one Dim_Date table, related to Order Date as the active relationship and to Delivery Date as an inactive one, switched on inside specific measures via USERELATIONSHIP whenever delivery based trends were actually needed.

DAX

Thirty plus measures later, I went back and specifically re-audited every one for a subtle class of bug: do the numerator and denominator actually draw from the same set of rows?
Total Cost failed this test. It was written as SUMX(Fact_Orders, Units Sold × Unit Cost). Looks correct, but it summed costs across every row that had a cost value, while realised revenue summed across a different set of rows that had a revenue value. 40 rows contributed real cost with zero matching revenue, dragging gross profit into negative territory in aggregate, even though individual transaction margins were fine.
The fix was to filter Total Cost down to only rows with usable revenue on the same row. The same discipline I had already applied to the cleaning phase, just now applied to the calculation layer.
Two smaller measures had the identical issue (Average Order Value, Logistics Cost % of Revenue). Caught the same way, by explicitly checking row set alignment rather than trusting that a measure "looked right" because the syntax was valid.

The dashboard and deciding what not to show

The Executive Dashboard ended up as five KPIs, a trend, a branch comparison, and a deliberate "requires attention" row. Not an attempt to cram every measure onto one page. Everything else lives on five detail pages, reachable via drill-through from a branch bar or a flagged order straight into an Investigation page built specifically around the Conflict Flag column.
What this project actually tested
Not whether I could make a chart. Whether I could tell the difference between a value that's wrong, a value that's merely unusual, and a value that's genuinely unknowable from the data available, and build a model honest enough to keep those three categories distinct all the way through to the dashboard.
Rather than quietly picking one interpretation and hiding the uncertainty.

The whole project has been uploaded and you can view and follow it up on my github Github project.

POWER BI: Data Modelling, Relationships & Joins

Brian Mugo — Tue, 15 Sep 2026 09:30:25 +0000

Building a good Power BI report is not just about visuals; anyone can drag a pie chart or a column bar chart onto a report. What really separates a working report from one that fails often lies beneath the visuals, and that is the data model. This article walks through the four pillars of Power BI data design: data modelling schemas, fact and dimension tables, relationships and filter direction, and joins in Power Query.

Data Modelling in Power BI

Data modelling is the process of organising the tables in your dataset, defining how they connect, and shaping them so that analysis becomes natural rather than forced. This usually happens in two places: Power Query, where raw data is cleaned and shaped before loading, and the Model view in Power BI Desktop, where tables are linked by relationships.

This matters for a few very practical reasons:
Reporting accuracy. A poorly structured model makes it easy to double count values or filter incorrectly without realising it.

DAX simplicity. Measures written against a clean model are short and easy to reason about. Measures written against a messy model often need extra logic just to compensate for the structure.

Performance. Power BI's engine compresses and stores data column by column. A model with fewer, narrower, well structured tables compresses better and queries faster than one wide table full of repeated text.

Scalability. A model that works with fifty thousand rows today needs to still work when it grows to five million rows next year. Structure is what allows that growth without a rebuild.

Maintainability. When a business rule changes (say, a product category gets renamed), you want to update it in one place, not chase it through thousands of duplicated rows.

Analysis. Clear relationships between tables mean you write simpler formulas and avoid messy, massive joins which make analysis of data simpler and faster.

I will compare three modelling approaches in this article:
Flat Table
Star Schema
Snowflake Schema

Flat Table

This is a single, wide table that holds everything, such as customer_id, product_id, date and price, etc., all side by side in one table with the rows repeating whatever information it belongs to.

Advantages

It is simple to understand and build for a one-off report.
There are no DAX calculations needed for basic slicing.
No relationships to manage or configure.

Disadvantages

Deteriorating performance as the table grows into maybe millions of rows.
Massive repetition of customer names and city as they are repeated on every single line for that customer.
Harder to maintain, especially if a detail changes, since it may need to be changed across thousands of rows.
No clear home for shared logic, such as a calendar, and no natural way to handle facts of different grains.

This table is appropriate in the following scenarios: tiny datasets, quick proofs of concept, or single-purpose extracts. It is rarely appropriate for a real business intelligence solution.

A flat table looks simple but scales badly. Power BI's compression engine works best on narrow, repetitive columns, and a flat table full of repeated text values fights against that. There is also no natural way to build clean relationships later without going back and rebuilding the data.

Star Schema

The model places one central fact table in the middle, surrounded by dimension tables, each connected directly to the fact table. When sketched, it looks like a star, hence the name.

The table is structured in a way that each dimension holds descriptive attributes and its own primary key. Every dimension connects straight to the fact table with a one-to-many relationship.

Advantages

Easy for business users to read and navigate.
Very DAX friendly since filters travel in a short, predictable path.
Scales well as new dimensions or facts slot in cleanly.
Compresses and performs well, since dimensions hold the repeated text and the fact table holds mostly numbers and keys.

Disadvantages

Requires some upfront design work to separate facts from dimensions correctly.
Some duplication still exists inside dimension tables (for example, product name and category repeating per product).

When it is appropriate: This is the default recommended approach for the vast majority of Power BI projects, from small departmental reports to large enterprise models.

Star schemas generally align well with how Power BI's analytical engine works, making them a strong default for reporting models.

Snowflake Schema

This is a star schema taken one step further where the dimension tables are split further into smaller related tables. A product dimension might be broken into product, subcategory and category tables.

Structure. A fact table connected to dimension tables, some of which are further connected to smaller sub-dimension tables, forming a branching, snowflake-like shape.

Advantages

Reduces duplication even further than a star schema, since shared attributes (like category names) live in one place.
Can mirror a normalised source database closely, which is sometimes convenient during a migration
Attributes are grouped logically, which can suit very large attribute sets

Disadvantages

More tables and relationships to manage, hence a harder navigation
More complex for report authors and also slightly heavier DAX in some cases
The model view starts to look cluttered, which hurts readability for the next developer.
Can hurt performance if overused, because Power BI has to resolve more relationship hops.

This type of table is appropriate when a dimension is large and shared across many other dimensions or when strict normalisation is a hard requirement.

Fact Tables and Dimension Tables

After committing to one schema (star or snowflake) everything now depends on correctly telling facts and dimensions apart.

Fact Table

These store measurable business events; they are the things that happened: sales, orders, and shipments. Facts are the verbs of the business: what happened, how much, and how many. These tables typically contain:
a) Foreign keys pointing to the relevant dimension tables (productid, customerid, etc.)
b) Measures which are numeric values you want to sum, average or count, such as cost, quantity or revenue.
They are usually long and narrow; they can have millions of rows but few columns.
An important concept is the grain, also referred to as granularity. This is what one row represents. It might be one row per order line, one row per individual transaction, or one row per day per product. Getting the grain wrong, or mixing grains in the same table, is one of the most common causes of confusing totals in a Power BI report. Every measure you write should be interpreted correctly at the grain the table was built at. Examples of fact tables include FactSales, FactOrders and FactTransactions.

Dimension Table

A dimension table stores the descriptive attributes that give the fact table its context and meaning. These are the who, what, where and when of the business event. Dimension tables typically contain:
a) A primary key that uniquely identifies each row (CustomerID, ProductID, DateKey).
b) Descriptive attributes, such as names, categories, regions and other text or classification fields that do not change with every transaction.
They are usually short and wide compared to fact tables and may have few rows but very many columns.
Common examples of dimension tables include DimCustomer, DimProduct, DimDate and DimLocation.

For example
Picture a retail business selling products to customers across different cities. Each sale is linked to a customer, a product, a date, and a store, while the Sales Fact table records the actual transaction. The surrounding dimension tables then provide the context needed to analyse those sales by customer, product, time, and location.

The image shows a retail star schema with the Sales Fact table at the centre. It connects to four dimension tables: Date Dimension, Customer Dimension, Store Dimension, and Product Dimension, which provide the details needed to understand each sale by customer, product, time, and location.

Relationships in Power BI

Real business data lives in separate tables. Sales may be in one system, customers in another and products in the third. A relationship in Power BI is a connection link between two tables based on a shared column, which tells Power BI to match rows in one table to rows in another table. Without relationships we would have to merge everything in a single table, which is exactly what we want to avoid. Also selecting a product category would have no effect on the sales number at all. The relationship is what makes filtering, aggregating, and cross-table DAX calculations possible in the first place.

Relationships are in cardinalities. Cardinality describes the shape of the relationship, specifically how many matching rows exist on each side.

One-to-Many (1:*). This is the most common relationship. One row on the dimension side matches many rows on the fact side. One customer places many orders; one product appears on many order lines. The primary key sits on one side, the foreign key on the other.

One-to-One (1:1). Each row on one side matches exactly one row on the other, typically because two tables describe the same entity, such as employee and person, country and country flag, or employee and employee parking pass. It is used sparingly, usually when a set of columns has been deliberately split into a second table for organisational or security reasons. If the two tables could easily be one, then they should be.

Many-to-Many (:). Many rows on one side can match many rows on the other. A student may enrol in many courses, and a course has many students. This is run well in Power BI but should be used carefully since it can lead to unexpectedly duplicated results if the underlying business logic is not well understood. It is safer to introduce a bridging table that breaks it into a one-to-many relationship instead.

Primary Keys, Foreign Keys and Related Concepts

Primary Key. A column (or set of columns) that uniquely identifies each row in a table such as CustomerID is the primary key of DimCustomer. It should uniquely identify each row and should not have blank values.
Foreign Key. A column in one table that references a primary key in another, such as a customerID, can appear in FactSales, but there it is a foreign key and is expected to repeat many times.
Unique Values. A primary key column must contain unique values for a relationship to behave predictably. This is exactly why CustomerID is unique in Dim Customer but appears repeatedly in FactSales.
Cardinality. As covered above, this defines the shape of the match between the two sides of a relationship.
Referential integrity. The assumption that every foreign key value actually exists on the primary side. Orphan keys (a sale pointing to a customer who is not in DimCustomer) cause blank rows or missing matches unless you clean them in Power Query.
Active and inactive relationships. A table pair can have more than one relationship, but only one can be active (the solid line). The others stay inactive (dashed lines) and are only used on demand with DAX functions such as USERELATIONSHIP.

Filter Direction

When a user clicks a slicer, Power BI does not move any data. It sends a filter through the relationship lines to decide which rows of the fact table take part in the calculation. Direction controls where that filter is allowed to go.

Single Direction Filtering

In a standard one-to-many relationship, filters flow from the "one" side to the "many" side by default. This means selecting a value in a dimension table filters the connected fact table, but not the other way around.

Bidirectional Filtering

Filters travel both ways. This enables features such as slicers cross-highlighting each other, and it can make visuals feel interactive. However, it should be used carefully for two main reasons: ambiguous filter paths, i.e., two competing routes from a dimension to a fact table, leaving the engine unsure which to use, which produces warnings and unreliable results. Unnecessary model complexity: Turning on bidirectional filtering everywhere, just in case, makes a model much harder to reason about and can quietly slow down report performance since Power BI now has more filter paths to evaluate for every single visual.

Always leave relationships as single-direction by default, and only switch a specific relationship to bidirectional when there is a clear, tested reason to do so.

Joins in Power Query

A join, inside Power Query, is a way of combining two tables based on matching values in a shared column, using the Merge Queries feature. Unlike a model relationship, a Power Query join actually produces a new, physically combined table as an output of the query.
To demonstrate each join type, imagine two simple tables:

Customers

CustomerID Name
1 Neal
2 Joe
3 Cruz

Orders

OrderID CustomerID Amount
101 1 500
102 1 250
103 4 800
Notice that CustomerID 4 in orders has no match in Customers, and CustomerID 3 (Cruz) in Customers has no matching order. This mismatch is exactly what makes the different join types behave differently.

Inner Join
Keeps only the rows where the join column has a match in both tables. Records retained: Only customers who have at least one matching order and only orders that have a matching customer. Example output: Neal's two orders (CustomerID 1) appear, matched with his name. Cruz and the CustomerID 4 order are both dropped, since neither has a match on the other side

Left Outer Join
Keeps every row from the first (left) table and attaches matching data from the second table where it exists. Records retained: All of the customers, plus matched order details where available. Example output: Neal appears twice (once per order), Joe appears with no order data attached, and Cruz appears with blank order fields, since she has no matching order.

Right Outer Join
The mirror image of a left join. Keeps every row from the second (right) table and attaches matching data from the first table where it exists. Records retained: All of the orders, plus matched customer details where available. Example output: All three order rows appear. The CustomerID 4 order shows up with blank customer fields, since no customer with that ID exists.

Full Outer Join
Keeps every row from both tables, matching them where possible and leaving blanks where there is no match on either side. Records retained: Everything from Customers and everything from Orders combined. Example output: Neal's two orders, Joe with no order, Cruz with no order, and the unmatched CustomerID 4 order, all appear in the same result.

Left Anti-Join
Keeps only the rows from the left table that have no match at all in the right table. Records retained: Customers with zero orders. Example output: Only Cruz appears, since she is the only customer with no matching order.

Right Anti-Join
The mirror image. Keeps only the rows from the right table that have no match in the left table. Records retained: Orders with no matching customer. Example output: Only the CustomerID 4 order appears, since that customer does not exist in the Customers table.
Anti-joins are useful for data quality checks, for example, finding orphaned records or verifying that referential integrity holds before loading data into a star schema.

Power Query Joins vs Power BI Relationships

Beginners often ask why they should bother with relationships when Merge Queries can combine tables. The two operations live at different stages of the workflow and solve different problems.

Merge physically combines tables. Relationships connect tables logically

A Power Query merge physically combines data. Columns from the second table are copied into the first, row by row. If the key repeats, rows multiply, and the result is one wider table loaded into the model. It happens during data preparation, before anything reaches the report.
A model relationship does not combine anything. Tables stay separate in memory, each holding its own data at its own grain. The relationship is a logical line the engine follows at query time to filter and aggregate across tables. It happens after loading, in the Model view.

A merge makes sense when you specifically need a flattened, denormalised table for a particular purpose, for example, preparing a lookup table for a specific export or doing a one-off data quality comparison.
However, merging tables together aggressively, as a habit, slowly turns a clean star schema back into something closer to a flat table. Every unnecessary merge reintroduces duplication, increases file size, and removes the very separation between facts and dimensions that made the model efficient and easy to filter in the first place. Keeping fact and dimension tables separate is preferable in a BI model because each table has one job, one grain, and one place to maintain each attribute. Relationships give you the analytical connection without the physical duplication.

Recommended Power BI Model

For the majority of real-world business intelligence projects, a star schema is the right default choice, with one-to-many relationships flowing from each dimension to the fact table and single-direction filtering used everywhere unless a specific, tested scenario genuinely requires otherwise.

The reasoning:
Query and report performance. Star schemas are what Power BI's VertiPaq engine is built to optimise. Short relationship paths mean faster filter resolution than either a flat table (which cannot filter at all in the same way) or a snowflake schema (which adds extra hops).
DAX simplicity. With single-direction relationships, measures like Total Sales, Sales YTD and Average Order Value stay short and readable. Ambiguity disappears, so there is nothing to debug.
Model readability. Anyone opening the model view can see, at a glance, what the fact table is and what each dimension describes. A flat table hides this entirely, and a snowflake schema spreads it across more tables than necessary.
Scalability. A star schema handles growth gracefully. Adding a new dimension, or millions more fact rows, does not require restructuring anything.
Model complexity. A star schema keeps the number of relationships and hops to a minimum, which keeps the whole model easier to test, document and hand over to someone else later.

A snowflake schema still has its place, particularly when a dimension is unusually large and shared across many other dimensions, or when a project has a hard requirement to mirror a normalised source system closely. A flat table can be acceptable for a genuinely small, one-off, throwaway piece of analysis. But as a general-purpose foundation for a Power BI solution meant to grow, get shared, and get trusted by a business, the star schema, built with one-to-many relationships and single-direction filtering as the default, remains the most balanced and dependable choice.

Conclusion

In Power BI, data modelling is more than just an intellectual exercise; it's what makes a report trustworthy or untrustworthy. Use Power Query merges just for true data preparation, link everything using one-to-many single-direction connections, keep the fact table short and honest about its grain, let dimensions represent the business, and let the star schema handle the hard lifting. If you do this, DAX becomes easier to write, performance becomes easier to manage, and the model can still make sense to the person who inherits it in two years.

Jumia Product Performance Analysis: From Raw Data to an Excel Dashboard

Brian Mugo — Mon, 07 Sep 2026 03:47:03 +0000

I recently completed a data analysis project using a Jumia product dataset, and honestly, it looked pretty simple when I first started.
The goal was to take the raw product data, clean it, find some useful patterns and then turn everything into an interactive Excel dashboard.
What I learned pretty quickly is that the dashboard is actually one of the last things you should worry about. There is quite a bit that needs to happen before you get there.

Starting With the Data

The dataset had 115 product records with information such as product name, current price, old price, discount, reviews and ratings.
Before doing any analysis, I had to clean the data.
There were duplicate records, inconsistent rating formats, missing ratings and reviews, negative review values, spelling issues, extra spaces and even a product where the price was given as a range instead of one value.
I kept the original data untouched and worked on a separate cleaned dataset. I also recorded the cleaning decisions because I wanted to be able to explain what I changed and why.
One thing I was careful about was missing values. A product with a missing number of reviews or rating does not mean it has zero reviews or ratings, so I did not just replace those values with zero but rather noted that it had missing reviews/ratings.
After cleaning the data and removing duplicates, I had 112 products for the main analysis.

Revelation?

One of the questions I wanted to answer was whether bigger discounts resulted in more customer engagement.
The answer was not what I expected.
The correlation between discount percentage and reviews was approximately negative 0.17. That is a weak negative relationship.
Medium discount products averaged about 15.9 reviews, while high discount products averaged about 10.8 reviews.
So based on this dataset, bigger discounts did not seem to result in more reviews.
I also looked at product price and ratings. High price products had an average rating of about 4.08, compared with 3.64 for low price products.
However, the correlation between price and rating was only about 0.11. So although there was a difference in the average ratings, the overall relationship was still weak.
This was a good reminder for me that seeing two things move together does not automatically mean there is a strong relationship between them.

Building the Dashboard

Once the cleaning and analysis was done, I used Excel PivotTables, PivotCharts and slicers to build the dashboard.
The dashboard brings together product ratings, reviews, discounts, prices, engagement and product rankings.
I also added slicers for rating category, discount category and price category so the data could be explored from different angles.
The main point was not just making something that looked nice. I wanted the dashboard to make it easier to look at the data and actually understand what was going on.

What I Learned

This project taught me that data analysis is not just about making charts.
A lot of the real work happens before you even get to the dashboard.
Cleaning the data, checking formulas, deciding how to handle missing values and questioning the results all turned out to be important.
I also became more careful about correlation. A correlation can tell you that two variables have some kind of relationship, but it does not mean that one caused the other.

Final Thoughts

I started with a messy spreadsheet and ended up with a cleaned dataset, analysis and an interactive Excel dashboard.
It was a really useful practical exercise and another step in my journey into data science.
I am still learning, and I know there is a lot more for me to learn, but projects like this are helping me understand how everything connects.
You clean the data, ask questions, analyse the results and then try to communicate what the data is actually saying.
And sometimes the hardest part of a data project is not building the dashboard.
It is making sure you can trust the data behind it.

The Project

You can view the complete project and documentation, on my GitHub repository Jumia Product Performance Dashboard

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

Brian Mugo — Fri, 28 Aug 2026 08:05:45 +0000

Data analysis is the process of examining, cleaning, transforming, and interpreting data to find useful information required for decision-making. In simple terms, we can say that it is the process of taking raw data, organising it, and finally turning it into useful information for better insight.
It is important to prepare raw data since if you try to interpret messy data, you will also get misleading results. Raw data usually is inconsistent, inaccurate, and not well structured. Common issues with raw data might include, but are not limited to, duplicate records, incorrect dates, incorrect data types, missing values, and incorrect spellings, among others.
Excel is especially useful for beginners since it provides an easy and accessible way to learn fundamental concepts for analysis before progressing to more advanced tools that require one to have programming knowledge, such as SQL, R and Python.
I will be demonstrating the different concepts learnt during the first week of Excel by using them to clean a raw dataset, prepare the information for analysis, identify patterns after the data has been cleaned and present the visualisation and insights I will derive from interpreting that data.

Excel Fundamentals for Data Analytics

Before working with any raw dataset, it is important to understand the features of Excel that make it useful for data analysis. These fundamentals provide the foundation for data entry, organising, cleaning and presenting the data.
I started by understanding how data is organised in cells, rows, columns and ranges. These may seem like very basic concepts, but it is very important to understand how data is organised in a worksheet, especially when dealing with large datasets. I also learnt the different types of data such as dates, numbers, currency and text and how to properly format them. This was useful because some data looked correct at first glance before formatting, but it was actually wrong, such as dates entered as text data. Sorting and filtering was also very important when dealing with different data types. I was able to custom sort multiple columns and filter records based on the respective values that they contained, which made it easier to examine.

Data validation is another concept I found very useful, since it can be used to apply specific rules to cells or ranges such as allowing users to select only values from a predefined list. This prevents incorrect data entry when maintaining a dataset over time.

Quality Issues Noted in the Dataset

I learnt all this using a dirty HR dataset that contained multiple columns such as Employee ID, First Name, Last Name, Department, Salary, Hire Date, Bonus, Gender, Age, Marital Status and Performance Score, among others.
The dataset was dirty by all standards, as it contained various inconsistencies, including inconsistent employee IDs, duplicates, incorrect formats, missing values, inconsistent capitalisation, invalid values, inconsistent categories, wrong date formats, spelling errors and multiple pseudo-blank categories. This allowed me to practise almost every data cleaning technique I learnt during the week.

This data is very important to an organisation but cannot be used to generate useful insights. Salary comparisons between departments, performance by department, and reliably reporting the hiring dates or even marital status would be a headache using this data. Thereby, it is important that such data be cleaned so as to assist in decision-making by the management/stakeholders.

Preparing/Cleaning the Data

The next step after identifying the quality issues was to prepare and clean the data before attempting any analysis with the data.

Inconsistent Employee IDs

A few employee IDs were inconsistent with the rest of the data, so I had to standardise that column data.

Here, I used the RIGHT() function to extract the rightmost characters since all the IDs had five characters at the end, hence =RIGHT(A2, 5). This worked, and it removed the EMP- at the beginning of the employee IDs.

Incorrect Formats

In the salary column there were multiple incorrect formats where some values were entered as text data and others had a wrong currency sign. I first converted those values entered as text to the numbers format and then formatted the whole column from the format in the ribbon and changed it from a number format to a currency format, and then I went ahead and chose the correct currency symbol, which made the column become standardised.

Inconsistent Capitalisation

In the name columns, there were multiple instances where the names were inconsistent in capitalisation; some were written in all uppercase while others were written entirely in lowercase.

I used the text function PROPER() and autofilled the whole columns so as to change any inconsistencies down the name columns =PROPER(A2), =PROPER(B2)

Invalid Values

There were also cases of invalid values, especially in some columns such as the age and hire date columns. There were employees listed as being four years old, while others were listed as having been hired in the year 1900, which cannot be the case; therefore, I had to replace the invalid values with blanks because they appeared to be cases of incorrect data entry. This is why data validation is actually important in such scenarios since it prevents invalid future entries in the dataset.

Inconsistent Categories

There were a lot of inconsistent categories in this dataset. Most of these occurred within the same columns where you would find for example, in the department column, instances of HR, H.R, Human Resources, IT, I.T. and Info Tech. In the office location column, you would find Nairobi, Nairobii, Tokio and Tokyo. These were meant to refer to the same thing but were entered differently. I therefore used find and replace to standardise the entries so that each category had a consistent representation. This made it easier whenever you wanted to filter employees by department, office location and other categorical variables.

Duplicate Records

The dataset also contained duplicate records where some employees appeared more than once. I used Excel's Remove Duplicates feature under the Data tab to identify and remove these duplicates after I checking to ensure they were actually duplicates and not just employees with matching details.

Analysis, Visualisation & Insights

After cleaning the dataset, I could now begin analysing it with more confidence that obvious inconsistencies would not affect the results. I used formulas and pivot tables to compare the salaries in different departments such as finance and HR using SUMIF. I went further and compared the salaries in these departments by gender using SUMIFS. For example, I used the following formulas:

=SUMIF(E2:E870,"HR",F2:F870)
=SUMIF(E2:E870,"Finance",F2:F870)
=SUMIFS(G2:G870,E2:E870,"HR",J2:J870,"F")
=SUMIFS(G2:G870,E2:E870,"HR",J2:J870,"M")
=SUMIFS(G2:G870,E2:E870,"Finance",J2:J870,"F")
=SUMIFS(G2:G870,E2:E870,"Finance",J2:J870,"M")

The SUMIF formulas allowed me to calculate the total salaries for each department, while SUMIFS allowed me to apply multiple criteria at the same time, in this case, department and gender.
I also created pivot tables to analyse the same information, making the comparison easier to interpret visually.

The analysis revealed that the HR department had a higher total salary expenditure than the finance department. It also showed that the female employees accounted for higher total salaries than the male employees in both departments. However, the HR department also had more employees overall than the finance department, which suggests that the difference in salaries between the two departments may partly be influenced by the difference in the number of employees rather than individual salaries.

This analysis proved that Excel is a vital tool in analysing data since it provides a basis for further analysis. It also highlighted the importance of clean data since the analysis does not begin with creating charts or tables but rather with the preparation and cleaning of the dataset. This exercise gave me a practical understanding of how Excel can be used to transform raw data into meaningful information. Also, analysis is not just about formulas and visualisations but about asking the right questions and interpreting the results carefully.

Understanding The Git Workflow: Working Directory, Staging, Commit and Push.

Brian Mugo — Sun, 23 Aug 2026 19:35:47 +0000

Introduction

Git is the most widely used version control system which tracks the changes to files over a period of time. This happens in the working directory which is where one makes changes to the files and you then move to the staging area where you prepare the changes before committing them.
The local repository is where your project is stored locally on your computer whereas the remote repository is where your project is hosted on the internet and allows collaboration by multiple people.

Git Workflow

The Git workflow is now the process of moving changes from the working directory to the staging area then to the local repository and finally to the remote repository.

Working Directory

This is where one creates or modifies files; for example
creating a file: touch README.md or modifying a file.
You then run git status to confirm you are in a git repository and that the README.md file now shows as an untracked file. If it says 'not a git repository' then run git init first.

Staging Area

Once you have made changes you then tell Git the changes that you want to include in the next commit: If you want to add a single file, use git add filename, for example git add README.md. To stage everything, use git add ..

Local Repository

Once satisfied with what you have staged you then go ahead and create a commit: git commit -m "Add README.md" which creates a local commit in your repo's history.

Remote Repository

Finally your local commit is ready to be sent to a remote repository such as GitHub by pushing it: git push.
There is another command git pull which brings changes from the remote repository into your local repository.

Conclusion

When I tried pushing my first repo things did not run as smoothly as my explanation makes it seem. Because I was writing my Git commands the wrong way, I was not leaving spaces for instance git add. instead of git add . where they are supposed to be, other times I was forgetting the hyphens and using lowercase letters where they were supposed to be uppercase such as the example below where the first command is how I wrote it instead of writing it as the second command line.

ssh-keygen t ed25519 -c "my email address"
ssh-keygen -t ed25519 -C "my email address"

These mistakes, though, made me understand that one has to get everything right when writing the Git commands. This is vital for the whole process of pushing from local repository to the remote repository and pulling from the remote repository to the local repository. If the commands are not written the right way, you will not be able to successfully complete the process.