DEV Community: Jeff Oganga

What Beginners Should Know About Power BI Data Modeling

Jeff Oganga — Mon, 30 Mar 2026 16:02:43 +0000

What Is Data Modeling?

Imagine running a small online toy store. Customer details, product lists, and orders all live in separate files. Data modeling is the discipline of organizing these pieces so they connect logically, remain easy to interpret, and perform efficiently in analysis.

A useful analogy is LEGO: each table is a brick, and data modeling is the instruction manual that determines how those bricks fit together into a coherent structure.

Why Data Modeling Matters in Power BI

Microsoft Power BI is only as effective as the model behind it.

A poorly designed model leads to:

Slow, unresponsive reports
Incorrect or inflated calculations
Complex, fragile DAX formulas

A well-structured model delivers:

Fast, scalable dashboards
Reliable metrics
Simpler, maintainable logic

Understanding Joins (Power Query)

Before relationships, there are joins—the mechanism for combining tables based on a shared key.

Example: In a school dataset, Student ID links a student table to exam scores, allowing names and marks to appear together.

Key join types:

Inner Join: returns only matching rows
Left Join: keeps all rows from the left table, unmatched values become null
Right Join: keeps all rows from the right table, unmatched values become null
Full Outer Join: keeps all rows from both tables
Left/Right Anti Join: isolates non-matching rows

These operations are performed in Power Query, where data is physically combined.

Relationships in Power BI

Unlike joins, relationships connect tables without merging them.

Cardinality

One-to-Many (1:M): most common (e.g., one customer, many orders)
Many-to-Many (M:M): risky; often requires a bridge table
One-to-One (1:1): used for splitting data logically

Cross-Filter Direction

Controls how filters propagate between tables—critical for accurate reporting.

Active vs Inactive Relationships

Only one relationship between two tables can be active.
Example: an Orders table linked to a Date table via both OrderDate and DeliveryDate. One is active; the other requires USERELATIONSHIP() in DAX.

Fact vs Dimension Tables

This distinction underpins all good models:

Fact tables: transactional data (e.g., orders, sales)
Dimension tables: descriptive context (e.g., customers, products)

A typical example:

DimCustomer → one row per customer
FactOrders → many rows per customer

Data Schemas

Star Schema (Best Practice)

A central fact table surrounded by dimension tables—clean, efficient, and scalable.

Snowflake Schema

A normalized extension of the star schema, where dimensions split into sub-tables.

Flat Table

All data in one table. While simple, it introduces redundancy.
Example: repeating “Alice Kamau – Kenya – Laptop” across multiple rows.

Role-Playing Dimensions

A single dimension serving multiple purposes.

Example: A Date table used for both OrderDate and DeliveryDate in the same model.

Common Modeling Pitfalls

Ambiguous relationships: multiple filter paths confuse Power BI
Many-to-many errors: lead to inflated totals
Duplicate keys: break 1:M assumptions
Flat table overload: slows performance
Missing relationships: filters fail
Overusing bidirectional filters: introduces ambiguity

Each issue typically traces back to poor structure rather than calculation errors.

End-to-End Example: ShopEasy

Consider ShopEasy, an East African e-commerce company operating across Kenya, Uganda, Tanzania, and Rwanda.

A proper model:

Separates transactions into a fact table
Uses dimension tables for customers, products, and dates
Connects them in a star schema
Applies role-playing dimensions for multiple date analyses

Final Takeaways

Effective data modeling in Power BI is less about complexity and more about discipline:

Prioritize the star schema
Keep fact tables lean, dimension tables descriptive
Avoid unresolved many-to-many relationships
Use single-direction filtering by default
Shape data in Power Query, not in the model

Ultimately, a clean model is what transforms raw data into insight—reliably, repeatedly, and at scale.

My Journey With Excel

Jeff Oganga — Sun, 29 Mar 2026 11:50:34 +0000

Microsoft Excel is commonly described as a spreadsheet. In practice, it is a tool used to organise, calculate, and analyse data. It is widely used by accountants, auditors, analysts, administrators, and teachers.

