DEV Community: Makori Nyachaki

Navigating the KES 1.85B Revenue Reality: An Enterprise Power BI Solution for JCars Logistics

Makori Nyachaki — Wed, 07 Oct 2026 02:59:12 +0000

How Data Auditing and Custom DAX Uncovered a 70.46% vs 10.64% Regional Margin Gap and a 68-Day Fulfillment Delinquency.

Executive Summary and Business Context

J-Cars Logistics is a major automotive logistics and dealership enterprise in Kenya, specializing in importing, marketing, and delivering imported and local vehicles to corporate, commercial, and individual clients across 7 geographic regions (Central, Coast, Eastern, Nairobi, Nyanza, Rift Valley, and Western). The business operates through 8 main branches located in Thika, Kakamega, Nakuru, Nairobi, Kisumu, Mombasa, Eldoret, and Athi River, supported by a network covering 11 cities and 8 counties.

The raw, unclean flat table provides was transformed into an enterprise grade Power BI decision support solution by the team. The dataset contained multiple anomalies, multicurrency transactions, unstandardized text, attributes, and invalid dates undermining management's ability to evaluate true operational profitability and logistical performance.

The article presents, complete architectural journey: from data auditing and Power Query ETL transformations, to modelling, advanced DAX logic development, Dashboard design, and strategic recommendations founded in verified data performance.

Performance Summary

Metric	Performance Value	Strategic Context & Impact
Gross & Net Revenue	Ksh. 1.85Bn Gross/1.77Bn Net	Reflects Ksh. 15.07M in refunds and regional revenue adjustments.
Net Profit & Margin	KSh. 447.52M(25.34% Margin)	Strong top-line margin, heavily weighed by premium vehicle sales.
Sales Volume & Orders	408 units Sold across 245 Orders	Average of 1.67 Vehicles per order; high commercial fleet bundling
Logistic and Delivery Costs	KSh. 24.72M Logistics/ KSh. 23.04M Delivery Fees	Net Logistics deficit of KSh. 1.68M where logistics expenses exceed customer fees.
Order to Delivery	23 days OTD average/ 26 Days Transit	Severe transit friction in digital channels i.e. WhatsApp average is 68 days

Dataset Grain and Quality Audit

Before modelling, a thorough audit was executed defining the grain and underlying anomalies. The dataset grain was established as one row per sales transaction item, representing a sales order line item containing all details pertaining the transaction; order details, branch details, customer details, sale rep details, payment details, delivery details, rating, and reviews attributes.

The following were critical data quality issues uncovered that posed a challenge to the management in reporting accurately if unattended.

Multicurrency Inconsistencies: Monetary fields(Unit Selling Price, Unit Cost, Logistic Cost, Delivery Fees) contained mixed currencies. The currencies were KES for Kenya, USD and $ for US dollar, EUR for the Euro, and ZA/ZAR/R for the South African Rand. Solved the issue by standardizing all non Kenyan Currency using the documented CBK average for the exchange rates in the year 2026, by creating an exchange rate table with the currency symbol and rate columns and splitting the fields using the digit and non digit values to obtain amount value and currency symbol. Those without currency symbol were assumed as KES. Also, formatting of some unit values were indicated i.e. as 1.4M implying they were in millions, we used a helper column to get a multiplier i.e. for any Value ending in M the Multiplier would be 1000000, K = 1000, else 1.
Negative and Zero Selling Prices, Unit Cost, Logistic Cost Delivery Fees, and Revenue Recorded: Test entries or erroneous records showing Zero/negative selling prices were flagged and excluded from core revenue measures. The following steps were followed for each:

Unit Selling Price
A negative Unit Selling Price usually means on of the following:
a. A Customer Return/Refund
b. A promotional credit.
c. a data corruption error

We were not provided with sufficient information to rule in favor of promotional credit. However, we had to determine for true refunds and find corrupted data. Before that we need to know and understand how to handle the errors.

Selling Price	Quantity	Total Revenue	Meaning and Context
-ve	+2	-Ve	System Refund: The system logs returns by making Unit Price negative instead of quantity.
-ve	-2	+ve	Accounting Bug: Double negative error. A return is logged with both negative price and quantity resulting in a false positive revenue.
-ve	+ve	-ve	Line-Item Credit: A gift card, trade-in, or price adjustment rebate was applied.

Decisions

Legitimate Returns: Keep the rows and fix the Math. Total revenue will be correct, but your Unit Cost metrics will be skewed. To fix make the Selling price positive and shift the negative sign to the quantity column.

if [Unit Selling Price] < 0 then ([Unit Sold] * -1) else [Unit Sold]

// Create a corrected Unit Selling Price column by converting it to an absolute value

Number.Abs([Unit Selling Price])

Remember to account for this while performing aggregations.

If both the Unit Selling Price and Units Sold are null, remove the records
Removed rows: Both Units sold and Unit Selling price is null where only 1 row was affected
Null Selling Price(1 row)
Errors (9 rows affected)
Units Sold is null (6 rows)

A total of 17 rows were removed.

Logistic Costs
Zero Logistics Cost in a data source, it usually implies one of two scenarios: a Free Shipping Promotion offered to the customer, or a System Drop-ship/Pick-up where no internal shipping occurred.
To handle the issues, we kept the following in mind:

Legitimate Reversals: kept them negative if it was a product return. This offset total expenses and allows us to calculate true net logistics spend.
Outflow Typos(Convert to positive): If a row was negative and it was a standard forward shipment but logged as negative it was considered a formatting error, thus converted to it is absolute value. -In the case of pure corruption: Converted to null.

Unit Cost
Required a distinct approach to handle simply because they can stem from completely different issues:

Negative: Are almost always data corruption or a system error since a product can not cost less than nothing to produce.
Zero Cost: Also most always legitimate such as giveaways, samples, bundle items, or inventory adjustments. However, if a standard item has 0 cost, it will artificially inflate profit margins.

To handle the errors, for negative Unit Cost we converted them too null.

Delivery Fee
For all negative delivery fees we removed them from the data. A total of 7 rows were affected.

After making all these changes, we removed all extra dirty columns to clean our data.

Recorded Revenue
From our findings, Recorded Revenue was a calculated field. After cleaning all our monetary values we calculated recorded revenue, handling it using the following DAX function in the Power BI editor and having it in a new column:

Revenue Recorded = 
(FactOrders[Unit Selling Price(KES)]*FactOrders[Units Sold]*(1-FactOrders[Discount])) + FactOrders[Delivery Fees(KES)]

Date Format Mismatches: Mixed date strings (DD/MM/YYYY vs MM/DD/YYYY). Parsed explicitly in Power Query using Locale setting (English - Kenya). Other errors were replaced with null after setting the correct type for both the Order Date and Delivery Date. The following custom M code handled the date issue i.e. for the Order Date by creating a new column. Follow the comments in the code for the steps:

// 1. Clean the text, but keep slashes/hyphens for now

rawTxt = Text.Clean(Text.Trim(Text.From([Order Date]))),

// 2. Try standard parsing first (handles Excel serial numbers and system-matching dates)

parseStandard = try Date.From(Number.FromText(rawTxt)) otherwise try Date.FromText(rawTxt) otherwise null,

// 3. If standard parsing fails, strip out hyphens, slashes, and dots to inspect the raw digits

cleanDigits = Text.Select(rawTxt, {"0".."9"}),
len = Text.Length(cleanDigits),

// 4. Conditional logic to extract components from the raw digits

parseCustom = if parseStandard <> null then parseStandard
else if len = 8 and (Text.StartsWith(cleanDigits, "20") or
Text.StartsWith(cleanDigits, "19")) then

// Handles YYYYMMDD (Assumes Year first)

try #date(Int16.From(Text.Start(cleanDigits, 4)),
Int16.From(Text.Middle(cleanDigits, 4, 2)),
Int16.From(Text.End(cleanDigits, 2)))

// Alternate backup: If Month > 12, handles YYYYDDMM

otherwise try #date(Int16.From(Text.Start(cleanDigits,
4)), Int16.From(Text.End(cleanDigits, 2)),
Int16.From(Text.Middle(cleanDigits, 4, 2)))

otherwise null
else if len = 8 then

// Handles MMDDYYYY (Assumes Year is at the end)

try #date(Int16.From(Text.End(cleanDigits, 4)),
Int16.From(Text.Start(cleanDigits, 2)),
Int16.From(Text.Middle(cleanDigits, 2, 2)))

// Alternate backup: If Month > 12, handles DDMMYYYY

otherwise try #date(Int16.From(Text.End(cleanDigits,
4)), Int16.From(Text.Middle(cleanDigits, 2, 2)),
Int16.From(Text.Start(cleanDigits, 2)))
otherwise null
else null
in
parseCustom

Missing Transmission, Color, Returned, Customer Name, and Vehicle Year, Region, Customer Type, Sales Rep, Lead Source, Car Make, Transmission, Vehicle Type, Fuel Type, Color, Payment Method, Delivery Status, Payment Status, City, Branch, County, and City Naming Variations: Null values in Transmission, Color, Returned and Customer Name columns were Categorized as Not Provided and Unknown respectively to prevent aggregation loss. For the Vehicle year it was set to text type and the missing values categorized as null.

Branches

Some branches had name variations i.e. one branch would be referred to different names but practically it was one branch. The variations were rectified and one branch would be referred to only one standard name. Example: Eld, Eldoret, Eldoret Branch were referring to the Eldoret branch. We replaced these values with Eldoret. Similarly, we did this for the other branches.

County

One county would also be referred to more than one name with spelling variation i.e. Kambu and Kiambu County were referring to the county government of Kiambu. We Replaced values with only Kiambu

Color
After doing replacement, we used mode to determine between Grey and Green which one we were to use as a replacement value for Gre. We used Grey.

