DEV Community: Victoria Ndei

Data Modelling, Relationships & Joins

Victoria Ndei — Mon, 14 Sep 2026 07:49:58 +0000

Introduction

Power BI helps organizations turn raw data into meaningful insights for better decision-making. A well-designed data model is essential for accurate analysis, efficient DAX calculations, and good report performance. This article explains data modelling, fact and dimension tables, relationships, filter directions, and Power Query joins, using practical examples to demonstrate how these concepts are applied when building an effective Power BI solution.

1. Data Modelling

What is data modelling?

Data modelling in Power BI is the process of organizing data from one or more sources into tables and defining the relationships between those tables so that the data can be analyzed efficiently.

A well-designed data model provides the foundation for accurate Power BI reports and dashboards. Instead of keeping all information in one large table, you can separate related information into logical tables and connect them using keys.

For example, in an agricultural business, crop production records could be stored in a central fact table while information about farmers, crops, locations, and seasons is stored in separate dimension tables.

A good data model is important because it:
Improves reporting and analytical accuracy.
Makes DAX calculations easier to write and understand.
Improves Power BI performance by reducing unnecessary data duplication.
Makes models easier to scale when new data is added.
Improves maintainability because each table has a clear purpose.
Makes relationships and filter propagation easier to understand.

There are three common approaches to structuring data: Flat Table, Star Schema, and Snowflake Schema.

1.2 Flat Table

A flat table stores all information in a single table. Instead of separating customers, products, locations, and transactions into different tables, all the attributes and business-event information are stored together.

Structure

For example, an agricultural dataset could look like this:

Illustration

Figure 1.1- shows a flat table

Advantages

Simple to understand.
Easy to import and begin analyzing.
Requires few or no relationships.
Suitable for small datasets and simple reports.

Disadvantages

Creates data redundancy because descriptive information is repeated.
Can make the dataset unnecessarily large.
Changes to descriptive information may need to be repeated across many rows.
Can make complex DAX calculations and modelling more difficult.
Less scalable as the dataset grows.

When is it appropriate?
A flat table can be appropriate when working with a small, simple dataset, especially for basic analysis where the data does not need to be reused across multiple business processes.

Power BI implications
Flat tables are initially simple, but large flat tables can contain substantial redundancy. This can increase model size and make the model harder to maintain as more business requirements are introduced.

1.3 Star Schema

A star schema separates the data into a central fact table and several surrounding dimension tables.
The fact table contains measurable business events, while dimension tables contain descriptive information used to analyze those events.

Structure

For the agricultural example:
Advantages

Excellent for Power BI reporting and dashboards.
Reduces data redundancy.
Makes DAX measures easier to understand.
Relationships are generally straightforward.
Supports efficient filter propagation.
Easier to maintain and extend.
Provides a clear and intuitive model structure.

Disadvantages

Requires more planning than a flat table.
Requires relationships between tables.
Users need to understand facts, dimensions, and keys.
Creating the model can initially take more time.

When is it appropriate?
Star schemas are particularly appropriate for business intelligence, reporting, and analytical systems where users need to analyze measures by different dimensions.

For example:

Total Profit by Crop, County, Farmer, and Season

can be easily analyzed using a star schema.

Power BI implications
Star schemas are generally preferred in Power BI because they provide simple relationship paths, efficient filtering, and a clear separation between descriptive attributes and measurable events.

1.4 Snowflake Schema

A snowflake schema is an extension of the star schema in which dimension tables are further divided into related tables.
For example, instead of storing the county and region directly inside DimLocation, location information could be separated:
Advantages

Reduces repeated information within dimensions.
Can be useful when dimensions contain complex hierarchies.
Can provide greater control over highly structured data.
May reduce redundancy in some large and complex datasets.

Disadvantages

Creates more tables.
Creates more relationships.
Makes filter propagation more complicated.
Can make DAX and report development less intuitive.
Increases model complexity.

When is it appropriate?
A snowflake schema may be appropriate when dimensions are large, hierarchical, or naturally normalized.

For example, a multinational agricultural organization could have:

Country → Region → County → Location

and maintaining these levels separately may be useful.

Power BI implications
Although a snowflake schema can reduce redundancy, it introduces additional relationships and filter paths. For many Power BI analytical models, a well-designed star schema is simpler and easier to work with.

2 Fact Table and Dimensions

A well-designed Power BI data model usually separates business events from the descriptive information used to analyze those events. This is where fact tables and dimension tables become important.

Fact Tables

