DEV Community: Moses Favour

JCARS LOGISTICS DATA ANALYSIS WITH POWER BI

Moses Favour — Thu, 01 Oct 2026 03:55:12 +0000

Introduction

Jcars is a dynamic automotive retail and distribution company operating across Kenya. Catering to a diverse clientele that includes individual retail buyers, corporate, government entities, and NGOs, the company manages a complex supply chain of vehicle procurement, logistics, and sales. With branches spanning from the bustling hubs of Nairobi and Mombasa to regional yards in Kisumu and Eldoret, Jcars deals in vehicles ranging from everyday sedans and SUVs to heavy-duty commercial trucks.

The Data Quality Challenge

When we first load the raw Jcars_data.csv file into Power BI, it became immediately apparent that we were dealing with a classic "real-world" dataset. We need to clean up the data before we can make any sense of it.

The primary data quality issues included:

Date Anomalies: A chaotic mix of standard text dates ("Aug 29, 2025"), regional formats (30/08/2025 vs 03/20/2026), Excel serial numbers (45671), and outright impossible dates (April 31 2026, 2026-13-04).
Textual Inconsistencies: Car makes and models were plagued by typos and casing issues ("Toyta", "TOYOTA", "Toyota Kenya"). Colors were heavily abbreviated ("Gre", "Bla", "Whi").
Financial Fragmentation: Prices and revenues were recorded in multiple currencies (KES, USD, EUR, ZAR) and mixed notations ("KSh 8,136,000", "9.14M", "$ 46,038.46").
Missing and Invalid Data: Blank Order IDs, negative discounts, and instances where the Delivery Date preceded the Order Date.
To build a reliable Star Schema and accurate DAX measures, we could not simply "clean" the data. I tackled this through m code

1. Date Inconsistencies

Dates are the backbone of any Time Intelligence analysis. Because our dataset contained mixed regional formats, and typos, relying on Power BI’s automatic date detection was impossible. We had to build a custom parser.
The Strategy:
We created a custom M function that evaluates the data type of the cell. IIf it’s text, it attempts to parse it using UK format (DD/MM/YYYY) first, falling back to US format (MM/DD/YYYY).We then added logic to catch transposition typos (like 2026-13-04) and normalize text (like changing "Sept" to "Sep"). Impossible dates (like April 31) are safely converted to null so they don't break the model.

