DEV Community: Wendy Adika

Data Modelling, Relationships & Joins in Power Bi

Wendy Adika — Sun, 13 Sep 2026 11:29:39 +0000

Introduction

Microsoft Power BI is a business intelligence platform used to find insights within an organization's data. It can help connect disparate data sets, transform and clean the data into a data model and create charts or graphs to provide visuals of the data. All of this can be shared with other Power BI users within the organization.
Every Power BI report is only as good as the data model sitting underneath it. Visuals, DAX measures, and refresh performance all trace back to a handful of structural decisions made early on: how tables are shaped, how they relate to one another, and how data is combined before it ever reaches the report canvas. This article works through those decisions using a running sales scenario as a consistent example throughout, before closing with a recommended model design and the reasoning behind it.

i). Data Modelling

A data model, at its core, comes down to how tables relate to one another. Each table is responsible for one piece of the data. An example is sales transactions or product details and clear connections that tie those tables together.

Data Modeling in Power BI is the process of organizing multiple tables and defining relationships between them so that Power BI can efficiently analyze and visualize business data. The main data modelling approaches are flat table, star schema and snowflake schema.

Importance of a well designed data model.

Reporting: It will organize data into familiar business terms using dimensional structures (like fact and dimension tables) rather than rigid source-system formats. It will prevents conflicting metrics across departments by establishing standardized definitions and relationships.
Dax Calculations: It eliminates the need for complex, nested filter manipulation (FILTER, EARLIER) by letting relationships handle context automatically.It also prevents common errors like double-counting or incorrect cross-filtering between unrelated tables.
Performance: It reduces memory consumption and file size by removing unnecessary columns and optimizing data types wich equals to faster load time and efficient storage.
Scalability: It handles expanding data volumes and historical additions without degrading query response times and makes adding new data sources straightforward because you already have a clear conceptual framework for where new information fits.
Maintainability: It allows you to modify or expand reporting requirements locally without breaking existing visuals or downstream dashboards.

1. Flat Table

A flat table stores most or all reporting information in one wide table. For example, a retail dataset could contain OrderID, OrderDate, CustomerID, CustomerName, ProductID, ProductName, Category, Store, Quantity, UnitPrice, Discount, and SalesAmount in every transaction row.

Advantages:

Simple to understand: There is nothing to join or relate since everything is visible in one place.
Fast to build for a one-off, small analysis with no ongoing maintenance need.
Straightforward for basic visuals.

Disadvantages:

Heavy data redundancy: The same customer or product attributes repeat on every transaction row, inflating file size and memory usage.
Update anomalies: if a customer's city changes, every historical row for that customer must be updated, or the data becomes inconsistent.
DAX becomes harder at scale: Measures that should be simple aggregations end up needing extra logic to avoid double-counting repeated attribute values.

When it is apprpriate:
In Power BI, a flat table can work well for a small dataset because there are fewer relationships and less modelling effort. However, as data grows, repeated customer, product, or location attributes increase redundancy and can make maintenance harder. A flat table also does not naturally express the business separation between facts and dimensions.

Performance and complexity implications:
Model complexity is low (one table, no relationships), but runtime performance is often poor at scale: wide flat tables compress less efficiently, and repeated text values consume far more memory than the same values stored once in a dimension table and referenced by a numeric key.

2. Star Schema

A star schema organizes data around a central fact table, connected directly to a set of surrounding dimension tables: one join per dimension, no intermediate tables. Viewed in Power BI's Model View, the fact table sits in the middle with relationship lines radiating outward to each dimension, resembling a star.

The fact table (FactSales) holds transactional measures and foreign keys. Each dimension table (DimCustomer, DimProduct, DimDate, DimStore) is fully denormalized on its own. For example, DimCustomer holds every customer attribute in one table, with no further breakout into separate store or date tables.

Advantages:

Simple, predictable relationship paths: Every dimension is exactly one join away from the fact table, which keeps DAX filter logic intuitive.
Strong VertiPaq compression: Narrow, single-purpose dimension columns with repeated values compress extremely well.
Fast query performance: Fewer joins mean fewer hops for Power BI to resolve when a report applies a filter.
Easy for report authors to navigate: Dimension fields are grouped logically and predictably in the field list.

Disadvantages:

Some redundancy remains inside each denormalized dimension table (e.g., a city name repeated for every customer in that city), though far less than a flat table.
Less “textbook normalized” than a snowflake schema, which can matter in strict data-warehousing contexts outside of BI reporting.

When it is appropriate:
The star schema is the recommended default for the overwhelming majority of Power BI reporting models such as sales analysis, financial reporting, operational dashboards because it balances simplicity, performance, and maintainability better than either alternative.

Performance and complexity implications:
Star schemas are what Power BI's VertiPaq engine and DAX language are optimized for. Model complexity stays low even as more dimensions are added, and performance scales well because each additional dimension is still just one join away from the fact table.

3. Snowflake Schema

