DEV Community: Bonny Mark

From Raw Data to Business Decisions: Building a Power BI Solution for JCars Logistics

Bonny Mark — Sun, 27 Sep 2026 16:08:02 +0000

Introduction

JCars Logistics imports, sells and delivers vehicles across Kenya. Management wanted a report that could answer simple questions like: how is the business doing, where is it making money, where is it losing money, and what needs a closer look? All I got was one raw CSV file, nothing else.

Understanding the data before touching it

I worked out what the data actually represented. One row equals one order line for a vehicle sale (sometimes more than one unit, since there's a Units Sold field). The 34 columns split into two kinds: descriptions of the customer, vehicle, branch and so on, and numbers like price, cost, fees and ratings. Two fields looked like they could be unique IDs, Order ID and Customer Name, but I didn't trust either one until I'd checked. Good thing I did.

Auditing the data

I didn't assume any field was correct. I loaded everything into a dataframe and ran checks for nulls, duplicates, mismatched formulas and impossible logic. I found a lot more than the ten issues the brief asked for. The most important ones:

1. The Revenue column was completely broken. This was the single biggest find, and it shaped everything I did after it. I checked Revenue against Units Sold times Unit Selling Price and the ratio wasn't random. It was always exactly 455 divided by Units Sold, and 455 happens to be the total number of units sold across the whole dataset. Somewhere along the line, whoever built this field multiplied Unit Selling Price by the sum of every unit ever sold, instead of just that row's own quantity. It's the kind of mistake you get from an Excel formula that locked onto the wrong cell range. I dropped the column entirely and used Revenue Recorded instead, which I checked separately and found actually lines up with Units Sold times Unit Selling Price times one minus Discount, plus Delivery Fee.

2. Order IDs weren't actually unique. There were five different formats floating around (ORD, CAR, LC, LCL, LCL-), a bunch of rows just said "UNKNOWN", and a few IDs were duplicated across totally different orders. So I couldn't count orders using a distinct count on Order ID. I made that mistake myself at first in my DAX before I caught it.

3. Some prices were way off. A Toyota Land Cruiser was listed at 122.4 million shillings when similar ones sold for 12 to 14 million. A VW Tiguan was listed at 47 million against a normal range of 4 to 5 million. Both look like an extra zero got typed in by mistake.

4. Delivery dates before order dates. 18 orders had a delivery date earlier than the order date, which obviously can't happen.

5. A lot of missing values. Discount, Delivery Date, Logistics Cost, Customer Rating and Vehicle Year all had a meaningful chunk of nulls. I needed a clear policy for these instead of just filling them in and hoping for the best.

6. Some very large discounts. A handful of orders had discounts up to 50%, way outside the normal 0 to 15% range.

For every one of these I made a decision instead of quietly fixing things behind the scenes. For the price outliers I kept the records and flagged them rather than deleting them, since I had no way to confirm what the "correct" value should have been. For category fields I standardized spelling. For currency, I assumed Kenya Shillings whenever no currency was shown, which is what the brief asked for.

Sorting out the currency

The dataset mixed KES with figures in USD, EUR and ZAR, using symbols, prefixes and "M" suffixes in no consistent way. In Power Query I built a function that detects which currency each value is in, then converts it using a rate table by currency and year.

Currency	Rate to KES
USD	130
EUR	150
ZAR	7.5

These rates aren't official exchange rates from some external source. I worked them out from the numbers already in the dataset, picking rates that turn the foreign figures into round KES amounts. I applied them the same way across every money column and wrote this down as an assumption, since the brief says there's no single correct rate they're looking for.

Cleaning it up in Power Query

I didn't just run a standard cleaning routine. Every step traces back to something the audit found. A Standardize function maps dozens of spelling variations (corp, Corporate, govt, Government) to one clean category per field. A date parser tries several formats in order, since the file had at least six different date layouts mixed together. Functions for discount and rating enforce sensible limits and turn impossible values, like a 120% discount, into nulls rather than guessing what they should have been.

Why I built a star schema

The original file was one big flat table with every field repeated on every row. I broke it into a star schema instead: one fact table holding the keys and numbers, and seven dimension tables for Branch, Geography, Sales Rep, Lead Source, Vehicle, Customer and Date. This stops branch names and vehicle details from being repeated hundreds of times, lets me do proper time comparisons with a real date table, and keeps the filtering behavior predictable.

Here are the dimension tables once they were built:

And the model taking shape, as I worked through wiring up the relationships correctly:

I'm going to be honest about a couple of mistakes I made while building this, because I only found them by actually testing the model, not by assuming it was right.

I ended up with two fact tables loaded at once, the original raw query and my new Facts table, both linked to the same dimensions. Power BI only allows one active relationship between two tables, so it quietly deactivated the new table's links to Branch, Geography and Sales Rep. Everything still looked connected, but filtering by branch or city wouldn't actually have worked on the new measures. I fixed it by turning off "Enable load" on the original raw query, so it exists purely as a source step for the other queries, never as its own table in the model.

I also had a bug in how I matched rows during the merges. I was checking "if the lookup result equals null" to catch rows with no match, but a failed merge in Power Query returns an empty table, not null. So any row with a genuinely blank join key, like a missing Vehicle Year, threw an error instead of just getting a null key. Twelve rows failed to load because of this. Switching the check to "is the table empty" instead of "does it equal null" fixed all twelve at once.

I also broke one query completely by renaming a step and forgetting that a later step still referenced the old name. Power Query gave me an error saying the import didn't match any exports, which is its way of saying "this step no longer exists." That one taught me to check every step downstream before renaming anything.

For dates, I made Order Date the active relationship to the date table, and kept Delivery Date as a second, inactive relationship that I switch on with USERELATIONSHIP whenever I need to look at something from the delivery side instead.

The measures and calculated columns

I tried to make sure every measure existed for a reason, not just to pad out the model. Some of the core ones:

Dax:

Total Revenue = SUM('Facts table'[Revenue Recorded])
Total Orders = COUNTROWS('Facts table')
Gross Profit = [Total Revenue] - [Total Cost]
Gross Profit Margin % = DIVIDE([Gross Profit], [Total Revenue])

Completed Revenue =
CALCULATE([Total Revenue],
'Facts table'[Payment Status] <> "Cancelled",
'Facts table'[Payment Status] <> "Refunded")

Revenue YoY % = DIVIDE([Total Revenue] - [Revenue PY], [Revenue PY])

One more bug worth mentioning. My first version of Total Orders used a distinct count on Order ID, which undercounted by 21 orders, because Order ID isn't actually unique (remember the UNKNOWN rows and the duplicates). Since each row in the fact table already represents one order, a plain row count was the right fix. Once I changed it, everything downstream that depended on it, like Return Count and Completed Orders, corrected itself automatically.

I also added a few calculated columns to make investigation easier without needing a separate measure for every possible slice: Discount Bracket, Rating Bracket, Price to Cost Ratio, Delivery Lag Days, and a Flag for Investigation column that catches any order with a strange price ratio, a very large discount, or a negative delivery lag. These are the same problems the audit found, just now something a manager can actually filter and click on in the report instead of something buried in my analysis notes.

Designing the Executive Dashboard

The first page is meant to answer "how is the business doing" in one glance. It has a title, six KPI cards, all built off "Completed" measures so they're consistent with each other (Completed Revenue, Completed Gross Profit, Gross Profit Margin %, Units Sold, Completed Orders, and Logistics Cost % of Revenue). That last card has conditional formatting, turning red once logistics cost passes 2% of revenue, which is my simple way of flagging something that needs attention rather than just displaying a number. Below that sits a monthly trend chart and two comparison charts, one for branch and one for car make. A year slicer sits next to the title.

The detail pages

I grouped related topics into pages instead of making one page per business question, since the brief specifically says a single well-designed page can answer several questions at once. I ended up with these five pages:

Sales, Vehicle and Profitability

Branch, Region and Sales Channel Performance

Payments, Delivery and Logistics

Customers, Discounts and Investigation

There's also a hidden Order Investigation page that you only reach through drill-through:

Making it interactive

There's a Year slicer on the Executive Dashboard. Right-clicking a branch bar anywhere in the report opens the hidden Order Investigation page, filtered to that branch's flagged orders, showing Order ID, Branch, Car Make, Car Model, Sales Rep, Payment Status, Discount, Price to Cost Ratio, Delivery Lag Days and Returned. Normal cross-filtering between visuals works throughout the report too.

What I found

The Revenue field, if I'd used it as it was, would have overstated total revenue by about 455 times. That's a good reminder that a field's name tells you nothing about whether the value in it is correct. Every number needs checking before you trust it.

Nairobi HQ has the lowest branch revenue in the dataset, despite being in the capital. That's worth looking into properly, whether it's staffing, stock levels or something about local demand, rather than guessing.

Pending and Partially Paid orders together make up around 40% of both order volume and revenue value. That's a lot of recorded sales that hasn't actually turned into cash yet, which matters more for cash flow than the headline revenue figure suggests.

Bigger discounts don't lead to bigger deals. Average order value is actually lower at 15% plus discount than it is at 0 to 5%. That suggests discounting might be reactive rather than a deliberate strategy.

Trucks and Sedans are the only vehicle types with negative or near-zero profit margin. SUVs carry most of the volume and most of the profit. That's a solid, specific thing to act on for pricing and stock decisions.

What I'd recommend

Look into why Nairobi HQ is underperforming specifically. Compare its lead source mix, staffing and stock availability against Kakamega and Thika Yard, the two strongest branches, before assuming you know the reason.

Review how Trucks and Sedans are priced. Since they're the only vehicle types losing money on average, either the cost side or the pricing side needs a second look, rather than a blanket instruction to "cut costs."

Put a tighter limit on discounts above 15%. Since bigger discounts don't seem to be buying bigger deals, someone giving away that much margin without a clear payoff is worth reviewing, whether that's a policy gap or a few individual exceptions.

I've kept these as things worth investigating rather than proven facts. Correlation isn't causation, and the brief is clear that it doesn't want claims the data can't actually back up.

What I struggled with and what I took away from it

The bugs I described above were the most useful part of the whole project. Every single one of them looked fine at first glance, the model appeared connected, the query loaded without complaint in preview, and only broke once I actually tested it properly: filtering with a slicer, loading the full 276 rows instead of a sample, or renaming something without checking what depended on it. The main thing I'm taking away from this is that a report that builds without an error showing isn't the same thing as a report that's correct. The only way to know the difference is to actually try to break it before you call it finished.

Repository: [link to GitHub repo]

Data Modelling, Relationships & Joins in Power BI.

Bonny Mark — Fri, 11 Sep 2026 11:17:12 +0000

Introduction

Power BI is used to analyse data and turn it into useful reports and dashboards. For the reports to produce accurate and meaningful results, the underlying data needs to be organized properly. This is where data modelling, relationships, and joins become important.

A good data model makes it easier to create DAX calculations, improve report performance, manage large datasets, and maintain a Power BI project as it grows. This article explains the main approaches to data modelling, the role of fact and dimension tables, relationships and filter directions, and the different types of joins available in Power Query.

Data Modelling in Power BI

Data modelling in Power BI is the process of organizing tables and defining how they are connected. A data model determines how information from different tables works together when creating reports, calculations, filters, and visualizations.

For example, a retail business may store customers, products, dates, locations, and sales transactions in separate tables. These tables can be connected using common fields such as CustomerID, ProductID, and DateKey.

A simple model can be represented as follows:

A well-designed model can make DAX calculations easier, improve query performance, reduce unnecessary duplication, and make the report easier to maintain.

There are three common ways of organizing data for analysis: flat tables, star schemas, and snowflake schemas.

Flat Table

A flat table stores most of the required information in a single table.

For example:

Information is often repeated. If examples one makes 1,000 purchases, his name, region, and other customer information may appear many times. This creates data redundancy and can increase the size of the model.

Advantages

Easy to understand.
Simple to create.
Use for small datasets.
Requires fewer relationships.
Suitable for simple reports.

Disadvanatges

Creates repeated information.
Can become very large.
More difficult to maintain.
Can make the model less flexible.
Not ideal for complex business intelligence solutions.

A flat table can be appropriate for a small report where the data is already prepared and there are relatively few records.

Star Schema

A star schema separates the main business events from the descriptive information used to analyse those events.

The centre of the model is normally a fact table, while dimension tables surround it.

Advantages

Clear and easy to understand.
Usually requires fewer relationships than a highly normalized model.
Makes filtering and reporting straightforward.
Makes DAX calculations easier to manage.
Reduces unnecessary duplication.
Scales well for business intelligence projects.

Disadvantages
Requires proper planning.
More tables are involved than in a flat table.
The designer needs to understand keys and relationships.
Poorly designed dimensions can still cause problems.

The star schema is appropriate for sales systems, financial reporting, inventory systems, customer analytics, and many other business intelligence applications.

It is also a good choice when several reports need to use the same customers, products, dates, or locations.

Snowflake Schema

A snowflake schema is similar to a star schema, but some dimension tables are divided into additional related tables.

For example, instead of storing a product's category directly in DimProduct, category information can be placed in a separate DimCategory table.

Advantages

Reduces some data duplication.
Can represent complex hierarchies.
Can be useful when dimensions contain large amounts of related information.
Can fit well with highly normalized source systems.

Disadvantages

Creates more tables.
Requires more relationships.
Makes the model harder to understand.
Can make filtering more complicated.
Can increase model maintenance requirements.

A snowflake schema may be useful when the additional separation provides a genuine benefit. However, unnecessarily splitting dimensions can make a Power BI model more complicated than it needs to be.

Fact Tables and Dimension Tables

Fact and dimension tables are the main building blocks of a star schema

A fact table stores measurable business events.

Examples include:FactSales,FactOrders,FactTransactions,FactInventory

Dimension Tables contain descriptive attributes that provide context for the facts.
Common dimensions include:DimCustomer,DimProduct,DimDate,DimLocation.

Practical Fact-Dimension Example

Consider an online retail company.

The central table is:

FactSales

It records each sale.

It connects to:

DimCustomer — who purchased?
DimProduct — what was purchased?
DimDate — when was it purchased?
DimLocation — where was the sale made?

The model can therefore answer questions such as:

What were total sales?
Which product generated the most revenue?
Which county generated the highest sales?
How much did each customer spend?
What were sales during 2026?
Which product category performed best?

This is the main strength of the star schema: dimensions provide the context while the fact table provides the measurable events.

Relationships in Power BI

A relationship connects two tables using related columns.

Relationships are necessary because business information is usually stored in multiple tables rather than one large table.

For example:

DimCustomer[CustomerID]

can be connected to:

FactSales[CustomerID]

When a customer is selected in a report, the relationship allows the selection to affect the relevant sales records. Power BI relationships propagate filters between connected tables.

Relationship Cardinality

Cardinality describes how values in one table relate to values in another.

Power BI supports four main cardinality types:

One-to-One 1:1
One-to-many 1:*
Many-to-many :

One-to-One Relationship (1:*)
A one-to-one relationship means each value in one table corresponds to only one value in another table.

Example:

Product SkU may appear once in both:

Product table
Productcategory table

When to use it

It can be useful when information belonging to one entity has been deliberately separated into two tables.

One-to-Many Relationship (1:*)

This is the most common relationship in a star schema.

Example:

DimCustomer[CustomerID] 1 → * FactSales[CustomerID]

A customer appears once in the customer dimension but can appear many times in the sales table.

When to use it

Use it when one record in the first table can be associated with multiple records in the second table.

This is normally the preferred relationship between dimensions and fact tables.

Many-to-Many Relationship (:)

A many-to-many relationship occurs when values in both tables can occur multiple times.

For example:

One student can belong to several courses.
One course can have many students.

Direct many-to-many relationships can be useful, but they can also make filter propagation harder to understand.

A bridge table can provide a clearer solution.

For example:

DimStudent

↓

StudentCourse

↓

DimCourse

The bridge table records the combinations between students and courses.

Keys and Referential Integrity

Relationships normally depend on matching key columns.

Primary Key

A primary key uniquely identifies a record.

Example:

DimCustomer[CustomerID]

could contain:

C001
C002
C003

Each value appears only once.

Foreign Key

A foreign key refers to a record in another table.

In FactSales, the CustomerID may appear repeatedly:

C001
C001
C002
C001

This is because one customer can make many purchases.

Referential Integrity

Referential integrity means that foreign-key values should correctly correspond to valid records on the related dimension side.

For example, if FactSales contains CustomerID C001, the corresponding customer should normally exist in DimCustomer.

If the dimension side is supposed to be the "one" side of a relationship, its values must be unique. Power BI can detect relationship problems when the supposed one-side column contains duplicates.

Active and Inactive Relationships

Power BI relationships can be active or inactive.

An active relationship is the default relationship used for filtering and reporting.

Inactive relationships can be useful when two tables have multiple possible relationships.

For example, a Sales table may contain:

OrderDate
ShipDate
DeliveryDate

All three dates could relate to a Date table, but normally only one relationship may be active at a time for a particular role.

Inactive relationships can then be activated in specific DAX calculations when required.

Filter Direction

Filter direction determines how filters move between related tables.

Single Direction

In a typical star schema:

DimProduct → FactSales

A product selection filters the sales table.

For example, selecting:

Product Category = Electronics

can filter the sales records belonging to Electronics products.

Single-direction filtering is commonly used because it makes the filter path easier to understand.

Bidirectional Filtering

Bidirectional filtering allows filters to travel in both directions.

For example:

DimProduct ↔ FactSales

A filter can travel from the product table to sales and, depending on the model, from sales back toward the product table.

Bidirectional filtering can solve certain modelling problems, but excessive use can create:

Ambiguous filter paths
Unexpected results
More complex models
Potential performance problems

Power Query Joins

Power Query joins are used during the data preparation stage.

In Power Query, a Merge Queries operation combines information from two queries using matching columns.

For example:
Sales

CountryID Units
1 20

2 15
3 30

Countries

ID Country
1 Kenya
2 Uganda
3 Tanzania

The tables can be merged using:

Sales[CountryID] = Countries[ID]

After the merge, the Country column can be expanded into the Sales query.

Power Query supports several join types, including Left Outer, Right Outer, Full Outer, Inner, Left Anti, and Right Anti.

Left Outer Join

A Left Outer Join keeps every row from the left table and adds matching information from the right table.

Sales: This table includes the fields Date, CountryID, and Units. CountryID is a whole number value that represents the unique identifier from the Countries table.

Countries: This table is a reference table with the fields ID and Country. The ID field represents the unique identifier for each record.

n this example, you merge both tables, with the Sales table as the left table and the Countries table as the right one. The join is made between the following columns.

Field from the Sales table Field from the Countries table
CountryID ID

The goal is to create a table like the following, where the name of the country appears as a new Country column in the Sales table as long as the CountryID exists in the Countries table. If there are no matches between the left and right tables, a null value is the result of the merge for that row. In the following image, this null value is shown to be the case for CountryID 4, which was brought in from the Sales table.

To do a left outer join:

Select the Sales query, and then select Merge queries.

In the Merge dialog box, under Right table for merge, select Countries.
In the Sales table, select the CountryID column.
In the Countries table, select the ID column.
In the Join kind section, select Left outer.
Select OK.

From the newly created Countries column, expand the Country field. Don't select the Use original column name as prefix check box.

After performing this operation, you create a table that looks like the following image.

Right Outer Join

One of the join kinds available in the Merge dialog box in Power Query is a right outer join, which keeps all the rows from the right table and brings in any matching rows from the left table

Examples :
Sales: This table includes the fields Date, CountryID, and Units. The CountryID is a whole number value that represents the unique identifier from the Countries table.

Countries: This table is a reference table with the fields ID and Country. The ID field represents the unique identifier for each record.

To do a right outer join:

Select the Sales query, and then select Merge queries.

In the Merge dialog box, under Right table for merge, select Countries.
In the Sales table, select the CountryID column.
In the Countries table, select the ID column.
In the Join kind section, select Right outer.
Select OK.

From the newly created Countries column, expand the Country field. Don't select the Use original column name as prefix check box.

After performing this operation, you create a table that looks like the following image.

Full Outer Join

One of the join kinds available in the Merge dialog in Power Query is a full outer join, which brings in all the rows from both the left and right tables.

Sales: This table includes the fields Date, CountryID, and Units. CountryID is a whole number value that represents the unique identifier from the Countries table.

Countries: This table is a reference table with the fields ID and Country. The ID field represents the unique identifier for each record.

The goal is to create a table like the following, where the name of the country appears as a new Country column in the Sales table. Because of how the full outer join works, all rows from both the left and right tables are brought in, regardless of whether they only appear in one of the tables.

To perform a full outer join:

Select the Sales query, and then select Merge queries.
In the Merge dialog box, under Right table for merge, select Countries.
In the Sales table, select the CountryID column.
In the Countries table, select the ID column.
In the Join kind section, select Full outer.
Select OK

Best use

A full outer join is useful when the objective is to identify and retain records from both datasets, including unmatched records.

Inner Join

An Inner Join keeps only records that have matching values in both tables.

For example:

Customers:

C001
C002
C003

Orders:

C001
C002
C004

The inner join returns:

C001
C002

C003 and C004 are removed because they do not have matching records in both tables.

Best use

An inner join is useful when only records with matching information in both datasets are required.

Left Anti Join

A Left Anti Join returns records that exist in the left table but do not have a matching record in the right table.

Example:

Customers:

C001
C002
C003

Orders:

C001
C002

The left anti join returns:

C003

Practical use

This can be used to identify customers who have never placed an order.

It is also useful for data-quality checks and identifying missing relationships.

Right Anti Join

A Right Anti Join returns records from the right table that do not have matching records in the left table.

For example:

Customers:

C001
C002

Orders:

C001
C002
C003

A right anti join returns:

C003

This could identify an order containing a CustomerID that does not exist in the Customers table.

Practical use

It is particularly useful for finding missing or unmatched records.

Power Query Joins vs Power BI Relationships

Although joins and relationships connect data, they perform different jobs.

Power Query Merge

A merge is performed during data preparation.

It combines data from two queries and can add columns from one query into another.

For example:

Sales + Customer

can produce a query containing:

SalesID
CustomerID
SalesAmount
CustomerName

After the merge, the resulting query can be loaded into the Power BI model.

Microsoft's Power Query documentation demonstrates that Merge Queries can be used to combine queries and then expand columns from the merged table.

Power BI Relationship

A relationship is created in the data model.

Instead of physically putting all columns into one table, the tables remain separate.

For example:

DimCustomer

↓

FactSales

The relationship allows filters to move between the tables.

Power BI relationships therefore support the analytical model, while Power Query merges are mainly used for preparing and shaping the data.

When Should You Merge?

A Power Query merge is appropriate when:

You need to add a column from another query.
Data needs to be cleaned or transformed before loading.
You want to resolve a lookup during data preparation.
The combined result makes the final model simpler.
The information logically belongs together.

For example, if a product ID needs to be converted into a product category before the data reaches the model, a merge can be appropriate.

When Should You Use a Relationship?

A relationship is normally preferable when:

Tables represent different business entities.
A fact table needs several dimensions.
The same dimension will be reused across multiple reports or fact tables.
You want dimensions to filter facts.
You want to maintain a star-schema structure.
The data should remain logically separated.

For example:

DimProduct → FactSales

is generally better than repeatedly copying product information into every sales record.

Problems with Excessive Merging

Although merging can be useful, excessive merging can create a very large and complicated table.

For example, instead of having:

DimCustomer

DimProduct

DimDate

FactSales

a developer might merge everything into one huge sales table.

This can result in:

Repeated customer information.
Repeated product information.
Increased redundancy.
Larger datasets.
More difficult maintenance.
Less flexible modelling.

A star schema avoids much of this unnecessary repetition by keeping dimensions separate from facts.

Recommended Power BI Model

For a typical business reporting application, the recommended approach is a star schema.

A suitable model could be:

DimDate

↓
FactSales
↑
DimCustomer

along with:

DimProduct → FactSales ← DimLocation

The fact table contains transactions and numerical measures, while dimensions contain descriptive attributes.

Why Star Schema?

Performance

A well-structured star schema provides a model that is optimized for analytical queries. Microsoft specifically describes star schema as an important design approach for Power BI semantic models optimized for performance and usability.

DAX Simplicity

Measures can be created in the fact table while dimensions provide the filtering context.

For example:

Total Sales = SUM(FactSales[SalesAmount])

A user can then place:

Year from DimDate
Category from DimProduct
County from DimLocation

into a visual while Power BI uses the relationships to filter FactSales.

Readability

A model with clearly named fact and dimension tables is easier for developers and report users to understand.

Scalability

New dimensions can be added without rebuilding the entire model.

For example, the organization could later add:

DimEmployee

DimSupplier

while retaining the central sales fact table.

Maintainability

Separating business entities reduces duplication and makes it easier to update descriptive information.

Filter Propagation

Relationships provide controlled paths for filters to travel from dimensions to facts.

Relationship Simplicity

The most common pattern is a one-to-many relationship:

Dimension 1 → * Fact

This makes the model easier to understand than a network containing many unnecessary many-to-many or bidirectional relationships.

Practical Design Example

Consider a supermarket reporting system.

The model could contain:

FactSales
SalesID
DateKey
CustomerID
ProductID
StoreID
Quantity
SalesAmount
Cost
DimProduct
ProductID
ProductName
Category
Brand
DimCustomer
CustomerID
CustomerName
Gender
CustomerType
DimDate
DateKey
Date
Month
Quarter
Year
DimStore
StoreID
StoreName
County
Region

Relationships:

DimProduct[ProductID] 1 → * FactSales[ProductID]
DimCustomer[CustomerID] 1 → * FactSales[CustomerID]
DimDate[DateKey] 1 → * FactSales[DateKey]
DimStore[StoreID] 1 → * FactSales[StoreID]

This model can support reports such as:

Total sales by product category

Sales by county

Monthly revenue

Sales per customer

Profit by store

Best-performing products

The model remains understandable because each table has a clear purpose.

Common Modelling Mistakes

Several mistakes can reduce the quality of a Power BI model.

Using One Huge Table

This creates unnecessary duplication and can make the model difficult to maintain.

Incorrect Cardinality

For example, treating a column containing duplicate customer IDs as the "one" side of a relationship can produce errors or incorrect results.

Power BI requires the one side of a relationship to contain unique values.

Excessive Many-to-Many Relationships

Many-to-many relationships can create more complex filtering behaviour. A bridge table can often provide a clearer design.

Excessive Bidirectional Filtering

Using Both everywhere can create ambiguous filter paths and make the model harder to understand.

Ignoring Fact Table Grain

A fact table should have a clearly defined meaning for each row.

Unnecessary Merging

Not every table needs to be merged. Maintaining separate fact and dimension tables is often preferable.

Conclusion

Data modelling is fundamental to successful Power BI development. The way tables are organized directly affects reporting accuracy, DAX calculations, performance, scalability, and maintainability.

A flat table is simple and can be useful for small datasets, but it becomes less suitable as data grows. A snowflake schema can reduce redundancy but introduces additional tables and relationships. For most Power BI analytical models, the star schema provides the best balance of simplicity, performance, scalability, and usability.

Fact tables should contain measurable business events at a clearly defined grain, while dimension tables should contain descriptive attributes. Relationships connect these tables and control filter propagation. One-to-many relationships are particularly important because they naturally represent the relationship between dimensions and facts.

Power Query joins serve a different purpose. They are mainly used to prepare and combine data before it reaches the model. Left, right, full, inner, and anti joins allow developers to control which records are retained during data transformation.

Building an Interactive Excel Dashboard.

Bonny Mark — Sat, 05 Sep 2026 08:59:28 +0000

Introduction

The dataset: 112 products scraped from Jumia Kenya, with fields for current price, old price, discount percentage, review count, and customer rating. The goal was to clean it, analyze it, and turn it into an interactive Excel dashboard with slicers and pivot charts, not just static tables.

Cleaning

Before any analysis, I had to fix:

Prices stored as text — Excel treats "KSh 1,499" as a string, not a number, so no math works until you strip the formatting
Ratings buried in descriptive text — extracting a clean numeric rating from messy scraped fields
Invalid negative review counts — clearly a scraping artifact, not real data
55 of 112 products (49%) with no review or rating. I could have dropped those 55 rows,Instead labeled them "No reviews" and "Not Rated" rather than treating them as blank. That turned out to matter a lot, because **half the catalog having zero customer feedback is itself one of the most important findings in the whole project, did need cleaning.

Building Categories for Pivot Analysis

To make pivot tables and slicers actually useful, I added four calculated columns:

Review_Category — Low / Medium / High / No reviews
Rating_category — Poor / Average / Excellent / Not Rated
Discount_category — Low / Medium / High Discount
price_category — Low / Medium / High / Premium (quartile-based bands)

Building the Dashboard

I built 8 pivot tables from a single shared table (tblCleaned) every pivot table needs to come from the same source table for slicers to control all of them simultaneously. Miss this, and you end up with a slicer that only filters one chart instead of your whole dashboard a mistake I made and had to fix along the way.

The dashboard includes:

5 KPI cards (total products, average price, average discount, average rating, total reviews)
3 top-10 bar charts (by rating, by reviews, by discount)
3 trend charts (discount vs. reviews, rating vs. reviews, price vs. rating)
2 pie charts (rating breakdown, discount breakdown)
3 slicers (rating category, discount category, price category) connected across all 8 pivots

What the Data Actually Said

Discount % vs. Review count: correlation ≈ -0.14
Higher discounts do not lead to more reviews. If anything, there's a very weak negative relationship.

Rating vs. Review count: correlation ≈ +0.06
Essentially zero relationship. Highly-rated products don't get meaningfully more reviews than mediocre ones.

Price vs. Rating: correlation ≈ +0.15
Barely positive. Expensive products are not reliably rated higher than cheap ones though mid-to-upper-priced items (KSh 1,190–1,820) did show the best average rating (~4.2) of any price tier.

Only 16 of 112 products (14%) hit "strong engagement" (14+ reviews)
90% of heavily discounted products (40%+ off) show low or no engagement
49% of the entire catalog has zero reviews, regardless of discount or price

The product that stuck with me most: a 120W Cordless Vacuum Cleaner with 69 reviews, most of any product in the dataset sitting at just 2.8★. High visibility, low satisfaction. That's a much bigger red flag than a discount that didn't move the needle.

Repo & Full Breakdown

Full project, including the cleaned dataset, the Excel workbook (raw data → cleaned data → pivot tables → dashboard), and a detailed README with all findings:https://github.com/kimutaikoros/jumia-product-performance dashboard

Building a Jumia Product Performance Dashboard in Excel.

Bonny Mark — Thu, 03 Sep 2026 12:14:51 +0000

An interactive Excel dashboard analyzing pricing, discounts, and customer engagement across 112 Jumia products built to uncover whether promotions, ratings, and price actually drive customer engagement, and which products need a different strategy.

Project Objective

Turn a raw Jumia product export into a working analysis that answers:

Are larger discounts associated with more reviews?
Do highly rated products attract stronger engagement?
Do price and rating move together?
Which products perform best based on ratings and reviews?
Which products may need a different pricing or marketing strategy?

Data Source

Raw product data scraped from Jumia (Kenya's e-commerce platform), covering 115 products with product name, current price, old price, discount percentage, review count, and star rating. The raw export contained several data quality issues typical of scraped e-commerce data — see Cleaning Process below.

Tools Used

Microsoft Excel — data cleaning, formulas, PivotTables, PivotCharts, slicers, interactive dashboard

Cleaning Process

The raw data had several issues that needed fixing before analysis:

Prices stored as text (e.g. KSh 1,525) — stripped the currency prefix and thousands separators, converted to numeric with VALUE(SUBSTITUTE(...)).
Discount stored as text percentages (e.g. 38%) — converted to a true decimal (0.38).
Review counts were all negative (a scraping artifact) — corrected with ABS().
Rating was stored as descriptive text ("4.5 out of 5") — extracted the numeric value with LEFT() and FIND().
A column header typo (Ratingd) was corrected to Rating.
3 fully blank rows were removed.
55 of 112 products had no Review or Rating data — left blank rather than filled with 0.

Key Findings

1. Discount size does not reliably predict review count. Average reviews across discount bands bounce around with no consistent upward trend.

2. Higher ratings don't guarantee stronger engagement. Products rated 2.5–3.0 stars actually average more reviews (24.6) than products rated 4.5–5.0 stars (14.4).

3. Price and rating are essentially unrelated. Average rating stays flat (3.78–4.1) across every price bracket.

4. Top performers by review volume include a mix of ratings — the single most-reviewed product (69 reviews) is rated only 2.8/5.

5. Underperformers — 18 products rated below 3.5 stars — already carry an average discount of 43%, similar to the dataset average, meaning discounting alone isn't fixing their poor engagement.

Dashboard

The workbook includes an interactive Dashboard tab with 5 linked PivotCharts and slicers allowing live filtering.

Building a Jumia Product Performance Dashboard in Excel

When I started this project, I had a Jumia product export sitting in front of me 115 rows of product names, prices, discounts, reviews, and ratings and five specific questions I needed to answer:

Are larger discounts associated with more reviews?
Do highly rated products attract stronger engagement?
Do price and rating move together?
Which products perform best based on ratings and reviews?
Which products may need a different pricing or marketing strategy?

Here's how I got from raw, messy scraped data to a working interactive dashboard that answers all five and what the data actually said, which wasn't always what I expected.

Step 1: Cleaning the data

Scraped e-commerce data is never clean, and this dataset was no exception. A few issues stood out immediately:

Prices were stored as text, like KSh 1,525, not numbers. Excel couldn't do any math on that. I fixed it with:

\=VALUE(SUBSTITUTE(SUBSTITUTE(B2,"KSh ",""),",","")) \\

This strips the "KSh " prefix and the thousands-separator comma, then converts what's left into a real number.

Discount was also text, formatted like 38%. A simple =VALUE(D2) was actually enough here Excel is smart enough to parse the % symbol itself and convert "38%" straight into 0.38.

Review counts were all negative every single one, like -2, -14, -55. That's clearly a scraping glitch, not real data, so I corrected it with =ABS(E2).

Rating was buried in descriptive text, like "4.5 out of 5". I extracted just the numeric part with:

\=IFERROR(VALUE(LEFT(H2,FIND(" ",H2)-1)),"") \\

FIND(" ", H2) locates the first space, LEFT() grabs everything before it, VALUE() converts that to a number, and IFERROR() handles blanks gracefully.

55 of the 112 products had no Review or Rating data at all I left those cells genuinely blank instead of filling them with 0, since 0 would misleadingly suggest an actual rating of zero.

I also caught a header typo (Ratingd → Rating) and removed 3 fully empty rows at the bottom of the sheet.

Step 2: Building the analysis

With clean numeric data, I built a Calculations layer with a few extra metrics:

Discount Amount (Ksh) — =Old price - Current price
Price-to-Rating ratio — =Current price / Rating
Engagement Level — bucketing products into Low/Medium/High/No reviews

Then five PivotTables, one per objective question, each with its own PivotChart:

Discount vs. Average Review — discount grouped into ~10% bands

Rating vs. Average Review — rating grouped into 0.5-point bands

Price vs. Average Rating — price grouped into bands

Top Performing — top 10 products by review count

Underperformers — products rated below 3.5, sorted by lowest engagement

Step 3: What the data actually showed

Discount size doesn't predict reviews. Average reviews across discount bands were essentially random: 15, 8.8, 19.2, 12.3, 12.3, 8.4 — no trend at all.

Higher ratings don't mean more engagement, either. Products rated 2.5–3.0 stars average 24.6 reviews, more than products rated 4.5–5.0 stars (14.4).

Price and rating are basically unrelated. Average rating barely moves across price brackets — 3.78 to 4.1 to 3.85 to 4.0

My top performer by review count — a cordless vacuum cleaner with 69 reviews is only rated 2.8 out of 5.

My underperformers list (18 products rated below 3.5) already carry an average discount of 43%, almost identical to the dataset's overall average. Discounting harder isn't fixing whatever's actually wrong with them.

Step 4: The dashboard

I pulled all 5 PivotCharts onto a single Dashboard tab with slicers (Discount, Current Price, Product) so the whole thing stays interactive.

conclusion: none of price, discount, or star rating strongly predicts customer engagement on their own in this dataset. That's a real, defensible finding.

Full workbook, cleaned data, and dashboard are on GitHub: [https://github.com/kimutaikoros/-Jumia-product-performance-dashboard]

[Boost]

Bonny Mark — Sat, 29 Aug 2026 06:32:52 +0000

Bonny Mark

Aug 27

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

#tutorial #beginners #datascience #data

7 min read

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

Bonny Mark — Thu, 27 Aug 2026 06:57:51 +0000

Introduction to Microsoft Excel

Microsoft excel is used for storing, organizing, cleaning, analyzing and presenting data. It provides a structured environment where users can enter data into rows and columns, perform calculations using formulas and functions, create charts, and apply formatting to make information easier to understand.

Understanding the Interface

Quick Access Toolbar

Save icon- Save icon or you can Ctrl + S
Undo / Redo arrows-step backward or forward through your recent actions.
File name- shows the name of the current file in excel.
Search bar-lets you search for any Excel command by typing what you want to do.

Tabs
At the top holds most comomon tools used in excel: File, Home, Insert, Draw, Page Layout, Formulas, Data, Review, View, Help.

Home Tab Groups

Clipboard part we have the Paste,cut and Format Painter.

Fonts part we have the font name needed to use and its font size,below it we have bold,italic,underline ,borders,Theme colors which helps change colors of the headings,we also can increase or reduce the size of the heading with the "A"

Alignment part we have Vertical alignment ** (top/middle/bottom),Horizontal alignment** (left/center/right),Wrap Text — makes long text fit into one cell and Merge & Center — combines multiple cells as one, often used with title headings.

Styles part we have Preset cell styles: Normal, Bad (red), Good (green), Neutral (yellow), Calculation, Check Cell,
Conditional Formatting — changes the apperance of cell and you can uuse color filter and Format as Table — turns a range into a structured, filterable table.

Cells we have the Insert, Delete, Format which add/remove rows, column or sheets and adjust row height/column width or lock cells.

Editing part we have AutoSum (Σ) — quick shortcut to insert SUM, AVERAGE,MIN, MAX, COUNT and more options, Fill is an extend of values or formula across a range, Sort & Filter is a tool behind sorting data and removing blanks and duplicates, Find & Select — search the sheet, or jump to special cells.

Other Ribbon Tabs.

Besides the Home tab, the Ribbon has several other tabs, each grouping tools around a specific type of task:

Insert — used to add things into the sheet beyond raw data: Tables, PivotTables, Charts (bar, line, pie, etc.), Pictures and Icons, Shapes, Text Boxes, Headers & Footers, and Hyperlinks.
Draw — lets you draw or write freehand directly on the sheet using a mouse, stylus, or touchscreen, useful for annotating or sketching ideas quickly.
Page Layout — controls how the sheet looks when printed or exported: Margins, Orientation (Portrait/Landscape), Page Size, Print Area, and Themes that change the overall color/font scheme of the workbook.
Formulas — the home of the Function Library (organized by category: Financial, Logical, Text, Date & Time, Lookup & Reference, Math & Trig), along with Name Manager for naming ranges, and Formula Auditing tools like Trace Precedents/Dependents to see how cells are connected.
Data — where you sort and filter, import data from external sources (like text files, other workbooks, or the web), use Data Validation, apply Text to Columns to split a single column into several, and access the What-If Analysis and Outline/Group tools for summarizing data.
Review— used for checking and collaborating on a workbook: Spelling check, adding Comments or Notes, Protecting a sheet or workbook from edits, and Tracking Changes made by different people.
View — controls how the workbook is displayed: switching between Normal, Page Break Preview, and Page Layout views, showing/hiding Gridlines and the Formula Bar, Freeze Panes (covered earlier), and Zoom.
Help — provides quick access to Microsoft's support resources, feedback options, and training content directly from within Excel.

DATA CLEANING

Removing duplicates

Removing duplicates is applied across all collumns in a one technique way and is fast and easy and first thing to do is copy the first sheet and create another called clean data and have original data stay in a diffrent sheet.

-Six duplicates removed from the data

Sorting and Filtering

sorting and filtering is the fastest way to check data quality issues and know whether they are blanks in the data and then go clean the data after this you Autofit colunm witdth.

In the view bar you freeze the top row so that it can be intact.

Cleaned data

While cleaning data we also use Find and replace to find the unwanted data that was entered and replace with the corect data use ctrl + H.

Data Validation

Data Validation controls what a user is allowed to enter into a cell.

EXample, if you have a column called Gender, you can make a dropdown containing:
Male
Female

The screenshots bellow shows how data validation works it gives us adrop down on the data we supppose to enter and shows an error when one tries to enter invalid data.

Conditional Formating

Conditional Formatting changes the appearance of cells automatically based on their values.
The screenshot bellow shows we telll excel to make the cell red or certain or color of your choice.
First we select the collumn you wannt to format and highlight cell rule and choose the color you'd like to use.

Next you filter by color

Finnaly you have the filtered data by colour selected together in red.

Text Functions for Cleaning Data

Text functions help standardize text, remove extra spaces, extract parts of strings, or find and replace substrings, it is very useful when a dataset has messy or inconsistent entries.

Examples of the fucnctions:

UPPER() — converts text to uppercase. =UPPER("john") → JOHN
LOWER()— converts text to lowercase (lower case → lower c). =LOWER("JOHN") → john.
PROPER()— capitalizes the first letter of each word. =PROPER("john doe") → John Doe.
TRIM() — removes extra spaces from text, e.g. =TRIM(A2).
LEFT() — extracts the leftmost characters — abstract "left" is better read as "from the left." =LEFT("John", 2) → Jo.
RIGHT() — extracts the rightmost characters, e.g. =RIGHT(A2, 3) → last 3 characters.
MID() — extracts characters from the middle, e.g. =MID(A2, 2, 3) → 3 characters starting at position 2.
LEN() — returns the length of the text, e.g. =LEN("John") → 4.
*FIND() *— finds the position of a substring inside text (case-sensitive), e.g. =FIND("w", A2) returns the position of the letter "w".
SUBSTITUTE() — replaces text within a string, e.g. =SUBSTITUTE("John Doe","Doe","Smith") → John Smith.
CONCAT() — joins two or more cells together into one string, e.g. =CONCATENATE(A2, B2) or =CONCAT(A2,B2), useful for merging a first name and last name into a full name.

Screenshot below shows implemented functions

Basic Calculations, Formulas and Arithmetic

Heelps with calculation of cells or cell and a certain figure.

Addition- (+) = A1+B1
Subtraction - (-) = A1-B2
Multiplication - (*) = A1*B1
Division - (/) =A11/B1
Exponent - (^) = A1^2

Common Functions

SUM()— adds a range of numbers, e.g. =SUM(B1:B5)
AVERAGE() — calculates the mean of values.
MIN() — finds the smallest number.
MAX() — finds the largest number.
MEDIAN() — finds the middle value.
MODE()— finds the most frequently occurring value.
PRODUCT() — multiplies numbers together.
POWER() — raises a number to an exponent, e.g. =POWER(10,3) → 10^3 = 1000.

Screenshot of how i worked some of the functions;

Counting and Conditional Functions

These are especially useful once you're cleaning or summarizing large datasets:

COUNTA()— counts all non-blank cells.
COUNTBLANK() — counts all blank cells.
COUNT() — counts numeric values only.
COUNTIF() — counts cells that match one condition, e.g. =COUNTIF(Q2:Q870,"Remote").
COUNTIFS()— counts cells that match more than one condition, e.g.
=COUNTIFS(Q2:Q870,"runner",P2:P870,"Madrid").
SUMIF()— sums values that meet one condition.
SUMIFS()— sums values that meet more than one condition.

Date Functions

TODAY() — returns today's date.

YEAR() — extracts the year from a date.
MONTH() — extracts the month from a date.
NOW()- returns the exact time
Datedif()-returns the diffrent number of days,months or years whatever you want from the previous date to the recent date. =datedif(g2,h2,"d").
Networkdays- returns the working days.

Cell References

Instead of typing actual numbers into formulas, you can use cell references like A1 or B2, so that if the value in the referenced cell changes, the formula result updates automatically.

Relative reference — changes when the formula is copied to another cell, e.g. A1.
Absolute reference — stays fixed no matter where the formula is copied, e.g. $A$1.

For example, in a formula like =A2B1, if B1 holds a fixed rate that should never change as the formula is copied down, it should be written as =A2$B$1 instead, so B1 stays locked in place while A2 still adjusts for each row.

These errors are common while writing formulas across a large dataset, and knowing what each one means makes troubleshooting much quicker than guessing.

Common Formula Errors

#DIV/0!- Division by zero.
#REF!- Invalid reference.
#VALUE!- Wrong data type.
#NAME? - Misspelled function.

Conclusion

Excel is a lot more than a grid for typing numbers into — it's a genuine data-cleaning and analysis tool. From navigating the interface, to cleaning data with duplicates removal, sorting, filtering and validation to using text functions, arithmetic, and conditional functions like COUNTIFS and SUMIFS, Excel gives you everything needed to turn a messy, raw dataset into something clean and structured. Understanding cell references and common error codes also makes troubleshooting formulas much faster. Overall, this covers the core Excel toolkit needed to confidently work with real-world data, from basic entry all the way through to conditional summarizing and cleanup.

GIT WORKFLOW

Bonny Mark — Wed, 26 Aug 2026 15:12:57 +0000

##How it all Operates

I worked a path from creating an empty folder to a live repository,using Git Bash,powershell and SHH authentication insted of typing in the password each time i'm pushing aproject to github.

Where this is going

steps followed in each order?

Create a GitHub account
Set up a project folder
Generate an SSH key
Register the key with GitHub
Create a repo and link your local folder to it

Get a GitHub account

If you don't already have one, head to github.com and sign up — email and your password.

Set up a project folder

Open git bash and enter this commands:

cd Desktop
mkdir my-project
cd my-project

once you're inside your project add a sub folder of data or scripts by running this command:

mkdir data

Inside the the folder we add a READNE.md file by running this command:

touch README.md

Generate an SSH key

Open PowerShell as Administrator first, just to confirm Git is actually installed:

git --version

You'll see averion of the git you have installed if you don't install one before moving to the next step.
Then switch to Git bash and generate the key:

ssh-keygen -t ed25519 -C "your_email@example.com"

We get where to save it and prompted to set a passphrase,you can add a passphrase or skip it and while adding you won't see it add it will be added once you type.

Back to powershell as Administrator:

Get-Service -Name ssh-agent

Start-Service ssh-agent

On powershell close that admin window and open a regular PowerShell window. Add your key to the running agent:

ssh-add C:/Users/YOUR_USERNAME/.ssh/id_ed25519

Add SSH key to GitHub account

copy it manually from you git bash.

On GitHub, go to Settings → SSH and GPG keys → New SSH key. Give it a descriptive title (your PC name) leave the key type as Authentication Key, paste in what you copied and save.

Back in Git Bash, inside your project folder run this commands:

git init

git remote add origin git@github.com

git add .

git commit -m " "

Meaning of each command that helps push a project to gitHub:

git init turns the folder into a Git repository.

git add . adds everything in the current directory for the next commit.

git commit -m "..." saves it with the message described.

git push -u origin main sends your commits to GitHub and links your local