let 
    // 1. Convert to text and trim spaces to prevent type errors
    CleanText = if [Order Date] = null or [Order Date] = "" then null else Text.Trim(Text.From([Order Date])),

    // 2. Check if the text is purely a number (Excel Serial Date)
    IsNumber = try (Number.From(CleanText) > 0) otherwise false,

    // 3. If it's a number, convert Excel serial to Date (Base date: Dec 30, 1899)
    SerialDate = if IsNumber then Date.From(#date(1899, 12, 30) + #duration(Number.From(CleanText), 0, 0, 0)) else null,

    // 4. FIX TYPO: Catch "YYYY-DD-MM" transpositions (e.g., "2026-13-04" -> "2026-04-13")
    FixedTypo = if not IsNumber and CleanText <> null and Text.Length(CleanText) = 10 and Text.Middle(CleanText, 4, 1) = "-" and Text.Middle(CleanText, 7, 1) = "-" then
        let
            Part1 = Text.Middle(CleanText, 0, 4), 
            Part2 = Text.Middle(CleanText, 5, 2), 
            Part3 = Text.Middle(CleanText, 8, 2)  
        in
            if Number.FromText(Part2) > 12 and Number.FromText(Part3) <= 12 then
                Part1 & "-" & Part3 & "-" & Part2 
            else
                CleanText
    else CleanText,

    // 5. NORMALIZE: Change "Sept" to "Sep" to ensure the date parser accepts it
    NormalizedText = if FixedTypo <> null then Text.Replace(FixedTypo, "Sept", "Sep") else null,

    // 6. Parse the text: Try UK format first, then US format
    TextDate = if not IsNumber and NormalizedText <> null then 
                   (try Date.FromText(NormalizedText, [Culture="en-GB"]) 
                    otherwise try Date.FromText(NormalizedText, [Culture="en-US"]) 
                    otherwise null) 
               else null
in 
    // 7. Return the valid date, or null if it's truly invalid
    if IsNumber then SerialDate else TextDate

2. Standardizing Text and Categorical Data

In a Star Schema, dimension tables rely on exact text matches. If a car is listed as "Toyta" in one row and "Toyota" in another, Power Query will create two separate dimension rows, breaking our analytics.
The Strategy:
We standardize the text leaving only one instance and replacing all other values with the correct instance.

3. Unifying Financial Metrics (Prices and Currencies)

Calculating total revenue is impossible if one row is in Kenyan Shillings (KES), another in US Dollars (USD), and another is written as "9.14M".
The Strategy:
We created a two-step process. First, we extracted the raw numeric value by stripping out currency symbols, commas, and text suffixes like "M" (millions). Second, we identified the currency prefix and applied an exchange rate to convert everything into a single base currency (KES).

// Step 1: Extract Raw Numeric Value
let
    RawText = Text.From([Unit Selling Price]),
    // Remove currency symbols and spaces
    CleanedText = Text.Replace(Text.Replace(Text.Replace(RawText, "KSh", ""), "KES", ""), "$", ""),
    CleanedText = Text.Replace(Text.Replace(CleanedText, "USD", ""), "EUR", ""),
    CleanedText = Text.Replace(CleanedText, "ZAR", ""),
    CleanedText = Text.Replace(CleanedText, ",", ""),
    CleanedText = Text.Trim(CleanedText),

    // Handle "M" suffix for millions
    Value = if Text.EndsWith(CleanedText, "M") then 
                Number.FromText(Text.Replace(CleanedText, "M", "")) * 1000000 
            else 
                try Number.FromText(CleanedText) otherwise null
in
    Value

// Step 2: Convert to Base Currency (KES)
let
    RawValue = [ExtractedNumericValue],
    CurrencyPrefix = Text.Start(Text.From([Unit Selling Price]), 3), // Simplified logic for example

    ExchangeRates = [USD = 130, EUR = 140, ZAR = 7], // Example static rates

    ConvertedValue = 
        if Text.Contains(Text.From([Unit Selling Price]), "USD") or Text.Contains(Text.From([Unit Selling Price]), "$") then RawValue * ExchangeRates[USD]
        else if Text.Contains(Text.From([Unit Selling Price]), "EUR") then RawValue * ExchangeRates[EUR]
        else if Text.Contains(Text.From([Unit Selling Price]), "ZAR") then RawValue * ExchangeRates[ZAR]
        else RawValue // Assume KES if no prefix
in
    ConvertedValue

4. Handling Missing and Invalid Data

A common mistake in data cleaning is deleting rows with missing Order IDs or negative discounts to make the dataset "look" clean. However, deleting rows artificially deflates revenue and hides operational flaws.
The Strategy:
Instead of deleting bad data, we preserved the financial transactions but flagged the metadata errors. For missing Order IDs, we generated a unique surrogate key (SalesKey) using an Index Column to ensure every row remained technically unique. For dates where the Order Date occurred after the Delivery Date, we didn't delete the row; instead, we created a boolean flag column. This allows management to see the revenue, but filter out the bad data when calculating precise operational KPIs like "Average Delivery Time."

Modelling Jcar Data

Once the data was clean, we now turn it into a Star Schema. This optimizes the PowerBI for maximum compression and query speed.
The Fact Table (Jcar_merge_facts): We established the grain of the model at the individual transaction level. The Fact table was stripped of all descriptive text and populated strictly with Foreign Keys linking to the dimension tables and numeric metrics (Units Sold, Unit Cost, Discount, Revenue, Logistics Cost).
The Dimension Tables: We turned descriptive context into highly optimized dimension tables by removing duplicates and creating unique IDs as PK to be linked as FK in the Facts table

Dim_Vehicle: Granular vehicle specifications (Make, Model, Year, Fuel, Transmission, Color).
Dim_CusLocation: Gives the customer location (Region → County → City).
DimBranch, DimSalesRep, DimCustomer, DimPayment: These give the operational and demographic contexts of the business helping determine key metrics that are important in gauging the performance of different facets in Jcar data in isolation and as a combination.

The primary keys in the various dimensions tables were linked were linked through merging. After cleaning the facts table I duplicated it such that the table that I merged the dimensions into was delinked from the facts table I had used to reference when creating the dimensions table. This is because merging to the file you referenced from will throw can error as you cannot merge a file you have referenced from in BI.
For all the dimensions table apart from Dim_Customer I used left outer join linking all matching columns facts table with the respective dimensions table while ensuring the keys FK:PK are also matching. I then deleted the matching columns from the facts table as it is now represented by the foreign key.

Cardinality and Direction

We configured all relationships as One-to-Many (1:*) with Single-Direction filter flow (from Dimension to Fact). This prevents ambiguous filter paths and ensures predictable DAX behavior. This is apart from the Dim_Customers which has a relationship of 1:1

Business Intelligence Logic

This is the comprehensive summary of the Jcars business logic, structured around the logical business analysis. This framework bridges the gap between raw data and strategic decision-making, demonstrating how specific analytical work drives actionable insights and recommendations. We use DAX calculations to show the metrics of various financial statements and use our expertise to recommend actionable measures for the business.

Total Revenue:
Total Revenue = SUM(FactSales[Revenue Recorded])
Total Gross Profit:
Total Gross Profit = SUMX(FactSales, FactSales[Units Sold] * (FactSales[Unit Selling Price] - FactSales[Unit Cost]))
Gross Profit Margin:
Gross Profit Margin = DIVIDE([Total Gross Profit], [Total Revenue], 0)
Total Goods Sold:
Total Units Sold = SUM(FactSales[Units Sold])

Dashboard

Findings, Insights and Recommendations

1. Key Findings and Metrics

1. Vehicle Performance (Make and Model)
The highest revenue by Make is Toyota. It stands out as the unparalleled market leader, generating approximately 0.65bn to 0.7bn Ksh which is roughly 44% - 47% of the total revenue. It also generates the highest absolute Gross Profit.
Similarly the highest return by car model is the Toyota Harrier, proving to be the best selling model with 34 units sold, followed closely by the Toyota Fit 30 units and Toyota Prado TX 29 units.
However there happens to be a high margin anomaly. While Toyota leads in volume and revenue, the Total Revenue and Total Gross Profit by Car Make chart reveals that Volkswagen and BMW have disproportionately high gross profit margins relative to their revenue. Purporting quite a high profit margin per unit sold.hhhmmm

2. Lead Source Performance
Our highest Revenue Lead Source comes Instagram. Performing in the generation of 271.03M (18.26%) of total revenue.
Social Media has dominated bar none with Facebook as our second highest at 230.57M (15.54%). Combined, Instagram and Facebook drive 33.8% of all company revenue.
Other Channels: Website 11.78%, Referrals (11.64%), and Phone Calls 11.63% perform relatively evenly, while Corporate Tenders contribute the least at 9.41%.

3. Customer & Payment Analysis
The top three revenue drivers Dealers (23.13%), Government (22.70%), and NGOs (20.22%). Individual customers contribute the least at 15.11% 224.3M.
Payment collection issues: The Total Revenue by Payment Method and Payment Status chart highlights a major operational risk. Across all major payment methods (M-Pesa, Cash, Cheque, Bank Transfer), there are massive blocks of "Pending" (purple) and "Partially Paid" (orange) statuses. An example is M-Pesa and Cash both have roughly 20-25% of their revenue stuck in pending or partially paid states.

4. Regional & Branch Performance
Top Branches: Kakamega is the highest performing branch (255M), followed by Thika 247M and Nakuru 212M .
Underperforming Branch: Nairobi is the lowest-performing branch, generating only 78.53M, which is roughly 30% of Kakamega's performance despite being in the capital city. This may need some investigation.

2. Strategic Insights

A. The "Toyota Reliance" Risk: Relying on a single make for nearly half of revenue exposes JCars to supply chain shocks or market saturation. Look to diversify into other brands that may spread the risk even though the toyota seems to be "darling"

B. Cash Flow impeding dilemma: The high volume of "Pending" and "Partially Paid" transactions indicates that while sales teams are good at closing deals, the collections/finance team is struggling to finalize payments. This artificially inflates revenue figures while starving the company of actual working capital.

C. Marketing ROI speaks for itself. Ancient methods such as walk-ins, phone calls and expensive B2B efforts like the Corporate Tenders are underperforming compared to social media marketing Ig and Fb, which bring in majority of the revenue.

D. The Nairobi anomaly in it's severe underperformance suggests either intense local competition, poor branch management, or a mismatch in inventory eg stocking cars that don't appeal to the Nairobi demographic, or part of the cars that are unaccounted for.

Recommendations

Procurement and inventory segment priorities

Maintain Toyota Stock and continue prioritizing the procurement of Toyota Harriers, Prado TXs, and Fits, as they are the proven volume and revenue drivers.
Prioritize increasing the inventory of Volkswagen and BMW, to capitalize on high margin brands the dashboards show they yield exceptionally high gross profit margins. Selling just a few more units of these could significantly boost the company's revenue without needing massive volume.
Diversify: Slowly introduce more Mazda and Mercedes-Benz inventory to capture the mid-to-high-end market that isn't buying Toyota.

Marketing & Sales Strategy

Shift marketing budget away from low-performing channels and heavily invest in Instagram and Facebook. Since they generate 34% of revenue, JCars should implement targeted ad campaigns specifically for the Toyota Harrier and Prado TX on these platforms.
Incentivize referrals as they generate 11.64% of revenue . Implementing a formal Customer Referral Bonus program could easily push this channel into the top 3.

Operational & Financial Improvements

I recommend having a collection taskforce as a course of action. Management must immediately address the "Pending" and "Partially Paid" backlog. Implement strict SLAs (Service Level Agreements) for sales reps to follow up on pending payments within 48 hours of delivery. Consider tying sales commissions to collected revenue rather than booked revenue.
Audit Nairobi branch: Conduct an immediate operational audit of the Nairobi branch. Compare its inventory mix, staff performance, and lead response times against the Kakamega branch to identify why the capital city branch is lagging so severely.
Boost Individual Retail: Since Individual customers make up the smallest segment (15.11%), JCars should consider creating Retail-Only weekend promotions or flexible micro-financing options to attract individual buyers who may not have the bulk capital of Dealers or NGOs.

Conclusion

This project demonstrates the critical journey from raw, unstructured data to strategic business intelligence for Jcars. By rigorously cleaning the dataset and creating a robust star schema, we transformed unreliable records into a high-performance analysis. Jcars is now equipped to optimize profit margins, streamline its supply chain, and drive sustainable growth in a competitive automotive market in the Kenya market.

https://github.com/waweroh/jcar-sales-modeling-analytics

Data Modelling, Relationships & Joins

Moses Favour — Fri, 25 Sep 2026 09:34:36 +0000

Data Modelling in Power BI

In data analytics and business intelligence the success of a power BI solution lies in it's data model. Exploring data modelling principles, relationships management and join operations equips you with the knowledge to design a robust data model. A well-architected data model ensures accuracy in your analytics as well as simplicity in your DAX calculations. Data modelling is the process of organizing, defining and designing tables, how they establish relationships with other tables and rules that govern data interactions. It becomes the blue print through which data is stored, filtered and queried to answer business questions and give insight.

Components of Data Modelling:

Tables: Containers for your data (facts and dimensions)
Relationships: Connections between tables (one-to-many, many-to-one). How many rows in one table can link to rows in another.
Keys: Unique identifiers (Primary Keys and Foreign Keys)
Cardinality: The nature of relationships (1:1, 1:M, M:M).
Filter Direction: How filters flow between tables (single or bi-directional)

Flat table

It represents the simplest form of data modelling where all data elements exist within a single table structure and with no relationships with other tables. The table is a massive data set which contains data redundancy, mixed granularity example the data contains customer information together with product store data. It's primary advantage is that its simple with no complex logic or relationships to understand. The disadvantage far out way advantages. Flat tables cannot provide any business intelligence solutions as the data is very redundant. If a customer like "Jack" has made fifty purchases, his name, email address, segment classification, and geographical information are duplicated fifty times across the table. This redundancy wastes memory and storage space, leading to bloated file sizes that can be five to ten times larger than necessary. When you need to update customer information, you must modify every single row where that customer appears, creating opportunities for data inconsistency and requiring expensive update operations. This leads to poor performance.

Star Schema

The star schema represents the industry-standard approach for dimensional modelling in business intelligence and is the recommended design pattern for Power BI solutions. This architecture consists of a central fact table surrounded by multiple dimension tables, creating a structure that resembles a star when visualized in the model diagram. The fact table **sits at the center and contains the measurable, quantitative data about business processes, while the **dimension tables radiate outward and contain the descriptive attributes that provide context for analysis.

Example of a star schema approach

In a retail sales scenario, the fact table **would be named FactSales and would contain one row for each individual sales transaction. Each row would include foreign key references to dimension tables, such as DateKey, CustomerKey, ProductKey, and StoreKey, along with numeric measurements like SalesAmount, Quantity, Discount, and Profit. **The dimension tables, such as DimDate, DimCustomer, DimProduct, and DimStore, contain the descriptive attributes. DimDate might include columns like FullDate, Year, Quarter, Month, Day, DayOfWeek, and IsHoliday. DimCustomer would contain CustomerName, Email, Segment, Region, and City. DimProduct would include ProductName, Category, Subcategory, and Supplier. DimStore would contain StoreName, Address, City, State, and Region.

Advantages

Separating the repetitive foreign keys and having relatively small dimension tables improves query performance ie faster than flat tables.
DAX calculations become remarkably simpler because relationships enable automatic filter propagation.
Data redundancy is minimized because each dimension attribute is stored only once. Customer information exists in a single row in DimCustomer and is referenced by the CustomerKey foreign key in every related sales transaction.
Scalability is excellent because the fact table can grow to billions of rows while dimension tables remain relatively small and stable.

Snowflake Schema Approach.

The snowflake schema represents a normalized variation of the star schema where dimension tables are further broken down into sub-dimensions, creating multiple levels of related tables that resemble a snowflake pattern when visualized.

Advantages

Has reduced storage requirements through additional normalization. Category names are stored only once in DimCategory rather than being repeated for every subcategory and product, and subcategory names are stored once rather than repeated for every product.

Disadvantages

Performance degradation is the most critical issue because queries must traverse multiple relationships to reach the descriptive attributes.
DAX calculations become more complex because measures must account for the additional relationship layers. Simple calculations that would be automatic in a star schema require explicit handling of filter context across multiple tables.

Relationships in Power BI

In Power BI, a relationship is a logical connection between two tables that enables the automatic propagation of filters from one table to another, allowing data from multiple tables to be queried and analyzed together as if it were a single unified dataset.Without relationships, Power BI would have no way of knowing how to connect a customer's name in DimCustomer to their sales transactions in FactSales, or how to associate a product's category in DimProduct with its sales amounts in FactSales.

Cardinalities

Relationship cardinality describes the numerical relationship between rows in one table and rows in another table, and Power BI supports three primary cardinality types: one-to-many, one-to-one, and many-to-many. Cardinality refers to the uniqueness of data in a column **and is closely related to the **concept of primary and foreign keys. A column with high cardinality has many unique values relative to the total number of rows, such as a transaction ID column where every row has a different value. A column with low cardinality has few unique values relative to the total number of rows, such as a gender column with only two or three distinct values. In relationships, the "one" side typically has high cardinality (one unique value per row), while the "many" side has low cardinality (many rows sharing the same foreign key value).

One-to-Many relationships

A single row in Table A can match multiple rows in Table B. Example: A Customer table linked to an Orders table (one customer can place many orders).This is the most used relationship.

One-to-One relationship

Each row in Table A matches exactly one row in Table B. Example: A User table linked to a User_Passport table (one person has one passport).

Many-to-Many relationship

A many-to-many relationship exists when rows in the first table can be associated with multiple rows in the second table, and rows in the second table can also be associated with multiple rows in the first table. Example: A Student table linked to a Classes table (a student takes many classes; a class has many students).

Filter Direction

Filter direction determines how filters applied to one table flow through relationships to affect other related tables in your data model. Understanding filter propagation is essential for creating accurate reports and avoiding common pitfalls that can lead to incorrect results or performance problems.

Single-direction filtering

Single-direction filtering, also known as one-way filtering, is the default and recommended filter direction for most relationships in Power BI. In a single-direction relationship, filters flow from the "one" side of the relationship (typically a dimension table) to the "many" side (typically a fact table), but not in the reverse direction. This means that when you select a value in a dimension table, such as clicking on "Electronics" in a DimProduct[Category] slicer, that filter automatically propagates to the FactSales table, showing only sales transactions for products in the Electronics category. However, filters do not flow from FactSales back to DimProduct, which means that selecting a specific sales amount range in a fact table measure would not filter the products shown in a dimension table visual.

Bidirectional filtering

Bidirectional filtering, also known as both-direction filtering or cross-filtering, allows filters to flow in both directions across a relationship, from the "one" side to the "many" side and from the "many" side back to the "one" side. When bidirectional filtering is enabled, selecting a value in either table affects the other table, creating a two-way filter propagation. Bidirectional filtering can be useful in specific scenarios, such as when you have a many-to-many relationship implemented with a bridge table, and you need filters to flow from both dimension tables through the bridge table to the fact table.

Joins in Power Query

A join is an operation that combines rows from two tables based on a related column between them, creating a single unified table that contains columns from both source tables. Joins are performed using the Merge Queries feature in Power Query Editor, which allows you to specify which columns to match between tables and what type of join to perform.

Left Outer Join

A left outer join, often simply called a left join, returns all rows from the left table (the first table you select in the merge operation) and only the matching rows from the right table (the second table). If a row in the left table has no matching row in the right table, the columns from the right table will contain null values for that row, but the row from the left table is still included in the result

Right Outer Join

A right outer join is the mirror image of a left outer join, returning all rows from the right table (the second table) and only the matching rows from the left table (the first table). If a row in the right table has no matching row in the left table, the columns from the left table will contain null values, but the row from the right table is still included.

Full Outer Join

A full outer join, also known as a full join or outer join, returns all rows from both tables, matching rows where possible and filling in null values where no match exists. This join type preserves every row from both the left table and the right table, regardless of whether matching rows exist in the opposite table.

Inner Join

An inner join returns only the rows that have matching values in both tables, excluding any rows that do not have a match in the opposite table. This is the most restrictive join type because it filters out any records that cannot be matched between the two tables.

Left Anti Join

A left anti join, also known as a left semi join or excluding join, returns only the rows from the left table that do not have matching rows in the right table. This join type is essentially the opposite of an inner join from the perspective of the left table, returning only the unmatched records.
In the Customers and Orders example, a left anti join would return only customers who have never placed an order. Any customer who appears in the Orders table would be excluded from the result.

Power Query Joins vs Power BI Relationships: Understanding the Distinction

A Power Query merge operation physically combines columns and data from two or more tables into a single unified table during the data transformation phase, which occurs before the data is loaded into the Power BI data model.
In contrast, creating a relationship in the Power BI data model does not physically combine tables or duplicate data. Instead, a relationship creates a logical connection between two separate tables that remain distinct entities in the model. The relationship enables filter propagation and allows DAX calculations to access data from related tables without physically merging them. The tables stay separate in memory, and the relationship is evaluated dynamically at query time when users interact with reports or when DAX measures are calculated.
When deciding whether to use a merge or a relationship, the guiding principle should be to use relationships whenever possible and reserve merges for specific scenarios where denormalization is necessary. Keeping fact and dimension tables separate through relationships rather than merging them into flat tables provides numerous advantages.

Conclusion

In conclusion, the star schema with one-to-many, single-direction relationships represents the optimal design pattern for Power BI data models. It delivers superior performance, simpler DAX, better scalability, easier maintenance, and more intuitive report development compared to flat tables or snowflake schemas. While it requires more upfront effort to design and implement than simply importing flat tables, the long-term benefits in terms of performance, maintainability, and user satisfaction make it the clear choice for any serious business intelligence project.

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

Moses Favour — Wed, 02 Sep 2026 19:21:32 +0000

Introduction

Microsoft Excel is a spreadsheet program used to organize, calculate, analyze, and visualize data in rows and columns. It is organized in worksheets that are contained in the workbook - the file.
Data analysis is the process of cleaning, organizing and examining raw data to find useful patterns the support business decisions.
Data is often received in it rawest form which is disorganized and in order to make business sense of it, the data must be cleaned and organized. So how do we clean raw data and make business sense out of it.

1. Filtering

Filtering is temporarily conceals rows that do not fit your rules. It displays a smaller, relevant subset of data while leaving the original row order untouched.

Using filtering to clean data

Auto fit the column width to see column data properly. Home tab Cell.
Select the row with all the headers and select filter. This will enable us to remove unwanted data fields our columns.
When cleaning data for non-unique input values we need columns to have only one instance of a text field. We do this by deleting all other text fields until we remain with unique ones. An example would be having HR and human resource both as input fields instead of one instance.

In this column we have only one instance of every department. In such columns we often use data validation to ensure that inputs are strictly of a specific type. Example Male or female, or a specific department.

Using filtering for analysis
In excel we can filter by certain types of data namely text, numbers and date. This means we can conceal all other data and get only the data that fits our criteria

Above is text filter. We are filtering last names column for names beginning with 'mbo'

We have various options for number filters. Example we can filter for salaries that are greater than 50,000 using the option provided.

This is date filter. We use it to get specific time lines and time ranges.

2. Sorting

Sorting changes the physical position and order of rows (such as A to Z, Z to A, smallest to largest, or by cell color). All data remains visible on the sheet unlike filtering that hides data giving only what was filtered. It is found in home -> editing

Multilevel Sorting

Here we are sorting our data by the columns department, gender and age. This rearranges the entire worksheet to fit this parameters.

3. Conditional Formatting.

Conditional formatting in Excel is a tool that changes the look of cells automatically based on their content. It lets you use **colors, icons, or data bars to spot trends, high values, or errors and duplicates **without looking through rows by hand

In this instance we are using conditional formatting on the employeeID column to highlight duplicates using a specific color, since the ID values are supposed to be unique any duplicate should be deleted
Conditional formatting can also be used on dates and time, getting values that are greater or less than, to highlight text that contains a specific text, getting top 10%. Explore the conditional formatting options

4. Data Validation

Data validation is a feature that restricts what users can type into a cell or a range of cells to keep the data clean and accurate. Found in the data tab --> data tools group

In the above image we have validate the department column to only accept a list of departments available in the organization

In this example we see that to input values into the department column we have a drop down list to select from

The data validation criteria can also cater for dates, time whole numbers, lists and text length. This ensures that the data being input in the cell already meets the criteria for that column or cell. The process ensures incoming data is clean.

5. Functions

Aggregation Function
Aggregate function calculate a single summary value such as a sum, average, or count from a list of numbers
Count - The function is used to count non-numeric cells.
CountA - counts non-blank cells in the column
CountBlank - Counts blank cells in a column

Conditional Aggregations
A conditional aggregate function in Excel summarizes a set of data (like adding numbers or counting rows) only for the cells that meet a specific rule or condition.
Examples are COUNTIFS, SUMIFS, COUNTIF.

In the above image we use SUMIFS to calculate the sum of salary(range) if the gender is female(range and text) and the department is Finance (range and text). Using the syntax provided.

Statistical Functions
Statistical Functions to analyze, summarize, and interpret data. They include mean, median and mode.
Note that the median and the average should not have a big difference if the data is consistent and has no outliers

Here we find the median for out salary column

Date and time functions
Excel date and time functions work like a hidden digital calendar and clock that let you track, display, and calculate days and hours with ease.
=YEAR(A1) =MONTH(A1) =DAY(A1) This are some of the date and time functions

Text Functions
This are functions that format text for a certain expected outcome. Example: CONCAT(adding two strings together), TRIM, PROPER(first letter capitalization)

In this example we use the text functions CONCAT and LOWER to join the first and last name and form an email field from that text

Conclusion.
This article explains how you can clean data and analyze data using excel using tools like data validation, functions, filters and sorts. It helps capture the power of excel in your data analysis journey.

Understanding the Git Workflow: Working Directory, Staging, Commit and Push

Moses Favour — Sat, 22 Aug 2026 09:52:30 +0000

Introduction

Git is a version control system that tracks changes in code. When writing code we need to save every significant change or feature that does a certain function as a commit that can be reverted to if we ever need that instance of the code. This helps programmers to traverse code since nothing is ever lost in the product life cycle of the project. It also helps in debugging since we have a copy of the previous working state. So how do we start out with git you ask.

Install git

Install git for your specific operating system. After installing git you will note that the file comes with git bash which is a command line terminal that is used to run commands or instruct the version control git.
To ensure that git has been installed open git bash and run the command git --version which will return the version of git installed

Download Visual Studio Code

Install VS code a text editor for writing code.

Create a github account

GitHub is a cloud-based platform used by developers to store, track, and collaborate on software code. It operates as a hosting service for git, an open-source version control system that tracks changes made to files.

Head over to github and create and account

Creating a directory tracked by git

On git bash run mkdir <filename>. This will create a folder ie the project that will contain your files of code.
Enter the folder through cd <filename> and create a file README.md file which explains what this project does touch README.md. Run the command ls to list files in the directory
Open the vs code from terminal by running code . and edit the README.md file. Now the file is ready to be tracked by git on github

Staging, committing and pushing

We now need to initialize the directory making sure that git is now tracking the file. Running the commands ls-la will confirm that git has been initialized in the repository. There are two ways of doing this either via the text editor VS code or the terminal. Let us go through both.

1. VS code

Make sure your VS code is connected to your github account.

Click the source control icon (third from top) and Initialize Repository. This will introduce git to track the file
Hover over the file and click the + button to add the file. This changes the file from U (untracked) to A (added)
Write the commit message in the input box above describing what you are committing eg add README.md file.
On the commit button press the drop down arrow **for more options and **press commit and sync. This will now stage your repository to be pushed to github.
Click on publish branch and set the repository to either public or private.
Go to your github and refresh to see your newly pushed repository that is now tracked by git.

2. Using the terminal

Open your terminal ie. gitbash and go to your folder.
Initialize Git using git init.
Add your files using git add . to track them.
Commit the files using git commit -m "first commit". This should describe what we are committing and will to save the change locally.
Link your remote repository using git remote add origin <your-repo-url>.Preferably use the ssh link option
Push the files using git push -u origin main to send your code to GitHub and publish the repository as the main branch.

With that now you know how to set up a project and keep track of your changes using github.