DEV Community: MERCY MUMBI WAHOME

Power BI: Data Modelling, Relationships and Joins

MERCY MUMBI WAHOME — Sun, 20 Sep 2026 13:07:38 +0000

Introduction

When I first approached a health dataset in Power BI, I was more interested in the dashboard than the structure behind it. I wanted to create visuals that could answer practical questions such as: How many patients visited each facility? Which health services were most used? How did patient visits change over time? Which locations recorded the highest activity?
I quickly realised that creating a chart was only part of the job. For a visual to answer a question accurately, the data underneath it must also be organised correctly. If tables are poorly structured or relationships are incorrect, a dashboard can look professional while still producing misleading results. This made data modelling an important part of my Power BI project.

Data Modelling

Data modelling involves organising tables and defining how they connect so that Power BI can correctly interpret and analyse the information. A good model supports accurate calculations, simpler DAX, efficient filtering, better performance and easier maintenance. I therefore began looking at my health dataset not just as rows and columns, but as information representing real-world events and entities. A patient visit, for example, is an event, while a patient, health facility, service or date provides descriptive information about that event.
This article follows my process of turning that health data into a model that can support reliable reporting. I explore fact and dimension tables, grain, flat, star and snowflake schemas, keys, relationships, cardinality, filter direction and referential integrity. I also examine Power Query Merge Queries and the six major join types, while explaining why a join is not the same thing as a Power BI relationship.
The goal is ultimately simple, to build a data model that allows the dashboard visuals to answer health-related questions accurately and flexibly.

Starting with Data Modelling

Before creating relationships or choosing a schema, I needed to understand what I was actually modelling. For this project, I used a simplified healthcare dataset built around patient visits. The information could be organised into tables such as:

FactVisits – records individual patient visits and numerical information such as visit count or service cost.
DimPatient – contains patient-related descriptive information.
DimFacility – contains health facility information.
DimService – describes the health services provided.
DimDate – contains dates, months, quarters and years.
DimLocation – contains geographical information such as county and sub-county.

This immediately gave me an important modelling question: What should each table represent, and how should the tables communicate with one another? A poor model could result in duplicated information, confusing relationships, inaccurate totals and complicated DAX calculations. It could also make the report harder to expand when new facilities, services or dates are added.
A good model, on the other hand, gives Power BI a clear structure. It also makes visualisation more flexible. For example, I should be able to select a facility from a slicer and have the relevant patient visits and services update automatically.

A structured data model serves as the foundation for comprehensive analysis, driving success in crucial areas such as:

Accurate Reporting – Ensures that visuals and figures reflect the underlying data correctly, reducing errors and misleading results.
DAX Calculations – Well-connected tables make it easier to create reliable measures and calculations without unnecessarily complicated formulas.
Performance – A properly structured model helps Power BI process queries efficiently, allowing dashboards and reports to respond faster.
Scalability – A good model can accommodate additional patients, facilities, services, and transactions as the health dataset grows without requiring a complete redesign.
Maintainability – Clearly organised tables and relationships make the model easier to understand, troubleshoot, update, and manage over time.

From a Flat Table to a Data Model

I started with the idea of storing all the health information in one flat table, where each row contained details about a patient visit, including the patient, facility, service, date, location and cost. Although this approach is simple, information such as facility names and service descriptions would be repeated every time a patient visited.
To make the data easier to analyse and maintain, I moved from a flat table to a structured data model. Instead of keeping everything in one table, the information is separated into a FactVisits table containing the actual visit records and several dimension tables containing descriptive information about patients, facilities, services, dates and locations.
These tables are then connected using keys and relationships. This approach reduces unnecessary duplication and provides a clearer structure for Power BI to filter data, perform DAX calculations and create accurate, flexible visualisations.

Fact Tables, Dimension Tables and Grain

Once I moved away from the flat table, the next question was how to organise the information. In this health dataset, I separated the data into a central FactVisits table and several dimension tables.
The FactVisits table records the actual events taking place. Each row represents a patient visit and contains measurable information such as the number of visits and cost, together with keys that connect the record to the relevant patient, facility, service and date.
An important concept here is grain, which defines exactly what one row in the fact table represents. For this model, the grain is one patient visit for a particular service, facility and date. Defining the grain clearly helps prevent double-counting and ensures that calculations and visualisations represent the health activity accurately.
The resulting structure can therefore be viewed as:
DimPatient → FactVisits ← DimFacility
DimService → FactVisits ← DimDate
DimLocation → FactVisits
This creates the foundation of a star schema, where the fact table sits at the centre and the dimension tables provide descriptive context around it.

Relationships in Power BI