A fact table stores measurable business events or transactions. It normally contains numeric values that can be aggregated, together with foreign keys that connect the facts to descriptive dimension tables.
The table contains business events such as sales transactions. Quantity and SalesAmount are measures that can be aggregated, while CustomerID, ProductID, and DateID identify the dimensions associated with each sale.

Dimension Tables

A dimension table contains descriptive information about the entities involved in business events. Dimensions provide the context used to analyze facts.

Examples include:

DimCustomer – customer name, gender, segment, and location.
DimProduct – product name, category, brand, and price.
DimDate – date, month, quarter, and year.
DimLocation – county, region, town, or country. For example: The distinction can therefore be summarized as:

Understanding Grain

One of the most important concepts when designing a fact table is its grain, or granularity.
The grain defines exactly what one row in the fact table represents.
For example, if the grain of FactSales is:
"One row represents one product sold in one sales transaction."
then every row should represent one product line within a transaction.
Clearly defining the grain prevents problems such as duplicated transactions and incorrect calculations.

3. Relationships in Power BI

A relationship connects tables in a Power BI data model. Relationships are necessary because business data is often distributed across multiple tables rather than stored in one large table.
For example, FactSales may contain CustomerID, while DimCustomer stores customer names and customer segments.
This relationship lets Power BI connect the two tables and use customer information when analyzing sales.

Primary Keys and Foreign Keys

A primary key uniquely identifies a record in a table.
For example, CustomerID can be the primary key in DimCustomer:
Each CustomerID should occur only once.

The same CustomerID can appear multiple times in FactSales because one customer can make many purchases:

Each CustomerID should occur only once.
A one-to-many relationship means one record in one table can relate to many records in another table.
For example:

|DimCustomer | |FactSales|

|CustomerID | | CustomerID|
C001 C001
C001
C001

DimCustomer[CustomerID] contains unique values, while FactSales[CustomerID] can contain repeated values.

This is the most common relationship type in a star schema.
Use it when a dimension contains unique entities and a fact table contains multiple business events associated with those entities.

3.2 One-to-One Relationship (1:1)

A one-to-one relationship means that each record in one table corresponds to only one record in another table.
For example:
EmployeeID EmployeeDetails
E001 ────> E001
E002 ────> E002
E003 ────> E003

This relationship may be appropriate when information about the same entity has intentionally been split into two tables.
However, a 1:1 relationship should not normally be used simply because two tables can be connected by a column. If the tables represent the same entity and there is no strong modelling reason to separate them, combining them may be simpler.

3.3 Many-to-Many Relationship (:)

A many-to-many relationship occurs when multiple records in one table can be related to multiple records in another table.
For example, students can enrol in multiple courses, while each course can have multiple students.

Students Courses

Student A ───────────────> Course 1
Student A ───────────────> Course 2
Student B ───────────────> Course 1
Student C ───────────────> Course 2

In a proper dimensional model, a bridge or factless fact table can often be introduced to resolve this situation.
Many-to-many relationships should be used carefully because they can make filter
propagation and DAX calculations more difficult to understand.

Cardinality
Cardinality describes how records in two related tables correspond to each other.
The main cardinalities in Power BI are:

1:* — One-to-Many
1:1 — One-to-One
: — Many-to-Many For most analytical models, 1:* relationships are preferred because they provide a clear dimension-to-fact structure.

Referential Integrity
Referential integrity means that foreign-key values should correspond to valid primary-key values in the related table.
For example, if FactSales contains:

CustomerID = C005

then C005 should normally exist in DimCustomer.
Missing dimension records can create unmatched or blank members in reports and may indicate data-quality problems.

Active and Inactive Relationships
Power BI can have both active and inactive relationships.
An active relationship is used automatically when filters move between related tables.
An inactive relationship is not automatically used but can be activated in a DAX calculation when required.
A common example is a sales table containing multiple date columns:

FactSales
---------
OrderDate
ShipDate
DeliveryDate

A date dimension may therefore have multiple relationships to the fact table. Only one can normally be active for the same pair of tables, while others can remain inactive and be activated in specific calculations.

4. Filter Direction

Relationships do more than connect tables. They also determine how filters propagate through the model.

Single-Direction Filtering

In a typical star schema, filtering flows from the dimension table to the fact table.
For example:

DimProduct
│
│ Filter
▼
FactSales

Suppose DimProduct contains:

If a user selects Electronics in a Power BI report, the filter is passed from DimProduct to FactSales.
Power BI then calculates sales using only the transactions associated with products in the Electronics category.
This is the preferred direction for many star-schema models because it is predictable and easy to understand.

Bidirectional Filtering

With bidirectional filtering, filters can move in both directions:
DimProduct
↕
FactSales