This was also done to other fields that had similar issues replacing them with more relevant values for that particular columns. This was essential not to affect aggregations and other actions performed.

Mixed and Incorrectly Formatted Order ID: The Order ID was incorrectly formatted as a text field where non digits and digit values were combined for some IDs while some had either the digit part or the non digit part only and some blank. After observations, the field had a sequential digit part starting at 1000. To solve this, we decided to use only the digit part to fill down the Order ID column by creating a new Custom Index Column.

Steps
In Power Query

On the Ribbon click on Add Columns
From the General group, click on the drop down arrow Index Column
Choose Custom instead of From 0 or From 1
On the Dialog box specify Starting Index = 1000, and the Increment = 1 and click OK. Our IDs will begin at index 1000 and be incremented by 1. Getting the index range of 1000 - 1275 for our Order ID column.
Remove the old formatted Order ID, rename the new Index Column to Order ID

Delivery Status Discrepancies: Orders marked 'Delivered' or 'Yard' but showing empty delivery fees the delivery fee was set to 0.
If the Delivery Fee = null and Delivery Fee < 0 and the Delivery Status = "Cancelled" and Delivery Status = "Held" then Delivery Fee = null.
If Delivery Fee = null and Delivery Status = "Yard" then Delivery Fee = 0
Else Delivery Fees

We removed all errors and nulls in the Delivery Fees column affecting a total of 9 rows.

Incorrect type for Discount, Rating, Reviews Count, and Age: Before converting to the correct type, we had to Trim and Clean the fields to get rid of leading and trailing whitespaces and extra spaces. Then for:

Discount
To handle the field was very critical. First, we hard to Standardize the Text to Numbers by creating a custom column, that detects if a value ends with a "%" sign, strips it, divides it by 100, and otherwise parses the value as a decimal.
Use the following procedure:

Open Power Query Editor (click Transform Data in Power BI).
Go to the Add Column tab and click Custom Column.
Name your column (e.g., Cleaned Discount) and use the following M formula:

let
// Remove any accidental leading/trailing spaces(Trim)

CleanDisc = Text.Trim([Discount]),

// Check if the value ends with a percentage sign

IsPercent = Text.EndsWith(CleanDisc, "%"),

// Extract the numeric part of the text

RawValue = 
if IsPercent then Text.Start(CleanText, Text.Length(CleanDisc) - 1) 
else CleanText,

// Convert text to a number (using Invariant Culture to handle
dot decimals safely)

NumberValue = Number.FromText(RawValue, Culture.Current)
in

// If it was a percentage text (like "10"), divide by 100 to  get 0.10. Otherwise, keep the decimal.

if IsPercent then NumberValue / 100 else NumberValue

Click OK.
Change the data type of this new column to Percentage or Decimal Number by clicking the icon next to the column header

Secondly, to handle outliers that is any discount value greater than 100% or greater than 1 is replaced by null in order not to skew the analysis and profit margins. A discount greater than 100% generally means the transaction is invalid.

If you want to keep the rows but change anything over 100% down to 100%, go to Add Column > Custom Column and use:

if [Cleaned Discount] > 1 then 1 else [Cleaned Discount]

For discounts less 0, they set to 0

if [Cleaned Discount] < 0 the 0 else [Cleaned Discount]

Finally, we cleaned our data by removing the old Discount field from the data to remain with our cleaned discount field and renamed it to Discount.

Age
The age field was also incorrectly formatted with mixed types. So;

Changed the data type to a Whole Number
Replaced Error with null
Replaced null values with the average age 45
Also, replaced age below 18 years with 18. Since in Kenya the minimum age for one to obtain official identification is 18 years, enabling one to make independent decisions and purchases that requires official identification i.e. Tax certificate.

Review Count
For the Review Count column:

We Trimmed and Cleaned the text.
Replaced ten with 10, N/A and - with blanks.
Change type to a Whole Number
We converted the negative numbers to absolute values
Select the Review Count column
On the Ribbon > Transform Tab (Number Group)
Click on the Scientific the from the options choose Absolute Value

Assumption: All negative reviews were not counted to be negative counts.

Customer Rating

Text Trimmed/Cleaned
Replaced Values out of 5 with blanks, Excellent with 5, N/A or - with blank, 4.7/5 with an average of 4.9.
Changed type to Decimal Number with 1 decimal place and converted negative values to their absolute equivalent.

Data Modelling

After successful data cleaning, we modelled our data into dimensional and fact tables. We modelled our data into a star schema with seven Dimensional tables and 1 fact table. Modelling reduces redundance and optimizes DAX engine evaluation.

The following tables were created, merged and relationships established:

Table Name	Type	Primary/Foreign Key	Description
DimCustomers	Dimensional	Customer ID	Holds customer details: `Customer Name`, `Customer Type`, and `Age`.
DimRegions	Dimensional	Region ID	Holds the region details: `Region ID` and `Region`
DimBranches	Dimensional	Branch ID	Branch Details: `Branch ID` and `Branch`
DimSalesRep	Dimensional	Sales Rep ID	Sales Rep Details: `Name` and `ID`
DimCities	Dimensional	City ID	City Details: `City` and `City ID`
DimCounties	Dimensional	County ID	County Details: `County ID` and `County` name.
DimDateTable	Dimensional	Date	Calendar Date Table: `Date`, `Year`, `Month Number`, `Month Name`, `Quarter`, `Year-Month`
FactOrders	Fact	Order ID, Customer ID, Sales Rep ID, Branch ID, Region ID, City ID, County ID	Holds the transaction details: Unit Selling Price, Unit Cost, Logistic Cost, Delivery Fees, Units Sold, Discount, Customer Rating, Reviews Count, Car Make, Car Model, Vehicle Type, Year, Color, Lead Source, Delivery Status, Payment Method, Payment Status, Fuel Type, Transmission, Returned, and Recorded Revenue.

After creating the tables in our models we established the following relationships as shown on the diagram below.

Note: All relationships enforce 1-to-Many (1:*) cardinality with a single direction filtering (Dimension to Fact), eliminating circular dependencies and ambiguity.

DAX Calculations and Reusable Business Logic

To enforce consistent business rules across all visual elements, DAX measures were constructed. Core DAX formulas used include:

Gross Revenue(Create a new custom column)

Gross Revenue = FactOrders[Units Sold] * FactOrders[Unit Selling Price(KES)]

Recorded Revenue(Create a new custom column)

Revenue Recorded = (FactOrders[Unit Selling Price(KES)]*FactOrders[Units Sold]*(1-FactOrders[Discount])) + FactOrders[Delivery Fees(KES)]

Discount Amount(Create a new custom column)

Discount Amount = (FactOrders[Unit Selling Price(KES)]/(1 - FactOrders[Discount])) - FactOrders[Unit Selling Price(KES)]

Net Profit Margin(New custom column)

Net Profit Margin = FactOrders[Revenue Recorded] - ((FactOrders[Units Sold]*FactOrders[Unit Cost(KES)])+FactOrders[Logistic Cost(KES)]+FactOrders[Delivery Fees(KES)])

Volume Quarter 1 2025

VolumeQtr125 = CALCULATE(SUM(FactOrders[Units Sold]), AND(QUARTER(FactOrders[Order Date]) = 1, YEAR(FactOrders[Order Date]) = 2025), NOT(ISBLANK(FactOrders[Order Date])))

Percentage Volume Quarter 1(Compare between Q1 2025 and Q1 2026 Performance. Create a new measure)

VolumePercentageQ1 = (([VolumeQtr126]-[VolumeQtr125])/[VolumeQtr125])

Total Revenue Recorded

TotalRevenueRecorded = SUM(FactOrders[Revenue Recorded])

Dashboard

After cleaning and the required aggregations, we designed the following executive dashboard to help the executive understand, draw insights for informed decision making.

Business Questions and Findings

Question 1: What is the overall general business performance and comparisons?

The business had:

Total Recorded Revenue: A revenue of 1.77Bn was recorded with 2025 recording KSh. 1.12Bn(63.36%)8 and 2026 recording *0.KSh. 53Bn(30.63%) of the total revenue recorded. Only 6.11% equating to KSh. 0.1Bn of the total revenue was not classified to either of the years. Thika branch recorded the most recorded revenue of KSh. 385.02 million compared to the Athi River branch which recorded the lowest revenue of KSh. 158.42 million. By region the Central region had the highest revenue accounting for KSh. 385.02 million compared to the Eastern Region with a revenue of KSh. 158.42 million.
Profit and Profit Margin: A net profit of KSh. 447.52Mn was recorded. Nairobi branch recorded 151.37Mn compared to Eldoret branch with a 21.34Mn profit. Grace Njeri earned KSh. 141.26M while Kevin Mwangi earned KSh. 8.59M in profits.
Order to Delivery Days(OTD): An overall average of 23 days were required for an order to be delivered. However, Nairobi region had the highest OTD of 40 days while Central had 17 days. By channel, Facebook had the highest OTD, 32 days and Instagram had 16 days.
Cancellation Rate:* An average cancellation rate of 7.35% was observed. Nairobi branch had an historical cancellation of 15% the highest while Mombasa had 2.78%. By channel, Tenders had the highest cancellation rate of 18.75% while Instagram had 2.94%.
Transit Days: An average of 26 days compared to 23 days of OTD.
Orders: A total of 245 orders were made, 408 units were sold. Referrals had the highest number of units sold, 61 compared to WhatsApp. Toyota pushed 116 units about 25% of the total units sold while BMW sold 11 unit which is about 2.7%.
Discount and Discount Margins: Toyota contributed to the highest discount amount of 34.46M while Isuzu contributed to the lowest discount amount of 1.73M.
Customers: A total of 245 customers who made a total of 245 orders purchasing 408 units across a period of about 2 years.