A snowflake schema takes a star schema's dimension tables and normalizes them further, splitting each into smaller, related sub-dimension tables. Instead of DimCustomer holding city and country directly, those attributes move into separate DimCity and DimCountry tables, connected in a chain back to DimCustomer.

The fact table still sits at the centre, but some dimensions now require two or more joins to reach. For example, filtering FactSales by Country means passing through DimCustomer > DimCity > DimCountry.

Advantages:

Eliminates redundancy within dimensions: A country name is stored once in DimCountry rather than repeated across every customer row.
Can reduce dimension table size when a dimension has genuinely hierarchical, highly repetitive attributes shared by many members.
Familiar to teams coming from traditional, fully normalized relational data-warehouse design.

Disadvantages:

More relationships to maintain and reason about, increasing model complexity.
Multi-hop filter paths can slow query performance, since Power BI must traverse several relationships to filter the fact table from an outer sub-dimension.
Harder for report authors to navigate because related fields are spread across more tables in the field list.
Diminishing returns in Power BI specifically: VertiPaq's columnar compression already minimizes much of the storage benefit that snowflaking is meant to achieve in traditional row-based databases.

When it is appropriate:
Snowflaking is worth considering when a dimension is very large and its sub-attributes are reused identically across many other dimensions (a shared DimGeography used by both DimCustomer and DimStore, for instance), or when a data source is already normalized upstream, and further denormalizing it into a full star schema is not practical.

Performance and complexity implications:
Snowflake schemas generally increase model complexity and can reduce performance relative to a star schema, because Power BI must resolve longer filter-propagation chains. The storage savings that justify snowflaking in traditional relational databases are largely already achieved by VertiPaq's compression, which weakens the case for snowflaking in most Power BI models.

Schema comparison Table

ii). Fact Table and Dimension Table

Star and snowflake schemas both depend on a clear division of labour between two table types: fact tables, which record business events, and dimension tables, which describe the entities involved in those events.

Fact Tables

A fact table stores measurable, numeric business events including the things a business wants to sum, average, or count. Typical fact tables include FactSales, FactOrders, and FactTransactions. Each row in a fact table generally represents one occurrence of the tracked event (one sale line, one order line, one transaction) and contains:

Foreign keys pointing to related dimension tables (CustomerKey, ProductKey, DateKey, LocationKey).
Measures which are numeric values meant to be aggregated, such as SalesAmount, Quantity, or Discount.

A crucial concept for any fact table is its grain. Grain is the level of detail a single row represents. A fact table with a grain of “one row per order line” is very different from one with a grain of “one row per order,” even if both are called FactSales; the grain determines what can and cannot be calculated correctly from the table. Grain should be decided deliberately and kept consistent. Mixing grains within a single fact table (some rows per line item, others pre-aggregated per order) is a common source of double-counting errors in DAX measures.

Dimensions table

Dimension tables contain descriptive attributes used to group, filter, and explain facts. Common dimensions include DimCustomer, DimProduct, DimDate, and DimLocation.

Measures vs. Descriptive Attributes
The distinction between a measure and a descriptive attribute is really a distinction between what belongs in a fact table and what belongs in a dimension table. SalesAmount is a measure: it is meaningful to sum it across thousands of rows. ProductCategory is a descriptive attribute: summing it makes no sense, but grouping and filtering by it does. Keeping measures and descriptive attributes in separate tables is what makes a star schema efficient. Power BI can compress and filter a column of repeated category labels very differently from a column of continuously varying sale amounts.

Worked Example: FactSales at the Centre of a Star Schema

Consider a retail business tracking online sales. A single central fact table, FactSales, records one row per sale line: a SalesID, the numeric SalesAmount and Quantity, and foreign keys to four dimensions. Each dimension supplies the descriptive context needed to make that fact meaningful in a report:

With this structure, a report can answer “total SalesAmount by Region and Quarter” by summing FactSales while grouping by attributes pulled from DimLocation and DimDate without a single duplicated customer or product name anywhere in the fact table itself.

iii). Relationships in PowerBI

A relationship in the Power BI data model is a defined link between two tables, based on a shared column, that tells Power BI how to combine data from both tables when a report requests it. Relationships are what make a star or snowflake schema function as a single connected model rather than a collection of unrelated tables. Without them, filtering DimProduct to a single category would have no effect on FactSales at all.
The diagram below shows the different types of relationships.

One-to-Many (1:*)

This is the standard, most common relationship in a star schema: one row in a dimension table (the “1” side) can relate to many rows in a fact table (the “*” side). Example: one product in DimProduct appears on many rows in FactSales. Use this whenever a dimension's key is unique and a related table repeats that key for multiple events. It should not be used when the “one” side is not actually unique. If DimProduct itself contains duplicate ProductKey values, Power BI will raise an ambiguity error or produce a many-to-many relationship.

One-to-One (1:1)

