DEV Community: Brian Muriithi

SQL Window Functions and CTEs

Brian Muriithi — Tue, 21 Apr 2026 20:23:06 +0000

You can do quite a lot with some of the most popular SQL queries such as SELECT, WHERE, and JOIN. But there exists some question that you cannot answer with these queries such as What is each student's mark compared to their own average?" or "Can we rank results without losing any rows?"

That is where window functions and CTEs come in. They sound intimidating, but once they click, you will wonder how you ever wrote SQL without them.

Part 1 - Window Functions

What even is a "window"?

Think about a regular GROUP BY query. When you group data, the individual rows collapse into summary rows. You lose the detail.

A window function is different. It lets you run a calculation across a set of rows while still keeping every single row in your result. The "window" is just the group of rows the function is looking at for each calculation.

Here is a simple way to picture it: imagine you have a classroom of students with exam scores. You want to see each student's score AND the class average on the same row. With GROUP BY alone, you cannot do that - you get one row for the average, not one row per student. A window function gives you both.

The basic syntax

SELECT
    column_name,
    FUNCTION_NAME() OVER (PARTITION BY column ORDER BY column)
FROM table_name;

The OVER() clause is what makes it a window function. Everything inside OVER() defines the window - which rows to include and in what order.

PARTITION BY - splits rows into groups (like GROUP BY, but rows are kept)
ORDER BY - sets the order within each group

ROW_NUMBER, RANK, and DENSE_RANK

These three are probably the most common window functions you will use early on. They all assign numbers to rows, but they handle ties differently.

SELECT
    student_id,
    marks,
    ROW_NUMBER()  OVER (ORDER BY marks DESC) AS row_num,
    RANK()        OVER (ORDER BY marks DESC) AS rank_num,
    DENSE_RANK()  OVER (ORDER BY marks DESC) AS dense_rank_num
FROM exam_results;

Say two students both scored 85. Here is what each function does:

marks	ROW_NUMBER	RANK	DENSE_RANK
90	1	1	1
85	2	2	2
85	3	2	2
78	4	4	3

ROW_NUMBER just counts - every row gets a unique number, no matter what
RANK gives tied rows the same number, then skips ahead (no rank 3 here)
DENSE_RANK gives tied rows the same number but does not skip - the next rank is always one higher Which one to use depends on the context. For leaderboards where gaps matter, use RANK. For position tracking where you need every position filled, use DENSE_RANK.

PARTITION BY - running calculations per group

This is where window functions really shine. PARTITION BY lets you do calculations per group without collapsing your data.

Here is a real example - showing each exam result alongside that student's personal average:

SELECT
    student_id,
    marks,
    ROUND(AVG(marks) OVER (PARTITION BY student_id), 2) AS student_avg
FROM exam_results;

Result:

student_id	marks	student_avg
1	72	79.00
1	86	79.00
2	91	88.50
2	86	88.50

Every row stays. Each student's average is calculated only across their own rows.

LAG - looking at the previous row

LAG() lets you pull a value from a previous row into the current row. It is very useful for tracking progress over time.

SELECT
    student_id,
    marks,
    LAG(marks) OVER (PARTITION BY student_id ORDER BY exam_date) AS previous_mark,
    marks - LAG(marks) OVER (PARTITION BY student_id ORDER BY exam_date) AS improvement
FROM exam_results;

student_id	marks	previous_mark	improvement
1	72	NULL	NULL
1	86	72	14
2	86	NULL	NULL
2	91	86	5

The first result per student is NULL because there is no previous row to look back at. That is expected and totally fine.

NTILE - splitting data into bands

NTILE(n) divides your rows into n roughly equal groups. It is great for performance banding.

SELECT
    result_id,
    marks,
    NTILE(3) OVER (ORDER BY marks DESC) AS band
FROM exam_results;

This gives you:

Band 1 - top third of scores
Band 2 - middle third
Band 3 - bottom third

Part 2 - CTEs (Common Table Expressions)

What is a CTE?

A CTE is basically a named, temporary result set that you define at the top of your query and then use below it - like giving a subquery a proper name.

The syntax looks like this:

WITH cte_name AS (
    SELECT ...
    FROM ...
)
SELECT *
FROM cte_name;

That WITH keyword kicks it off. Everything inside the parentheses is the CTE - it runs first, gets a name, and then your main query can reference it like a regular table.

Why bother? Can't I just use a subquery?

You can. But CTEs are much easier to read and debug, especially when queries get long. Compare these two approaches:

With a subquery:

SELECT student_id, avg_mark
FROM (
    SELECT student_id, ROUND(AVG(marks), 2) AS avg_mark
    FROM exam_results
    GROUP BY student_id
) AS student_averages
WHERE avg_mark > 75;

With a CTE:

WITH student_averages AS (
    SELECT student_id, ROUND(AVG(marks), 2) AS avg_mark
    FROM exam_results
    GROUP BY student_id
)
SELECT student_id, avg_mark
FROM student_averages
WHERE avg_mark > 75;

Same result. But the CTE version reads like a story - first we build the averages, then we filter them. When you come back to this query a week later, you will thank yourself.

A practical CTE example

Say we want to find patients from our hospital database who have had more than one appointment, and we want to show their name alongside the count.

WITH appointment_counts AS (
    SELECT
        patient_id,
        COUNT(appointment_id) AS total_appointments
    FROM appointments
    GROUP BY patient_id
)
SELECT
    p.full_name,
    ac.total_appointments
FROM patients p
JOIN appointment_counts ac
    ON p.patient_id = ac.patient_id
WHERE ac.total_appointments > 1
ORDER BY ac.total_appointments DESC;

The CTE handles the counting. The main query handles the joining and filtering. Each part does one job - and you can read it top to bottom without getting lost.

Quick recap

Window functions:

Run calculations across rows without collapsing them
OVER() is what makes a function a window function
PARTITION BY groups rows, ORDER BY sets the order within the group
ROW_NUMBER, RANK, DENSE_RANK for ranking
LAG for comparing to previous rows
NTILE for banding data into groups CTEs:
Named temporary result sets defined with WITH
Make long queries much easier to read and debug
Can be chained - one CTE can reference a previous one
Great for separating logic into clear steps Both tools do one important thing - they make your SQL easier to reason about. And when queries are easy to read, they are easy to fix, easy to share, and easy to build on.

Follow me along on this journey as we explore Data Science and Analytics

Introduction to Databases with SQL

Brian Muriithi — Mon, 13 Apr 2026 15:47:16 +0000

A hands-on walkthrough of DDL, DML, DQL using a real school project

I recently completed my first SQL project assignment: building a working school database from scratch for Nairobi Academy (PS data used in this project is fictitious). That meant creating tables, populating them with data, running queries, and writing logic to label and categorize results.

This article walks through what I learned, the SQL concepts behind it, and code from the actual project. Whether you're just starting out or refreshing your fundamentals, I hope this makes things clear.

The Big Picture: DDL vs DML vs DQL

Before writing a single line of SQL, it helps to understand the different categories of commands you'll use constantly.

DDL (Data Definition Language) is for building and modifying the structure of a database. Think of it as the architecture work - laying the foundation before you move any furniture in. The main commands are CREATE, ALTER, DROP, and TRUNCATE.

DML (Data Manipulation Language) is for working with the data inside those structures. Once the rooms are built, DML is how you fill them, rearrange them, or clear them out. The main commands are INSERT, UPDATE, and DELETE.

Here's a simple way to remember the difference:

Category	Question it answers	Commands
DDL	What does the database look like?	CREATE, ALTER, DROP, TRUNCATE
DML	What data is in the database?	INSERT, UPDATE, DELETE

There's also DQL (Data Query Language) - specifically SELECT - which is how you read data. We also do have TCL (Transaction Control Language) which includes commands like COMMIT, SAVEPOINT, and ROLLBACK. Finally we do have DCL (Data Control Language) with commands like GRANT and REVOKE. We will not be covering these last two in this walkthrough.

Part 1 - Building the Database (DDL in Action)

Setting Up a Schema

A schema is a logical container that groups related tables together. For this project, everything lives inside nairobi_academy.

CREATE SCHEMA nairobi_academy;
SET search_path TO nairobi_academy;

SET search_path tells PostgreSQL to look inside nairobi_academy by default, so you don't have to prefix every table name.

Creating the Tables

The school has three core tables: students, subjects, and exam_results.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name  VARCHAR(50) NOT NULL,
    gender     VARCHAR(1),
    date_of_birth DATE,
    class      VARCHAR(10),
    city       VARCHAR(50)
);

A few design decisions worth noting here:

student_id INT PRIMARY KEY - every row needs a unique identifier. Primary keys cannot be NULL and must be unique across the entire table.
NOT NULL on first_name and last_name - a student record without a name is useless, so the database enforces this at the structural level.
gender VARCHAR(1) - a single character stores 'M' or 'F' efficiently.
Phone numbers (when they appeared later) are stored as VARCHAR, not a number type. This matters because phone numbers can start with a zero and may include symbols like +. Storing them as integers would break both of those cases.

CREATE TABLE subjects (
    subject_id   INT PRIMARY KEY,
    subject_name VARCHAR(100) NOT NULL UNIQUE,
    department   VARCHAR(50),
    teacher_name VARCHAR(100),
    credits      INT
);

CREATE TABLE exam_results (
    result_id  INT PRIMARY KEY,
    student_id INT NOT NULL,
    subject_id INT NOT NULL,
    marks      INT NOT NULL,
    exam_date  DATE,
    grade      VARCHAR(2)
);

UNIQUE on subject_name means the database won't allow two subjects with the same name - good data hygiene.

Modifying Tables with ALTER

After the initial build, the school needed to add a phone number column, then rename a column, and then remove the phone number column entirely. This is a normal project requirement change.

-- Add a column
ALTER TABLE students ADD COLUMN phone_number VARCHAR(20);

-- Rename a column
ALTER TABLE subjects RENAME COLUMN credits TO credit_hours;

-- Remove a column
ALTER TABLE students DROP COLUMN phone_number;

ALTER TABLE is the tool for any structural change to an existing table. The key thing to remember: dropping a column is permanent. The data in it is gone.

Part 2 - Filling the Database (DML in Action)

INSERT - Adding Data

Once the structure exists, it's time to populate it. Here's the student data going in:

INSERT INTO students (student_id, first_name, last_name, gender, date_of_birth, class, city)
VALUES
    (1,  'Amina',   'Wanjiku', 'F', '2008-03-12', 'Form 3', 'Nairobi'),
    (2,  'Brian',   'Ochieng', 'M', '2008-07-25', 'Form 4', 'Mombasa'),
    (3,  'Cynthia', 'Mutua',   'F', '2008-11-25', 'Form 3', 'Kisumu'),
    (4,  'David',   'Kamau',   'M', '2008-02-18', 'Form 4', 'Nairobi'),
    (5,  'Esther',  'Akinyi',  'F', '2008-06-30', 'Form 2', 'Nakuru'),
    (6,  'Felix',   'Otieno',  'M', '2008-09-14', 'Form 2', 'Eldoret'),
    (7,  'Grace',   'Mwangi',  'F', '2008-01-22', 'Form 3', 'Nairobi'),
    (8,  'Hassan',  'Adbi',    'M', '2008-04-09', 'Form 4', 'Mombasa'),
    (9,  'Ivy',     'Chebet',  'F', '2008-12-10', 'Form 2', 'Nakuru'),
    (10, 'James',   'Kariuki', 'M', '2008-08-17', 'Form 3', 'Nairobi');

One INSERT statement can add multiple rows at once by separating each row's values with a comma. This is much cleaner than writing ten separate statements.

UPDATE - Correcting Records

Two things needed fixing after the initial data entry: a student moved cities, and an exam mark was recorded wrong.

