DEV Community: Tracy K

Power Bi: Data Modelling, Relationships and Joins

Tracy K — Wed, 23 Sep 2026 18:29:41 +0000

Introduction

Picture this measures that return the wrong totals, slicers that don't filter what you expect, and reports that slow down as data grows, this can usually be traced back to how tables were shaped and connected. Most Power BI issues that look like DAX problems are mostly caused by flaws in the data model.

Data Modelling

Data modelling in power bi is the process of organizing your data into tables and defining how those tables relate to each other so you can analyze it accurately and efficiently. Think of it as drawing the blueprint for your data before you build anything on top of it.
You may be wondering why a well designed data model matters. The key reasons are explained below:
1. Reporting and accuracy - correct relationships ensure visuals pull the right data and also prevents duplicating values in reports.
2. Performance - well structured model reduces query complexity and speeds up reports load time with quicker dashboard interactions.
3. Analytics - selecting the appropriate data model with the right relationships minimizes logic errors, ensuring that every derived metric produces accurate insights.
4. DAX Calculations - a good model makes it easy to write, interpret, and maintain calculations since the calculations rely heavily on good relationships in order to function correctly.
5. Maintainability - makes it easy to edit, and troubleshoot errors when need be without the hustle of making changes in individual records
6. Scalability - clean models handle growing data volumes well without major rework ensuring that new data can be integrated smoothly.

Data Modelling Approaches in Power Bi

Refers to the process of organizing tables and defining the relationships between tables so that you can use Power Bi to analyze, filter and summarize data correctly .
Reasons for structuring are:
Clean business structure- splits data and puts it in places based on the business it self
To ensure information shared on Power Bi can enable it work efficiently and correctly so that you get fast performance and nice response
I will describe the three main approaches frequently used:

Flat Table

It refers to a single table that stores all the data together, both the facts and descriptive context with no relationships to other tables.
It is mostly appropriate when dealing with a small simple dataset where data does not require complex relationships The table above represents a flat table containing the details of customers who bought products in a store

Advantages of Flat Table

It is simple to build and understand.
It is easy to import and load data since its a single table.
No relationships created hence no relationship errors.

Disadvantages of a Flat Table

High data redundancy due to repeated values across rows.
DAX functions are difficult to manage since data is not separated.
Slower performance on large datasets due to the file size and lack of compression efficiency.

Star Schema

It is represented by one fact table connected to dimension tables that are directly connected to the facts table.
The main structure is that it radiates outward like a "star" comprises of a fact table with several dimension tables surrounding it, each having a particular relationship. The diagram represents an example of a star schema where the Sales is the fact table while the other tables are dimensional tables

Advantages of Star Schema

Gives a clean way to filter data that is a clean filter path.
Makes it easy to use DAX functions since data is well stored in different tables.
Makes it easy to build visuals (charts) since numbers are all found in facts table while the labels are in dim table.

Disadvantages of Star Schema

Can have data redundancy within dimension tables when they are not properly normalized
Requires the user to have knowledge on building and managing relationships for efficient use.
Requires the user to understand concepts on grains, keys, cardinality and filter direction in order to build the model correctly.

The star schema is mostly appropriate to use on standard business reporting scenarios such as sales operations

Snowflakes Schema

It is similar to a star schema however, the dimension tables are further broken down into smaller sub dimension tables giving more information about them.
It creates a branching "snowflake-like" structure since the fact table connects to primary dimension tables which link further to smaller tables. The diagram represents a snowflakes schema where the Sales is the fact table while the rest are dimension tables where product and customer tables have sub-dimensional tables

Advantages of Snowflakes Schema

Reduces data redundancy through normalization of all the tables across multiple levels.
Makes it easy to edit data since changes are made in the respective tables in stead of interfering with the whole dataset.
Organizes complex hierarchies clearly for large datasets.

Disadvantages of Snowflakes Schema

Increased query complexity due to more table and joins required for instance an error in one dimension table affects the entire dataset.
It can be harder to write and understand DAX calculations due to the added table layers.
It is more complicated to understand and work with.