Question 2: What is the relationship between Discount Amounts and Profit Margin across branches?

Analysis revealed that high discounting does not correlate with higher net profit. Thika issued Ksh. 14.76M in discounts while maintaining a 35.91% margin due to premium vehicle mix. In contrast, Nakuru issued Ksh. 15.49M in discounts but achieved only a 12.42% margin, demonstrating margin erosion from excessive price concessions on low-margin inventory.

Question 3: Which sales channels exhibit severe delivery latencies and high logistics cost ratios?

WhatsApp leads generate high unit volume (35 units) but suffer from an extreme Average Order-to-Delivery latency of 68 days—more than double the company average of 26 days. Tenders also exhibit a high cancellation rate (18.75%) and long transit times (42 days).

Question 4: How does vehicle performance vary when evaluated by Profit Margin Tier vs. Volume Share?

Volkswagen models represent only 4.17% of total units sold (17 units) but generate an extraordinary 77.19% profit margin (Ksh 126.50M net profit). Conversely, Toyota accounts for 28.43% of total volume (116 units) with a solid 29.21% margin (Ksh 201.98M net profit). Nissan and Mitsubishi represent 'fleet volume' categories with profit margins below 10%.

Question 5: Is customer rating correlated with sales representative revenue productivity?

Interestingly, top revenue producer Faith Achieng (Ksh 347.30M revenue) holds a moderate customer rating of 3.37, while Peter Kiptoo holds the lowest rating (2.98) alongside low profitability (Ksh 15.49M). High-performing rep Grace Njeri maintains both strong revenue (Ksh 295.00M) and solid margins (Ksh 141.26M profit).

Question 6: What is the impact of payment methods on transaction cancellation and cash flow stability?

Mpesa is the dominant payment channel by volume (101 units, 24.75%) and value (Ksh 513.17M, 29.06%). Asset Financing accounts for 66 units (Ksh 219.51M) but exhibits higher processing delays, impacting delivery completion rates.

Key Management Insights

Insight 1: Premium Trim Pricing Power Drive Executive Margin Growth

Volkswagen (77.19% margin, Ksh 126.50M profit) and Toyota (29.21% margin, Ksh 201.98M profit) generate the vast majority of company net profit. High-end SUV trims (Prado, Harrier, CX-5, C200, E250) exhibit strong pricing power, whereas hatchback/sedan volume models (Fit, Demio, Axio) operate on slim margins below 10%.

Insight 2: Severe Regional Profit Disparity Between Nairobi and Rural Branches

Nairobi branch achieves an unparalleled 70.46% profit margin (Ksh. 151.37M profit on Ksh. 214.84M revenue), driven by luxury sales with low delivery overhead. Conversely, Kisumu branch yields a modest 10.64% profit margin (Ksh. 21.34M profit) burdened by Ksh. 11.94M in customer refunds and Ksh. 9.00M in discounts.

Insight 3: High Digital Lead Volume Belied by Severe Logistics Friction

Website (Ksh. 316.25M revenue) and Walk-Ins (Ksh. 300.99M revenue) represent the primary profit drivers. However, social media and messaging channels—specifically WhatsApp—suffer from acute supply chain friction, recording a 68-day average OTD time that severely harms customer satisfaction.

Insight 4: Tender Sales Suffer from Abnormally High Cancellation Rates

Tender sales channel records an alarming 18.75% cancellation rate—more than 2.5 times the company baseline of 7.35%. This stems from protracted procurement timelines (42 days transit) and strict contract specs that fail post-award inspection.

Insight 5: Concentration of Sales Rep Profitability Creates Key-Person Risk

Two sales representatives—Faith Achieng (Ksh. 131.61M profit) and Grace Njeri (Ksh. 141.26M profit)—generate Ksh. 272.87M, or 60.98% of total company net profit. The remaining 8 sales reps combined generate less than 40% of net profits.

Recommendations

Realign Vehicle Inventory Procurement Toward High-Margin Trims

Shift procurement capital away from low-margin fleet models (Nissan, Mitsubishi) toward high-margin Crossover/SUV models (Volkswagen Tiguan/Golf, Toyota Prado/Harrier, Mercedes C200/E250).
Expanding high-margin trim volume by 15% will increase total company net profit by an estimated Ksh. 65M annually without increasing overall unit storage overhead.

Overhaul WhatsApp Order Fulfillment & Digital Logistics Workflows

Establish dedicated digital logistics fulfillment protocols for social and messaging leads (WhatsApp/Instagram) to compress OTD latency from 68 days down to the company benchmark of 28 days.
Eliminating fulfillment bottlenecks will reduce customer cancellations, decrease refund liabilities (currently Ksh. 15.07M), and elevate average customer rating scores above 4.0.

Enforce Discount Guardrails & Restructure Branch Pricing Governance

Institute a strict discount approval matrix limiting branch managers to a maximum 5% discount on vehicles yielding margins under 15%. Require executive approval for discounts exceeding Ksh 500,000.
Curbing unwarranted discounting in Nakuru, Kisumu, and Eldoret will recover an estimated Ksh. 18M to Ksh. 25M in annual profit leakage.

To view and see the project visit my GitHub Page at Jcars Logistics Executive BI Solution

# Power BI Data Modelling: A Great Path to Great Analysis

Makori Nyachaki — Wed, 16 Sep 2026 19:05:44 +0000

Data Modeling in Power BI

Introduction

At a time when data is rapidly generated by different activities ranging from experiments to business, more often do we get data in different formats and structures. The data may or may not be related depending on the source. Related data in different formats and structure need to be re-organized to establish existing relationships for accurate analysis and reporting. To establish data model relationships and properties we need advanced tools that can connect to these sources. Then load the different datasets from source, and perform modelling to connect the links.

Data Modeling therefore, becomes very important. Data Modeling is the process of defining the data structure, properties, and relationships within a Data Model. Through organizing tables and columns, defining relationships between tables in the model, setting cardinality and cross-filter direction we achieve the best model which supports accurate calculations and reporting.

A Data Model is the logical representation of how data is structured and related within the data model.

Before modeling ensure that your data is clean and up to date, ready for modeling.

Why Data Modeling

Improved Performance and Scalability - join optimization, filters, and aggregations lead to a good model that is efficient and reliable. However, poorly designed models with complex relationships and redundant records cause delays, making queries slow.
Accuracy and Consistency - Guarantees the accuracy and consistency of the data used, making it reliable in reporting and drawing precise insights and improved decision making.
Supports Data Exploration - Users can use hierarchies and drill-down paths to support efficient data exploration.
Ease of Maintenance - Any changes in one model or report are reflected everywhere in real time enabling reusability and scalability, reducing the effort required to maintain reports and models as the business grows.

Components of Power BI Data Model

a. Tables and Sources

Tables are the raw building blocks of data models. They contain fields and records of particular data from different sources.
Sources, where we get our data from can vary depending on the context. We can get our data from SQL Database servers, Excel files, Data warehouse, SharePoint, and Power BI Dataflows.

Designing Source Tables

i. Fact Tables

Fact tables contain quantitative data, numerical data that can be aggregated and analyzed. Examples the Sales, Orders, or Invoices. This information is about specific events, business transaction, or measurements at the core of the data model.

Fact tables keeps one record for each transaction or event, includes only keys and metrics, and they avoid storing descriptive attributes.

Fact tables contain:

Foreign Key(FK) - a link to the Dimension table i.e. DateKey, ProductKey, e.t.c.
Measures(Numeric values) - Quantitative data you can aggregate. i.e. Quantity, UnitPrice, TotalAmount etc.
Degenerate dimensions - Descriptive data that does not warrant its dimension table. i.e. OrderNumber, InvoiceNumber.

ii. Dimension Tables

Dimension tables provide context or additional details to the data in Fact Tables. They contains fewer rows while fact tables can contain a large number of rows and they are on growing.

Relationship are used to establish links and connections between the fact and dimension tables in Power BI.

Dimension table contain:

Primary Key(PK) - Unique identifier e.g. ProductID
Descriptive attributes - Text, categories, hierarchies e.g. ProductName
Hierarchy Columns - Columns that form drill-down paths e.g. Country -> City -> Town.
Natural key - original identifier from the source system e.g. ProductCode

iii. Reference and Lookup Tables

They are small but critical tables in supporting the integrity and interpretation of data. They support additional mappings or categorizations. Example: DiscountTypes or StatusCodes.

Loading Data

Open Power BI
On the Ribbon Home tab, Click on Get Data from the Data group option.

From the drop down list, choose the source of your data. i.e. Text/CSV if your data is text or comma separated file.

Choose the path to your file i.e. C:/Users/User/Downloads/file.csv and click on Open.
Click on Connect on the Power Query editor window.
Your data is extracted to Power Query for cleaning. Click on Transform - Data to clean explore and clean it on the Power Query editor.

Finally, on the Power Query Editor Window Ribbon, Click on Close & Apply from the Home tab.

Changes are loaded and applied to Power BI. We will load all our Toy Store E-Commerce Database data set for modeling using the procedure above. The data is already clean, we are going to use it for demonstrating relationships in data modeling.

b. Relationships

Relationships are an important aspect of data modeling in Power BI. Relationships are for establishing connections and links between tables of a data model. They allow related tables to communicate with each other. They are the link between Fact Tables and Dimension Tables.

Relationships determine how data in the model is filtered, aggregated, combined, and visualized. They're critical to the accuracy and performance of queries in Power BI.

Cardinality

Cardinality type defines each relationship in a model. Cardinality refers to the number of unique values in one table related to the number of unique values in another.

Types of Cardinality in Relationships