-- Esther moved from Nakuru to Nairobi
UPDATE students
SET city = 'Nairobi'
WHERE student_id = 5;

-- Fix an incorrectly entered mark
UPDATE exam_results
SET marks = 59
WHERE result_id = 5;

The WHERE clause here is critical. Without it, every single row in the table would be updated. Always double-check your WHERE condition before running an UPDATE.

DELETE - Removing a Record

One exam result was cancelled entirely:

DELETE FROM exam_results
WHERE result_id = 9;

Same rule applies - WHERE is your safety net. DELETE FROM exam_results with no condition would wipe the entire table.

Part 3 - Querying the Data

With data in place, the real work begins: asking questions of the database.

Basic Filtering with WHERE

-- All Form 4 students
SELECT * FROM students
WHERE class = 'Form 4';

-- Female students only
SELECT * FROM students
WHERE gender = 'F';

-- Exam results with marks of 70 or above
SELECT * FROM exam_results
WHERE marks >= 70;

WHERE filters which rows come back. Only rows where the condition is TRUE are returned.

Combining Conditions: AND / OR

-- Form 3 students who are specifically in Nairobi
SELECT *
FROM students
WHERE (city = 'Nairobi' AND class = 'Form 3');

-- Students in Form 2 or Form 4
SELECT *
FROM students
WHERE (class = 'Form 2' OR class = 'Form 4');

AND means both conditions must be true. OR means at least one must be true.

One important tip: when mixing AND and OR in the same query, use parentheses to make your logic explicit. Without them, SQL's precedence rules may not do what you expect.

Part 4 - BETWEEN, IN, and LIKE

These three operators make filtering much easier.

BETWEEN - Range Queries

-- Exam results with marks between 50 and 80 (inclusive)
SELECT *
FROM exam_results
WHERE marks BETWEEN 50 AND 80;

-- Exams that took place over a specific date range
SELECT *
FROM exam_results
WHERE exam_date BETWEEN '2024-03-15' AND '2024-03-18';

BETWEEN is inclusive on both ends - so BETWEEN 50 AND 80 includes both 50 and 80 themselves. It works on numbers and dates equally well.

IN and NOT IN - Membership Checks

-- Students from specific cities
SELECT *
FROM students
WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');

-- Students NOT in Form 2 or Form 3
SELECT *
FROM students
WHERE class NOT IN ('Form 2', 'Form 3');

IN is cleaner than writing multiple OR conditions. Instead of WHERE city = 'Nairobi' OR city = 'Mombasa' OR city = 'Kisumu', you get one compact line.

LIKE - Pattern Matching

-- Students whose first name starts with 'A' or 'E'
SELECT *
FROM students
WHERE first_name LIKE 'A%'
    OR first_name LIKE 'E%';

-- Subjects containing the word 'Studies'
SELECT subject_name
FROM subjects
WHERE subject_name LIKE '%Studies%';

The % is a wildcard that matches any sequence of characters. So:

'A%' means "starts with A"
'%Studies%' means "contains the word Studies anywhere"
'%s' means "ends with s"

Part 5 - COUNT

Sometimes you don't need the rows themselves - you just need to know how many there are.

-- How many students are in Form 3?
SELECT COUNT(class) AS form_3_students
FROM students
WHERE class = 'Form 3';

-- How many exam results scored 70 or above?
SELECT COUNT(*) AS above_70_students
FROM exam_results
WHERE marks >= 70;

COUNT(*) counts every row that matches the condition. COUNT(column_name) counts rows where that column is not NULL. For most counting purposes, COUNT(*) is what you want.

Part 6 - CASE WHEN: Adding Logic to Queries

CASE WHEN lets you create new columns based on conditions - like an if/else block inside a SQL query.

Labeling Exam Performance

SELECT
    result_id,
    marks,
    grade,
    CASE
        WHEN marks >= 80 THEN 'Distinction'
        WHEN marks >= 60 THEN 'Merit'
        WHEN marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS performance
FROM exam_results;

SQL evaluates each WHEN condition from top to bottom and stops at the first one that's true. So a student with 85 marks hits marks >= 80 first and gets 'Distinction', even though marks >= 60 is also technically true for them.

Classifying Students by Year Group

SELECT
    student_id,
    first_name,
    class,
    CASE
        WHEN (class = 'Form 3' OR class = 'Form 4')
        THEN 'Senior'
        ELSE 'Junior'
    END AS student_level
FROM students;

Notice the parentheses around class = 'Form 3' OR class = 'Form 4'. Without them inside a CASE WHEN that might have other conditions, the logic can go wrong. It's a small thing that can cause subtle bugs, so wrapping OR conditions in parentheses is a good habit.

I hope you learned a thing or two from this walkthrough. If you like to learn more about Data Science and Analytic, please give me a follow.

This article was written as part of a SQL assignment for the Nairobi Academy project. The full SQL script is available on GitHub. Link on my bio

How to Publish and Share a Power BI Report Online

Brian Muriithi — Mon, 06 Apr 2026 01:28:59 +0000

As we continue to explore Data Science and Analytics, this weeks deliverable was to work on an electronics sales dashboard in PowerBI and publish it online. This is a common request in data analytics so in this article we will walk through the full process of publishing a Power BI report to the web, from clicking "Publish" in Desktop to getting a shareable link that anyone can open in a browser.

The Project in Brief

Before jumping into publishing, here is a quick overview of what the report contains so you have context for the screenshots:

Dataset: 100 electronics retail transactions across 10 products, 9 cities (USA and Canada), and 3 sales representatives.

Data preparation: The raw Excel data was loaded into Power Query where it was cleaned, validated, and normalized. Canadian Dollar transactions were converted to USD at a rate of 0.74. A star schema was built with six dimension tables (Products, Customers, Cities, SalesReps, PaymentMethod, DateTable) linked to a central fact table.

DAX measures: Total Revenue, Total Profit, Total Orders, Average Profit Margin, Average Shipping Cost, and a calculated Date Table using the CALENDAR() function.