The snowflakes schema is most appropriate to use for complex hierarchical data with many sub-levels.

Facts and Dimension Tables

Facts Tables

It is a table that stores quantitative records of a business events or transactions. It tends to describe what happened.
The facts table mainly contains:
1. The foreign keys linking it to other dimension tables for example CustomerId, ProductId
2. Numeric data that can be used to perform calculations using DAX functions such as Sum, Count and Average
3. Dates marking when the event/transaction occurred.
It is important to note that the duplicates in the facts table are not removed because every record in it is unique.
It is usually the largest table in the model since it grows continuously with new transactions.

Examples of a Fact Table

FactSales — contains sales amount, quantity sold, discount, date key, product key, customer key
FactOrders — contains order value, order quantity, order date, customer key
FactTransactions — contains transaction amount, transaction type, date, account key The above shows a simple fact table showing sales made

Dimension Table

It stores attributes that describes the context about the business events/transactions. That is, it explains the who, what, where, and when behind the facts.
The dimension table mainly contains:
1. Text based descriptive fields eg names, categories and labels
2. Primary keys which are the unique identifiers which connect to the fact table as foreign keys.
Always remove duplicate records in the dimension table to avoid data redundancy.
Attributes are used for filtering, grouping, and slicing data.

Examples of a Dimension Table

DimCustomer — contains customer name, age, gender, location
DimProduct — contains product name, category, brand, color, size The above shows a dimension table showing customer details

Granularity/Grains

It is the lowest level of detail represented by a single row in a data table describing what each record of a table consists of.
The granularity of the table below indicates that each record represents a unique store, described by its storeId, storeName, city, and country of location.

Relationships in Power Bi

Refer to connection between two tables that defines the rules, helping Power Bi combine data from both tables in the visual layer(front end).
We establish relationships by matching primary keys of a dimension table to the corresponding foreign key in a facts table.
In order to build relationships, you have to define the keys, the cardinality(values inside the keys) and filter direction.

Why are relationships necessary?

Avoids data duplication eg a product's category will not be repeated in every sales row.
Easier maintenance for instance you update a customer's address once not in every transaction row.
Enables cross table analysis by allowing you to filter data by different categories.

Cardinality In Data Modelling

Refers to how data in one table relates to data in another table based on the numerical count of the matching rows.
There are two main types:
Column cardinality which is the uniqueness of data in a single column
Relationship cardinality describes how many instances one entity can be associated another entity.

One-to-One (1:1)

It exists when a single row in a table only links to one row in another table and vice versa.
A real life example is where one person can hold only one active passport at a time and that specific passport belongs to only one person.

One-to-Many (1:*)

It is the most common type of relationship used.
It exists when a single row in a table matches multiple rows in another table.
The "one" side is typically the dimension table (Products, Customers) while the "many" side is the fact table (Sales, Transactions). The above illustration implies that one customer in the dimension table can have multiple purchases in the sales table.

Many-to-Many (:)

It exists when several rows in one table can link to multiple rows in another table.
The main challenge is that Power BI struggles to determine clear filter paths for this relationship type which leads to unexpected aggregation errors.
The best solution for this is creation of a separate bridge/join table containing unique values of the linking ID from both original tables.
A real world example is a school scenario where one student can take many courses and one course can have many students. Connecting Students and Courses directly isn't ideal, since it creates ambiguous filtering. Instead, a bridge table (course roster)is introduced to link them, turning the single many to many relationship into two well defined one to many relationships.

Key Concepts In Power Bi