One-to-One(1:1) - Every single record in one table is related to another single record in another table. Commonly used when splitting a large table for performance and security reasons.
One-to-Many(1:N) - Refers to when a single record in one table is related to many records in another table(s). Very common type of relationship in Power BI models. The Fact table is on the Many side while the Dimension table is on the One side when linking Fact tables and Dimension tables.
Many-to-Many(N:N) - Refers to when many records in the first table are related to many other records in the second table.

Cross Filter Directions

Used to determine the direction which filters propagate.

Single - When applied, only one of the tables in the relationship can filter data across the other table. Filtering data on the other table does not affect the data shown on the first table.

Bi-Directional - When applied, data can be filtered on either direction between the tables. Filtering data in one table affects data shown on the other table and vice versa. It is less often used and can impact performance negatively and create ambiguous filters.

Cross-filter options are largely dependent on the cardinality type.

Cardinality	Cross-Filter Options
1:1	Bi-Directional
1:*	Single or Bi-Directional
:	Single or Bi-Directional

Table 1: A table showing cardinality and their cross-filter options.

Creating a Relationship

To create relationships, first you need to:

Identify the matching columns(keys).
Choose the right cardinality.
Decide on the Filter direction.

The Model View Interface

To manage relationships in Power BI Desktop:

Click the Model icon in the left sidebar (the icon with three connected boxes)

You'll see all your tables displayed as boxes with their columns listed

Lines between tables represent relationships
The line style indicates cardinality: 1 on one end and * on the many end

Auto-Detect Relationships

When you load tables, Power BI automatically recognizes columns with same data type and name and creates relationships. However, you shoul always create relationships manually or review those created automatically.

Just like in our case, Power BI automatically creates relationships between tables in our model. We will review them.

When auto-detect works well:

Column names match exactly (e.g., ProductID in both tables)
Data types are compatible
Values actually correspond between the tables

Creating Relationships Manually

Using the Managing Relationship Dialog

On the Home tab → Manage Relationships

Click New

Select the first table and column, From Table
Select the second table and column, To Table
Configure cardinality and cross-filter direction

Click Save

Step by Step for Creating Relationships for our Toy Store E-Commerce Database

Load all tables:

```
    products
    orders
    order_items
    order_item_refunds
    website_sessions
    website_pageviews
```

Open Model view and delete any autodetected relationship.

To delete a relationship:

In Model view, right-click on the line joining the products and order_items tables
From the options displayed, Click on Delete

Save your work

Create the following relationships:

From Fact Table	To Dimension Table	Cardinality	Cross-Filter
products[product_id]	order_items[product_id]	One-to-Many	Single
orders[order_id]	order_items[order_id]	One-to-Many	Single
orders[order_id]	order_item_refunds[order_id]	One-to-Many	Single
website_sessions[website_session_id]	orders[website_session_id]	One-to-One	Bi-Direction
website_sessions[website_session_id]	website_pageviews[website_session_id]	One-to-Many	Single
order_items[order_item_id]	order_item_refunds[order_item_id]	One-to-one	Bi-Direction

Table 2: Table showing relationships to be created.

Creating a relationship between the products and order_items tables

On the Home tab → Manage Relationships
Click New
Select the products table and specify the column product_id
Select the order_items table and specify the column product_id
Set cardinality to One-to-Many and the cross-filter as Single

Click Save

Complete creating all the other relationships following the procedure. You will get something similar to:

Verify by checking that filters from any dimension table correctly filter the fact table.

Conclusion

Data modeling is very crucial to building powerful reports and visualizations. It is key to great analysis because it establishes relationships, links, and connections existing in a data model making it wholesome and complete. Further, it improves query performance increasing efficiency and reliability of queries. Correct data modeling can lead to accurate reporting and result reliability, thus informed decision making. To view the complete model and data for your practice on GitHub visit Toy Store E-Commerce.

Building a Jumia Product Performance Dashboard in Excel.

Makori Nyachaki — Sat, 05 Sep 2026 12:31:13 +0000

Introduction

Building a professional dashboard is rarely a simple task; it requires careful planning and a series of strategic decisions. A clean, well-structured build ensures that complex business contexts are easy to understand, providing stakeholders with an immediate overview of the problem at hand, key findings, and actionable solutions. Clarity is the hallmark of an effective dashboard.

In this post, we will analyze Jumia's product performance to translate raw data into meaningful insights that drive business growth and improve service delivery. By examining customer engagement, pricing models, and promotional effectiveness, we can better understand the specific factors influencing product success.

Business Questions

Are larger discounts correlated with a higher volume of reviews?
Do highly-rated products naturally attract stronger customer engagement?
What is the relationship between product pricing and customer ratings?
Which products represent the top performers based on ratings and review volume?
Which products require a pivot in their current pricing or marketing strategy?

Data Methodology

Data Quality Audit

After receiving the dataset, find any errors that may exist within the dataset. We checked whether the dataset had headers, checked for formatting, duplicates, missing values, data types, and any inconsistencies. We used filters and conditional formatting to find out where these errors were. This was very helpful in identifying the errors during the data cleaning process.

Note: Copy your dataset and retain the original. Work on the copied dataset.

We found the following errors and inconsistencies in the dataset:

Mispelt headings Ratingd instead of Rating
Mismatching Header cases old prices and Current prices
Trailing white spaces and extra spaces in column 1
Duplicates: we found 3 duplicates with an exact match in all fields.
Negative review values in the reviews column
One price range in both the Current Price and Old Prices columns instead of a price value.
Improper data types in the Rating and Prices columns.
Missing values in the Rating and Review columns.

So, after copying our dataset into the new sheet to retain the original dataset, create a data table. Select all the data, go to Insert > Table from the Tables group section.

Fig 1: An image of Raw Data

Data Cleaning

Now that we have understood the structure and where the underlying issues are in our data, it is time to get rid of the messy details from our dataset in readiness for analysis.

The following are the steps and decisions that we made through the cleaning process.

For the headings, we had to change to correct spellings for Rating from Ratingd
For the price columns, we had to convert headers using the PROPER() function to convert them to proper case. That is, the first letter of each word is capitalized.
=PROPER(B1) =PROPER(C1)
B1 and C1: is the address to the Current Price and Old Price headers in the sheet. The function returns the new text formatted to proper case.
Then we used copy and paste special(Paste as Values), where we copied and pasted the new text to the header, replacing the old one.
Removed all trailing whitespace and extra spaces using the TRIM() function.

Steps

Insert a new column to the right of the Product column
In Cell B2, type the function below:

=TRIM(A2)
Press Enter to return the new text value
The column is auto-filled.

_Copy and Paste Special _

Select all the values in the B column, i.e., from B2:B116
Copy the values using the Shortcut CTRL + C
Then, select cell A2, and right- click on it
Then from the dialog box choose, Paste as Values instead of Paste.

Use the CLEAN() function to also remove non-printable characters from the Product column.

Steps

Clear the column we used for trimming
In B2 enter the following function =CLEAN(A2)
Copy the values in B2:B116, paste special in in cell A2.

Duplicates

Three duplicates were found; they were an exact match in every field. Remove them to avoid redundancy and incorrect results of analysis due to redundancy.

Steps

Select all the data
On Data Tab click on Remove Duplicates in the Data Tools group section
Select all the fields, and then click OK

A confirmation pop-up message will show you the number of duplicates removed.

Negative Reviews

For the negative values in the Reviews column, convert them to absolute values using the ABS() function.

Steps

Insert a new column to the right of the review column
On the new column, F, enter the following function in cell F2: =ABS(E2)

E2 is the address of the first review negative value

Press Enter
Copy and Paste Special the values to replace those in the review column.

Price Ranges

Used the midpoint of the price range to get the value for the particular products. We found the midpoint using the formula:

`Price = (Lower Range Limit + Upper Range Limit) / 2`

Data Types

Current Price and Old Price Columns

Used Find and Replace to replace KSh with Blanks
Converted the remaining values to Number type, then to Currency type to 2 decimal places with the symbol KSh, and used the , 1000 separator

Rating

Converted Rating to Number type, formatted to one decimal place.

Review

Converted Review column to Number type, a whole number.

Steps to Change to Number Type

Select the Column with the data
Go to the Home tab; in the Number group section, from the drop-down, select More Number formats
On the left side of the pop-up window, select Number or Currency and specify the other options.
Then click OK to apply the changes.

Missing Values

For missing values and blanks, never assume anything. Leave them blank as they are. Note that a Blank does not mean zero.

Exclude blanks from metrics that require the missing field.

Add a data status field (for example, Complete, Missing Rating) if it can help users understand better.

Enriching Fields and Excel Formulas

We added the following columns to our cleaned data.

1. Discount Amount

Use the following formula to get the values for the column:

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

Optionally, compare the advertised discount with the calculated discount:

=IFERROR(([@[Old Price]]-[@[Current Price]])/[@[Old Price]],"")

Flag any difference instead of overwriting the advertised value.

2. Rating Category

Rating values are classified as follows:

Poor < 3

Average Between 3 and 4

Excellent > 4.5

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

4.1-4.5 are not classified; if the original boundaries must be followed literally, label 4.1-4.5 as Unclassified.

3. Discount Category

Classify your Discount into the following categories:

Low Discount < 20%

Medium Discount 20%-40%

High Discount > 40%

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

4. Price Category

On the Analysis worksheet, define thresholds rather than arbitrary values. A preferred approach uses the first and third quartiles

=QUARTILE.INC(tblProducts[Current Price],1) =QUARTILE.INC(tblProducts[Current Price],3)
Name those sales as Price_Q1 and Price_Q3, respectively.
Use the following classifications

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

Remember to record the final KSH thresholds in the Data Dictionary for ease of interpretation by other users.

5. Engagement and Performance Flag

For strong 'Customer Engagement,' define a measurable rule such as review count at or above the 75th Percentile. You may also create flags for:
high discount and low rating;
high discount and low engagement;
many reviews and average rating; and
strong engagement and excellent rating
State the exact thresholds.