After separating the health data into fact and dimension tables, the next step was to connect them. In Power BI, a relationship defines how two tables are linked using a common key. This is important because the information needed for analysis is often distributed across different tables rather than stored in one place.
In my model, FactVisits contains the actual patient visit records, while the dimension tables provide additional context. Connecting these tables allows Power BI to identify which patient each visit belongs to without repeatedly storing the patient's name and other details in the fact table.
The same approach is used for facilities, services, dates and locations. The dimension tables contain unique primary keys, while the fact table contains the corresponding foreign keys, which may appear multiple times because the same patient, facility or service can be associated with many visits.
These relationships allow filters and calculations to move between the tables. For example, selecting a particular facility can filter the visits recorded for that facility, allowing a visual to show the number of visits or total cost associated with it.
For this health dataset, the relationships form a star schema, with FactVisits at the centre and the dimension tables surrounding it. This structure keeps the model organised and makes it easier to create accurate and flexible reports.

Cardinality

With the relationships established, I then considered cardinality, which describes how many records in one table can relate to records in another table. Understanding cardinality is important because it determines how Power BI interprets the connections between tables.
The most common types are one-to-many (1:*), one-to-one (1:1) and many-to-many (:).
In my health data model, the main relationship is one-to-many (1:*). For example, one patient appears once in DimPatient, but that patient can have many records in FactVisits. Similarly, one facility can have many patient visits, and one service can be recorded across many visits. This makes the 1:* relationship appropriate for the star schema.
A one-to-one (1:1) relationship means that each record in one table corresponds to only one record in another table. This can occur when information about the same entity has been separated into two tables, although combining the information may sometimes be simpler.
A many-to-many (:) relationship occurs when multiple records on both sides can correspond to multiple records on the other side. For example, if patients could be associated with multiple health programmes and each programme could include multiple patients, a many-to-many relationship would exist. In Power BI, these relationships need to be handled carefully because they can create ambiguous filtering and unexpected results.
For this dataset, one-to-many relationships are the natural choice because the dimension tables contain unique identifiers while the fact table records repeated business events.

Active and Inactive Relationships

A relationship in Power BI can be either active or inactive. An active relationship is the default connection Power BI uses when filtering data and performing calculations. Inactive relationships are created when there is more than one possible relationship between the same tables, but only one can normally be active at a time.
A common example involves dates. A health organisation may want to analyse visits using both the visit date and a follow-up date. Both dates could connect to the same DimDate table. Power BI can keep one relationship active and the other inactive to avoid ambiguity.
In my model, the current DimDate to FactVisits relationship is active because DateID represents the date of the patient visit. If another date were added, such as a follow-up date, that second relationship could be inactive and activated only when specifically required in a DAX calculation.
This approach allows the same date dimension to support different types of date analysis without creating unnecessary tables or ambiguous filter paths.

Referential Integrity

Referential integrity means that the keys used to connect related tables should correctly correspond. In this health model, every foreign key in FactVisits should have a matching primary key in its related dimension table.
For example, if FactVisits contains FacilityID = F001, then F001 must exist in DimFacility. If a visit contains a facility ID that does not exist in the dimension table, Power BI may not be able to associate that visit with the correct facility. This can lead to blank categories, incorrect filtering or unexpected results in reports.
Maintaining referential integrity therefore improves the reliability of the data model. Before creating relationships, I would check that the IDs in the fact table have corresponding values in the dimension tables. This is particularly important in health reporting, where inaccurate or unmatched records could affect the interpretation of service utilisation and facility performance.

Filter Direction

After creating the relationships, I also needed to consider filter direction, which determines how filters move between related tables. In Power BI, filters can generally flow in one direction or in both directions.
For this health model, I used Single filter direction. This means a filter flows from the dimension table to the fact table. For example, if I select Nanyuki County Hospital from DimFacility, Power BI filters FactVisits to show only the visits associated with that facility.
This approach works well with a star schema because the dimension tables provide descriptive context while the fact table contains the events being analysed. It also keeps the model easier to understand and reduces the risk of creating ambiguous filter paths.
A Both or bidirectional filter allows filters to travel in both directions between tables. Although this can be useful in specific situations, it should be used carefully. In a larger model, bidirectional filtering can create multiple paths through which a filter can travel, potentially producing ambiguity and unexpected results.

A Join Is Not a Relationship

Although both a join and a relationship connect tables, they serve different purposes and happen at different stages of working with data.
A join is used in Power Query to combine data from two tables based on a matching column. For example, I could merge FactVisits with DimFacility using FacilityID to bring the facility name into the visit data. The result is a physically combined table containing columns from both sources.

A relationship, on the other hand, is created in the Power BI data model. It does not physically combine the tables. Instead, it tells Power BI how the tables are connected so that filters, calculations and visualisations can work across them.
This distinction is important because merging everything into one table can create unnecessary duplication and make the model harder to maintain. Keeping fact and dimension tables separate and connecting them through relationships is often more appropriate for a star schema.

1. Left Outer Join