Primary keys - a column that uniquely identifies each record in a table, no two rows can share one key and it can't be null.
In DimCustomer table, CustomerID is the primary key — one row per customer, each ID appears exactly once.
Foreign keys - a column in one table that refers to the primary key of another table.
In FactSales, CustomerID is a foreign key referencing DimCustomer table ID. Unlike in the dimension table, this same CustomerID can and should appear many times in FactSales, because a customer places many orders.
Unique values - describes a column where no value repeats.
In a one to many relationship, the "one" side needs unique values, while the "many" side can repeat those values multiple times.
Referential integrity - is the rule that a foreign key value must always correspond to an existing primary key value in the referenced table, orphan keys result to blank rows or missing matches in your dataset.
Active and Inactive Relationships - for power bi, two tables can be connected through more than one path but only one relationship can be "active" at a time to avoid ambiguity.
Active relationships are shown as a solid line in the model view while inactive ones are shown as a dashed line.

Filter Direction

Filter propagation shows that relationships between tables control how a choice made in one table affects the data shown in related tables that is, when data is filtered in a table the changes also affect the other table.
In power bi, there are two main types of filters namely: Single-Direction Filtering
Here filters move from the "one" side of the relationship to the "many" side, but not the other way. The above model demonstrates a single filter from ProductsTable to SalesTable. When a user filters a product such as "milk" from ProductTable, Power BI narrows ProductsTable down to that product (milk) and passes this filter across to SalesTable. As a result, any chart or total based onSalesTable, such as total revenue or units sold, now only shows data for the "milk" product.

Both/Bidirectional filtering

Here filters move in both directions from the dimension table to the fact table, and back again from the fact table to the dimension table.
Though this may seem convenient for filtering, it poses possible problems such as:
1. Confusing paths – With multiple bidirectional relationships, Power BI may not know which route a filter should take, leading to blocked relationships or unexpected results.
2. Harder to follow – It becomes difficult to predict how selecting one value affects the rest of the report, unlike single-direction filtering, which always flows the same way.
3. Slower performance – Checking filters in both directions takes more processing power, which can slow down larger reports.

Joins in Power Query

Joins combine two tables using matching columns, the matching columns are identified using primary and foreign keys.
In Power Query joins are performed using merge queries tool. To demonstrate this I will use two table, the first one a CustomerTable and the second one an OrdersTable

CustomersTable

OrdersTable

Left Outer Join

Keeps all the rows from the first table and the matching rows from the second table
Records retained- all of Customers, plus matching Orders data. Unmatched customers get blanks (null) for the order columns.

Right Outer Join

Keeps all the rows from the second table and only the matching rows from the first table
Records retained- all the data from Orders, plus matching Customers data. Unmatched orders get blanks for the customer columns.

Inner Join

Keeps only the rows where the key exists in both tables.
Records retained- just the matched intersection hence no blanks anywhere in the result.

Full outer Join

Keeps every row from both tables, matching where possible and filling blanks where there is no match on either side.
Records retained- the union of both tables that is everything from Customers and everything from Orders.

Left Anti Join

Keeps only the rows in the first table that have no match in the second table.
Records retained- customers with zero orders and no columns from Orders are brought in at all since there is nothing to match.

Right Anti Join

Keeps only the rows from the second table that have no match in the first table
Records retained- Orders that do not belong to any known customer in the Customers table. This is useful for spotting orphaned or bad data.

Power Query Joins vs Power BI Relationships

A merge combines data from two tables during the data preparation stage in Power Query meaning it physically combines data while a relationship connects two separate tables in the data modelling stage. It does not physically combine the tables.
You chose merging over relationships when the two tables describe the same entities eg customer_email and customer_info while yo chose to build relationships if the tables describe different entities eg sales and customer_info.
Excessive merging affect the data model by making it larger, more complicated, and harder to maintain due to:
- More data redundancy since the same information is repeated many times
- Poor readability as it becomes harder to understand what each column represents.
- Harder maintenance as changes to one type of information may require changes to a large table.

Recommended Power BI Model