Although bidirectional filtering can solve some modelling scenarios, it should be used carefully.
It can create:

Ambiguous filter paths

Unexpected filtering behaviour
More complex DAX calculations
Performance problems in complex models
Difficulty understanding why a visual is being filtered

For example, if several dimension tables can reach each other through multiple paths, Power BI may have more than one possible route through which a filter can travel.
Therefore, single-direction filtering from dimensions to facts is generally preferable in a standard star schema.

_Figure 4.1 single-direction filtering versus bidirectional filtering between DimProduct and FactSales _

5. Joins in Power Query

Relationships are not the only way to work with multiple tables. Power Query provides Merge Queries, which allow tables to be joined during the data transformation stage.
A join matches records from two tables using one or more common columns.
Consider these two tables.

The tables can be merged using CustomerID.

5.1 Left Outer Join

A Left Outer Join keeps every record from the left table and matching records from the right table.
If Customers is the left table:

Carol remains in the result even though she has no matching order.
Use case: Find all customers and show their orders where available.

5.2 Right Outer Join

A Right Outer Join keeps every record from the right table and matching records from the left table.

Order O004 remains even though customer C004 does not exist in the Customers table.
Use case: Keep all orders and add customer information when available.

5.3 Full Outer Join

A Full Outer Join keeps all records from both tables, whether or not they have a match.
The result would include:

This is useful when you need to identify both:

Customers without orders
Orders without valid customers It can therefore be particularly useful for data-quality analysis.

5.4 Inner Join

An Inner Join keeps only records that exist in both tables.
Customers without orders and orders without matching customers are excluded.

Use case: Analyse only orders associated with valid customers.

5.5 Left Anti Join

A Left Anti Join returns records that exist in the left table but have no matching record in the right table.
Using Customers as the left table:

This identifies customers who have not placed an order.
Use case: Finding inactive customers or records missing from another dataset.

5.6 Right Anti Join

A Right Anti Join returns records that exist in the right table but have no matching record in the left table.
Using Orders as the right table:

This identifies orders whose customer does not exist in the Customers table.
Use case: Detecting invalid foreign keys or data-quality issues

_ Figure 5.3 -illustrates the summary of different joins _

6. Power Query Joins vs Power BI Relationships

Although both joins and relationships connect tables, they serve different purposes and operate at different stages of the Power BI workflow.

Power Query Merge

A Merge Query is a data-transformation operation.
It occurs before the data is loaded into the Power BI model.
For example:
Customers
+
Orders
↓
Power Query Merge
↓
Combined Table

The merge can bring columns from one table into another.
For example, an Orders table could be merged with Customers to add:
CustomerName CustomerSegment County
The result is physically represented as a combined query/table when loaded into the model.

Power BI Relationship

A relationship is created after the tables are loaded into the data model.
For example:
DimCustomer
│
│ 1:*
▼
FactSales

The relationship does not physically combine the tables.
Instead, it tells Power BI how the tables are connected so that filters and calculations can work across them.

Key Difference

When Should You Use a Merge?
Tables, Relationships, Filter Direction and Joins in Power BI
A merge is appropriate when the columns are genuinely required in the same table.
For example, if a source system stores product information separately but a transformation requires the product category to be included in a staging table, merging may be appropriate.

When Should You Use a Relationship?

A relationship is preferable when the tables represent different business entities.

For example:

DimProduct
│
▼
FactSales

There is usually no need to copy product name, category, and brand into every sales transaction.
Keeping them in DimProduct reduces data redundancy and preserves a clean dimensional model.

Problems with Excessive Merging

Excessive merging can create a very wide table containing repeated descriptive information.
For example, instead of:
DimProduct ProductID ProductName Category Brand
and:
FactSales SalesID ProductID Quantity Quantity SalesAmount
a merge could produce:
SalesID ProductID ProductName Category Brand Quantity SalesAmount
If thousands or millions of transactions contain the same product information, the data becomes unnecessarily repetitive.
This can increase model size, reduce readability, and make maintenance more difficult.
For business intelligence solutions, keeping fact and dimension tables separate is often preferable because it supports a cleaner and more scalable model.

7. Recommended Power BI Model

For a typical business intelligence project, I would recommend a star schema rather than a flat table or a highly normalized snowflake schema.
A typical model could look like this:
graph TD
`
Customer["DimCustomer"]
Product["DimProduct"]
Date["DimDate"]
Location["DimLocation"]
Sales["FactSales"]

Customer -->|1:*| Sales
Product -->|1:*| Sales
Date -->|1:*| Sales
Location -->|1:*| Sales

`
The fact table contains measurable business events, while dimensions contain descriptive attributes.
Why Choose a Star Schema?