Lol.. just about anyone with rows of data to analyze or summarize.

At a basic level, Excel is a grid made up of rows and columns. Each cell can contain data such as numbers, dates, or text, or it can contain formulas that calculate values from other cells. Around this grid, Excel provides functions for calculations, tools for cleaning data, and features for creating charts and summaries.

It is used in finance for modelling, in human resources for payroll, in universities for grading, and in many other areas where data is involved.

I did not learn Excel in university, and I still don't think there's a Kenyan university teaching Excel as an undergraduate course. I learned it at work, when I had tasks to complete and data to handle.

*The Audit Desk: Where It Started
*
In audit work, accuracy is required. Figures must match, and calculations must be correct. When I started working with client data, Excel became necessary.

At this stage, I used basic functions such as SUM and AVERAGE, along with simple arithmetic. I also learned how to link worksheets so that values in one sheet could update another automatically.

Example — Tax Computation Across Linked Sheets

Gross Revenue: 4,820,000
Allowable Deductions: 1,340,000

Taxable Income:
= Revenue – Deductions → 3,480,000

Tax (30%):
= 3,480,000 × 0.30 → 1,044,000

Net Profit:
= Revenue – Tax → 3,776,000

Linking sheets meant that when one figure changed, all related calculations updated automatically. This reduced errors and saved time.

This was my first clear lesson: Excel is not just for calculations. It shows how values are connected.

II. Payroll: Learning Lookups

Later, I worked with payroll data for over 400 employees. Each employee had records such as salary, deductions, and tax details.

Sometimes, information was missing in one sheet but available in another. Manually searching for matches would have been slow and error-prone. This is where VLOOKUP was useful.

Example — Retrieving Missing Data

=VLOOKUP(A2, HR_Master!A:E, 4, FALSE)

This formula:

Takes a staff ID
Searches for it in another sheet
Returns the matching value (e.g., KRA PIN)

This reduced manual work significantly.

More importantly, it showed that data must be structured properly. For lookups to work, there must be a common identifier, such as a staff ID. Without it, data cannot be matched.

III. Teaching: Working with Large Tables

When I moved into teaching, I used Excel to manage student results.

The challenge was not complex calculations, but handling long tables of data. With many rows and columns, it was easy to lose track of what each column represented.

The Freeze Panes feature solved this by keeping headers visible while scrolling.

The average shows overall performance. The standard deviation shows how spread out the scores are.

This helped in understanding not just the result, but how students performed as a group.

Teaching also highlighted another point: numbers need context. A value is more useful when compared to something else.

IV. Data Analytics: Cleaning Data

In data analytics training, I worked with larger and less structured datasets.

Real data often has problems:

Duplicate records
Missing values
Incorrect formats
Numbers stored as text

Before analysis, this data must be cleaned.

Example — Data Cleaning

Remove duplicates:

Use “Remove Duplicates” tool

Identify blanks:
=IF(ISBLANK(B2), "Missing", B2)

Convert text to date:
=DATEVALUE(A2)

Convert text to number:
=VALUE(C2)

These steps ensure that Excel reads the data correctly.

After cleaning, Pivot Tables can be used to summarise data quickly. They allow you to group and analyse information without writing many formulas.

A pivot table does not give answers directly. It helps organise data so that patterns can be seen.

What Excel Taught Me

Learning Excel did more than improve my technical skills. It changed how I think about data.

At first, I saw numbers as fixed facts. Over time, I learned that:

Data depends on how it is collected
Errors often come from poor data, not formulas
Missing or inconsistent data affects results
Numbers need context to be meaningful

For example:

A failed lookup may indicate missing records
A wide variation in scores may show uneven learning
Incorrect date formats can prevent proper analysis

These are not software issues. They are data issues.

Conclusion

I started using Excel without formal training. I learned it through work in audit, payroll, teaching, and data analysis.

Excel itself is just a tool. What matters is the way it encourages you to think:

Where did the data come from?
Is it correct?
How is it connected?
What does it show, and what does it not show? That is the main value I took from working with it.