For a real world business intelligence project, I would have the Star Schema as my modelling approach of choice while for the relationship choice, I would use the One to Many relationship with Single direction filtering. This is because of the following reasons:
Query and report performance- a well designed star schema model works efficiently on power bi because of its ability to keep dimensions relatively wide and the fact table focused on transactional data.
DAX simplicity- measures are easier to write because dimensions provide clear filtering paths to the fact table
Data redundancy- descriptive attributes are stored once in each dimension, not repeated in the fact table.
Scalability - new dimensions or additional fact tables can be added without redesigning the entire model with ease.
Maintainability- separating business events from descriptive information makes it easier to modify dimensions and fact tables independently.
Ease of creating reports- report developers can drag fields from dimensions into slicers and filters and use measures from fact tables for calculations.
Model Readability- a star schema is easy to understand since the user can immediately identify which tables contain facts and which contain descriptive attributes.

Conclusion

Effective Power BI modelling is the foundation of a reliable and maintainable report. A well-designed star schema, clear relationships, and properly structured fact and dimension tables simplify DAX, improve performance, and support accurate business insights. Ultimately, a strong data model turns raw data into trusted decisions

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

Tracy K — Sun, 06 Sep 2026 12:29:03 +0000

Introduction

I have now successfully completed my training in Microsoft Excel at LuxDev HQ. Before the training, I viewed Excel mainly as a tool for viewing and organizing spreadsheets. However, I soon discovered that Excel is far more powerful and versatile than I had initially imagined.
Throughout the training, I gained practical skills in data cleaning and analysis using a variety of tools, including text and arithmetic functions, formatting and sorting techniques, and methods for correcting and managing data types. I also learned how to create pivot charts and interactive dashboards, which provided me with a better understanding of how raw data can be transformed into meaningful and actionable insights.
Upon completion of the course, it was time to move from theory to practice. The practical assignment was to develop an interactive dashboard to analyze the Jumia product dataset and uncover insights that could be be driving the pricing, promotions and customer-engagement.

Project Objective

The key questions this project aims to address are:
- whether larger discounts are associated with more reviews;
- whether highly rated products attract stronger engagement;
- whether price and rating move together;
- which products perform best based on ratings and reviews; and
- which products may need a different pricing or marketing strategy.
The provided dataset consists of 116 products listed on the rows while the columns showcasing the product name, current price, old price, the discount, customer reviews and customer ratings.
The dataset may seem virtually okay but there are some inconsistencies within it. For example, there are duplicated rows, cells are written using wrong data types and some cells are left blank. Let's work through this systematically moving from data cleaning to dashboard development and ultimately to translating the results into clear and actionable business insights.

Data-Quality Audit and Cleaning

I first downloaded the Jumia dataset file in csv format then I imported it into excel via clicking on Data tab➡️ get data➡️from text/csv. Excel displays a preview of the imported data then the content is loaded in a new worksheet. I then duplicated the query in a new sheet and renamed it as desired, this prevented me from tampering with the original data. I was now ready to begin data cleaning.

The above shows the dataset in its original form prior to any cleaning
I carried out a comprehensive data-quality audit on the raw dataset. This step allowed me to systematically identify inconsistencies such as missing values, duplicate rows, non-numeric entries, and out of range values. The findings from this audit are clearly shown in my jumia_project_dashboard.xlsx file
Next step, I clicked on the Cleaned_Data worksheet then activated Power Query in order to use it in cleaning my data since this is simpler than having to write multiple function for cleaning. On the top left there is a Query icon then clicked on Edit and performed the changes shown below:
- First I check for any duplicates- I highlighted the first three columns then clicked on Remove Duplicates this ensured that only exact-match rows were removed. Three duplicated rows were found and deleted.
- On the Product column, I used Transform then Trim to remove any unnecessary spaces in the cells while still retaining meaningful punctuation.
- To finish I used Close & Load and my dataset was loaded.
On the Current Price column, I used ctrl+h then replaced "KSh" with " ", now changed the data type from General to Currency ie Ksh2,199.00 for computation purposes. I then repeated the same process for the Old Price column.
The header Ratingd is misspelled, I also use ctrl+h to change "Ratingd➡️Rating".
For the Discount column, my observation was that once I imported my dataset from Csv format to Excel, the values were automatically converted to decimals. Therefore, the next thing was to change the data type into Percentage i.e 0.38➡️38%.
On the Review Column, all the values were negatives, I therefore treated the sign as a scraping artifact since review counts cannot be negative. Next I used If function to convert the numbers to absolute values as shown below.
On the Rating Column, the rates were written as "4.5 out of 5" though I wanted to only have the value without the "out of 5". Hence I used the IF function to make the changes as shown
The current price for Seater Elastic Sofa (row 52) is given as a range (Ksh1,620–1,980). I used the midpoint as the expected price, since it is a safer estimate than picking from either extreme. Using the formula:
=IF(B52="","",AVERAGE(VALUE(TEXTBEFORE(B52,"")),VALUE(TEXTAFTER(B52,"-"))))