It keeps all records from the left table and adds matching records from the right table. If there is no match, the right-side information is returned as blank.
Using the health facility example, Facilities is the left table and FacilityInspections is the right table. The result keeps all four facilities, while inspection information is added where a matching FacilityID exists.
Therefore, F001 and F002 receive their inspection status, while F003 and F004 remain in the result with blank inspection information. This type of join is useful when the records in the left table must be retained regardless of whether matching information exists in another table.

2. Right Outer Join

It keeps all records from the right table and adds matching records from the left table. This join is useful when the right-hand table represents the records that must all be preserved.
The Left and Right Anti joins are particularly useful for data-quality checks because they can identify records that exist in one dataset but are missing from another.

3. Inner Join

It keeps all records from the right table and adds matching records from the left table. This is useful when the analysis requires only records that exist in both datasets.

4. Full Outer Join

It keeps every record from both tables. Matching records are combined, while unmatched records from either table are retained with blank values on the opposite side.
In this example, the result would contain F001, F002, F003, F004 and F005. This provides a complete view of facilities appearing in either dataset.

5. Left Anti Join

This returns records that exist in the left table but have no matching record in the right table.
Using the facility data, F003 and F004 would be returned because they exist in Facilities but not in FacilityInspections.
This type of join is particularly useful for data-quality checks, such as identifying facilities that have not yet appeared in an inspection dataset.

6. Right Anti Join

It returns records that exist in the right table but have no matching record in the left table.
In this example, F005 would be returned because it appears in FacilityInspections but does not exist in Facilities.
This can help identify records that need investigation before being incorporated into a reporting model.

Where Merge Queries Fits into the Data Model

Merge Queries belongs to the Power Query stage, before the data reaches the final Power BI model. It is useful when columns from one table need to be brought into another table as part of data preparation.
Not every table needs to be merged. In my health dataset, keeping FactVisits and the dimension tables separate allows the model to retain a clear star-schema structure. A relationship can then connect the tables without physically duplicating their columns.
I would use Merge Queries when data needs to be transformed or combined, but use relationships when separate tables need to work together during analysis. Excessive merging could create wider tables, duplicate information and make the model more difficult to maintain.

The Model That Fits the Data Set

After comparing the different modelling approaches and applying relationships and joins to the health dataset, I selected a star schema as the final model. The reason is that the dataset is centred around patient visits, making FactVisits a natural fact table surrounded by descriptive dimensions.
The model contains FactVisits at the centre, connected directly to DimPatient, DimFacility, DimService, DimDate and DimLocation. The fact table stores measurable events such as visit count and cost, while the dimensions provide the context needed to analyse those events.
The star structure also makes the model easier to understand. Each dimension has a unique key, while the corresponding key can appear multiple times in FactVisits, creating one to many relationships. Filters flow from the dimensions towards the fact table using single-direction filtering.
This structure supports DAX calculations, report creation and filter propagation without requiring all the information to be physically combined into one table. It also reduces unnecessary duplication and makes the model easier to maintain if additional facilities, services or visits are added later.

A flat table would be simpler initially but would contain repeated descriptive information. A snowflake schema could further separate dimensions into additional related tables, but that would introduce more relationships and complexity than this relatively straightforward dataset requires. The star schema therefore provides a clear balance between simplicity, performance, scalability and maintainability for this analysis.

Dashboard Visualisation

I could now use Power BI visuals to answer practical questions from the health data. The purpose of a dashboard is not simply to display charts, but to present information in a way that makes patterns and comparisons easy to understand.
For this dataset, the visuals can answer questions such as: How many visits were recorded? Which facilities had the most visits? Which services were most used? How did visits change over time?

The data model makes these visuals interactive. For example, selecting a facility or service can automatically filter the relevant visit records through the established relationships. This demonstrates how good data modelling supports accurate and flexible dashboard visualisation.

Lessons Learned

This project taught me that effective data analysis starts with a well-structured model, not the visual itself. I learned how identifying the grain of a fact table, separating facts from dimensions, and creating the right relationships can improve the accuracy and clarity of a report.
I also gained a practical understanding of the difference between Power Query joins and Power BI relationships. Testing the different join types helped me see how data can be combined during preparation, while relationships allow separate tables to work together during analysis.
Coming from a public-health background, I found it useful to connect technical concepts to real health information. I could see how a well-designed model can turn records about patients, facilities, services and dates into information that is easier to interpret and use.
My biggest takeaway is that a good dashboard begins with good data modelling. When the foundation is accurate and organised, analysis becomes clearer, more flexible and more reliable.

Conclusion

This project has given me more confidence in using Power BI and has made data analytics feel more practical and achievable. I am beginning to see how I can combine what I already know about health with the technical skills I am developing. This experience has encouraged me to keep learning and to explore how I can use data to solve real problems and create meaningful insights in my future work.

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

MERCY MUMBI WAHOME — Mon, 07 Sep 2026 15:38:33 +0000

Introduction: Turning Jumia Product Data into Business Insights