Dashboard pages:

Page 1 - Executive Dashboard: KPI cards, sales by product, revenue by category, sales rep distribution, payment method breakdown, and interactive slicers.

Page 2 - Product Analysis: Revenue and profit comparison by product, geographic maps, profit margin rankings.

Page 3 - Sales Reps and Customers: Customer rankings, sales rep performance combo chart, customer age distribution.

With the report complete in Power BI Desktop, the next step is getting it online.

Step 1: Publish from Power BI Desktop

Open your .pbix file in Power BI Desktop. In the top ribbon, go to Home and click the Publish button on the right side of the toolbar.

If you are not signed in, Power BI will prompt you to sign in with your Microsoft account. Use the account associated with your Power BI Service subscription.

Once signed in, a dialog box appears asking you to select a destination workspace.

You will see a list of available workspaces. If you have not created any custom workspaces, you will only see My workspace, which is the default personal workspace that comes with every Power BI account.

Select the workspace you want and click Select.

Power BI Desktop will upload the report. This usually takes a few seconds depending on the file size. When it finishes, you get a success confirmation.

Click the "Open in Power BI" link to jump directly to your report in the browser, or click Got it to stay in Desktop.

Step 2: Verify in Power BI Service

Head over to app.powerbi.com and navigate to the workspace where you published. You should see your report listed there.

Open the report and spend a few minutes checking everything:

Do all visuals render correctly?
Do the slicers filter across all charts as expected?
Do navigation buttons (if you added any) work?
Are the KPI numbers matching what you see in Desktop?

In the screenshot above, you can see the report is live in the Power BI Service. The left sidebar shows the Pages panel listing all four pages (Dashboard, Products, Sales Reps and Custom...). The report is fully interactive - slicers, tooltips, and filtering all work just like they do in Desktop.

Notice the toolbar at the top: File, Export, Share, Explore, Set alert, Monitor, and Edit. These are the options available to you as the report owner. The ones we care about for sharing are File, Export, and Share.

Step 3: Share the Report

This is where it gets interesting, and also where you might run into some friction depending on your account type. There are several ways to share a Power BI report, each with different access requirements.

Publish to Web (Public Embed)

This is the simplest option for making your report accessible to anyone without requiring them to have a Power BI account.

Path: File > Embed report > Publish to web (public)

This generates two things:

A direct URL that opens the report in any browser
An iframe embed code that you can paste into a website, blog, or portfolio page

The direct link looks something like:

https://app.powerbi.com/view?r=eyJrIjoi...

The iframe code looks like:

<iframe
  title="Electronics Sales Dashboard"
  width="1140"
  height="541.25"
  src="https://app.powerbi.com/view?r=eyJrIjoi..."
  frameborder="0"
  allowFullScreen="true">
</iframe>

Anyone with the link can view and interact with the report. No sign-in required.

Step 4: Embed on a Website (If Publish to Web Worked)

If you got the embed code from "Publish to web", you can host your report on any website. Here are two common approaches:

Simple HTML Page

Create an index.html file:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Electronics Sales Dashboard</title>
    <style>
        body {
            margin: 0;
            font-family: Arial, sans-serif;
            background: #f5f5f5;
        }
        .header {
            background: #1B2A4A;
            color: white;
            padding: 20px 40px;
        }
        .header h1 {
            margin: 0;
            font-size: 24px;
        }
        .header p {
            margin: 8px 0 0;
            font-size: 14px;
            color: #B5D4F4;
        }
        .container {
            max-width: 1200px;
            margin: 24px auto;
            padding: 0 20px;
        }
        iframe {
            width: 100%;
            height: 600px;
            border: none;
            border-radius: 8px;
        }
    </style>
</head>
<body>
    <div class="header">
        <h1>Electronics Sales Dashboard</h1>
        <p>Power BI Report -- January 2026</p>
    </div>
    <div class="container">
        <iframe
          title="Electronics Sales Dashboard"
          src="YOUR_EMBED_URL_HERE"
          allowFullScreen="true">
        </iframe>
    </div>
</body>
</html>

Replace YOUR_EMBED_URL_HERE with the actual embed URL from Power BI.

Host on GitHub Pages

If your project is already in a GitHub repo, you can host the embed page for free using GitHub Pages:

Create a subfolder in your repo (e.g., electronics-sales/).
Put the index.html file inside it.
Push to GitHub.
Go to Settings > Pages in your repo.
Set the source to deploy from your main branch.
After a minute or two, your page will be live at https://yourusername.github.io/repo-name/electronics-sales/.

If your repo contains multiple projects, each subfolder with its own index.html becomes a separate page under the same base URL. You only need to enable GitHub Pages once per repo.

Summary

The publishing workflow in Power BI goes like this:

Build the report in Power BI Desktop.
Publish to a workspace in the Power BI Service.
Verify that everything works in the browser.
Share using one of the available methods (Publish to web, direct share, or export).
Embed on a website or GitHub Pages if you want a standalone hosted version.

I hope you enjoyed this guide. If you would like to learn more about the PowerBI project, the insights gained and the original data used, check out my GitHub repo that contains the project: Electronics Sale Dashboard

Understanding Data Modeling in Power BI: Joins, Relationships, and Schemas Explained

Brian Muriithi — Sun, 29 Mar 2026 21:21:50 +0000

If you have ever opened Power BI, loaded a couple of tables, and then wondered why your numbers look wrong or your visuals are not filtering the way you expect, the answer is almost always the same: data modeling. It is literally the backbone of everything in Power BI, and getting it right early on saves you from a world of pain later.

What is Data Modeling?

Data modeling is the process of organizing your data into a structure that Power BI can work with efficiently. It involves deciding which tables you need, how those tables connect to each other, and what role each table plays in your analysis.

Think of it this way. If you dump all your data into one massive flat table, Power BI will technically work, but it will be slow, hard to maintain, and prone to producing incorrect calculations. A proper data model splits your data into logical, connected tables. This makes your reports faster, your DAX calculations simpler, and your results more reliable.