The midpoint value is Ksh1,800. Repeating the same process in order to find the midpoint value of the old price which is Ksh2,700.
Data cleaning is now complete i.e ratings and prices have been standardized, validation checks are in place, and Raw_Data remains untouched.

Data Enrichment

Data Enrichment is the next step which refers to enhancing the existing dataset by adding extra useful information to it to make it more complete and accurate for analysis. For my dataset I decided to add some columns as explained below.
Data Status column that would help users understand coverage of whether a product is "Complete" i.e has both review and rating or missing either of the field using the formula:

=IF(E2="","Missing Review/Rating","Complete")

Discount Amount column so that I could compare the advertised discount with the calculated discount. Formula used to calculate discount was: Further analysis confirmed that the two values were aligned, and therefore no discrepancy was flagged.
Rating Category column that would group the ratings was either "Poor" if below 3, "Average" that is between 3–4, and Excellent if above 4.5. This left the values between 4.1 and 4.4 as unclassified, as instructor clarification was not available, I chose to apply the working assumption of Poor < 3, Average 3–4.5, Excellent > 4.5 to ensure an exhaustive three-category classification.

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

Discount Category column to to group the discounts as either "Low" if its below 20%, "Medium" if its between 20%-40% and "High" if above 40%. Formula used was:
Price Category, deriving this required the identification of the first(Q1) and third(Q3) quartile prices first. This means finding the price below which the lowest 25% of products fall and the price below which 75% of products fall i.e. the top 25% lie above this value. Formulas used were:
=QUARTILE.INC(tb1Products[Current Price],1) - for Q1 price
=QUARTILE.INC(tb1Products[Current Price],3) - for Q3 price
- I then renamed the Q1 price cell as Product_Q1 which was "Ksh 493" while the cell containing Q3 price as Product_Q3 which was "Ksh 1669.50". Next was to classify the current prices as either "Low", "High" or "Medium". All the prices below the Price_Q1 value were classified as "Low" while those prices between Price_Q1 and Price_Q3 value as "Medium" while the prices above the Price_Q3 value as "High". This was a better approach of classification other than choosing random arbitrary values.
- I used the formula below to perform the task: =IF([@[Current Price]]="","Missing",IF([@[Current Price]]<=Price_Q1,"Low Price",IF([@[Current Price]]<=Price_Q3,"Medium Price","High Price")))
- The results are shown below:

Engagement and Performance Flags

That section defined a set of yes/no labels known as "flags" that got attached to each product in the dataset based on its discount level, customer rating, and review volume relative to the overall product catalog. Rather than relying on subjective judgment, each flag was defined using percentile-based thresholds calculated directly from the dataset's distribution.
The base metrics used were:
|Field Name|Formula|Value|Meaning
|---|---|---|---|
|Engagement_Q3|=QUARTILE.INC(tb1Products[Review],3)|14|Products with 14 or more reviews are considered to have strong customer engagement/High reviews|
|Engagement_Q1|=QUARTILE.INC(tb1Products[Review],1)|5|Products with 5 or less reviews are considered to have low reviews|
|Discount_Q3|=QUARTILE.INC(tb1Products[Discount],3)|49%|Products discounted at 49% or more are considered to have high discount|
|Rating_Q1|=QUARTILE.INC(tb1Products[Rating],1)|3|Products rated 3 or below are considered to have low rating|
|Rating_Q3|=QUARTILE.INC(tb1Products[Rating],3)|4.6|Products rated 4.6 and above are considered to have high rating|
I also created flags for 4 different categories as shown:

Data Analysis

At this stage, it was time to begin analyzing the data in order to use this later in answering the key questions and derive meaningful insights.
I calculated the KPI's(Key Performance Indicator) needed using various formulas and the findings were displayed in the Analysis worksheet. as shown below.

Relationship Analysis

I copied the tb1Products data into a new worksheet named Helper_Data and filtered it to retain only rows with complete data across all four fields: Current Price, Discount, Rating, and Review. Using this cleaned dataset, I created three scatter charts: Discount vs Reviews, Rating vs Reviews, and Current Price vs Rating. Each with a linear trendline displaying the equation and R-squared value(R^2). The resulting charts are presented in the Analysis worksheet
Afterwards I calculated the Pearson correlations(r), the R-squared value(R^2) shown on your trendline is simply the Pearson correlation coefficient(r) squared i.e R^2=r^2 and the results achieved are shown below.

Products Rating

I then created tables to rank the products according to different categories required for the analysis, I used a PivotTable placing the "Product" name on the Rows field and "relevant measure" on the Value field, Sorted from Largest to Smallest and applied the Value Filter. I was required to make ranked tables for the following categories and the results posted in the Analysis sheet. From the ranked tables, the business question to be answered were:
- Which products have review counts above the engagement threshold? The engagement threshold was identified as 14 using the third quartile hence only 16 products were above this.
- Which products combine weak engagement with high discount? There were 7 products in this category. This means that even though these products have very high discounts the reviews are not many showing negative correlation between the two.
- Which products have high engagement but fall in the Average rating category? There were 6 products for this meaning they have a lot of customer attention though the ratings sit in the "average" band rather than being top performers as expected.

PivotTables and Pivot-Charts

After I ensured that everything was okay on the dataset, it was time to create several pivot tables and pivot charts that were required for analysis to compare different product measures. They are displayed in the Pivot_Tables worksheet.
Next I Inserted Slicers for Rating Category, Discount Category and Price Category. I then Reported Connection for the slicers this is a very important step because it enables the user to filter all charts simultaneously when interacting with the dashboard.

Final Dashboard

Now came the fun part creating an interactive Excel dashboard where all the KPI's, relevant charts, slicers, key insights and recommendations would be displayed. The dashboard should be limited to information that is essential to client and to the insights the analyst intends to convey.
Orange was selected as the dashboard's primary color to reflect the established brand identity of Jumia, the client for this project.

Key Findings

Higher Discounts do not necessarily attribute to high customer engagement of products. Therefore, sellers should not rely solely on offering high discounts on products with the expectation of getting more reviews resulting from more sales.
The total number of Reviews a product has does not attribute to it's Rating. From the dataset it was clear that popular products are not always the best rated and high sales volume does not mean high satisfaction
Products with higher prices tend to have slightly higher ratings than the rest implying that price may signal quality, or high end buyers may simply be easily satisfied by their purchases.

Recommendations for Jumia Sellers

Investigate the quality on highly discounted, low rated items. This includes reviewing the products for any defects, checking listings accuracy and and identifying possible delivery challenges before offering deeper discounts.
Run controlled tests across different discount levels to see which level actually drives better customer engagement, instead of assuming bigger discounts always results to more sales.
Improve the titles, photos, descriptions, and specifications of products with high engagement but average ratings, since these products already reach a large audience volume.
Encourage customers to submit ratings and reviews after purchase to improve the quality and completeness of future analysis.
Promote products with both strong ratings and strong engagement as trusted picks because they have demonstrated customer interest and satisfaction.
Collect sales, revenue, returns, delivery, and customer complaint data in future analysis to create a more complete view of product performance.

Limitations