Each row in one table relates to exactly one row in the other, with unique keys on both sides, for example, DimEmployee and a supplementary DimEmployeeDetail table both keyed by the same unique EmployeeID. This is uncommon in typical star schemas and is generally used only when splitting one logical entity across two physical tables (for security reasons, or because the data arrives from two separate systems).

Many-to-Many (:)

Both sides of the relationship can contain repeated key values. For example, FactSales and a DimPromotion table where a single sale can qualify for multiple promotions, and a single promotion can apply to multiple sales. Many-to-many relationships should be used carefully and sparingly. They can produce ambiguous or unexpectedly inflated aggregation results, and are usually better resolved with an intermediate bridge table that turns the problem back into two clean one-to-many relationships.

Primary Keys, Foreign Keys, and Uniqueness

Every relationship in Power BI depends on two roles being clearly assigned:

Primary key: A column in a dimension table containing unique values, with no duplicates, that identifies each row (CustomerID in DimCustomer).
Foreign key: A column in another table (typically a fact table) that stores those same key values, but is expected to repeat many times, once for every event associated with that dimension member (CustomerID repeated across every row in FactSales for that customer).

This is precisely why CustomerID contains unique values in DimCustomer but appears many times as a foreign key in FactSales: DimCustomer holds one row per customer, while FactSales holds one row per sale, and any customer can have many sales.

Cardinality, Referential Integrity and Active/Inactive Relationships

Cardinality describes the matching pattern between tables: 1:, 1:1, or *:. Referential integrity means every foreign key value in the fact table has a matching primary key value in the related dimension table: no “orphan” sales pointing to a CustomerID that does not exist in DimCustomer. Power BI handles violations by grouping unmatched rows under a blank member rather than failing outright, but persistent referential-integrity gaps usually indicate an upstream data-quality problem worth fixing at the source.
A single pair of tables can have more than one plausible relationship eg FactSales might have both an OrderDate and a ShipDate, each of which could relate to DimDate. Power BI allows only one active relationship between two tables at a time (the one used automatically by visuals); any additional relationship must be marked inactive and invoked explicitly in DAX using the USERELATIONSHIP function when a calculation specifically needs the alternate date.

iv). Filter Direction

Filter direction determines which way a selection made on one table propagates to affect another, related table. This is set per relationship and is one of the most consequential yet most commonly misunderstood settings in a Power BI model.
The diagram below shows the two filtering directions:

Single-Direction Filtering

In single-direction filtering, a selection on the “1” side of a relationship (a dimension table) filters the “*” side (the fact table), but not the reverse. For example, selecting “Electronics” in DimProduct filters FactSales down to electronics sales but a selection made directly on FactSales does not filter DimProduct back. This is the default behaviour for one-to-many relationships in Power BI, and it correctly matches how most star schema reporting is meant to work: dimensions drive filters on facts, not the other way around.

Bidirectional Filtering

Bidirectional (both-direction) filtering allows the filter to propagate both ways: a selection on DimProduct still filters FactSales, but a selection or calculation touching FactSales can now also filter back onto DimProduct. This is occasionally necessary, for example, to make a slicer built from a fact table correctly limit an unrelated dimension in a many-to-many scenario but it should be applied carefully and only when needed.

The main risks of overusing bidirectional filtering are:

Ambiguous filter paths: When multiple bidirectional relationships create more than one possible route between two tables, Power BI may be unable to resolve which path should apply, or may apply one path unexpectedly.
Unnecessary model complexity: Bidirectional relationships are harder to reason about and debug, since the effect of a single filter can now ripple through the model in ways that are not immediately obvious from looking at a single relationship line.
Performance cost: Resolving filters in both directions requires more computation than a single, predictable direction.

The general guidance is to leave relationships single-directional by default and enable bidirectional filtering only on a specific relationship, for a specific, well-understood reason but never as a default troubleshooting step when a filter “isn't working.”

v) Joins in Power Query

A join (called a Merge Query in Power Query) combines rows from two tables based on matching values in one or more key columns. It is a similar in concept to a SQL join, but performed during data loading and transformation, before the data ever reaches the Power BI data model. Power Query's Merge Queries dialog supports six join kinds, illustrated below using a Customers table and an Orders table.
See figure below.

Left Outer Join

Definition: keeps every row from the left table (Customers) and attaches matching columns from the right table (Orders) where a match exists; unmatched left rows are kept with nulls in the right-hand columns.
Retained: all left rows, plus matched right rows.
Example: Customers = {Amina, John, Wanjiru}; Orders = {Amina–Order1, John–Order2}. A left outer join on Customer returns Amina with Order1, John with Order2, and Wanjiru with a null order. Wanjiru is not dropped even though she has no order.

Right Outer Join

Definition: the mirror image of a left outer join. It keeps every row from the right table (Orders) and attaches matching columns from the left table (Customers) where a match exists.
Retained: all right rows, plus matched left rows.
Example: using the same tables, if Orders additionally contains an order with no matching customer record (a deleted or missing customer), a right outer join still returns that order, with null customer details attached.