1. Performance

Star schemas generally provide an efficient structure for analytical queries because dimensions are separated from large transaction tables and relationships are straightforward.

2. Simpler DAX

Clear relationships make DAX calculations easier to understand.
For example, a measure such as:
Total Sales = SUM(FactSales[SalesAmount])
can be analyzed by product, customer, location, or date through the relationships in the model.

Model Readability A star schema is visually easy to understand. Developers and report users can quickly identify:
Which table contains transactions
Which tables contain descriptive attributes
How the tables are connected ###4. Scalability As the business grows, additional dimensions can be added without redesigning the entire model. For example:

             DimCustomer
                  |
DimProduct — FactSales — DimLocation
                  |
               DimDate

A future DimSalesperson or DimPromotion could also be connected to the fact table.

5. Reduced Redundancy

Product and customer information does not need to be repeated for every transaction.
Instead of storing:

Laptop | Electronics | HP | 500 times

the information can be stored once in DimProduct and referenced through ProductID.

6. Maintainability

If a product category changes, it can be updated in the dimension rather than repeatedly changing the same information across thousands of fact records.

7. Easier Reporting

Report users can drag attributes such as:

DimProduct[Category]
DimDate[Year]
DimLocation[County]

`
and measures such as:

[Total Sales]

into visualizations without needing to understand complex joins.

Recommended Relationships

For a standard star schema, I would normally implement:

One-to-many (1:*) relationships
Dimension table on the 1 side
Fact table on the *side
Single-direction filtering from dimensions to facts
Unique primary keys in dimension tables
Matching foreign keys in fact tables
Clearly defined fact-table grain
Active relationships for the main analytical paths Many-to-many relationships should only be introduced when the business requirement genuinely requires them, and bridge tables should be considered where appropriate.

Star Schema vs Flat Table vs Snowflake Schema

A flat table can be useful for small or simple datasets, but it can become inefficient and difficult to maintain as the amount of data grows.
A snowflake schema normalizes dimensions into additional related tables. This can reduce redundancy further, but it introduces additional relationships and complexity.
For most Power BI analytical models, the star schema provides the best balance between performance, simplicity, scalability, and maintainability.

Final Recommendation

My preferred Power BI model would therefore be a star schema with a central fact table surrounded by dimension tables. I would use one-to-many relationships wherever possible, with dimensions on the 1 side and facts on the * side.
I would also use single-direction filter propagation from dimensions to facts unless there is a specific modelling requirement for bidirectional filtering.
This approach produces a model that is easier to understand, easier to maintain, and easier to use when developing DAX measures and reports. More importantly, it separates business events from descriptive context, allowing the model to scale as the organization's reporting requirements grow.

A good Power BI model is therefore not simply about connecting tables. It is about designing those connections so that the resulting model is accurate, performant, understandable, and useful for business analysis.

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

Victoria Ndei — Sun, 06 Sep 2026 19:32:17 +0000

Introduction

E-commerce platforms generate large amounts of product information that can help explain pricing strategies, customer engagement, and product performance.

For this project, I analyzed a dataset of products from Jumia, an e-commerce marketplace, using Microsoft Excel. The goal was to transform raw product data into a cleaned dataset, perform descriptive and relationship analysis, and develop an interactive dashboard that communicates the most important findings.
The analysis focused on four major areas:

Product pricing
Discounts
Customer reviews
Product rating
The final result was an Excel-based analytical dashboard designed to help sellers identify high-performing products, products requiring attention, and potential pricing and marketing strategies.

Project Objectives

The goal of this project is to create an interactive Excel dashboard that provides insights into the
performance of products listed on Jumia and is capable of answering the following questions:
What is the average product price?
What is the average discount?
Which products have the highest ratings?
Which products have the highest number of reviews?
Do higher discounts result in higher customer engagement?
Are highly rated products more expensive?
Which products are performing well?
Which products may require improved pricing or product strategies?
What recommendations can be made to Jumia sellers?

Dataset Description

The dataset contains information about Jumia products
using the following fields:
Product:product name
Current Price:Current selling price
Old Price:Original/listed price
Discount:Percentage discount
Review:Number of customer reviews
Rating:Customer rating out of 5
The original dataset contained 115 records.
After data cleaning and duplicate removal, the final dataset contained 112 products

Raw Data

The first step was to inspect the raw dataset before making any changes.

The initial audit looked for:

Missing values
Duplicate records
Incorrect data types
Negative review counts
Invalid ratings
Invalid discounts
Price inconsistencies

Data Cleaning

Data cleaning was important because raw e-commerce data is not always immediately suitable for analysis.
For my data cleaning, I used Power Query, which made the cleaning process much easier.

Removing Duplicate Records

Duplicate records can cause products to be counted more than once and can distort totals and averages.
The dataset contained three duplicate records. These duplicates were removed, leaving 112 unique records.

Cleaning Review Counts

Some review values were negative.
A review count cannot logically be negative, so these values were converted to their absolute values.
Blank review values were retained as missing values rather than automatically being treated as zero.
This distinction is important because a missing review count does not necessarily mean that the product received zero reviews.

Cleaning Ratings

Ratings were originally stored in text formats such as:
4.5 out of 5
These values were converted into numerical ratings so that averages, sorting, and correlations could be calculated.

Cleaning Discounts

Discount values were originally represented as percentages, such as:
38%
These were converted into numerical percentage values for analysis.

Cleaning Prices

Currency text was removed from the price fields so that prices could be treated as numerical values.
One product contained a price range rather than a single price. A midpoint was used for the numerical analysis so that the record could be included in price calculations.
Also, we removed the 'KSh' and ',' before changing prices to whole numbers.

Data Enrichment

After cleaning the original fields, additional analytical columns were created.

Discount Amount

The discount amount was calculated using:

Discount Amount = Old Price - Current Price

This provides the actual monetary reduction rather than only the percentage reduction.

Rating Category

Products were categorized according to their rating performance.
The categories were used to make it easier to analyze the overall distribution of customer ratings.

Discount Category

Products were also grouped into discount categories:

Low
Medium
High These categories made it possible to compare product performance across different discount levels.

Price Category

Products were grouped into price ranges to make price analysis easier and to support interactive dashboard filtering.

Excel Techniques Used

Several Excel features were used throughout the project.

Excel Formulas

Formulas were used to create calculated fields and perform summary calculations.
For example, the discount amount was calculated by subtracting the current price from the old price.
Functions such as AVERAGE, SUM, COUNT, MAX, MIN, IF, and other conditional functions were useful for transforming and analyzing the dataset.

Sorting and Filtering

Sorting was used to identify:

Highest-rated products
Lowest-rated products
Products with the most reviews
Products with the highest discounts
Products requiring attention

Pivot Tables

Pivot Tables were used to summarize product performance by categories such as rating and discount levels.
They provided a flexible way to aggregate the data without manually calculating every summary.

Pivot Charts

Pivot Charts converted the Pivot Table results into visualizations suitable for the dashboard.

Slicers

Slicers were added to make the dashboard interactive.
Users can filter the dashboard based on variables such as:

Rating Category
Discount Category
Price Category

Data Analysis

1 Descriptive Analysis

After cleaning the data, the following statistics were obtained:

Trend Analysis

Relationship Between Discounts and Reviews

One of the main questions was whether larger discounts result in greater customer engagement.
Customer engagement was represented using the number of reviews.
The correlation between discount percentage and review count was approximately:
-0.14
This represents a very weak negative relationship.
Therefore, the analysis does not provide evidence that increasing the discount percentage automatically results in more customer reviews.
Some products with substantial discounts had very few reviews, while some products with moderate discounts had significantly more reviews.

Business Interpretation

Sellers should therefore avoid treating discounts as the only method of increasing engagement.
Product quality, value, visibility, presentation, and customer experience may also influence customer response.

Relationship Between Price and Rating

The correlation between current price and rating was approximately:
+0.11
This is a very weak positive relationship.
The result suggests that expensive products were not necessarily rated much higher than cheaper products.
For example, the Electronic Digital Display Vernier Caliper had a relatively low current price but achieved a rating of 4.6/5 alongside 49 reviews.

Business Interpretation

Price alone does not appear to determine customer satisfaction.
Sellers should focus on providing strong value for money rather than assuming that a higher selling price will result in higher ratings.

Relationship Between Ratings and Reviews

The correlation between rating and review count was approximately:
+0.06
This is effectively a negligible linear relationship.
A product can therefore have many reviews without necessarily having a high rating.
This is particularly important when evaluating product performance.
A high number of reviews indicates strong customer engagement, but it does not automatically indicate customer satisfaction.

Sales Performance Analysis

The following table summarizes the product performance analysis by:

Strong demand proxy: Which products have review counts above the engagement threshold?
Pricing/marketing attention: Which products combine weak engagement or weak ratings with high price/high discount?
Many reviews, average experience: Which products have high engagement but fall in the Average rating category? -Promotion inefficiency: Which high-discount products remain below the engagement threshold?

Building the Dashboard

The final stage of the project was creating an interactive Excel dashboard.
The dashboard was designed to provide a quick overview of product performance without requiring the user to inspect the raw dataset.

KPI Cards

The dashboard includes key performance indicators such as:

Total Products
Average Current Price
Average Discount
Average Rating
Total Reviews ##Product Performance Charts Charts were created to display:
Top 10 products by rating
Top 10 products by reviews
Top 10 products by discount ###Category Analysis The dashboard also presents the distribution of:
Rating categories -Discount categories -Price categories ###Interactive Slicers Slicers allow the user to filter the dashboard by selected product characteristics.

Screenshot: Pivot Tables

Insert screenshot of your Pivot Tables here.

Screenshot: Charts

Insert screenshot of your charts here.

Screenshot: Final Dashboard

Insert screenshot of your completed Dashboard here.

Key Business Insights

The analysis produced five major conclusions.

1. Large discounts do not guarantee engagement

The discount-review correlation was approximately -0.14, showing a very weak negative relationship.

2. Higher prices do not guarantee better ratings

The price-rating correlation was approximately +0.11.

3. Review volume and rating measure different aspects of

performance
Review count reflects customer engagement, while rating provides an indication of customer satisfaction.

4. High-performing products combine engagement and satisfaction

Products such as the Cake Decorating Tool Set and Vernier Caliper demonstrated strong performance through both review volume and rating.

5. Some heavily discounted products still perform poorly

High discounts cannot compensate for poor product quality or customer dissatisfaction.

Recommendations for Jumia Sellers

Based on the findings, sellers should consider the following strategies.

1. Do not rely entirely on discounts

Discounting should be part of a broader marketing strategy rather than the only method used to generate engagement.

2. Monitor customer ratings carefully

A product receiving many reviews but maintaining a low rating should be investigated immediately.

3. Prioritize high-rated and highly reviewed products

Products demonstrating both strong engagement and customer satisfaction should receive greater promotional attention.

4. Improve product quality

Products with consistently poor ratings should be reviewed for quality and functionality issues.

5. Improve product descriptions

Accurate product descriptions and specifications can help reduce differences between customer expectations and the actual product.

6. Use competitive pricing

The analysis indicates that higher prices do not automatically result in higher ratings. Sellers should therefore balance profitability with customer-perceived value.

7. Use data for decision-making

Instead of deciding on pricing and promotions based solely on intuition, sellers can use review counts, ratings, discounts, and prices together to identify products requiring action.

Conclusion

This project demonstrated how Microsoft Excel can be used to transform raw e-commerce data into an interactive business intelligence dashboard.
The analysis showed that product performance cannot be explained by discounts or prices alone.
The weak relationship between discounts and reviews indicates that larger discounts do not automatically produce higher engagement. Similarly, the weak relationship between price and ratings indicates that expensive products are not necessarily better rated.
The most useful approach is to evaluate multiple indicators together.
Products with high review counts and strong ratings represent stronger performers, while products with high engagement but poor ratings may require quality or customer-experience improvements.
Ultimately, the dashboard provides sellers with a practical way to identify opportunities, monitor product performance, and make more evidence-based pricing and marketing decisions.

Final Takeaway

The key lesson from this project is simple:
A large discount can attract attention, but customer satisfaction and product value are essential for sustainable product performance.

Getting Started With Excel For Data Analytics: From Basic Data to Cleaning

Victoria Ndei — Sat, 29 Aug 2026 20:47:00 +0000

Introduction

This week at Luxdevhq, our data analyst group, we dove into Excel, learning how to navigate it and use its tools as real analysts. Join me as I explain what we covered this week. But before we go any further, we must first understand what Excel is.

What is Excel

Excel is a spreadsheet software program designed by Microsoft that allows you to collect, organize, analyze, calculate, and visualize data efficiently. It allows users to create charts,and manage large datasets, making it one of the most widely used tools in data analysis and business reporting.

1 Excel Fundamentals

1.1 Workbooks, Worksheets and Cells

Excel file is called a workbook, and a workbook is made of one or more worksheets(often called sheets), visibly seen as tabs at the bottom of the screen. Each worksheet is a grid of cells arranged into columns labelled with letters A, B, C... and rows labelled with numbers:1,2,3...
Every cell has a unique address formed by its column and row, such as A2 or G12, and that address is what formulas use to refer to data elsewhere in the sheet.

1.2 Data Types

Numbers: plain quantities use in calculations e.g: 2, 56, 134
Texts: words or codes that Excel won't try to calculate e.g Nairobi
Dates: stored internally as serial numbers;1/12/2000
Formulas: expression begins with equals sign(=) that calculates result from other cells. e.g (=A2+C2)

The Ribbon and Navigation

The ribbon is the toolbar across the top that contains all commands organized into tabs (Home, Insert, Page Layout, Formulas, Data, Review) being the most commonly used.
Home tab covers formatting(fonts,colors,number formats),the formula tab gives access to the function library, and the Data tab is where most data-cleaning tools are found, including Removing Duplicates, Text to Columns, Data Validation, and Sort& Filter.

1.4 Basic Formulas and Functions

A formula is an expression that calculates the value of a cell. Formulas begin with an equals (=) sign and can include:

Constants (numbers like 100)
Cell references(like A1, B2)
Operations like (+, -, *, /)
Built-in functions (like SUM, AVERAGE)

FUNCTIONS
The following are some of the functions we went through this week:

=SUM() - adds a range of numbers
=AVERAGE() -calculates the mean of a range
=COUNT()/=COUNT() -counts numeric cells, or non-blank cells respectively.
=COUNTIFS() and =SUMIFS() -conditional counting and summing

2 Data Cleaning Exercise

This section we will walk through a realistic sample dataset: fifteen order records from a small online store selling electronics and furniture. This dataset was built in away that it contains different problems every analyst runs into early in a project; inconsistent capitalization, stray whitespace, mixed data formats, a duplicate record, missing values, and a data entry error

2.1 The Raw Data

Below is the "Raw Data" worksheet exactly as it may arrive from an export or a manual entry log. A few problems can be noted immediately, a few become obvious once formulas are applied to check for them:

2.2 Standardizing Text with TRIM, PROPER, and LOWER

These text functions mostly do most of the work when cleaning inconsistencies text entries:

=TRIM() -removes leading, trailing, and repeated internal spaces (e.g, turns " Peter Otieno" into "Mary Achieng")

=PROPER() -converts text to Title Case, so "john mwangi" and "MARY ACHIENG" to "John Mwangi" and "Mary Achieng"

=LOWER() -converts texts to lowercase, useful for standardizing email addresses so "JOHNMWANGI@GMAIL.COM and johnmwangi@gmail.com are recognized as equal. ### 2.3 Handling Dates and Catching Format Errors Data in Excel can sometimes look correct but actually can be stored incorrectly, which can cause problems when calculating and sorting data. e.g a date like 13/06/2023 might be understood differently depends on Excel's regional settings. The formula checks whether Excel can understand the entry as a real date. If it's a real date, it changes it format DD-MMM-YYYY such as 13-05-2023 ### 2.4 Flagging Duplicates, Blanks, and Errors This step is about finding problems in a data automatically instead of checking rows manually. Three checks are used:

Duplicate detection: Finds records that appear more than once and marks them as "Duplicate"
Blank Detection: Finds cells where information is missing and marks them as "Yes".
Price validation: Checks whether the price is negative. If it is, it shows "Negative Price"; otherwise, it shows OK.
Conditional formatting is used to highlight these problems in red problems making them easy to spot:

2.5 Producing the Final, Clean Dataset

With every issue identified, the final step combines Excel's inbuilt data; Remove duplicates with the corrections suggested by the flag columns: the duplicate order (1007) was removed, the negative price on order was corrected to its intended positive value, missing quantities and regions were filled after checking the original records, and every data was rewritten in single consistent format. The result is a clean analysis ready table:
" width="799" height="280">

Key Takeaways

Data cleaning is very important in data analysis. Excel functions like TRIM, PROPER, LOWER, COUNTSIFS help fix common data problems.

Use conditional formatting to highlight problems so that they are easy to see.
Always check dates. A date may look correct but could be stored as a text, which can cause errors.
Use formulas for analysis instead of manually typing.

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

Victoria Ndei — Sat, 22 Aug 2026 14:55:35 +0000

Introduction

This week I learnt how to create my first GitHub project from a local folder on my desktop using tools such as Git and an SSH connection. Git is used to track changes in the source code and files over time, while SSH is a key used for authenticating your desktop with your GitHub account. There are several steps that we need to follow before connecting our project to our GitHub account.

1 Creating a Folder

There are two ways to create a folder; the first way is to go to your file explorer and select Desktop to create the folder directly there. The second step is to use Git Bash. When using Git Bash, first, you run the command pwd to see which folder you're currently in; e.g when using Windows, you may see /c/Users/John _.To change your directory to Desktop, you run the command

cd Desktop

Then run ls to see the files located on your desktop. Now we create our folder. To create a folder, we run

mkdir Kenya_Health_Records

Then we run *cd Kenya_Health_Records * to change the directory to our folder.

2 Adding Files to The Folder

This is our second step after creating our folder. We are going to add the following folders to our project folder:

data
notebooks
scripts

To add the folders, we will run the following commands:

mkdir data

mkdir notebooks

mkdir scripts

We will also add our README.md using the command:

touch README.md`shell