E-commerce platforms generate a lot of product data, but raw numbers become useful only when they can support better decisions. For Jumia sellers, prices, discounts, ratings and customer reviews can provide clues about product performance, customer engagement and possible pricing strategies.
For this project, I worked with a dataset of 112 Jumia products to explore these relationships using Microsoft Excel. I wanted to find out whether higher discounts are associated with more customer reviews, whether highly rated products receive stronger engagement, and whether product price is related to rating. I also wanted to identify the products performing best and those that may require a different pricing or marketing approach. I followed a complete data-analysis workflow:

Raw Data → Cleaning → Transformation → Analysis → Visualization → Insights → Recommendations

The project uses Excel Tables, Power Query, formulas and functions, PivotTables, PivotCharts, slicers and dashboard techniques. This article documents that process and shows how the raw Jumia data was transformed into an interactive dashboard and, ultimately, evidence-based business recommendations.

Understanding the Dataset and Its Initial Problems

Before cleaning the data, I first needed to understand what I was working with. The dataset contains 112 Jumia products and six main fields: Product, Current Price, Old Price, Discount, Review and Rating. Current Price and Old Price represent product pricing, Discount captures the promotional percentage, Review represents the number of customer reviews, while Rating records the average customer rating out of 5. I treated this stage as a data-quality audit rather than immediately changing anything. The purpose was to identify issues that could affect calculations and visualizations later.
The raw dataset contained formatting and consistency issues that needed attention, particularly around numerical fields. Prices could contain currency symbols or unnecessary characters, while ratings and review counts needed to be standardized into usable numerical values. I also needed to check systematically for missing values, duplicates and other inconsistencies before beginning the analysis.
This preliminary inspection was important because reliable analysis depends on reliable input data. Instead of correcting problems as I encountered them later, I first established what needed to be fixed and why.

Data Cleaning and Preparation

The initial dataset contained 115 products, but some records were duplicates. The Balloon Insert product appeared three times, while the 6 Layers Steel Pipe Shoe Cabinet appeared twice. I removed the duplicate records so that each product was represented only once, leaving the required 112 products for analysis.
The Review column also contained negative values such as -2, -14 and -55. Since review counts cannot be negative, I removed the minus signs and converted the values into positive numbers. At the same time, 58 products had missing review counts and ratings, so I kept these as missing rather than incorrectly treating them as zero.
The rating field was stored as text, for example 4.5 out of 5, and the column itself was labelled Ratingd. I renamed the column to Ratings and extracted the numerical rating. Price values also contained KSh and commas, so I removed these characters and converted the prices into numerical values. One product used a price range rather than a single price, which required special handling before numerical analysis.

Data Enrichment

I added calculated columns to support deeper analysis. These additional fields transformed the original variables into useful measures and categories that could later be used in PivotTables, charts, slicers and the dashboard. I created a Discount Amount column to measure the absolute difference between the Old Price and Current Price. I used the Excel formula*=C2-B2*
I also created a Rating Category column to classify products as Poor, Average or Excellent according to the project criteria. I used the logical IF and AND functions with the formula =IF(G2="","Not Rated",IF(G2<3,"Poor",IF(G2<=4,"Good","Excellent"))) .
A Discount Category column was added to group products into Low Discount, Medium Discount and High Discount based on the required percentage thresholds. The formula used was =IF(E2<20%,"Low Discount",IF(E2<=40%,"Medium Discount","High Discount")).
Finally, I added a Price Category column to segment products into Low, Medium and High price groups using the formula =IF(B2<=1275,"Low",IF(B2<=2512,"Medium","High")) for the price thresholds established for the analysis. These enriched variables provided a stronger foundation for comparing product behaviour and developing the later seller-performance analysis.

Excel Techniques and Descriptive Analysis

After completing the cleaning and enrichment stages, I created a separate Analysis sheet to calculate the key descriptive statistics for the cleaned dataset. I started by determining the total number of products using the formula =COUNTA('Cleaned Data'!A2:A113), which confirmed that the cleaned dataset contained 112 products.
I then calculated the total number of reviews using the formula =SUM('Cleaned Data'!H2:H113), which added the available review counts while leaving missing values as blanks rather than treating them as zero.
To understand the overall pricing level, I calculated the** average current price** using =AVERAGE('Cleaned Data'!B2:B113) and the average old price using =AVERAGE('Cleaned Data'!C2:C113).
I also calculated the average discount using =AVERAGE('Cleaned Data'!E2:E113) and the average rating using =AVERAGE('Cleaned Data'!G2:G113).
To identify the most expensive product, I first used =MAX('Cleaned Data'!B2:B113) to find the highest Current Price, then used** =INDEX('Cleaned Data'!A2:A113,MATCH(MAX('Cleaned Data'!B2:B113),'Cleaned Data'!B2:B113,0))** to return the product associated with that price.
I followed the same approach for the least expensive product, using =MIN('Cleaned Data'!B2:B113) to identify the lowest Current Price and =INDEX('Cleaned Data'!A2:A113,MATCH(MIN('Cleaned Data'!B2:B113),'Cleaned Data'!B2:B113,0)) to return the corresponding product name.
These calculations gave me the initial numerical summary of the dataset and established a foundation for investigating relationships between pricing, discounts, customer reviews and ratings.