Full Outer Join

Definition: keeps every row from both tables, matching where possible and filling with nulls on whichever side has no match.
Retained: all rows from both Customers and Orders.
Example: the result includes Amina and John with their orders, Wanjiru with a null order, and any order with no matching customer, with null customer details or nothing from either table is dropped.

Inner Join

Definition: keeps only rows where a match exists in both tables.
Retained: only matched rows, which is the intersection.
Example: only Amina and John appear in the result, since only they have matching orders; Wanjiru (no order) and any unmatched order are excluded entirely.

Left Anti Join

Definition: keeps only rows from the left table (Customers) that have no match in the right table (Orders); no columns from the right table are attached, since by definition there is nothing to attach.
Retained: unmatched left rows only.
Example: the result is just Wanjiru and is useful for a “customers who have never placed an order” report.

Right Anti Join

Definition: the mirror image of a left anti join. It keeps only rows from the right table (Orders) that have no match in the left table (Customers).
Retained: unmatched right rows only.
Example: the result is only the order with no matching customer record. It is useful for finding orphaned transactions that point to a missing or deleted customer.

Join summary table

vi). Power Query Join vs Power BI Relationships.

Merging tables in Power Query and creating a relationship in the Power BI data model can look superficially similar because both connect two tables on a matching key but they operate at different stages of the workflow and have very different consequences for the resulting model.

A merge is the right choice when data genuinely needs to be flattened once upstream for example, resolving a lookup value from a small reference table into a single column before the query is loaded, where creating a full dimension table would be overkill. A relationship is the right choice for connecting a fact table to its dimensions, because it preserves each table's independent structure, avoids duplicating dimension attributes across every fact row, and lets Power BI's filter-propagation engine do the work of applying filters across tables at query time rather than at load time.
Excessive merging pulls a model back toward the flat-table pattern discussed in Section 1: descriptive attributes get copied into transactional rows, redundancy increases, and the model loses the clean separation between facts and dimensions that makes a star schema efficient. Keeping fact and dimension tables separate, connected by relationships rather than merged into one wide table is preferable in a BI model because it keeps redundancy low, keeps each table's grain clean and unambiguous, and lets a single dimension table (like DimDate) be reused across multiple fact tables without being duplicated into each one.

vii). Recommended Power BI Model

For a typical business intelligence project like sales, operations, or financial reporting against a fact table connected to a handful of descriptive dimensions(a star schema), connected with single-direction, one-to-many relationships, is the recommended default design.

Why a star schema over a flat table
A flat table may look simpler at first glance, but it fails on almost every criterion that matters as a report grows: redundancy inflates file size, compression suffers, and DAX measures that look simple can silently double-count values once a single wide table mixes transactional and descriptive data. A star schema solves all three problems by separating facts from dimensions from the outset.

Why a star schema over a snowflake schema
A snowflake schema's main benefit is reduced redundancy within dimensions. It is only marginally useful in Power BI, because VertiPaq's columnar compression already stores repeated values efficiently. What a snowflake schema does add is more relationships and longer filter-propagation paths, which increase model complexity and can slow down report performance without a corresponding benefit. Snowflaking is only worth the added complexity when a dimension is genuinely very large and its sub-attributes are shared identically across multiple other dimensions.

Relationships and filter direction
Within the recommended star schema, relationships should be one-to-many between each dimension and the fact table, with the dimension on the “1” side. Filter direction should default to single-direction (dimension > fact), which matches how star schema reporting is meant to work and avoids the ambiguous filter paths that bidirectional relationships can introduce. Bidirectional filtering should be reserved for specific, well justified cases such as a genuine many-to-many scenario resolved through a bridge table rather than applied broadly

Conclusion

In short, build fact and dimension tables separately, connect them with one-to-many relationships kept single-directional by default, use Power Query merges sparingly and only for genuine upstream flattening needs, and reach for a snowflake schema only when a specific, large, shared dimension clearly justifies the added complexity. This combination gives the best balance of performance, simplicity, and long-term maintainability for the great majority of Power BI reporting projects.

Building a Product Analytics Dashboard from E-Commerce Data: A Jumia Case Study

Wendy Adika — Sun, 06 Sep 2026 15:48:44 +0000

1. Introduction

E-commerce sites collect huge amounts of listing data, but it's rarely ready to analyze right out of the box. Prices show up as messy text instead of numbers, review counts sometimes don't make logical sense, and ratings are missing more often than you'd expect. This article walks through the full process of turning raw Jumia product listings into a working, interactive Excel dashboard including the data problems I ran into, how I cleaned them, the formulas used to build new analytical fields, and what the data ultimately showed. The goal isn't just to show the final result, but to explain why each decision was made, so the same approach can be reused on similar marketplace data.

The main question behind this project was easy to ask but harder to answer well: does discounting actually help sellers on Jumia, and if so, how? More specifically: do bigger discounts lead to more customer engagement (reviews), or better perceived quality (ratings)? To answer that, the analysis moved through four stages: getting the raw data in, cleaning and validating it, engineering useful features, and building a dashboard that non-technical people could actually use.