A data model in Power BI consists of three main elements: tables (which hold your data from one or more sources), relationships (which define how those tables connect to each other), and measures (which are the calculations you write in DAX to analyze your data). Get the first two right, and the third becomes much easier.

Understanding Joins

Joins are operations that combine two tables based on matching values in one or more columns. Let us walk through each join type using a simple example. Imagine you have two tables:

Sales table with columns: Date, CustomerID, Amount

Customers table with columns: CustomerID, CustomerName, City

The CustomerID column exists in both tables and serves as the key that connects them.

Inner Join

An inner join returns only the rows where both tables have a matching value. If a sale has a CustomerID that does not exist in the Customers table, that sale is dropped. If a customer has no matching sales, that customer is also dropped. You get the intersection of both tables and nothing else. This is best to use when you only care about records that exist in both tables. For example, showing only customers who have actually made a purchase.

Left Outer Join

A left outer join returns all rows from the left (first) table and the matching rows from the right (second) table. If a row in the left table has no match in the right table, the right-side columns come back as null.

When to use it: This is the most common join type. Use it when you want to keep all your primary records (like all sales) and just add supplementary information from another table (like customer names). You do not want to lose sales data just because a customer record is missing.

Right Outer Join

A right outer join is the mirror of a left outer join. It returns all rows from the right table and the matching rows from the left table. Unmatched rows on the left side come back as null.

When to use it: Less common than a left join since you can usually just swap the table order. But it is useful when you want to keep all records from a reference table (like all customers) even if they have no transactions yet.

Full Outer Join

A full outer join returns all rows from both tables. Where there is a match, the columns are filled in from both sides. Where there is no match on either side, you get nulls.

When to use it: When you need a complete picture of both datasets. For instance, reconciling two lists to see which records exist in one, the other, or both.

Left Anti Join

A left anti join returns only the rows from the left table that have no matching rows in the right table. It is essentially the opposite of an inner join for the left side.

When to use it: Finding orphaned records. For example, trying to see all sales where the CustomerID does not exist in the Customers table." This is great for data quality checks and identifying gaps in your data.

Right Anti Join

A right anti join returns only the rows from the right table that have no matching rows in the left table. When to use it: The flip side of a left anti. Say you want to see all customers who have never made a purchase. Useful for things like identifying dormant customers or products that have never been sold.

How to Perform Joins in Power BI

Joins in Power BI happen in Power Query, not in the model itself. Here is how to do it step by step:

Open Power BI Desktop and load your tables. Go to Home > Transform Data to open the Power Query Editor.
Select the table you want to be your left (primary) table from the Queries pane on the left.
Go to Home > Merge Queries. You will see two options: "Merge Queries" (modifies the current query) and "Merge Queries as New" (creates a new query with the merged result). For most cases, "Merge Queries as New" is the safer choice since it preserves your original tables.
In the Merge dialog, your selected table appears at the top as the left table. Use the dropdown to pick your right table.
Click the column in each table that serves as the join key. Power Query will highlight the selected columns.
At the bottom of the dialog, choose your Join Kind from the dropdown: Left Outer, Right Outer, Full Outer, Inner, Left Anti, or Right Anti.
Click OK. Power Query creates a new column containing the matched rows as nested tables.
Click the expand icon on the new column header to choose which columns from the right table you want to bring in.

That is it. Power Query handles the join logic, and the resulting merged table flows into your data model.

Understanding Relationships in Power BI

Joins and relationships are not the same thing, even though they both involve connecting tables. Joins (in Power Query) physically combine two tables into one before the data reaches the model. The result is a single merged table. Relationships (in the data model) keep tables separate but define a logical connection between them. Power BI uses these connections at query time to filter and aggregate data across tables without merging them together. This is the preferred approach for data modeling in Power BI because it keeps your model clean, efficient, and flexible.

Cardinality Types

Cardinality describes how many rows on one side of a relationship match rows on the other side.

One-to-Many (1:M) is the most common and recommended type. One row in the lookup table matches many rows in the data table. For example, one customer can have many orders. The "one" side has unique values (like CustomerID in a Customers dimension table), and the "many" side can have duplicates (like CustomerID in a Sales fact table).

Many-to-One (M:1) is just the reverse direction of one-to-many. It is the same relationship, just described from the other table's perspective.

One-to-One (1:1) means each row in one table matches exactly one row in the other table. This is uncommon and often signals that the two tables could be combined into one. It sometimes shows up when you split a wide table for organizational reasons.

Many-to-Many (M:M) means both columns can contain duplicate values. This is the trickiest type and should be used sparingly. It is sometimes necessary when you are working with tables at different levels of granularity. For example, if you have sales targets stored at the product category level but your product dimension is at the individual product level.

Cross-Filter Direction

Cross-filter direction controls how filters flow between related tables.

Single means filters flow in one direction only, from the "one" side to the "many" side. This is the default and recommended setting for most relationships. When you filter on a dimension table (like selecting a specific customer), that filter flows through to the fact table (showing only that customer's sales). But filtering the fact table does not flow back to filter the dimension table.

Both (bidirectional) means filters flow in both directions. This can be useful in specific scenarios, like when you need a slicer on a fact table to filter a dimension. But bidirectional filtering can cause performance issues and create ambiguous filter paths, so use it only when you genuinely need it.

Active vs. Inactive Relationships

Power BI only allows one active relationship between any two tables at a time. In the Model view, active relationships show as solid lines, while inactive relationships appear as dashed lines.

Why would you have an inactive relationship? The most common reason is role-playing dimensions. Suppose you have a Date table and a Sales table, but your Sales table has three date columns: OrderDate, ShipDate, and DeliveryDate. You can create three relationships from the Date table to the Sales table, but only one can be active at a time. The other two become inactive.

To use an inactive relationship in a DAX calculation, you use the USERELATIONSHIP() function:

Total Shipped = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[ShipDate], Date[Date]))