Correlation Analysis

To investigate whether pricing, discounts and customer engagement were related, I used correlation analysis. Correlation measures the direction and strength of a linear relationship between two variables, with values ranging from -1 to +1. A value close to +1 indicates a strong positive relationship, a value close to -1 indicates a strong negative relationship, while a value close to 0 indicates little or no linear relationship. I conducted three correlation tests using the Excel **CORREL **function.

Discount vs. Reviews
The first test examined whether products offering higher discounts received more customer reviews. I used the Excel formula =CORREL('Cleaned Data'!E2:E113,'Cleaned Data'!H2:H113). The resulting correlation coefficient was -0.14, indicating a very weak negative relationship. In the Jumia dataset, this means that products with higher discounts tended to have slightly fewer reviews, but the relationship was too weak to indicate a meaningful pattern. Therefore, the data does not support the assumption that increasing a product's discount automatically leads to greater customer engagement through reviews.

Rating vs. Reviews
The second test investigated whether products with higher customer ratings also attracted more reviews. I used the formula =CORREL('Cleaned Data'!I2:I113,'Cleaned Data'!H2:H113). The result was 0.06, which is very close to zero and indicates an almost nonexistent linear relationship. Within this dataset, highly rated products were therefore not necessarily the products with the highest number of reviews. This suggests that customer satisfaction, represented by rating, and customer engagement, represented by review volume, were largely independent of each other among the products analysed.

Current Price vs. Rating
The final test examined whether the price of a product was associated with its customer rating. I used the formula =CORREL('Cleaned Data'!B2:B113,'Cleaned Data'!I2:I113). The resulting coefficient was 0.11, showing a very weak positive relationship. This indicates that more expensive products tended to have slightly higher ratings, but the relationship was not strong enough to suggest that price was an important predictor of customer satisfaction. In this dataset, a higher-priced product was therefore not necessarily better rated than a lower-priced product.

Top 5 Highest and Lowest Rated Products

To identify the products with the strongest and weakest customer ratings, I used Excel's LARGE and SMALL functions. These functions were appropriate because they allowed me to rank the numerical ratings and identify the highest and lowest values directly from the cleaned dataset.
For the top 5 highest-rated products, I first used =LARGE('Cleaned Data'!G2:G113,1) to identify the highest rating, which was 5. I then used the FILTER function to retrieve the products associated with this rating. Five products achieved the maximum rating of 5, making them the highest-rated products in the dataset and potential indicators of strong customer satisfaction.
For the top 5 lowest-rated products, I used the SMALL function to rank the ratings from lowest upward. The five lowest ratings was 2.0 . I then used FILTER to identify the products corresponding to these ratings. These products represent the weakest-rated products in the dataset and may require further investigation into product quality, customer expectations or seller performance.

Top 10 Products with the Highest Discounts

To identify the products receiving the highest discounts, I ranked the discount percentages from highest to lowest using Excel's LARGE function. I used =LARGE('Cleaned Data'!D2:D113,1) to identify the highest discount and adjusted the ranking number to retrieve the top 10 values. I then used FILTER to identify the products corresponding to these discounts. This showed which products were being promoted most heavily and provided a basis for comparing high discounts with reviews and ratings.

Top 10 Products by Customer Reviews

To identify the products with the strongest customer engagement, I ranked the review counts from highest to lowest using Excel's LARGE function. I used =LARGE('Cleaned Data'!H2:H113,1) to find the highest review count and then ranked the remaining values from 2 to 10. I used XLOOKUP to match each review count to its corresponding product with =XLOOKUP(F61,'Cleaned Data'!H2:H113,'Cleaned Data'!A2:A113).
The highest review count was 69, and the Top 10 results identified the products receiving the most customer reviews. These products represent the strongest customer engagement in the dataset and can later be compared with their ratings, prices and discount levels to understand what may be associated with stronger customer interaction.

High Discounts with Low Ratings

I also investigated products that were receiving high discounts but poor customer ratings. I used the FILTER function with two conditions: a discount above 40% and a rating below 3. The formula was** =FILTER('Cleaned Data'!A2:A113,('Cleaned Data'!F2:F113>40%)('Cleaned Data'!I2:I113<3),"No matching products")*. This was useful because it highlighted products where heavy price reductions were not translating into strong customer satisfaction, providing potential areas for sellers to review product quality, pricing or marketing strategies.

Products Requiring Better Pricing or Marketing Strategies