2. Dataset

The raw dataset consists of 115 rows and six columns as per the Raw_Data worksheet in the Jumia_product_dashboard.xlsl file. Below are screenshots of the Raw_Data worksheet and field descriptions.

This dataset is just a single snapshot in time;it doesn't include sales numbers, how old each listing is, or who the seller is. That limits what we can actually claim. The analysis can show that things are related, not that one causes the other.

3. Data Quality Assessment

Before any transformation, the raw data was profiled systematically. This produced a data quality audit with the following findings:

345 price cells were text, not numbers. Prices had things like currency symbols, commas, or extra spaces so Excel couldn't do math on them until we cleaned them up.
58 blank cells in both Review and Ratings. These weren't zero they just meant no review or rating existed yet. If they were treated as zero, it would have made every average look much worse than it really was.
57 review counts were negative. A review count can't be negative in real life, so this was likely an error from scraping or exporting the data.
*57 discount values didn't make sense *. Discounts should always be between 0% and 100%, so these were probably also export errors.
3 rows were exact duplicates. If left in, they would have been counted twice in every total and average.
2 prices were written as a range (like "100–200") instead of one number, which formulas can't use directly.
Column headers weren't consistent with spelling mistakes. This seems minor, but it causes real problems once formulas depend on typing the header name exactly right.

4. Data Cleaning

Before cleaning, I copied the data from Raw_Data sheet to Cleaned_Data sheet and converted the data range to an Excel Table with Ctrl+T and gave it the name tblProducts.

Removed the 3 duplicate rows. They added no new information and would have thrown off every total. 112 Products remained.
Renamed the column headers. so they were consistent. This also made them safe to use as table references (like tblproducts[Current Price]) instead of plain cell ranges.
Formatted prices column.Removed KSh, commas, and extra spaces, then convert to a number using this formula. =VALUE(SUBSTITUTE(SUBSTITUTE(B2,"KSh",""),",","")) Format cleaned values as KSh #,##0.00
Turned the price ranges into a midpoint number. This keeps the row usable without guessing at an exact figure. I used the average function to do this.
Changed disount column data type. Remove %, convert to a number, divide by 100 if necessary, and format as Percentage using the formula below. =VALUE(SUBSTITUTE(D2,"%",""))/100
Converted negative review counts to whole numbers. Since a negative review count isn't real, it was treated as an error and corrected it rather than deleting the row using the formula below. =IF(E2="","",ABS(VALUE(E2)))
Formatted Ratings column. Removed out of 5, converted to a decimal, and leave genuinely missing ratings blank using this formula. =IF(F2="","",VALUE(SUBSTITUTE(F2," out of 5","")))
Kept missing Review and Ratings cells as "Missing," not zero. This was the most important cleaning decision in the whole project.
Added checks for Price, Ratings and discounts column using the formulas shown.

5. Excel formulas and enrichment fields.

Once the numbers were clean, new columns were added to turn raw numbers into useful, easy to read categories.

Discount Amount:
=[@[Old Price]]-[@[Current Price]]

Rating Category (fixed thresholds, since a star rating means the same thing everywhere):
=IF([@Ratings]="","Missing",IF([@Ratings]<3,"Poor",IF([@Ratings]<=4.5,"Average","Excellent")))

Discount Category Chosen to match how discount tiers are usually talked about in retail.
=IF([@Discount]="","Missing",IF([@Discount]<20%,"Low Discount",IF([@Discount]<=40%,"Medium Discount","High Discount")))

Price Category used the dataset's own quartiles that were calculated in the analysis sheet instead of fixed numbers, since "expensive" or "cheap" depends on what's actually in this dataset:

Price_Q1 = QUARTILE.INC(tblproducts[Current Price], 1)
Price_Q3 = QUARTILE.INC(tblproducts[Current Price], 3)

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

Customer Engagement used the same quartile approach, splitting products into Low, Steady, and Strong engagement.
=IF([@Review]="","Missing",IF([@Review]<=Review_Q1,"Low Engagement",IF([@Review]<=Review_Q3,"Steady Engagement","Strong Engagement")))

Review Category
=IF([@Review]="","Missing",IF([@Review]<=Review_Q1,"Low Reviews",IF([@Review]<=Review_Q3,"Steady Reviews","Many Reviews")))

Finally, three simple QC check columns were added so problems would show up immediately if new data was ever added. Price, Ratings and Disounts.

6. PivotTable and analysis workflow;

After cleaning, 112 products remained. Summary numbers were calculated directly from the cleaned table.
These were the formulas used:

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

Below is a screenshot of the summary.

The Xlookup function was used to find the excat most and least expensive product.
=XLOOKUP(MAX(tblproducts[Current Price]),tblproducts[Current Price],tblproducts[Product])

Correlation was also tested between the following fields using this formulas:

*Discount (x) versus review *(y); -0.136822724
As disount goes up the number of reviews tend to slightly go down but because the value is very close to zero the linear relationship is non-existent. That's worth pausing on, because it goes against the common assumption that bigger discounts automatically mean more customer engagement.

Ratings (x) versus review (y); 0.057209035
As the product rating increases, the number of reviews tends to increase very slightly.Because the strength of this relationship is so close to zero there is no meaningful linear relationship between the two variables, hence a product's rating does not predict how many reviews it will receive and vice versa.

Current price (x) versus rating (y); 0.110090213
As the current price of a product increases, its rating tends to increase slightly.The relationship is highly minimal. Because the value is so close to zero, price explains only a tiny fraction of the variation in product ratings. You cannot reliably use a product's price to predict its consumer satisfaction rating.

There are also three line charts for each correlation analysis.

From there, PivotTables were built on top of the cleaned data. PivotTables act as a middle layer. They make the charts faster to load and let the slicers filter several charts at once without slowing the whole workbook down.
The following are the tables created:
top 5 and bottom 5 products by rating;
top 10 products by discount;
top 10 products by reviews;
top 10 products by rating;
products with high discounts but low ratings;
products with high discounts but low engagement; and
products with many reviews but average ratings.

7. Dashboard Design and Slicer Connections

The dashboard was built so a non-technical person like a category manager could explore the data without ever touching a formula.

Below are screenshots of the Dashboard.

The top part of the dashboard is the dashboard title: Product Performance and Pricing
The furthest left side of the board are the slicers; Price category, Rating Category and Discount Category. All are connected to every PivotTable behind the charts, so clicking one option filters all the visuals on the sheet at once. Connecting both slicers to all five PivotTables (instead of just one chart each) is what makes the dashboard feel interactive rather than static: a single click updates the whole picture at once. You do this by right clicking on each slicer then reporting connections and choose all tables.

Eight charts:

Top 10 products by ratings
Top 10 products by review
Top 10 products by Discounts
Discount vs Reviews: does a bigger discount mean more reviews?
Rating vs Review: how does review count change across different rating scores?
Price vs Ratings: do more expensive products get rated higher?
Rating Mix: what share of products fall into each rating category, including "Missing"?
Discount Mix: how many products sit in each discount tier?

Insights
The insight text box contains findings and recommendations.

8. Key Findings

Bigger discounts bring more reviews, but not better ratings. High and Medium Discount products get far more total reviews than Low Discount ones, but the correlation check shows this isn't a strong, reliable pattern at the individual product level.
Most products sit in the High Discount group (about 63 products, versus 24 in Low Discount)The catalog leans heavily toward deep discounting.
Higher priced products get slightly better ratings (about 4.1 vs. 3.75 for the cheapest tier), a small but consistent gap.
A clear "Missing" ratings group exists, separate from "Poor": these are simply unrated products, not confirmed bad ones.
Discount size barely predicts review count (correlation ≈ −0.14) something else is driving engagement more than the discount itself.

9. Business Recommendations

Test smaller discounts on Low Discount products to see if reviews still grow without hurting ratings.
Check a sample of High Discount listings to see if they're real deals or just aging stock being cleared out.
Improve photos and descriptions on Low Price listings, where ratings tend to be a bit lower.
Focus review-request campaigns on "Missing" rated products before worrying about the smaller "Poor" group.
Don't rely on discount depth alone to grow reviews because the data shows it's a weak lever on its own.

10. Limitations and Lessons Learned

This dataset has no sales numbers, no listing age, and no seller identity hence "more reviews" can't be read as "more sales," and older listings can't be told apart from genuinely more popular ones. With only 112 cleaned rows, smaller groups (like Low Discount, at about 24 products) carry more uncertainty than larger ones. And because this is a single snapshot, none of these patterns should be treated as fixed facts but starting points for real tests, like controlled discount experiments, not final answers.

The biggest lesson from this project wasn't about charts or formatting. It was the decision to keep missing ratings labeled as "Missing" instead of quietly treating them as zero or dropping them. That one choice is what let the dashboard tell the difference between a product nobody has rated yet and one people genuinely dislike, a distinction that changes what a seller should actually do next.

10.Links

Github Repo
Dashboard file

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

Wendy Adika — Thu, 27 Aug 2026 18:14:07 +0000

What is Excel?

Excel is a spreadsheet program that enables users to capture, organize, calculate and visualize data. Excel is part of Microsoft office and is available for Windows and Mac. Operating systems that can't access excel like Linux will first require installation of a virtual Machine(A software based computer inside another physical computer) through a cloud-service provider like Microsoft Azure, Google Cloud Platform(GCP) or Amazon Web Services(AWS).

How Organizations Use Excel.

Collection and storage of data.
Data cleaning.
Data analysis and visualization.
Performance reporting.
Accounting.
Administrative and project management.

Excel User Interface and Components.

Interface.