This tells Power BI to temporarily activate the ShipDate relationship for that specific calculation.

How to Create Relationships in Power BI

There are several ways to create and manage relationships in Power BI Desktop:

Method 1: Drag and Drop in Model View

Click the Model view icon on the left sidebar of Power BI Desktop (it looks like a diagram).
You will see all your tables displayed as cards with their columns listed.
To create a relationship, simply click on a column in one table and drag it to the matching column in another table.
Power BI will auto-detect the cardinality and cross-filter direction. You can double-click the relationship line to edit these settings.

Method 2: Manage Relationships Dialog

Go to the Modeling tab in the ribbon and click Manage Relationships.
This opens a dialog that lists all existing relationships. You can see the tables, columns, cardinality, and whether each relationship is active.
Click New to create a relationship. Select your two tables and the columns to join on. Set the cardinality and cross-filter direction.
Click OK to save.

Method 3: Autodetect

In the Manage Relationships dialog, click Autodetect.
Power BI will scan your tables for columns with matching names and data and attempt to create relationships automatically.
This is convenient but always review the results. Autodetect can create incorrect relationships if column names are coincidentally similar.

Method 4: Model Explorer (Properties Pane)

In the Model view, you can also right-click on the Relationships section in the Model Explorer pane and select New relationship. This opens a streamlined Properties pane where you fill in the details. This approach generates fewer queries, which matters when working with large datasets or DirectQuery connections.

Fact Tables vs. Dimension Tables

Understanding the difference between fact tables and dimension tables is fundamental to building a good data model.

Fact tables store your transactional or event data. These are the tables with the numbers you want to analyze: sales amounts, order quantities, revenue figures, website clicks. Fact tables tend to be tall (many rows) and relatively narrow (fewer columns). Each row represents a single event or transaction. Fact tables contain foreign keys that link to dimension tables.

Dimension tables store the descriptive context around your facts. They answer the "who, what, where, when" questions: customer names, product categories, store locations, dates. Dimension tables tend to be wide (many descriptive columns) and shorter (fewer rows compared to facts). Each row represents a unique entity, and the key column has unique values.

A simple way to think about it: if you are summing, counting, or averaging a column, it probably belongs in a fact table. If you are filtering or grouping by a column, it probably belongs in a dimension table.

Schemas: Star vs Snowflake, and Flat Table

A schema is the overall shape of your data model, meaning how your fact and dimension tables are arranged and connected.

Star Schema

The star schema is the gold standard for Power BI and the one Microsoft recommends. It gets its name from the way it looks: a central fact table surrounded by dimension tables, like a star.

In a star schema, each dimension table connects directly to the fact table through a single relationship. The dimension tables are denormalized, meaning they contain all their descriptive attributes in a single table rather than splitting them into sub-tables.

Example: A retail star schema might have a Sales fact table in the center, connected to Customer, Product, Date, and Store dimension tables. If you want to filter sales by product category, you go through the Product dimension. If you want to filter by month, you go through the Date dimension.

Why it works well in Power BI: Star schemas minimize the number of joins needed for queries, which improves performance. They also make the model easy to understand: every dimension table has a one-to-many relationship with the fact table, filters flow naturally from dimensions to facts, and DAX calculations behave predictably.

Snowflake Schema

A snowflake schema is a variation of the star schema where one or more dimension tables are normalized. This means a dimension table is broken into related sub-tables instead of keeping everything in one table.

Example: Instead of a single Product dimension with columns for ProductName, Category, and SubCategory, you might have a Product table linked to a Category table linked to a SubCategory table. Each level is its own table.

When it comes up: Snowflake schemas often appear when you import data directly from a normalized relational database. The structure makes sense for storage efficiency in a database, but in Power BI, it adds complexity. More tables mean more relationships, more joins at query time, and more chances for filters to break or behave unexpectedly.

Recommendation: If you encounter a snowflake schema in your source data, consider flattening the snowflaked dimensions in Power Query before loading them into the model. Merge those sub-tables back together so that each dimension is a single table. Your model will be simpler and will perform better.

Flat Table (Single Table / DLAT)

A flat table model puts everything into one big table. Every column, both transactional data and descriptive attributes, are side by side. No relationships needed because there is only one table.

When people use it: This is very common for people coming from an Excel background, where everything lives on one worksheet. It works fine for very small datasets or quick one-off analyses.

Why it is problematic at scale: Flat tables repeat descriptive data across every row (the customer name appears on every transaction for that customer), which bloats the file size. They also make DAX calculations harder to write and can lead to performance issues as the data grows. Power BI's engine is optimized for star schemas, not flat tables. If your "flat table" model starts misbehaving or slowing down, restructuring into a star schema is usually the fix.

Role-Playing Dimensions

A role-playing dimension is a single dimension table that plays multiple roles in the model. The Date table is the classic example.

Consider a Sales table with OrderDate, ShipDate, and DeliveryDate. All three columns relate to the same Date dimension, but each relationship represents a different analytical perspective. You might want to analyze sales by order date for revenue trends, by ship date for logistics, and by delivery date for customer experience metrics.

In Power BI, you handle this by creating three relationships between the Date table and the Sales table (one for each date column). Only one can be active; the others are inactive. You then use USERELATIONSHIP() in your DAX measures to activate the specific relationship you need for each calculation.

In some situations, an analyst may prefer to create separate copies of the Date table (OrderDate table, ShipDate table, DeliveryDate table) to avoid dealing with inactive relationships entirely. Both approaches work. Separate tables are simpler for report consumers who are using slicers, while a single table with USERELATIONSHIP() keeps the model more compact.

Common Data Modeling Issues (and How to Fix Them)

Circular Dependencies

This happens when your relationships create a loop. Table A connects to Table B, Table B connects to Table C, and Table C connects back to Table A. Power BI will flag this and prevent you from creating the relationship.

Fix: Restructure your model to break the loop. Usually this means removing one of the relationships or rethinking which tables need to be directly connected.