I then identified products that may require a different pricing or marketing strategy by combining three indicators: poor ratings, low review activity and discounts above 20%. I used the FILTER function with the formula =FILTER('Cleaned Data'!A2:A113,('Cleaned Data'!I2:I113<3)*('Cleaned Data'!H2:H113<5)*('Cleaned Data'!F2:F113>20%),"No matching products"). I chose these conditions because they highlight products that have low customer satisfaction, limited engagement and are already being discounted. The analysis returned several products, suggesting that discounting alone may not be sufficient for this group and that sellers may need to review their pricing, product quality, positioning or marketing approach.

Key Insights

The Jumia dataset reveals that customer response is more complicated than simply offering a cheaper price. Despite discounts reaching as high as 64%, the relationship between discounts and reviews was almost nonexistent (-0.14). This suggests that aggressive promotions may attract attention, but they do not automatically create lasting customer engagement.
A particularly interesting pattern is the separation between popularity and satisfaction. With a rating-review correlation of only 0.06, products generating reviews are not necessarily the products customers rate highly. This means a seller could have a product that attracts considerable attention while still failing to deliver a satisfying experience.
The data also shows that price is a poor substitute for perceived value. The price-rating correlation of 0.11 indicates that paying more does not guarantee a better customer experience. At the same time, the presence of products rated 5.0 alongside products rated as low as 2.0 suggests that product-level factors can create much larger differences in customer satisfaction than price alone.

Business Recommendations

Turn discounts into a targeted tool, not a rescue strategy. Sellers should use large discounts strategically on products that already demonstrate good customer response, rather than repeatedly discounting poorly rated products. A product with a 64% discount and weak customer feedback should trigger investigation before another promotion.
Build around the products customers already love. The five products achieving 5.0 ratings provide potential benchmarks. Sellers should examine what makes these products successful such as product quality, presentation, value or reliability and replicate those strengths across weaker products.
Treat poor ratings as a product problem first. Products falling around 2.0–2.2 should be reviewed for quality issues, misleading descriptions, poor fulfilment or unmet customer expectations. Reducing their price further may increase sales temporarily but could worsen customer dissatisfaction if the underlying problem remains.
Use different strategies for different products. The absence of products with more than 40 reviews and average ratings of 3–4 shows that highly engaged products in this dataset tend to fall outside that specific middle-rating segment. Sellers should therefore segment products by both engagement and satisfaction instead of applying one marketing strategy to the entire catalogue.

Conclusion

Working on the Jumia dataset was more interesting than I expected. At the beginning, I thought I would simply clean the data, apply a few Excel formulas, create some charts and be done. But as I worked through the dataset, I started noticing things that made me question my own assumptions. I expected bigger discounts to attract more customers and expensive products to have better ratings, but the data did not always support that. That was probably the biggest surprise for me.
I also realised how much the quality of the data matters. Removing duplicates, organising the information and creating useful categories may seem like small steps, but they made a big difference in how confidently I could interpret the results. I started seeing Excel differently too. It was no longer just about knowing which formula to use; it was about knowing what question I was trying to answer and what the result was actually telling me.
The part I found most interesting was watching ordinary rows of data turn into something that could tell a business story. A rating represents a customer's experience, a review shows engagement, a discount reflects a seller's strategy, and the price gives another piece of the picture. Putting all these pieces together made the dataset feel much more real.

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

MERCY MUMBI WAHOME — Mon, 31 Aug 2026 08:57:23 +0000

Introduction

When I began my journey into data science and analytics, Excel was one of the first tools I encountered. Week 1 changed my perspective by showing me that Excel is much more than a platform for entering and storing information. It can be used to organize, validate, transform, and prepare data for meaningful analysis. In this article, I explore the fundamental Excel concepts I learned and apply them to an HR dataset containing employee information. I demonstrate how to work with an Excel workbook, understand cells and data types, navigate the Ribbon and Home tab, use functions and operators, and identify common data-quality problems.
The main focus is data cleaning, since reliable analysis begins with reliable data. Using the HR dataset, I demonstrate how seemingly small inconsistencies—such as incorrect formatting, duplicate records, missing values, inconsistent text, and invalid dates; can affect the quality of information produced from a dataset. The process therefore moves from understanding Excel → entering and examining data → identifying problems → cleaning the data → preparing it for analysis.

What is Excel

Microsoft Excel is a spreadsheet application used to organize, calculate, analyze, and present data. It provides a structured environment in which information can be stored in rows and columns, with each intersection forming a cell. For data analytics, Excel is particularly useful because it combines data storage with tools for calculations, filtering, sorting, visualization, and data cleaning. It also provides functions that can transform raw information into useful insights. My first important realization was that good analysis does not begin with immediately calculating results. It begins with understanding the data and ensuring that the data is accurate, consistent, and suitable for analysis.
To begin working with Excel, I installed and opened Microsoft Excel on my computer. When Excel opens, it provides options for creating a new workbook or opening an existing one. An Excel workbook is the main file that contains one or more worksheets. A worksheet is the individual spreadsheet where data is entered and manipulated. Each worksheet consists of rows, columns, and cells. A cell can contain different types of information, including text, numbers, dates, percentages, and currency values. Recognizing these data types is important because Excel handles each type differently. For example, a date should be stored as a date rather than ordinary text if I want to sort dates chronologically or perform calculations involving time.