Note: Do not select the products subjectively.

Pivot Tables and Analysis

Calculate:

Metric	Example Excel formula
Total products	`=ROWS(tblProducts[Product])`
Average current price	`=AVERAGE(tblProducts[Current Price])`
Average old price	`=AVERAGE(tblProducts[Old Price])`
Average discount	`=AVERAGE(tblProducts[Discount])`
Average rating	`=AVERAGE(tblProducts[Rating])`
Total reviews	`=SUM(tblProducts[Review])`
Most expensive price	`=MAX(tblProducts[Current Price])`
Least expensive price	`=MIN(tblProducts[Current Price])`

Then find and return the product names with the least and most expensive prices:

=XLOOKUP(MAX(tblProducts[Current Price]),tblProducts[Current Price],tblProducts[Product])
=XLOOKUP(MIN(tblProducts[Current Price]),tblProducts[Current Price],tblProducts[Product])

Report all ties using FILTER,, not only the first match.

Scatter Plots

To establish relationships between variables, create scatter plots, using one product per point:

Discount (x) versus reviews (y);
Rating (x) versus reviews (y); and
Current price (x) versus rating (y).

Add a linear trendline, display the equation and R-squared value, and calculate the Pearson Correlation:

=CORREL(tblProducts[Discount],tblProducts[Review])
=CORREL(tblProducts[Rating],tblProducts[Review])
=CORREL(tblProducts[Current Price],tblProducts[Rating])

A PivotTable or Filter helper range containing only complete pairs may be required because blanks can cause errors in some versions of Excel.
Interpret direction carefully.
Note that correlation does not establish causation.

Ranked Tables

Create ranked tables for:

top 5 and bottom 5 products by rating;
top 10 products by discount;
top 10 products by reviews;
top 10 products by rating;
products with high discounts but low ratings;
products with high discounts but low engagement; and
products with many reviews but average ratings.