Ambiguous Relationships

When there are multiple paths between two tables, Power BI does not know which relationship path to use for filtering. This shows up as unexpected aggregation results or error messages.

Fix: Ensure there is only one active path between any two tables. Deactivate extra relationships and use USERELATIONSHIP() in DAX when you need the alternate path.

Many-to-Many Pitfalls

Many-to-many relationships can produce inflated or incorrect totals because Power BI cannot determine a unique mapping between the tables.

Fix: Introduce a bridge table (sometimes called a junction table) that breaks the many-to-many into two one-to-many relationships. This is cleaner and gives you more control over the results.

Bidirectional Filters Causing Issues

Setting cross-filter direction to "Both" on multiple relationships can create performance problems and ambiguous filter paths.

Fix: Default to single-direction filtering. Only switch to bidirectional when you have a specific use case that requires it, and test thoroughly to confirm the numbers are correct.

Missing or Incorrect Relationships

If Power BI is not filtering your visuals as expected, check the Model view. A missing relationship between two tables means filters will not flow between them. An incorrect join key (like joining on the wrong column) will produce mismatched or blank results.

Fix: Open Model view, verify all relationships exist and connect on the correct columns, and double-check the cardinality indicators (1 and * symbols on the relationship lines).

Conclusion

Data modeling is one of those topics that does not always get the attention it deserves, but it is arguably the most important skill in Power BI. A report is only as good as the model behind it.

I hope you enjoyed reading this and picked up some new concepts. Please leave a comment below if anything was unclear. If you would like to learn more about Analytics and Data Science, give me a follow

How Excel is Used in Real-World Data Analysis

Brian Muriithi — Fri, 27 Mar 2026 22:05:20 +0000

A practical walkthrough from beginner to building a dashboard

I have always known Excel as a spreadsheet application that I have mostly used to complete class assignments and do simple calculations. I have recently learnt that Excel is more than that. Excel is not just a spreadsheet application. It is a powerful tool for cleaning data, analyzing it, and presenting insights in a way that supports decision-making.

As part of this learning journey, I worked on a real project: building a Jumia Product Performance Dashboard. Throughout this article, I will use examples from this project to explain how Excel is used in real-world data analysis, from loading raw data to building an interactive dashboard.

Getting Started with Excel

The first step was installing and opening Excel on desktop. Once launched, the interface introduces several important components:

Workbook: The entire Excel file
Worksheet (Sheet): Individual tabs within a workbook
Rows and Columns: Where data is stored
Cells: The intersection of rows and columns
Ribbon: Contains all Excel tools and features
Formula Bar: Used to enter and edit formulas

Understanding this layout makes it easier to navigate Excel and perform tasks efficiently.

Loading Data into Excel

One of the first practical things I learned was how to load external data. Excel can open CSV files, text files, and data from various other sources. The most straightforward way to load a CSV is to just open it directly in Excel through File > Open, or you can use the Data tab and click "From Text/CSV".

When importing, Excel gives you a preview and lets you set things like the delimiter (comma, tab, semicolon), the data types for each column, and the encoding.

For my project, I worked with a dataset of product listings from Jumia, one of the largest e-commerce platforms in Africa. The dataset had columns for product name, current price, old price, discount percentage, number of reviews, and customer ratings. Nothing too complicated on the surface, but as I quickly learned, raw data is almost never clean enough to analyze straight away.

Data Cleaning: The Part Nobody Talks About Enough

This is where I spent most of my time, and I think that is true for most data work in general. The Jumia dataset had several issues that needed fixing before I could do anything meaningful with it.

Handling missing values. Some products were missing reviews or ratings. In Excel, you can spot these by filtering columns or using functions like COUNTBLANK() to count empty cells. Depending on the situation, you might fill in missing values with an average, remove the rows entirely, or leave them and account for it in your analysis. The Reviews and Rating columns had missing values. Instead of leaving them blank, I used:

Find and Replace (Ctrl + H)

To replace:

Blank reviews → "No Reviews"
Blank ratings → "No Rating"

Removing duplicates. The dataset had some duplicate product entries. Excel has a built-in "Remove Duplicates" feature under the Data tab that makes this pretty painless. You select the columns you want to check for duplicates, click the button, and Excel tells you how many duplicates it found and removed. I went from having some repeated products down to 109 unique entries.

Converting data types. This was a big one. The price columns came in with "KSh" (Kenyan Shillings) as text stuck to the numbers. You cannot do math on text, so I needed to strip out "KSh". The Find and Replace tool (Ctrl+H) was a lifesaver again. I searched for "KSh" and replaced it with nothing. Then I changed the data type to Currency.

Cleaning the discount column. The discount percentages also needed to be converted from text to numeric format. Again, Find and Replace came in handy, along with the LEFT() and RIGHT() functions for extracting parts of text strings.

Fixing the rating column. Ratings were stored as text like "4.5 out of 5" instead of just the number 4.5. I used text functions to extract just the numeric portion.

Converting negative reviews. Some review counts came through as negative numbers, which does not make sense for a count of reviews. The ABS() function quickly converted those to positive values. Find and Replace can also be used to achieve the same.

The TRIM function was another one I used a lot. TRIM() removes extra spaces from text, which is one of those invisible problems that can cause formulas to break or lookups to fail. If a product name has a trailing space, Excel treats it as a different value than the same name without the space, which can mess up your analysis without you even realizing it.

Building New Columns: Data Enrichment

Once the data was clean, the next step was to create new columns that would make the analysis richer.

Discount Amount was straightforward. It is just the Old Price minus the Current Price, giving you the actual shilling value of the discount rather than just the percentage.

Rating Category used an IF formula to classify each product:

"Poor" for ratings below 3
"Average" for ratings between 3 and 4.4
"Excellent" for ratings of 4.5 and above

In Excel, that looks something like:

=IF(H2<3,"Poor",IF(H2<4.5,"Average","Excellent"))