Exploring the Excel Interface

Excel organizes its tools through the Ribbon, which appears at the top of the workbook. The Ribbon contains tabs such as Home, Insert, Page Layout, Formulas, Data, Review, and View, with each tab grouping related commands. The Home tab is especially useful when working with data. It contains options for formatting text and numbers, changing alignment, inserting or deleting cells, sorting and filtering data, and finding or replacing information. These features make it possible to improve both the appearance and quality of a dataset without leaving the worksheet. An Excel workbook also has several useful features, including multiple worksheets, formulas, functions, formatting tools, tables, sorting, filtering, and conditional formatting. Together, these features allow Excel to support the entire journey from raw data to analysis.

Data Entry and Understanding the Dataset

Data entry involves recording information into the appropriate cells while maintaining consistency in how the information is represented. For example, numbers should remain numerical, dates should use a consistent date format, and text should be entered consistently. For my practical exercise, I used the HR_Dataset_Dirty.xlsx dataset. It contains employee information such as Employee ID, names, department, salary, hire date, age, gender, performance score, work experience, employee type, location, and training hours. Before making any changes, I examined the dataset to understand its structure and identify possible data-quality issues.

Data Cleaning

Before cleaning the data, I made a copy of the original dataset. This is an important data-management practice because the original data should always be preserved in case I need to compare changes or recover information. I created a separate worksheet for the cleaned data and worked on the copy rather than altering the original dataset. This allowed me to distinguish between the raw data and the cleaned data.
Data cleaning is the process of identifying and correcting inaccurate, incomplete, inconsistent, or incorrectly formatted data. It is an essential stage of data analytics because poor-quality data can produce misleading results. While examining my dataset, I found several examples of these problems. For instance, employee names appeared with inconsistent capitalization, departments were represented in different ways, and some dates were invalid or inconsistently formatted.

Text Cleaning and Standardization

Text cleaning involves correcting inconsistencies in text so that similar information is represented in a uniform way. Common examples include inconsistent capitalization, extra spaces, spelling variations, and different labels referring to the same category. Excel provides several useful text functions for this. PROPER capitalizes the first letter of each word, UPPER converts text to uppercase, and LOWER converts text to lowercase. TRIM removes unnecessary spaces, while Find and Replace can be used to replace inconsistent words or labels throughout a dataset.
For example, the names JOHN, john, and JoHn represent the same person but have different formatting. Using PROPER standardizes them to John. Similarly, department labels such as HR, H.R, and Hr may need to be standardized to one consistent value. In my HR dataset, I identified inconsistent capitalization in the First Name column. Some names appeared entirely in uppercase while others were lowercase or mixed. I therefore applied the PROPER function to standardize the names.

Removing Duplicates

Duplicate data occurs when the same record, or an identifier that should be unique, appears more than once. Duplicates can distort analysis by causing certain employees or observations to be counted multiple times. Excel provides Remove Duplicates **under the **Data tab. Duplicates should be reviewed before deletion because two records may share one value while still representing different records. For my HR dataset, Employee ID is particularly useful for checking duplicates because it should uniquely identify an employee. We found repeated Employee IDs, so this gives us a real example of why duplicate checking is necessary.

Formatting and Data Types

Data formatting ensures that information is displayed consistently and that Excel recognizes each value according to its correct data type. Common types include text, numbers, dates, percentages, and currency. Correct data types are important because Excel performs calculations and sorting differently depending on how a value is stored. For example, a salary should be numerical so that it can be calculated, while a hire date should be recognized as a date so that employees can be sorted chronologically.
Common Excel tools for this include Number Format, Currency, Date, and Format Cells. In my HR dataset, I found inconsistencies in the Salary and Hire Date columns. Some salary values used different currency representations, while some dates were entered in different formats. I standardized these values so that the dataset would use consistent formats.

For valid dates, I standardized the display format so that all dates follow the same pattern.

Missing Values and Blank Cells

Missing data occurs when information expected in a dataset has not been provided. A blank cell and a zero should not automatically be treated as the same because a blank may indicate unavailable information, while zero can be a valid value depending on the field. Excel tools such as Filter, Find & Select, and functions such as COUNTBLANK can help identify missing values. For example, a missing salary or department requires investigation, while a project count of zero may be a legitimate record. In my HR dataset, I checked for blank values and considered the meaning of each field before deciding how the missing information should be handled. This approach helped ensure that cleaning my data did not introduce new errors by replacing meaningful values unnecessarily.

Find and Replace

This is used to locate a particular value or text and replace it with a consistent or corrected value. It is especially useful when the same inconsistency appears many times in a dataset. For example, HR, Hr, and H.R may all refer to the same department, but Excel treats them as different text values. In our HR dataset, we can standardize such department names using Find and Replace so that the same department has one consistent label. This improves filtering, grouping, counting, and later analysis.