When you open excel on your laptop, this is what you essentially see:

Components of Excel.

Ribbon: A row of tabs that helps you locate and navigate commands. The tabs include Home, Insert, Page Layout, Formulas, Data, Review, View and Help.
Quick Access Toolbar: Contains icons for save, undo and redo. Marked blue in the image below is the quick access toolbar.
Formula bar: Where contents of a selected cell are displayed. The formul bar is circled red in the image below.
Worksheet: A set of rows and columns.
Column: A vertical line of cells labelled in Alphabetical Order(Column A, Column B,C...)
Row: Horizontal line of cells labelled in numerical order( Row 1, Row 2,3...)
Cell: Intersection of a row and column where raw data is captured. It is labelled as column_row(A1, B12)
- Worksheet tabs: Tabs at the bottom (Original, Cleaned, Sheet2) that switch between spreadsheets within a workbook
Workbook- A collection of multiple spreadsheets/worksheets that create an excel file.
Status bar: Displays workbook status and quick calculation information. Refer on what it looks like using the image below.

Data Entry and Editing.

Entering data:

To enter data into a cell, click on any cell then add values. Columns store fields and rows store records. Click enter to move to the next row or the arrow keys to move to whichever cell you want.
See example below:

Data Types:

Some commonly used data types that can be found on the home tab under number group include:

General: No specific format
Text: Words(Gloria)
Number:2
Dates: 02/12/2026
Currency: $280

Data types are also accessible when you select a cell/cells then right click and choose format cells on the dropdown list that appears.

Data formatting in excel:

All data formatting commands are found in the home tab.

