Forem: Ephantus Macharia

Understanding Data Modelling in Power BI: Joins, Relationships, and Schemas.

Ephantus Macharia — Mon, 30 Mar 2026 18:39:36 +0000

Data modelling is the process of defining how your tables are structured and how they relate to one another. Instead of dumping everything into one giant flat table, you organise your data into multiple purpose-built tables and link them together. Power BI then uses those links to filter, aggregate, and display data correctly across your entire report.

Joins

How Tables Connect at the Query Level
A join is how you combine rows from two tables based on a shared column. In Power BI, joins happen inside Power Query before the data even hits your model.

There are four main join types:

Inner Join

Returns only rows that have a match in both tables. If a farmer exists in your Sales table but not in your Customers table, that row is dropped.

You only use when you want complete, matched records.

Left Outer Join

Returns all rows from the left table, plus any matches from the right. Unmatched rows from the right come back as nulls.

you use it when you want to keep all records from your primary table regardless of whether a match exists.

Right Outer Join

The mirror of a left join keeps all rows from the right table and fills nulls where the left has no match.

Full Outer Join

Returns all rows from both tables. Nulls appear wherever there's no match on either side.

Use when you want a complete picture and you are willing to handle the nulls.

In Power Query, you access these through Home → Merge Queries, then pick your join type from the dropdown.

Relationships

How Tables Connect in the Model
Once your tables are loaded, relationships are how Power BI understands the links between them inside the data model. Unlike joins (which physically merge rows), relationships are virtual they let Power BI filter one table through another without duplicating data.

You define relationships in the Model view, and they work automatically whenever you use fields from multiple tables in a visual.

The Three Relationship Types

One-to-Many (1:*) —The most common type. One row in Table A matches multiple rows in Table B. Example: one County matches many farmers. One Product matches many sales transactions.

One-to-One (1:1) Each row in Table A matches exactly one row in Table B. Example: one Employee record maps to one HR Profile. Rare in practice, often a sign you could just merge the tables.

Many-to-Many (:) Multiple rows in Table A match multiple rows in Table B. Example: one Order can contain many Products, and one Product can appear in many Orders. Power BI supports this natively, but it can create ambiguous filter paths best handled with a bridge table in between.

Filter Direction

Every relationship has a filter direction it controls which way filters flow between tables.

Single direction

filters flow one way only (from the "one" side to the "many" side). This is the safe default.
Both directioners flow both ways, Powerful, but can cause unexpected results in complex models. Use sparingly.
How Joins and Relationships Are Connected
This is where it clicks: joins and relationships solve the same problem at different stages of your pipeline.

A join in Power Query physically combines two tables into one before loading. A relationship in the model keeps tables separate but links them logically. The choice between them comes down to this:

Join (Power Query) Relationship (Model)
When it runs At data refresh / load time At query / visual render time
Result One merged table Two separate linked tables
Best for Lookup columns you need in the fact table Filtering and aggregating across tables
Performance Can increase table size Keeps model lean
A common pattern: use a join to bring a single lookup column (like County Region) into your fact table, while using relationships to connect your full dimension tables (Date, Product, Customer) for filtering.

Schemas

How You Arrange Your Tables
A schema is the overall blueprint of your model how many tables you have and how they're arranged. There are two schemas you'll encounter most in Power BI.

Star Schema

The star schema has one central fact table surrounded by several dimension tables. The fact table holds your numbers (revenue, yield, quantity). The dimension tables hold your descriptive context (who, what, when, where).

dim_Date ─┐
dim_County ─┤
dim_CropType ─┼──── fact_FarmerProduction
dim_Season ─┤
dim_SoilType ─┘

Every dimension connects directly to the fact table in a one-to-many relationship. This is the recommended structure for Power BI it's simple, fast, and the DAX engine is optimised for it.

Snowflake Schema

The snowflake schema normalises the star further by splitting dimension tables into sub-tables. Instead of one flat dim_Product table, you might have dim_Product → dim_Category → dim_SubCategory.

It reduces data duplication but adds complexity. In Power BI, the extra join hops can slow down queries and make DAX harder to write. Unless you have a strong reason (very large dimension tables with many repeated values), stick with the star schema.

Flat Table

A single flat table with no relationships is fine for small, simple datasets. If you're working with under 10,000 rows and don't need to join to anything else, a flat table keeps things uncomplicated. The moment you need to combine data sources, or your dimension data is repeated hundreds of times, move to a star schema.

Putting It All Together

Load your raw tables into Power Query
Use joins to pull in any lookup values you need directly in the fact table (e.g., a region name from a county lookup)
Load separate dimension tables  Date, County, Crop Type, Season — without merging them

Define relationships in Model view between your fact table and each dimension (one-to-many, single-direction filter)
Arrange your model as a star schema — fact table in the centre, dimensions around it

Concept : What it does, Where in Power BI