Small sample sizes where some groups had few data points making averages less reliable.
Missing data point since for the Reviews and Rating, half of the cells were blank resulting to inefficient data for analysis.
Due to the absence of sales or revenue figures, review counts stood in as an engagement measure though not confirming the actual sales.
Correlation does not establish causation. The relationships identified in this project describe patterns rather than proving cause and effect.
The dataset does not include important business variables such as units sold, revenue, profit margin, returns, delivery time, or customer complaints.

Key Lessons

Set thresholds before analyzing by defining "high," "low," and "average" upfront which keeps the analysis objective.
Treat reviews, ratings, discounts, and prices as separate measures because they represent different aspects of performance.
Use Power Query for repeatable data-cleaning steps.
Brand alignment builds trust hence using Jumia's orange made the report feel client specific and not generic.
Keep the dashboard focused on the information required for decision making.
These insights are a starting point, not a final verdict. As more data becomes available, revisiting this analysis will sharpen the recommendations and surface new patterns worth acting on.
The complete project can be accessed on GitHub using the repository link below: https://github.com/Tracy-Kihara/Jumia-Product-Dashboard

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

Tracy K — Sun, 30 Aug 2026 20:42:32 +0000

Introduction

Microsoft Excel is a spreadsheet program developed by Microsoft used to collect, organize, analyze and visualize data. It organizes data into rows and columns, allowing for complex computations and visual representations.
It is a powerful and versatile tool for data analysis, while effective data cleaning ensures accuracy providing a strong foundation for meaningful insights and informed decision-making.

The Excel Interface

An excel file is known as a workbook.
A workbook is made up of worksheets that can be renamed as desired for easier identification.
An excel worksheet is made up of rows and columns. The rows are horizontal lines of cells numbered 1,2,3... while the columns are vertical lines of cells labeled A,B,C...
A cell is an intersection of a row and a column where you enter data such as A1
A ribbon is the toolbar across the top that contains all commands organized into tabs that is: Home, Insert, Draw, Formulas, Page Layout and Data.
Once you open an excel workbook containing data, it is advisable to open a new worksheet and copy paste the existing data which you will use in data cleaning so that you do not alter the original data.
Below is an example of an excel workbook:

Data Types

Data types are the different forms of data that can be entered in an excel sheet. They comprise of:
- Text -words e.g Nairobi
- Numbers - used for counting and computations e.g 17, 40, 56
- Date - e.g 20/08/2004
- Currency - money amounts e.g $500
- Formula - a command that performs calculations e.g =A1+B1

Data Formatting

It changes how cell values appear without modifying the underlying raw data, it makes data look more clear and well organized.

Basic Types of Formatting

Font- it changes the color, type and size of a text.
Bold(B)/Italics(I)/Underline(U)- changes the style of a text. Example John➡️John, John➡️John and John➡️John.
Borders- adds lines on a cell.
Horizontal Alignment- positions data to the left, center or right
Vertical Alignment- positions data to the top, middle or bottom
Wrap text- makes long text to appear on multiple lines in the same cell

Number Formatting

It controls how numbers look in a cell depending on the format applied. -Go to the Home tab, in the Number group, choose a format from the drop-down list. Examples:
- Number- 1234.567➡️1,234.56
- Currency- 1234.57➡️$1,234.56, Ksh1,234.56
- Percentage- 0.25➡️25%
- Fraction- 0.25➡️1/4
- Date- 2021-12-01

Data Cleaning Using Text Functions

It refers to removing incorrect, unwanted, or messy data so that it is accurate and easy to use by use of various text functions

Upper()- converts text to upper case
Lower()- converts text to lower case
Proper()- capitalizes first letter of each word
Left()- extracts leftmost letters in a word
Right()- extracts rightmost letters in a word
Trim()- removes extra spaces in a word
Len()- shows the number of letters in a text
Mid()- displays the characters from the middle in a word

Case Example

Below is an excel worksheet containing data that needs to be cleaned:

Cleaning the Patient ID column