Adding information to our README.md

To add information to our README, we use the echo command, two double quotes, and the> sign.
Example:
echo " ## My First GitHub Project" >README.mdshell
If you want to check that the information is added to the README.md file, we use

cat README.mdshell

3 Initializing the Project with Git

To initialize the project, we use the command git init
git init initializes the directory as a Git repository.
When I ran git init, it created a hidden .git directory inside my project to track changes.
Afterwards, I checked the status of my files using git status.

4 Stage the Whole Project

To stage the remaining changes to the whole project, I ran:

git add.shell
The period (.) tells Git to add all files to the current directory. I ran *git status * to check if the files have been added.

5 Creating the First Commit

Run: git commit-m" Add Kenya Health Records`
git commit is a snapshot of the changes made to the project.

6 Check Current Branch

Run:

git branch

shell
You may see *main, which is the current branch we are in.
For our beginner project, we will use main as our primary branch. If your branch says master, rename it using git branch -M main`

7 Creating GitHub repository

Sign in to your GitHub account and then click New repository.
Name your repository as Kenya-Health-Records.
You can choose whether to set it as private or public.
Our project already has a README.md, so do not select to add it to the repository.

8 Selecting SSH

After creating the repository, GitHub displays connection options like HTTPS and SSH.
Select SSH because it was already configured earlier.
The address will look similar to:git@github.com:USERNAME/kenyan-hospital-health-records.git

9 Connecting the Local Repository to GitHub

Open your git bash and run;

git remote add origin git@github.com:USERNAME/kenyan-hospital-health-records.gitshell
I replaced the username with my GitHub username.
I verified the remote connection using:
git remote-v

10 Pushing the Project to GitHub

Run:

git push -u origin main
git push sends the changes from my computer to my remote repository.

11 Verifying the Project on GitHub

After the push was completed, I went to GitHub and refreshed the page to see if my project files had been added to the repository.

Conclusion

I gained practical experience in how to upload my local folder using Git and SSH on GitHub. The experience was truly great, and now I'm more familiar with the tools, making it easier to work on more projects on my own from henceforth.

DEV Community: Victoria Ndei

Data Modelling, Relationships & Joins

Introduction

1. Data Modelling

What is data modelling?

1.2 Flat Table

1.3 Star Schema

1.4 Snowflake Schema

2 Fact Table and Dimensions

Fact Tables

Dimension Tables

Understanding Grain

3. Relationships in Power BI

Primary Keys and Foreign Keys

3.2 One-to-One Relationship (1:1)

3.3 Many-to-Many Relationship (:)

4. Filter Direction

Single-Direction Filtering

Bidirectional Filtering

Ambiguous filter paths

5. Joins in Power Query

5.1 Left Outer Join

5.2 Right Outer Join

5.3 Full Outer Join

5.4 Inner Join

5.5 Left Anti Join

5.6 Right Anti Join

6. Power Query Joins vs Power BI Relationships

Power Query Merge

Power BI Relationship

Key Difference

When Should You Use a Relationship?

Problems with Excessive Merging

7. Recommended Power BI Model

1. Performance

2. Simpler DAX

5. Reduced Redundancy

6. Maintainability

7. Easier Reporting

Recommended Relationships

Star Schema vs Flat Table vs Snowflake Schema

Final Recommendation

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

Introduction

Project Objectives

Dataset Description

Raw Data

Data Cleaning

Removing Duplicate Records

Cleaning Review Counts

Cleaning Ratings

Cleaning Discounts

Cleaning Prices

Data Enrichment

Discount Amount

Rating Category

Discount Category

Price Category

Excel Techniques Used

Excel Formulas

Sorting and Filtering

Pivot Tables

Pivot Charts

Slicers

Data Analysis

1 Descriptive Analysis

Trend Analysis

Relationship Between Discounts and Reviews

Business Interpretation

Relationship Between Price and Rating

Business Interpretation

Relationship Between Ratings and Reviews

Sales Performance Analysis

Building the Dashboard

KPI Cards

Key Business Insights

1. Large discounts do not guarantee engagement

2. Higher prices do not guarantee better ratings

3. Review volume and rating measure different aspects of

4. High-performing products combine engagement and satisfaction