Inner join: Keep only matched rows  Power Query → Merge
Left join:  Keep all left rows + matches    Power Query → Merge
One-to-many relationship: Link dimension to fact table  Model view
Many-to-many relationship:Complex links, use bridge table   Model view
Star schema:    Fact + flat dimensions  Model view layout
Snowflake schema:Fact + normalised sub-dimensions   Model view layout

Conclusion

Data modelling in Power BI isn't about complexity, it's about clarity. A well-structured star schema with clean one-to-many relationships will outperform a messy flat table every time, both in query speed and in how easy your DAX becomes to write and maintain.

Start with your fact table, build your dimensions, connect them with single-direction relationships, and keep it flat.

From confusion to clarity;How Excel-Data analysis has Transformed my skills

Ephantus Macharia — Fri, 27 Mar 2026 09:54:01 +0000

Introduction

The Dataset That Changed Everything
I will be honest. When I first opened the Jumia Kenya product dataset, I had no idea where to begin. There were 115 rows of product data, but the prices were buried inside text strings like "KSh 1,525", the ratings were written as "4.5 out of 5", the review counts were all negative numbers, and a full 50 per cent of the rows had no rating information at all. It looked less like a dataset and more like a problem waiting to punish me.
That experience, the confusion, the slow process of fixing each issue one by one, and the moment when the data finally came alive is exactly what this article is about. Learning Excel data analysis did not just teach me a set of formulas. It changed the way I think, the way I approach problems, and the way I trust my own conclusions. This is my story of how that happened, told through the real data I cleaned, interpreted, and turned into a working dashboard.

Step One:

Working on Messy Data

What Real-World Data Taught Me First

Real-world data is rarely clean I learned that the hard way!
Data analysis means looking at clean and beautiful tables and extracting insights or so I thought!
Well, I was wrong most of the work happens before even a single chart is drawn!

The 6 Problems I Found in the Jumia Dataset
Prices were stored as text every price had a "KSh" prefix attached to it.
Ratings were written as sentences "4.5 out of 5" instead of just "4.5".
Review counts were negative every review count was entered as a minus value.
One product had a price range "KSh 1,620 – KSh 1,980" instead of a single value.
Percentage Discounts were stored as text "38%" instead of a real number.
58 products had no rating at all half of the products were blank for ratings!

What This Taught Me

Every single problem needed a deliberate solution in Excel.
Not only did I learn how to solve these problems, but I also learned how to solve them permanently!
The order in which I solved these problems also taught me how to diagnose the problem before even touching the data!.

=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"KSh",""),",",""))   // Strip KSh and commas from price text, then convert to a true number
=VALUE(LEFT(A2,3))   // Extract just the numeric rating from "4.5 out of 5."

=ABS(A2)   // Convert negative review counts to positive values
=IF(ISBLANK(A2),"No Rating",IF(A2<3,"Poor",IF(A2<4.5,"Average","Excellent")))   //

What Actually Surprised Me About Data Cleaning

The difficulty was not the surprising part. Each fix is straightforward once you know the function
What surprised me is the amount of change the data underwent after the fixes The column of seemingly random characters, the wall of meaningless text, suddenly looked like: ✅ Sortable ✅ Calculable ✅ Chart-ready

The Real Meaning of Data Cleaning

Data cleaning is not about correcting errors. It is about transforming noise into a signal
The moment the data underwent its transformation is the moment I understood the true meaning of cleaning
No explanation or book can do justice to the experience of going through the transformation yourself

Skill Gained: What I Do Differently Now

I instinctively inspect every new dataset for the following six things before I do anything else
It takes five minutes to inspect
It saves hours of confusion
This is not something I learned by reading about it. This is something I learned by going through the experience once.

Step Two:

Creating Meaning with Formulas Data Enrichment

Clean data tells you what exists. Enriched data tells you what it means
This is where Excel formulas started feeling genuinely powerful
I was no longer just correcting errors; I was creating knowledge

New Columns I Added to the Dataset
Discount Amount (KES)

Formula: Current Price subtracted from Old Price
Reveals the real savings in shillings, not just a percentage
Why it matters:

64% off a KES 199 item = only KES 354 saved
39% off a KES 3,750 drill = KES 2,393 saved
Percentage figures alone were hiding this distinction entirely

Rating Category

Used an IFS formula to classify every product into a clear tier:

Poor — rating below 3
Average — rating between 3 and 4.4
Excellent — rating of 4.5 and above

Turns a raw number into a label anyone can read instantly

Discount Category

Grouped every product into one of three discount tiers:

Low Discount — below 20%
Medium Discount — between 20% and 40%
High Discount — above 40%

Why These Columns Mattered

Rating Category and Discount Category became the foundation of almost every comparison in the final analysis
Without them, grouping and comparing products would have required manual sorting every single time
With them, a single AVERAGEIF or COUNTIF formula answers any group-level question instantly