Some data consists of numbers while others consist of numbers and texts. The proper data type should be text since patient ID is an identifier and although it is made up of values, there is no computation that can be done.
Select the column, press ctrl+h for the find and replace tool, replace "PM-" with " ". This will omit the unwanted text as shown.

Cleaning the first and last name column

Use the Proper() function discussed earlier to ensure that text in the first and last name column are written in proper case. ⬇️

Cleaning the gender column

The gender column consists of texts that have been misspelled, the goal is to ensure that all the cells are either "Male" or "Female".
Use ctrl+h to find all misspelled texts and replace them. ⬇️

Cleaning the bill amount column

The column consists of values in both text and number data types as well as values in different currencies.
The words formatted on the left have text data type while those formatted on the right have number data type.
First delete letters appearing before the numbers then use the currency format and select "Ksh" to ensure the currency is in Kenyan Shilling.

Creating an email column

Assuming you want to add a new column containing the patients email addresses, you can use Concat() function to join the first and last names, then, use lower() and concat() functions together to create an email address as shown below:

Sorting and Filtering

Sorting arranges data in a specific order. This include:
- Text Sorting (A to Z or Z to A)
- Number Sorting (Smallest to Largest or vice versa)
- Date Sorting (Oldest to Newest or vice versa)
Filtering shows only the records that meet specific conditions while the other records are temporarily hidden. Common types of filter are:
- Text Filter(Contains, Does Not Contain, Begins With)
- Number Filter(Greater Than, Less Than, Between)
- Date Filter(Before, After, This Month, Last Year)
- Color Filter(Filters by cell or font color)

Functions in Excel

An excel function is a preset formula that performs specific calculations.
Sum()- adds numeric values in a range.
Average()- calculates the mean of values.
Max()- finds largest value.
Min()- finds lowest value.
Count()- counts how many numbers are in a range of selected cells
CountA()- counts the number of cells that are not empty both numbers and texts.
If()- checks whether a condition is true or false and returns a specified result.
CountIf()- counts the number of cells that meet a specific condition.
SumIf()- adds values that meet a specific condition.

Conclusion

This article provides an introduction to the basics of excel, but there is so much more this powerful tool can do. Mastering excel is a continuous journey that requires practice, consistency, and a willingness to learn. Start your journey with excel today to turn data into insights, and insights into impact.

Git Workflow

Tracy K — Sun, 23 Aug 2026 07:30:21 +0000

File Creation and Working directory

*From your local device, locate and open git to access git bash
*From the git bash command execution area, enter the command cd to take you to the home path.
*Then enter command cd Desktop to navigate to the desktop folder.

*Use the commands below to create a new folder *
*mkdir my-first-project. This opens a new folder. There are two options in naming:
*use hyphens to separate the name not spaces
*write the folder name under quotation marks e.g 'my first project'. This prevents generation of three individual folders instead of one.

*cd my-first-project. It takes you inside your created folder hence, this will be your working directory.
*touch filename.extension. This creates a new file that is specific to the type of extension you want. For example, data.xlsx for excel, script.py for python and notes.txt for text file.

One file creation, you data edit and add content in your respective created file above.

GitHub repository creation

*Open your browser and log into github.
*From the home page, on the top right locate (+) icon and select new repository to create a new repository.
*Give your repo an appropriate name, add a description which should be clear and precise but not mandatory.
*You can make the repo either public or private depending on your preference. Public repo is visible to everyone while private repo is only visible in your account.

Staging and commit

To stage your files, use commands below
*from the working directory in git, use the following commands:
*git init to initialize your git
*git add . to move your files e.g script.py from working directory to the staging area. The staging area is where your files stay before they get committed.
*git commit -m 'initial commit' to move your files from the staging area to the local repository. Local repository is a hidden repo that is managed by git.

*Go back to github , locate your created repo, open the repo and find the green button written 'code', click on the drop down symbol and copy the link that is under https.

*git remote add origin github_https_link'. This connects a remote repository hosted on GitHub to your local Git repository on your PC.
*git push origin main to push your files from your local computer to your github repository