Use a PivotTable(Rows: Product; Values: relevant measure; sort in ascending/descending order, and apply Value Filters > Top 10.

Use review count as a tiebreaker for rating ranks and rating as a tiebreaker for review ranks. Exclude ratings from rating lists and show how you handle ties.

Seller Performance Questions

Translate ranked tables into evidence-based answers:

Strong Demand

Which products have review counts above the engagement threshold?

Pricing/Marketing Attention

Which products combine weak engagement or weak ratings with high price/high discount?

Many Reviews and Average Experience

Which products have high engagement but fall in the Average rating category?

Promotion Inefficiency

Which high-discount products remain below the engagement threshold?

Fig 2: Cleaned Data

Pivot Tables

These are the suggested PivotTables:

PivotTable	Rows	Values	Recommended visual
Rating mix	Rating Category	Count of Product	Doughnut or column chart
Discount mix	Discount Category	Count of Product	Column chart
Price vs rating	Price Category	Average of Rating	Column chart
Engagement by discount	Discount Category	Average/Sum of Review	Column chart
Top products by rating	Product	Average of Rating	Horizontal bar chart
Top products by reviews	Product	Sum of Review	Horizontal bar chart
Top products by discount	Product	Average of Discount	Horizontal bar chart

And use Insert > Slicer for Rating Category, Discount Category, and Price Category. Connect each slicer to every compatible PivotTable using Report Connections (or Pivot Table Connections).
Remember to test each slicer independently.

Fig 3: Pivot Tables and Pivot Charts

Dashboard Design and Slicer Connections

Here is the structure of the Jumia Product Performance dashboard:

+------------------------------------------------------------------+
| Title, refresh date, and slicers                                 |
+------------------------------------------------------------------+
| Total Products | Avg Price | Avg Discount | Avg Rating | Reviews |
+------------------------------------------------------------------+
| Top 10 by Rating | Top 10 by Reviews | Top 10 by Discount        |
+------------------------------------------------------------------+
| Discount vs Reviews | Rating vs Reviews | Price vs Rating         |
+------------------------------------------------------------------+
| Rating Mix | Discount Mix | Key Insights / Recommendations       |
+------------------------------------------------------------------+

Dashboard Standards

Ensure a clean and tidy dashboard is created. Use consistent color, formatting, font, and relevant symbols for the different columns.

Fig 4: Image showing Jumia Product Performance Dashboard

Key Findings

High discounts do not mean a high number of reviews or better engagement. From the analysis, there is no relationship at all between discount and reviews. The correlation coefficient between discount and reviews is -0.14.
There is a weak relationship that establishes no pattern between rating and reviews. This indicates that more reviews do not translate to higher ratings in the data; hence, no causation. The correlation coefficient stands at 0.06.
The analysis establishes a weak positive correlation between prices and ratings of 0.11. Prices are not influenced by the rating of the product.

Business Recommendations

Investigate quality issues on highly discounted products but have low ratings.
Find out why highly rated products have higher prices than the average and lower-rated products. Does this influence come from sales made?
Combine promotions of products with high ratings and higher prices to increase engagement and check if they can translate to sales.

Limitations and Lessons

We've learnt valuable lessons throughout the project. We were able to use and apply functions, build PivotTables and Pivot Charts, build a clean and interactive dashboard, access price, discount, rating, and engagement relationships, clean data for real data analysis, and communicate findings, limitations, and business recommendations. This will go along way in helping sellers to make informed business decisions for their businesses.

Conclusion

None of the performance metrics- discount, rating, and reviews translate to better engagement. They do not influence either metric or engagement.

Links to GitHub

To review and see the completed project, visit Jumia Product Performance Dashboard on GitHub.

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

Makori Nyachaki — Sat, 29 Aug 2026 22:12:42 +0000

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

Excel

Introduction

Excel is one of the most foundational tools critical to Data Analysts. It is useful for preparing data for analysis. Excel can be used from the point of data collection to creating dashboards. It is the foundational tool used by most Data Analysts in cleaning, reformatting, and structuring the dataset ready for analysis. Excel provides unique technical tools that support collecting, cleaning, analyzing, visualizing, and reporting. Removing duplicates, filtering and sorting, validating data, aggregating, and performing some statistical operations are some of the major tools that Microsoft Excel supports and are useful during data analytics.

Excel provides a ton of benefits to many organizations and businesses. It can serve as a database for storing and retrieving business data, i.e., sales data; it can serve as an electronic ledger, a data analytics tool, and many more functions.

Understanding Excel

Before diving into data analytics using Excel, we first need to understand Excel and all its functional components that are core to data analysis. This will give us an overview of the Excel interface and basic functions for the different components.

Interface

Ribbon - Contains all the command tabs you will work with. The tabs are: File, Home, Insert, Draw, Page Layout, Formulas, Data, Review, View, Help, and many others. These tabs have more groups from which we can access and apply different commands to our dataset.
Toolbar - At the top right corner of the workbook. Used to hold and display the most common commands we use in Excel, like "Save", "Undo", "Redo", or/and any other command you may wish to add to the toolbar.
Scroll Bar(Horizontal and Vertical) - Scrollbars at the end of the worksheet. The vertical scrollbar is used to scroll through your worksheet up and down, and vice versa. The horizontal one helps you scroll through his/her worksheet from left to right and back.
Worksheet - Where you work from. It contains cells where you do your data entry.
Columns - Named alphabetically and move vertically in the worksheet.
Rows - Named numerically and moves across/horizontals in the worksheet
Cell - An intersection between a row and a column. Where we enter our data.
Formula Bar - Where you enter and edit your formulas in Excel.
Name Bar/ Address Bar - Displays the address of the active cell.

Workbook - One or more worksheets existing in the same Excel file.

Home Tab

Most of our formatting commands will be done from this tab. It is divided into around 8 groups:
- Clipboard: From here you can access the Copy, Cut, Paste, and Format Painter commands.
- Font: From here you can access text formatting commands. They include: Bolding, changing font face, font size, borders, highlighting, font color, italicizing, underlining, and many more commands.
- Alignment: You can access text alignment(Vertical and horizontal) commands, orientation, and indentation commands.
- Number: Where you can access different data type formatting features. i.e., date, currency, number, and many more.
- Styles: Conditional Formatting, Formatting as Table, and applying different cell styles.
- Cells: You can insert, delete, and format cells from this group.
- Editing: AutoSum, find and replace, filter, and sort command options are accessible from this group.
- Add-ins: Where you can access your Excel add-ins.

Insert Tab

A very important tab too. We can access the following groups essential for data analytics:
- Tables: We can work with Pivot Tables from this group
- Charts: We can create and insert charts from this group during our visualization and reporting.
- Sparklines: Insert sparklines for monitoring trends in our analytics.
- Filter: You can insert slicers and timelines, which are used for filtering our data and output in the dashboard mostly.

Formulas Tab

This tab is very essential and critical to data aggregation and statistical manipulation. The tab contains formulas and functions that are used to carry out mathematical and statistical manipulation on all numeric and textual data. It contains the following sections:
- Function Library: Contains Financial, Logical, Text, Date & Time, Lookup & Reference, Math & Trigonometric, and many more functions used while working with our dataset.
- Formula auditing: Contains tools that are used to understand the logic and structure of complex formulas(Dependents and Precedents).

Data

It contains the following groups, which serve an important role in preparing our data for analysis:
- Get & Transform: Used to import data from different sources, i.e., web and CSV files. Also, it is useful in doing transformation of datasets into Excel-compatible datasets.
- Sort and Filter: Used for sorting and filtering our dataset. This is important when you want to understand your data ranges or find anomalies that exist in your dataset for correction and further investigation.
- Data Tools: From here, we can get data validation commands, remove duplicates, convert text into columns, and many other commands that help maintain data consistency, reliability, and integrity during collection and analysis.
- Forecast: Used to do what-if analysis and forecast what will happen in the future to help with reporting and in making informed decisions.

Data Analysis

Introduction

Data analysis is the process of investigating a dataset to uncover patterns, trends, and insights for informed decision-making and to drive growth and success in business and organizations. Through data analytics, businesses and organizations can solve problems, improve operations, and optimize stakeholder decisions. Therefore, data analysis involves transforming raw opinions or data into meaningful facts or information.

Data analysis is crucial across fields like marketing, sales, health, finance, sports, and retail industries. It helps industry owners to be decisive and informed for a better future that fosters growth.

Types of Data Analytics

These are the types of data analytics. They all serve to achieve a common goal.

Descriptive - Used to answer the question: what happened? It investigates and summarizes historical uncover trends and patterns. Involves data aggregation and visualization.
Prescriptive - Used to answer the question: what is likely to happen next? It uses models to foretell what the future looks like based on past events. Involves machine learning and linear regression.
Prescriptive Analytics - Answers the question: What should we do? It recommends actions by combining data analysis with optimization techniques. Involves algorithm optimization.
Diagnostic - Answers the question: Why did it happen? Uses statistical methods to identify the cause. Involves correlation analysis and hypothesis testing.

Steps in Data Analysis

There are several steps involved in data analysis. The sequential steps are important if you want to begin collecting data by yourself.

We will use a publicly available dataset to illustrate the steps involved in data preparation before the actual analysis. Some of the steps may require actual data collection; hence, our dataset will not be used in those steps but rather in the preceding steps.

We are going to use the Cafe Sales - Dirty Data for Cleaning Training Dataset, available for download from Kaggle.

Note: Before you start the process, ensure that you keep a copy of your data.

Data Preparation

This is the process of collecting, cleaning, transforming, and analyzing the collected data. It readies

Data preparation ensures its accuracy, reliability, consistency, and regulatory compliance. These increase the level of trust and collaboration.

Data Collection

Data collection involves gathering raw opinions and facts right from the source.

There are various methods involved in data collection, which include but are not limited to: Conducting interviews, surveys, API integrations, IoT devices, web scraping, observation, or getting it from third-party sources.

The data is then loaded into tools like spreadsheets for further activities like cleaning and analysis.

Data Quality

Before the cleaning process, data is assessed to determine whether it meets the data standards that can help us avoid costly mistakes such as misleading results and flawed decisions.

Ensuring data is accurate, timely, complete, consistent, relevant, and of integrity is the key objective in this step.

Data Cleaning

Data cleaning ensures that datasets are complete, accurate, and consistent before analysis. This is achieved by getting rid of errors, fixing broken data, and addressing all issues that the dataset might have.

Data cleaning helps us to handle missing values, duplicates, outliers, inconsistent formatting, and irrelevant data.

From this point forward, we will be using our dataset to demonstrate how we can handle these issues we have listed. We are cleaning our data using Microsoft Excel. Open your CSV dataset using Excel.

Open the Dataset

- Navigate to the project folder `excel-data-cleaning/`
- Then go to the data folder `data/`
- Double-click on the file `dirty_cafe_sale.csv`; it will open in Excel.

Saving a copy of your dataset in Excel format

- In  excel on the Ribbon click on the *File* Tab, then go *Save AS*
- Choose your save location. In our case, it is in the project root folder, then in the `dashboard/` subfolder.
- Give it the name, in our case *cleaned_cafe_sales.xlxs*
- Click on *Save*

Now you have made a copy of your dataset, which is a working file. So inspect your file carefully to determine the missing details and existing mistakes that are visible.

You can also bold your column titles, convert the dataset into tables, and apply other basic formatting, like changing the font if the current font is not readable.

To Bold Titles

- Select the first row containing titles.
- Click on the *Home* Tab
- Navigate to the *Font* group section. 
- Click on the *Bold* icon(*B*)

Alternatively, you can use the shortcut Ctrl + B to bold your selected titles.

Converting the Dataset into a Table

- Select all your data.
- Click on the *Insert* tab
- In the _Tables_ group section, click on the Table icon.

Alternatively, you can use the Ctrl + T Keyboard shortcut after selecting your data. This helps us to easily filter and organize our data.

Filtering and Sorting

You can filter and sort your dataset to check for blanks and missing information.

*Filter*

Applying a filter to your data allows you to select a specific type of data you want to view in a given column. In our case, we want to view Blanks in the Item column.

- Select the data
- Click on the *Home* tab
- On the _Editing_ group section click on *Sort & Filter*
- From the drop-down, click on *Filter*
- A filter icon is displayed on the title column.

- Click on the Filter icon on the title of any column. In the drop-down list of commands, in the Fields section, uncheck _Select All_, scroll through the field list and select *Blanks*, and click *OK* to see only blank fields.

Note:

To clear filters, click on the filter icon on the respective column, then click on Clear Filter from Item.
You can also sort the field using the steps. However, you can click on Sort A to Z - Ascending or Sort Z to A - Descending order.
You can also filter or sort custom, i.e., Filter by color or Sort by color, in case you have already applied conditional formatting.

Since our column has blanks, we need to count the blanks to know how many there are using the Excel function:

=COUNTBLANK(B2:B10001): there are 333 blank cells, which translates to around 3.33%. This will help us to understand the amount of data that we need to clean and how it will affect analysis or the level of bias. Below 5%, we will clean our data by replacing blanks with Unknown. We will do cleaning later.

Conditional Formatting

Conditional formatting highlights duplicate values, helping in spotting patterns or identifying anomalies.

Applying Conditional Formatting

- Select the columns or range to inspect; in our case, the  _Quantity_ Column.
- Navigate to the *Home* tab.
- On the *Styles* group, Click on _Conditional Formatting_
- Choose a criteria to conditionally format your column i.e. _Highlight Cells_, then Go to _Duplicate Values_, choose a color scheme and Click *OK*.
- All duplicate values in the column will be highlighted.

These are some of the basic tools in Excel we'll use while dealing with missing data, detecting outliers, duplicate detection, identifying inconsistent formats, irrelevant data, and detecting erroneous entries.

*1. Removing Duplicates*

Removing duplicates helps analysts to avoid redundancy, which can distort analysis, inflate counts, and skew findings that can lead to wrong and misguided business decisions.

To remove duplicates

- Select all data
- Click on the *Data* tab
- In the *Data Tools* section, click on the Remove Duplicates icon

- the fields from which you want to get rid of duplicates. In our case, we want to remove all matching records, so we'll select all fields.

- Click *OK*. All duplicates will be removed, with a pop-up dialog window indicating the total number of duplicates removed.

In our case, there were no duplicate records; thus, the message was "No duplicate values found."

*Handling Missing Values*

Missing values can result from human error, system limitations, incomplete forms, or data integration issues. All these can lead to inaccurate analyses, biased models, and misleading conclusions due to the variation resulting from missing data.

Excel provides multiple imputation techniques we can use to handle missing data. These techniques include, but are not limited to, replacing missing values with the mean, median, or mode. This depends on the type of data you are dealing with. For example, a data analyst replaces the product rating with an overall average of the product rating across all blanks in the product rating column to ensure consistent review data.

We can use the following techniques in Excel to handle missing data:

1. Filtering and Sorting to Find Blanks

You first have to find where missing values are.

Apply filters as discussed above on the item column to find blanks. Replace all blanks with the text value.

Steps

- Click on the filter icon on the *Item*column.
- Uncheck all Fields, then select only the *Blanks* field and click *OK*
- On the first cell of the *Item* column, type the text _Unknown_ and copy it down through the blank cells.

To Copy the Text

- Select the cell where you typed the replacement text("Unknown")
- Drag your pointer to the bottom right of the selected cell; your pointer turns into a dark cross at the tip of the bottom right corner.
- Click and drag the cross down the empty cells to copy the text into the blank cells
- Click and save your work once all blank cells are filled, and clear the filters.

Alternatively, you can use Find and Replace. To use Find and Replace:

- Undo the changes you have made and clear all filters first.
- Select the Item column
- Navigate to *Home* tab, on the *Editing* group click on _Find & Select_

- From the dropdown options, click on Replace
- In the Dialog box, leave the Find what: text box, leave it blank. We are finding blanks
- On the Replace with textbox enter "Unknown" text, and click Replace All to make the changes.

- A message box will be displayed showing how many blanks the changes were made to.

Also, you can use the IF() function to find and replace missing values in a column. The syntax is =IF(B2="", "Unknown", B2)

Explanation

B2="": Cell referencing the value contained in it; also the condition to check if B2 is blank.
Unknown: Value returned if B2="" is TRUE, that is, it is blank.
B2: If B2="" is FALSE, it retains the value or text held in B2.

To achieve this, you need to create a blank column to the right of the item column, then enter the function in cell C2, copy and drag down the formula, then copy the values held in cell C2:C10001, then paste special (As values only) the values into cell B2. All the other text values will be replaced.

*Text Cleaning and Standardization*

Inconsistencies such as trailing whitespaces, unprintable characters, and mixed text cases are common in text-based data. However, Excel offers functions that we can use to correct these errors and mishaps.

What is an Excel Function?

A function in Excel is an inbuilt formula used to perform mathematical, logical, statistical, or financial operations in Excel. A function must begin with an equal sign.

In cleaning text data, we use functions such as:

a. TRIM()

Used to remove extra spaces that were typed into cells and fields.

In our dataset, we can apply the function to Item to remove any trailing or extra spaces.

We need a new column to do this, then later copy and paste special the values of the new column.

In Cell C2 of the new column, type the formula on it and press Enter, then copy it down the column to get the rest of the values.

=TRIM(B2)

b. CLEAN()

Used to remove any unprintable characters. Also requires a new column and later copying the values into the original column.

We are going to work on the Item column, then copy the values to the original column.

In cell C2 enter =CLEAN(B2)

Copy the formula down to auto-fill values.

c. LEFT(), RIGHT(), and MID()

LEFT() is used to extract the specified number of characters from a text value starting from the left. I.e. Extracting the first three characters of the Transaction ID we use =LEFT(A2, 3) and we get "TXN"
RIGHT() extracts the specified number of characters starting from the right. I.e., extracting the last three characters of the transaction ID in cell A2: =RIGHT(A2, 3) we get "124"
MID() is used to extract a specified number of characters starting from a specified position. I.e. extacting the 4 characters of the Transaction ID starting position 2. =MID(A2,2,4) we get "N_99"

d. UPPER(), PROPER(), and LOWER()
UPPER() - Converts a text string into all uppercase.
PROPER() - Converts a text string into proper case; that is, it capitalizes the first letter of each word. We will convert our Item column to proper case for consistency.
LOWER() - Converts a text string into all lowercase.

To convert our Item column into proper case, we need another column, then copy the values back to the original column.

- Use this function in cell C2 `=PROPER(B2)`
- Copy it down through the column for all values.
- All the Item values have been converted into proper case.

Standardizing Dates

Dates may be formatted as:

- MM/DD/YYYY
- DD/MM/YYYY
- YYYY-MM-DD

And sometimes they may be misinterpreted depending on the local regional settings.

To format a date, you may use the Excel built-in date feature by following the steps below:

- Select the date column
- Go to *Home* tab, on the _Number_ group, from the drop down list choose _More numbers Formats_.
- From the dialog window, choose _Dates_ from the right section, then specify the date format you prefer and click *OK* to apply the changes.

We'll format the Transaction Date and change the column to Date format for consistency.

Alternatively, you can use the TEXT() function to format dates. The syntax is, e.g., =TEXT(H2, "MM/DD/YYYY), where H2 is a field containing a date.

Other date functions can help us work best with dates. I.e. NOW(), TODAY(), DATEDIF(), YEAR(),MONTH(), WEEK(), DAY()... You can research them.

e. CONCAT()

Used for joining two or more texts. For example: Joining the first and last name to make one name.

i.e. =CONCAT(A1," ", B1)

This is important for preparing data for standardized formatting, reporting, or creating lookups across datasets.

There are many more functions that can be used to handle text data.

*Handling Outliers*

Outliers are errors resulting from values that are much higher or lower than the expected range. For example: finding a person with an age of 223 in the age field.

The errors may result from human error, measurement inconsistencies, or valid but extreme occurrences.

We can use Filter & Sort to work around these inconsistencies. We can also use basic statistics to determine these errors.

Basic statistics: Mean, Standard Deviation, Median, and Mode

Mean and Standard deviation are data-driven methods to define what is considered a normal dataset.

AVERAGE() function is used to calculate the mean. i.e., calculating the average Quantity in our data.

=AVERAGE(C2:C10001) We get 3.

STDEV() is used to find the standard deviation of a sample. i.e the standard deviation of our Quantity column:

=STDEV(C2:C10001) we get 1.4

Values more than 2 or 3 standard deviations from the mean may be considered outliers.

MODE() and MEDIAN() functions are used to find the value with the highest frequency and the middle number, respectively.

For missing values, depending on the type of data you are working on. The mean, mode, or median can be used to fill in the missing values, considering that the data distribution is normal.

*Data Validation and Restriction*

Data validation ensures that data entered into a worksheet conforms to specific rules or formats, minimizing the risk of errors or inconsistencies. It is important before data collection is carried out. However, you can validate your data if already been collected to avoid future data entry errors.

For example: We are going to validate our Location field to only allow "In Store", "Take Away", or "Unknown".

To access data validation:

- Select the _Location_ column in our dataset.
- Click on the *Data* tab from our Ribbon
- Click on the validation icon.

- From the dialog box, choose List as the validation criteria

- Enter the list of items, separated by commas, OR refer to a list range.
- Here is how it looks on the worksheet.

Error Alerts and Input Message

These are useful guides during data entry.

Input Message: A brief output that appears when the user selects the validated cell, offering guidance on the expected input.

Error Alert: A message that appears if the user tries to enter invalid data. Stop: blocks entry, Warning: Allows the entry but prompts the user, and Information: gives the user feedback without blocking.

These messages, if well designed they reduce confusion and improve the quality of the data.

*Pivot Tables*

Pivot tables are tools in Excel that we can use to summarize our data. They are important tools used during visualization to create charts.

To create a pivot table in Excel:

Select the data

Click on *Insert_ tab, and from Tables group click o Pivot Table
From the dialog box, choose where to enter your pivot table; choose a new sheet

Click OK
Select the fields you want to summarize using your Pivot Table.

Summary of the Complete Data Cleaning Process

Selected all my data and converted it into a table. I used the shortcut Ctrl + T.
Added BOLD to my column titles and increased their font size to 14.
Used filters and sort to inspect my data. Accessed the dataset to find missing values, outliers, or any inconsistencies within the data.
Found blanks, columns with ERROR, and UNKNOWN as their input. I used Find and Replace to fill the blanks with Unknown, and also replaced ERROR and UNKNOWN with Unknown for consistency.
Quantity Column: Used Find and Replace to replace the text strings ERROR and UNKNOWN with blanks. Secondly, I set the data type for the column to Number type, a whole number without a fraction part.
Price Per Unit Column: Used Find and Replace to replace the text strings ERROR and UNKNOWN with blanks. Converted the column to the Currency type with the United States Dollar sign as the currency prefix, formatted to 2 decimal places. From observation through sorting and filtering, using the item and its respective price per unit, all items add up to the same price for the individual item. I had to manually fill in the blanks for individual items with the correct price.
Total Spent Column: Used Find and Replace to replace the text strings ERROR and UNKNOWN with blanks. Converted the column to the Currency type with the United States Dollar sign as the currency prefix, formatted to 2 decimal places. From the observation through sorting and filtering, total spent was the product of Quantity and Price Per Unit. So, I had to recalculate the total spent on each transaction using the PRODUCT() function in a new column, then used copy and paste special to paste the new values into the Total Spent column.
Recalculated the Quantity since some values were blank, yet they had the Price Per Unit and Total Spent filled. To get the Quantity used the formula =[@Total Spent]/[@Price Per Unit]. Then used Paste Special.
Payment Method Column: Used Find and Replace to replace the text strings ERROR and UNKNOWN with Unknown. Validated the field as a list field with the following options: "Cash", "Credit Card", "Digital Wallet", and "Unknown".
Location Column: Found blanks, columns with ERROR, and UNKNOWN as their input. Used Find and Replace to fill the blanks with Unknown, and also replaced ERROR and UNKNOWN with Unknown for consistency. Validated the field as a list type with only three fields: "In-Store", "Take Away", and "Unknown"
Transaction Date Column: Found blanks, columns with ERROR, and UNKNOWN as their input replaced ERROR and UNKNOWN with Blanks for consistency. The blanks were untouched. Then converted the field to a date type with the format "DD/MM/YYYY".
Inserted a pivot table summarizing the quantity of each item.
Found the average quantity, price per unit, and total spent.
Found the maximum and minimum price per unit.
Found the Total sales using the SUM() function. i.e. =SUM(E:E)

These are the operations performed on the dataset.

Conclusion

We have learnt great and unique technical skills in data analysis. We have found how important data analysis is and the best way to carry it out. If standardized, results will be meaningful in helping stakeholders make informed decisions that drive growth. Data cleaning is the most time consuming in the whole process, but if done right, it will serve a great deal.

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

Makori Nyachaki — Sun, 23 Aug 2026 05:34:57 +0000

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

GIT

Introduction

Git is an open source version control system used to manage projects. It is fast and efficient to manage projects using git. You can easily track the project version, history, modifications, and many more project activities. It allows for ease of collaboration while working on projects as teams.

Installing Git

Download and install git from Git. Ensure you download for your respective operating system.

Configuring Git

Before using Git effectively, you have to configure your identity by setting up your username and email.

This allows to easily track any changes to your project and the associate, that is; who made the changes?

To set up your identity, follow the following steps, using Git Bash a git terminal:

Steps

To set your username

git config --global user.name "YOUR NAME"

In place of YOUR NAME type in your correct user name that will be associated with your github account activity.

To set your email

git config --global user.email "youemail@example.com"

Note: It is recommended to use your correct email that is associated with your GitHub account.

Rename you GitHub branch to main

git config --global init.Branch main

After setting up your identity you can verify your configurations to confirm your identity for each field set.

To check your:

User name

`git config --global user.name`

`git config --global user.email`

Branch

git config --global init.Branch`

You can use the following command to check all you account details instead:

`git config --global --lists`

SSH

SSH key is a secure credential used to authentication to accessing network resources confidentially without need of a password. The ssh-key contains two keys. A Public and Private key.

Public key is used publicly, that is it is shared between peers. Used to identify you remote resources.

Private key it is a pair key that is kept as a secret for identifying your local machine.

Generating an SSH Key

Using Git Bash

Before generating a new SSH Key, it is recommended to check and confirm if you have an existing key which you can use.

To check if you have an existing key use the following command:

`ls -al ~/.ssh`

The command will list all existing ssh keys. Here sample output:

If there is no existing key, you can generate a new key that you will use to link your computer with your github account.

To generate a new ssh key, follow the following steps:

Genereate a new ssh-key

ssh-keygen -t ed25529 -C "youremail@example.com"

The email should be the one associated with with your github account.

Follow the prompt by adding the name of your key, and also you can add the passphrase which is optional.

The passhrase should be memorable, you will need to use it each time you want to access you github account.

Add your key to the SSH-Agent.

Open your Powershell Terminal and follow the steps below:

- Run the following command to start your SSH Agent

    `Get-Service -Name ssh-agent | Set-Service StartupType Manual`

- Add your key to the  path using the  command

    `ssh-add <PATH/TO/YOUR/PRIVATE_KEY>`

After the command, you should specifify the path to your private key and add it to your host machine's ssh-agent. i.e. ssh-add "C:/Users/<YOUR_USER_NAME/.ssh/<YOUR_PRIVATE_KEY>

If you don't know your computer's user name, you can use the whoami command to see your username.

Your PRIVATE_KEY_NAME is the name that you gave to your key.

- Once you're done, close your Powershell terminal and return to Git Bash.

- Type the following command

    `git config --global core.sshCommand "C:/Windows/System32/OpenSSH/ssh.exe`

- Then Clip and copy your key using the following commands:

    `clip < ~/.ssh/<PUBLIC_KEY.pub>`

i.e clip < /.ssh/my_key.pub

    `cat ~/.ssh/<PUBLIC_KEY.pub>`

i.e. 'cat ~/.ssh/my_key.pub` to view your public ssh key

- After displaying your key, select and copy the key to add to your GitHub account.

Adding Your Key to GitHub

To connect your computer and git-hub is necessary to facilitate access to your remote resources from your local computer.

You need to add your SSH Keys to GitHub to facilitate the connection. To achieve this, follow the steps:

If you have an existing GitHub account:

a. Login to your account using your email/username and password.

b. From the profile section, go to Settings

c. Scroll to SSH and GPG Key. Navigate and click on New SSH Key on the SSH Key Section to your right.

e. Add the Title/Name of the key i.e. Ubuntu Laptop, the key will be used for Authentication

f. Paste your key to the available textbox.

g. Finally, click on Add Key to save your key. You'll be prompted to re-enter your git-hub account password (login password), to confirm your authenticity before saving.

Now you are ready to go. Before you start, it is good you test your connection to check if it was successful.

Run Powershell as an Administrator and use the following command to test connection:

`ssh -T git@github.com`

If the connection is successful, your username will be displayed along with a success message.

If successful, you are good to go and work on your project.

Working With Folders

To keep you project structural and more organized; you've to keep its files and scripts in folders.

This will make it easier to navigate and access the project. Follow the steps below to work with folders in Windows.

In Git Bash Terminal(Opened normally)

To navigate and change location use the following command
```
`cd <path_location>`
```

To select the current location

    `cd .`

To get to the previous location one step back use

    `cd ..`

Getting back to the root directory use

    `cd ~`

You can also move to a specific folder by specifying the path to that folder

    `cd <path/to/folder>

example: cd "C:/Users/Admin/Desktop/My-Project"

My-Project/ is a folder located in Desktop, which is for the user with the username Admin.

To create a directory or folder use
```
`mkdir <directory_name>`
```

i.e. Create a directory named My-First-Project in Desktop mkdir My-First-Project

Navigate to your created folder. Assuming you are on Desktop, use the following command:
```
`cd My-First-Project/`
```

So your bash terminal path will look like ~/Desktop/My-First-Project

To list and view items use the following command
```
`ls`
```

It will list all files and folders excluding the hidden files and folders. As shown below:

To list and view all files and folders including the hidden ones you add -a attribute to the ls command as below

    `ls -a`

To list files and display their attributes we add the -l attribute to the ls command.

    `ls -l`

Note: You can combine the attributes to list all files and their attributes i.e. as ls -al

You can delete an empty folder using
```
`rm -r <folder_name>`
```

To delete a folder together with its contents we use the rm -r <non-empty_folder_name> command.

Example: Create a folder in you my project folder using the mkdir command, inspect the folder created and its contents, then delete the folder using the rm command:

Steps

Create scripts: mkdir scripts
Navigate to scripts and back to your project folder: cd scripts/ and cd ..
Inspect scripts: ls scripts/
Delete scripts: rm -r scripts/

Note: rm without any attribute will no delete a directory/folder even if empty, the attribute -r tells the command rm that what you're deleting is a directory.

You can copy and paste contents between different folders.

To copy contents moves a duplicate of the files to another location leaving the original intact.

While using the cp command you have to specify the file/contents path and the destination address. Example:

To copy some file named hello.txt to the scripts folder; you need to recreate the folder and create the file in the root directory then copy it to scripts folder in your projects folder

Steps

In your project folder create scripts mkdir scripts
Navigate to your project root folder and create the file hello.txt touch hello.txt
Copy the file to scripts cp hello.txt ~/scripts
Inspect both locations to see that the file exists in both location. ls and ls scripts

Note: You can specify the file path in the cp command if the file is in a different location and the destination path address

i.e. cp <~/path/to/file_to_be_copied.txt> <~/path/to_destination/>

You can move a file or any content completely from the source to an new destination using the mv command.

Example: Move hello.txt file from the project's folder to a new folder named docs

`mv hello.txt docs/

Note: You can also specify the file path in case the files and destination address are in different locations i.e. mv <~/path/from_source/hello.txt> <~/path/to_destination_folder/>

Working With Files

While working in your project you will need to create different files for your project.

The different files will be saved in different directories depending on their type and use.

The organization will help us to achieve a proper project structure, easier to navigate and access our project files.

We will create our files using Git Bash terminal.

To create a file we'll use the touch command.

Create a file named hello-world.txt in your project root directory.

    `touch hello-world.txt`

Use the ls command to confirm your file has been created.
To view the contents of the file we use the cat command, since our file is empty it will display 'nothing'.
```
`cat hello-world.txt`
```
To add contents to our file we use the echo command.

Add the following text to the file hello-world.txt; "Hello, World"

    `echo "Hello, World!" > hello-world.txt`

We use the > to redirect the output to our file. > Rewrites any content in the file.

If you want to add more content to the file use >>, more text will be added to our file.

Example; add the following text to our file "I am happy to learn SSH and Git".

    `echo "I am happy to learn Git and SSH" >> hello-world.txt`

cat hello-world.txt to view the contents of the file.

* Editing you file using nano *

Nano is an inbuilt text editor in Git Bash.

It helps us to navigate through our files; scripts, text file, .... once we make mistakes.

To open our script or text file we use the nano command in git bash then specify the file we want to open, for example open hello-world.txt using nano:

    `nano hello-world.txt'

There are many more commands for nano that will help you edit your text file. For example the most common commands are:

1. Ctr + O - To save your file

2. Ctrl + X - To exit your nano text editor

3. Ctrl + K - To Cut text in the editor

4. Ctrl + U - To Paste your text

5. Ctrl + G - To find help for you nano editor.

There are many more commands of help that you can use to edit you files in nano editor.

Your First Project

Initialize your first project with git

Before running the command, check to confirm that you are in your project folder using the pwd command.

The pwd command means print working directory it shows your current location with regard to the root directory.

If in your project directory, the path will end with the name of the project folder. I.e. My-First-Project

Use the git init command.

The command is used to initialize our project directory as a git repository.

Git repository structure.

After running the above command, you may notice notification of git initializing an empty git repo...

If you list to view the structure using the ls command you may notice a .git/ folder.

The folder contains Git's internal repository information, which git uses to manage activities such as: push, commits, pull, Branches, project history, repository configurations,....

Checking the project status

    `git status`

Used to show what's happening with your project currently.

It can show things such as untracked files, staged files to be committed yet, if the working tree is clean meaning everything has been committed.

The above show that there is nothing that has been staged for commit, there are three untracked files in the initialized repository.

Staging your project files

    `git add .`

It adds your project files to git for tracking.

Checking on the status of the staged file, you should get such an output with git status command

It prepares all you changes for the next commit.

You can stage a single file by specifying the file/folder you want to stage i.e. git add <file_name.txt>.

You should see almost a similar output when using git status command after staging a single file

Using the . means you stage everything in your project folder.

Commiting you staged files

    `git commit -m "<commit message>"`

Creates a saved checkpoint.

The message should be precise and clear, related to the activity carried out. i.e. "My First Project Using Git Commit"

It does not upload anything to git-hub yet.

Uploading to git-hub

    `git push -u origin <branch_name>

Sends all the committed changes to GitHub. However, in our case we get the above error because we have not linked our local project repo to a git-hub remote repository.

We will be linking our repositories in the next section.

After running this command, you can easily check on the changes whether they reflect on GitHub via your browser.

However, for us we are yet to see these changes. This may not work since we are yet to create a remote repository which we will then connect to our local project in our laptop.

Creating a remote repository

To create a remote repository, follow the following steps:

Login to your git-hub account
In your account click on "Create New Repository"
Enter the name of your Repository as "My-First-Project"
Do not create a README, uncheck the checkbox
Provide a brief descriptions for your project. The description is: "My First Project: From a Local Folder to Git hub Using Git and SSH"
Then click on Create Repository

A new empty repository is created with the name "My-First-Project"

Navigate to your repository
Click on the code section, then click on ssh tab and copy the ssh code

We'll use the ssh code to connect our remote and local repositories before pushing our project.

Connecting our local and remote repositories

Navigate to your git initialized project folder

Type in the following command and thereafter paste the ssh code for your remote repository and hit enter.

    `git remote add origin <your_remote_ssh_code>`

i.e. git remote add origin git@github.com:MakoriNyachaki/My-First-Github-Project.git

Rename your branch

If your remote repository's branch is not main rename it to main

    `git branch -m main`

Check the remote

    `git remote -v`

Push your project to git-hub

    `git push -u origin <branch_name>`

i.e. git push -u origin main this works to upload our committed changes to our git-hub repository.

Summary

An introductory article for learning GIT and SSH. It is thrilling practical exercise for generating ssh keys, working with folders and files, connecting local project folders with remote repositories, and working with git. The introductory article requires one to do further research on so many commands that are available for the different tools we have used here in order to be an expert. Let's learn forward.