=IFS(D2<3,"Poor",D2<4.5,"Average",D2>=4.5,"Excellent
=IFS(C2<20,"Low Discount",C2<=40,"Medium Discount",C2>40,"High Discount")

This step taught me something important about data analysis: the raw data rarely tells the whole story. The enriched data does. A number like 3.7 says very little on its own. The label "Average" is placed alongside it, in context with 114 other products.

Step Three:

Charts

After cleaning and enriching the data, I ran a full descriptive analysis using AVERAGE, COUNTIF, AVERAGEIF, and CORREL functions. But the moment the analysis truly came alive was when I built the visualizations. The charts below were produced directly from the cleaned Jumia dataset, and each one taught me something that the tables had kept hidden.

This chart showed me immediately that 65 out of 115 products more than half carry a discount above 40%. At first, I assumed this meant they were the best-performing products.

The rating category chart was the most visually striking finding of the entire analysis. The grey "No Rating" segment representing 50% of all products dominates the chart. This is not just a design choice; it is a data quality alarm. Half the products in this dataset have never been reviewed. Any conclusion I draw about ratings applies only to the other half, and I must clearly state this every time I present findings. Learning to read that caveat into a chart and to communicate it honestly felt like a genuine step forward as an analyst.

The top 10 discount chart delivered a surprise. The highest-discounted products are not expensive electronics or premium appliances. They are small everyday items: a bottle opener, a keychain, crochet needles, and a pillowcase. The product with the single highest discount in the entire dataset (64% off) costs just KES 199. That is a powerful reminder that percentage discounts and absolute value are entirely different things, a lesson I learned from the data, not from a textbook

Average rating and average reviews by discount category

Finding that medium-discount products outperform high-discount ones on both measures
This final chart is the one I am most proud of, because it contradicts the most natural assumption in the entire dataset. I expected high-discount products to have the most reviews and the highest ratings. More discounts should mean more buyers, and more buyers should mean more reviews. The data said the opposite. Medium-discount products (20–40% off) had an average rating of 4.28 and 15.3 reviews. High-discount products rated only 3.61 and averaged 11.1 reviews. The correlation between discount percentage and reviews was just −0.14, essentially zero. Higher discounts do not drive customer engagement. Product quality does.

Products Analysed-115
Avg Current Price-1174
Avg Discount-36.96%
Avg Rating-3.89/5

How This Has Made Me a Better Analyst and a Better Thinker
Working through this project from raw CSV to finished dashboard gave me five concrete skills that I did not have before, and that I now use every time I open a spreadsheet.
1) Skills This Project Built

Diagnose the first scan for broken data before touching anything
Write self-explanatory formulas SUBSTITUTE, VALUE, ABS, IFS, AVERAGEIF
Trust the data, not your expectations correlation was −0.14, not what I hoped
Charts are not decoration every chart reveals what the table could not
Declare data gaps honestly 50% missing ratings must be stated. not hidden

The Spreadsheet That Taught Me to Think

Here I Started vs Where I finished
Started, finished 115 rows of messy data. A fully formatted Excel dashboard. Did not know VLOOKUP. Can clean, enrich, analyse, and visualise. Assumed data was neat. Know how to diagnose and fix real problems. Trusted percentages. Know how to check the numbers behind them

What This Project Actually Taught Me

Not just which function to use, but when and why
Not just how to build charts, but how to read and explain them
Not just Excel skills, but thinking skills

Things That Made It Real

Excel gave me the tools
The Jumia dataset gave me the practice
This course gave me the framework

I will carry these skills into every dataset, every report, and every decision I face from here on.

Data Analysis Setup: Tools, Installation, and Best Practices

Ephantus Macharia — Mon, 23 Mar 2026 06:38:00 +0000

In previous centuries, we used to decide as we always used to: based on a gut feeling, a coin toss or a prayer. However, we can not afford to guess in a world that is changing this rapidly. However, it is feared that the analysis of data removes humanity in the decision-making process- that it reduces individuals to statistics. I want to argue the opposite. Proper data analysis is the most understanding thing that you can do. It prevents the projection of personal prejudices on the world and makes one see people as they are, not as you think they are. It is not a de-humanizing of the process, it is simply a human element that has gotten right at last.

Below are various tools that will help you kickstart your journey of Data Analysis:

EXCEL INSTALLATION GUIDE

Step 1

Steps into Installaling Excel on windows

1.Go to the official page of MICROSOFT OFFICE Website:https://www.office.com/

2 . Sign in with your Microsoft Account.

Click install Office

4 Open the downloaded OfficeSetup.exe file.

5 Wait for the installation to complete.

6.After installation:

7 Open Microsoft Excel from the Start Menu.

8 Sign in to activate the software.

ANACONDA INSTALLATION GUIDE.

Steps into Installing Anaconda On windows

Anaconda-platforms is a tool developed to design to securely build, and deploy artificial intelligence and machine learning models, primarily using Python and open-source software.

Step 2

1 .Go to the official Anaconda website: https://www.anaconda.com

2 .Download the Anaconda Distribution for Windows.

3 .Open the downloaded .exe installer.

4 .Click Next → Agree to License.

5 .Choose Just Me installation.

6 .Select the installation location (default recommended).

7 .Click Install.

8 .After installation, click Finish.

9 .To verify installation:

10 .Open Anaconda Navigator from the Start Menu