Data Validity and Logical Checking

Data validity means checking whether values are reasonable and make sense within the context of the dataset. It involves checking whether values fall within an expected and reasonable range. In an HR dataset, age is a good example because employees would generally be expected to fall within the defined working-age range. For this exercise, I used 19 to 65 years as the acceptable range. Excel's logical functions, particularly IF, together with comparison operators such as >= and <=, can be used to identify values outside this range. This allows unusual records to be flagged for investigation rather than automatically deleted. Applying this check helps improve the reliability of the dataset before analysis.

Conditional Formatting

It is an Excel feature that automatically changes the appearance of cells when they meet a specified condition. It helps identify unusual values quickly without manually checking every record. For example, it can highlight high salaries, low performance scores, missing values, or unusually long work experience. In our HR dataset, I used conditional formatting to highlight employees with more than 25 years of work experience. This makes potentially unusual records easier to identify and investigate before analysis.

Operators and Functions
Excel uses operators and functions to transform and analyze data. Operators are symbols that tell Excel how to perform an operation or comparison. Arithmetic operators include addition, subtraction, multiplication, and division, while comparison operators include equal to, greater than, less than, greater than or equal to, less than or equal to, and not equal to. In my dataset, I applied PROPER for name standardization and used IF and AND to validate employee ages against the 19–65 range. I also used comparison operators to identify work experience exceeding 25 years. These examples demonstrated how functions and operators can turn simple spreadsheet values into useful data-quality checks.

Conclusion

My first week with Excel showed me that effective data analytics begins with clean, reliable data. By working with the HR dataset, I learned how to identify and correct inconsistencies, duplicates, missing values, formatting issues, and invalid information using Excel's tools and functions. Most importantly, I learned to look beyond the numbers and question the quality and meaning of the data before analysing it. This experience has given me a stronger foundation and greater confidence as I continue my journey in data science and analytics.

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

MERCY MUMBI WAHOME — Mon, 24 Aug 2026 18:03:00 +0000

Beginner friendly explanation of Git and GitHub: Kenya Hospital Health Records

When I started learning Git and GitHub, I was completely new to them and the terms used like repository. I slowly began to understand how these tools work together. Git helps track and manage changes to a project while GitHub allows us to store the project online.
In this project, I applied what I learned by creating Kenya Hospital Health Records and moved it from my local computer to GitHub. So this article explains my journey from the start to successfully uploading my project to GitHub, explaining each step in a simple way.

1.Understanding the Git Workflow

I learnt the basics of a G it workflow: working directory where I create and edit files, staging where I prepare selected changes, commit to save them as a checkpoint and pushing to send the work from my computer to GitHub.

2.Connecting Git to GitHub Using SSH

I first confirmed that Git was installed on my computer, then checked for an existing SSH key and found mine

3.Creating the project locally

I created a folder named Kenya Hospital Health Records and opened it in GitHub, then created the project files that would make my hospital records project. I then checked the folder to confirm the files have been created successfully. This gave me the basic project structure that I would later track using Git.

4.Initializing Git and Checking the Project

Now that my files were ready, I needed to introduce Git to the project by creating a local repository that would allow me to track changes to my files. Then I checked the Git status to see how it viewed the files. They appeared as untracked.

Next, I staged the files which tells Git which changes I want to include in my next commit. I then created my first commit, the initial commit. A commit is like a checkpoint that saves the current state of my project in Git's history.

5.Creating the GitHub Repository

After creating and committing my project locally, I needed an online place to store it, so I created a new repository on GitHub and gave it a name similar to my local project. I kept the repository empty because the project and its initial commit were already on my computer which will allow me to connect to the existing local project to the new GitHub repository

6.Connecting the Local Project to GitHub

I then connected my local Git repository to the GitHub repository using the SSH address I had copied from GitHub. I gave this remote connection the name** origin**, the standard name for the main remote repository. I checked the remote connection to confirm that the GitHub SSH address had been saved correctly for both fetching and pushing.

7.Setting the main branch

The next step was to make sure my project used the main branch. A branch is a separate line of development where changes to a project can be managed.

8.Pushing the project to GitHub

I used the push command to send my committed files from the local repository to the GitHub repository. The command also connected my local main branch with the remote main branch so that Git would know where to send future updates. Git confirmed the main branch was successfully set up to track origin. This was an important moment in my learning because I could see the work I had created and saved locally could finally appear online.

After the push was done, I opened my GitHub repository and I could see my project files online confirming that the local project had been successfully uploaded.

Conclusion

This project helped me understand Git and GitHub by actually using them. I learned how to create a local project, track it with Git, connect it securely to GitHub using SSH and upload online.
The workflow:** Working Directory > Staging > Commit > Push > GitHub** may seem complicated at first but once each stage is understood, it becomes easier to follow.