Discount Category followed the same logic:

"Low Discount" for discounts below 20%
"Medium Discount" for discounts between 20% and 40%
"High Discount" for discounts above 40%

These new categories turned raw numbers into meaningful groupings that I could later use for pivot tables and dashboard filters.

Understanding Data Types (A Simple but Powerful Tip)

One simple but very useful trick I learned is how to quickly identify data types in Excel:

Numbers are aligned to the right
Text is aligned to the left

This helps you immediately spot issues. For example, if a column that should contain numbers is left-aligned, it means Excel is treating it as text, which can break calculations.

Useful Functions I Picked Up Along the Way

Beyond the cleaning functions, here are some of the Excel functions that came up repeatedly during this project:

AVERAGE() calculates the mean of a range. I used this to find that the average product rating was about 3.9 out of 5, the average discount was 36%, and the average current price was around KSh 1,210.

COUNT() and COUNTA() count the number of cells with data. COUNT() works for numbers only, while COUNTA() counts any non-empty cell.

MAX() and MIN() find the highest and lowest values. The most expensive product was a 32-piece Portable Cordless Drill Set at KSh 3,750, while the cheapest was a 3-piece Knitting Crochet Needle Set at just KSh 38.

SUM() adds up values in a range. The total number of reviews across all 109 products was 723.

COUNTIF() and SUMIF() are conditional versions that only count or sum values that meet a specific condition. These were useful for breaking down totals by category.

VLOOKUP() searches for a value in one column and returns a corresponding value from another column. While I did not use it heavily in this particular project, it is one of those functions that comes up constantly in real-world Excel work.

Pivot Tables: Where Excel Gets Really Powerful

If there is one feature that changed how I think about Excel, it is pivot tables. A pivot table lets you summarize, reorganize, and analyze large datasets without writing complex formulas. You drag and drop fields into rows, columns, values, and filters, and Excel does the aggregation for you.

For example, I created pivot tables that showed:

The count of products in each discount category (59 in High Discount, 32 in Medium, 18 in Low)
The count of products in each rating category (23 Excellent, 22 Average, 12 Poor)
The average rating and review count broken down by discount level
The top 10 products by discount percentage and by number of reviews

To create a pivot table, you go to Insert > PivotTable, select your data range, and choose where you want the table placed. Then you drag fields from the field list into the areas you need. It is surprisingly intuitive once you try it a couple of times.

The pivot tables served as the data source for most of the charts on my dashboard, which is exactly how professionals use them. Instead of pointing a chart directly at raw data, you point it at a pivot table that already has the summary you need. This keeps things clean and makes updates easier.

Charts and Visualizations

Excel offers a solid range of chart types, and choosing the right one matters for communicating your findings clearly.

Bar and column charts work well for comparing values across categories. I used a bar chart to show the top 10 products by discount percentage, which made it easy to see that the 6-in-1 Bottle Can Opener and Creative Owl Shape items had the highest discounts at over 60%.

Horizontal bar charts are great when product names are long (which they often are in e-commerce data). My "Top 10 Products by Number of Reviews" chart used this format so you could actually read the product names without them getting cut off. The 120W Cordless Vacuum Cleaner led the pack with 69 reviews. I used the same chart to plot of ratings versus number of reviews which showed that the highest-reviewed products (177 reviews) had a 4.6 rating, which is above average but not the highest. Meanwhile, products with a perfect 5.0 rating only had 12 reviews. So higher ratings do not automatically mean more reviews, and that is a useful insight to consider for product strategy.

Pie and donut charts show proportions. I used pie charts to show product distribution across discount categories and rating categories. For example, 54% of products fell into the High Discount bucket, while only 17% were in Low Discount.

Line charts are useful for showing trends. I used one to plot discount percentage against number of reviews, which revealed an interesting pattern: the relationship is not linear. The product with the most reviews (118) had a 49% discount, but there were also products with much lower discounts that still had strong engagement.

Building the Dashboard

All of this work came together in the dashboard. A good dashboard gives someone a quick, high-level view of the data and lets them drill down when they want more detail.

My Jumia Product Performance Dashboard has four KPI cards across the top:

Total Number of Products: 109
Average Rating: 3.9
Average Discount Percentage: 36%
Total Number of Reviews: 723

Below that, there are six charts covering the different angles of analysis, from top products by discount and reviews, to distribution charts showing how products spread across categories, to relationship charts exploring whether discounts and ratings actually drive customer engagement.

I also added slicers for Rating Category and Discount Category. Slicers are visual filters that you can click to instantly filter the data across multiple charts. So if someone wants to see only the "Excellent" rated products, they click that slicer button and every chart connected to the underlying pivot tables updates. It makes the dashboard interactive without any coding.

Key Insights from the Analysis

Working through this dataset taught me that numbers rarely tell a simple story. Here are a few things that stood out:

The average discount is 36%, but more than half of all products (54%) have discounts above 40%. That suggests heavy discounting is the norm on the platform.

Higher discounts do not automatically lead to more reviews. The product with the highest number of reviews had a 49% discount, which is above average, but there were plenty of products with steep discounts that had very few reviews. Other factors like product type, visibility, and quality probably matter more.

Similarly, higher-rated products tend to get more reviews, but it is not a guarantee. Products with the most reviews (177) had a solid 4.6 rating, while products with a perfect 5.0 score only had 12 reviews.

Products rated "Poor" (below 3) made up only about 11% of the catalog, which suggests either a baseline of quality or that poorly rated products get removed from the platform over time.

Why Excel Still Matters

After a week of working with Excel intensively, I can see why it remains a n important tool for analysts across industries. It is accessible. You do not need to set up a development environment or learn a programming language to start analyzing data. The visual, grid-based interface makes it easy to spot patterns and anomalies just by scrolling through your data.

It is also surprisingly powerful. Between formulas, pivot tables, conditional formatting, and the charting tools, you can go from raw data to a polished dashboard without ever leaving the application. For small to medium datasets, this is often all you need.