To Format text: Click on home tab then under font group select whatever format you desire. Some format options include Bold(B), Italics(I), Underline(U). You can also change Font type, Font color and Font size.
To Align cells> Check alignment group.Some alignment options include aligning to either the right, left, center top or bottom, merging cells(combining cells) and wrappint text(moving words to a new line when they reach a margin instead of overflowing.
Normally, when the data type is text, the cell contents are aligned to the left. Numerical data is usually aligned to the right.
To Format numbers: Check the Number group which contains actions like number types and choose your data type from the dropdown list.
Borders and fill colors: Select your cell range then go to font group and click on the diagram that looks like a window pane. See borders in the image below marked in blue.

Saving a Workbook:

Different ways to save your workbook:

Click on file then click on save as. Select the directory/folder you want it save in.(eg.Desktop) then write your file name. Make sure the file type is excel workbook/ .xlsx.
You can also click on the save icon at the quicktool bar at the top left part of the screen.
To save time, use the save shortcut by clicking ctrl and S.

Data Sorting and Filtering.

Sorting:

Sorting means arranging data in a specific order. You can sort text in alphabetical order(A-Z) or (Z-A), numerical values from largest to smallest and vice versa and date from oldest to newest and vice versa.
Sorting helps to make data easier to read, search and manage.
To sort:

Select column or range of cells you want to sort.
Go to home tab then sort and filter under the editing group.
Choose what type of sorting you desire. You can also custom sort. Then click ok to run that command.

Filtering:

Filtering hides cells that don't meet a certain criteria. It only displays the cells that do. The filer button is at the same place where the sort is. Check the sorting diagram above.
To filter:

Click anywhere on your data set.
Click on sort&filter on the hometab in the editing group, then click filter.
A dropdown arrow will appear on the column you want the filter.
Click on the arrow and choose your filter option. Click ok.

There are three types of filter options:

Text Filter: Contains, Begins With, Ends With.....
Number Filter: Greater Than, Less Than......
Date Filter: Before, After, Between.....

Data Validation:

Data validation restricts the type of data that can be keyed into a cell in a particular field/column.

Select range of cells you want to validate.
Go to data tab then click on data validation
On the dialog box, select the criteria you want to use.

An example is when you want your column to only accept K, L and M, you can click on list then add the 3 values. No other value that is not the three will be accepted in that particular column. See demonstration below.

Any attempt to key in any value that is not in the criteria will bring an error message as shown in the illustration above.

Data Cleaning.

Data cleaning is the process of finding and fixing errors, missing values and removing duplicates in a dataset to make it more accurate and ready for analysis.

Before cleaning your data, you can perform this actions to make navigation easier:

Autofit: When values in cells are cut off showing ###errors, you can first select all data on your workspace using the short cut C*TRL+A* then autofit by clicking on the home tab, then on the cells group click format, then click autofit width length as shown below.

2.Freezing Panes:
Freezing panes helps to lock specific rows or columns so that they remain visible as you scroll. This makes it easier to work on the data especially when dealing with a large data set.
Click on the view tab the freeze panes the choose what you intend to freeze, then click ok.

Removing Duplicates:

To remove duplicates,

Select your complete dataset by clicking the ctrl+A shortcut.
Go to the Data tab and select remove duplicates. See illustration below.
In the dialog box, check the columns to consider. Often it is wiser to check all. Then click ok.

Number Formatting:

Number formatting changes how numerical values appear on a spreadsheet without changing the actual data stored in the cell.
To format number,
You can select cells then right click to return a dropdown box with a format cells option.

Select cells then under the home tab go to number group and click on number format bar. These data types are used to show prices(currency), percentages(percentages/%)and dates.

Conditional Formatting:

Conditional Formatting is used to highlight cells that follow a rule that you have set, making it easier to search for a particular value and also spot trends.
To conditional format:

Select cells.
Go to home bar, under styles group click on conditional formatting.
Choose the rule you wish to apply. Click ok
Enter the value you want to highlight then click ok.

Text Functions

Text functions can be used to clean data by addressing issues such as extra spaces, inconsistent text formats, unwanted symbols, or multiple text strings combined in a single cell.
Below are the different text functions:

UPPER-Changes all text in selected cell to upper case- Example: If cell k5 contents is "Grace", when you use the upper function =UPPER(K5) and run it GRACE will be displayed.
LOWER-Changes all text in selected cell to lower case- Example: If cell k5 contents is "Grace", when you use the lower function =LOWER(K5) and run it grace will be displayed.
PROPER-Capitalizes the first letter of each word in a cell.If cell k5 contents is "grace kamau" when you use the proper function =PROPER(K5) then "Grcae Kamau" will be displayed.
TRIM-Removes extra spaces within text. If you =TRIM( " Grace Kamau " then "Grace Kamau" will be displayed.
LEFT-Extracts leftmost characters. If you =LEFT("Grace,2) then "Gr" will be displayed. Similar to the RIGHT function where if you =RIGHT("Grace",2) it will display "ce".
MID-Extracts characters from the middle. if you =MID("Grace", 1) then "a" is displayed.
LEN-Calculates length of text. If you =LEN("Grace") then "5" is displayed.
FIND-Finds position of a substring. If you =FIND("a","Grace") the "3" is displayed.
CONCAT-Combine two texts from different cells into one. If Grace(k5) and Kamau(L5) are in different cells you can =CONCAT(K2," ",L2) then "Grace Kamau" will be displayed. the " " on the function is for when you want a single space between the two texts.
SUBSTITUTE-Replaces a text within a string. If you =SUBSTITUTE("Grace kamau"," kamau"," Mutua") it will display "Grace Mutua".

Other functions include

Aggregate functions: SUM, PRODUCT, POWER, MODE, MEDIAN, MAX, MIN, SQRT etc. Statistical functions: COUNT, COUNTA, COUNTBLANK etc. Conditional: COUNTIF,SUMIF....etc. Date and Time: TODAY,NOW,DAY,MONTH,YEAR,DATEDIF,NETWORKDAYS etc.

Formulas.

A formula is an expression in a cell that performs calculations. It must always start with the = sign.
It can include

Cell references(M20)
Operators(*,+)
Numbers(45)
Functions(SUM) An example: =78+22 which will return 100

Conclusion

This article has explained the basics of Mircosoft excel including components and the different ways to you can use it to clean data(Sorting and filtering, Data validation, removing duplicates, number formatting). Moreover, you can practice on ai generated unclean date and explore other articles to deepen your skills.

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

Wendy Adika — Thu, 20 Aug 2026 12:20:47 +0000

This tutorial guides on how to push your local projects to Github using Git and SSH.

The prerequisite steps are:

Installed and configured Git using your user name and email.
Set up your Github account.
Generated and added your public ssh key to your Github.
Tested your ssh connected and make sure it is successful.
Created a local project with Readme file, data ,scripts and notebooks where applicable.

Step 1: Project Initialization With Git

Open gitbash terminal.

Before initialization, ensure your location(which is at the end of the path) is the project folder you intend to push.
Run:
pwd which stands for print working directory. Working directory is the folder you are currently in.
You can move to intended directory using the cd command.
To initialize the folder as a Git repo(repository) run:
git init

This will appear:

Also check for git's hidden files by running: ls -la You should see .git among files. .git folder acts as the git memory.

Step 2: Check Status

To check the current status of your working directory and staging area run:
git status

Because it is a new project, the files are untracked.

Step 3: Add

To prepare our project files for the next commit you run:
git add .

Step 4: Commit

To save any changes to your project files run:
git commit -m "----"
Inside the quotes, write a message that explains what was saved.
See example:

Always ensure that the branch is main.

Step 5: Pushing to Github

Create a New Github Repo

Sign in to your github account.
Create a new repo- should be empty.
Fill in new repo form then click on create(Repo name, description and choosing either public or private, the rest you can skip).
Click on ssh then copy ssh address.

Go Back To Gitbash

Ensure location is still project folder.
To link your local git repository to your github repository run: git remote add origin **the_copied_ssh_address**
To confirm that Git can send and receive information from the correct github repo run: git remote -v .
To push your project run: git push -u origin main This means that git has sent any commited changes to your github repository.
Confirm your project has been pushed to github by refreshing the page.