DEV Community: Tom Chege

Introduction to Python in Data Analytics

Tom Chege — Sun, 10 May 2026 09:28:00 +0000

What is Python?

Python is a programming language used to tell computers what to do. It is popular because it is easy to read and write, making it beginner-friendly. Python can be used to build websites, automate tasks, analyze data, and create machine learning applications.

One of the reasons Python is widely used is because it is versatile. Instead of being limited to one area, it can be applied in many industries and careers such as software development, automation, artificial intelligence, cybersecurity, and data analytics.

It's a tool that can be used for many different purposes.

Why Python is Popular in Data Analytics

Python is popular in data analytics because its syntax is simple and easy to read, making it easier for beginners to learn compared to many other programming languages.

Another major reason for its popularity is the availability of powerful libraries that help analysts clean, organize, analyze, and visualize data efficiently.

Python allows analysts to automate repetitive work, reduce human errors, and process large amounts of data much faster than manual methods.

For businesses, this means:

Faster reporting
More accurate insights
Improved decision-making
Better understanding of customer behaviour and business performance

Python Libraries Used in Data Analytics

A Python library is a collection of pre-written code that helps developers perform specific tasks more easily and efficiently.

Instead of building everything from scratch, developers and analysts can use libraries to speed up their work.

Some popular Python libraries used in data analytics include:

Pandas

Pandas is a Python library used to clean, organize, and analyze data quickly and efficiently.

It helps analysts:

Remove duplicates
Handle missing values
Filter and sort information
Organize data into tables
Work with CSV and Excel files

Using Pandas increases speed and accuracy compared to manual data handling.

Requests

Requests is a Python library used to communicate with websites and APIs over the internet.

It helps analysts retrieve data from external systems and online services for analysis.

How Python is Used to Clean, Analyze, and Visualize Data

Data used in Python can come from:

Databases
Excel files
CSV files
APIs
Websites

Data Cleaning

Data cleaning involves preparing raw data for analysis.

During this stage, analysts:

Remove duplicates
Fix formatting issues
Handle missing values
Correct errors
Organize the data into usable formats

Data cleaning is important because poor-quality data can produce misleading insights and lead businesses to make incorrect decisions.

A common phrase in analytics is:

"Garbage in, garbage out."

This means poor quality data produces poor quality analysis.

Data Analysis

After cleaning, analysts study the data to identify:

Trends
Patterns
Customer behaviour
Business performance
Unusual activities

Python helps analysts process large datasets quickly and accurately.

Data Visualization

Visualization is used to present findings using charts and graphs that are easier to understand than large spreadsheet tables.

Visualizations help businesses:

Spot trends quickly
Compare performance
Identify problems
Communicate insights clearly

Instead of reading thousands of rows of data, management can quickly understand business performance through dashboards and charts.

Real-World Examples of Python in Data Analytics

Python is used in many industries to solve real business problems.

Banking

Banks use Python to monitor customer transactions and identify unusual activities that may indicate fraud.

Supermarkets and Retail

Retail businesses use Python to analyze customer buying behaviour and determine which aisles customers visit most often. This helps improve product placement and store organization.

Businesses can also personalize customer experiences based on shopping behaviour to increase sales and customer retention.

Healthcare

Hospitals and healthcare providers use Python to analyze patient records, monitor disease trends, and improve service delivery.

Transport and Logistics

Transport companies use Python to optimize delivery routes, monitor vehicle performance, and improve operational efficiency.

Why Beginners Should Learn Python

Python is considered one of the best programming languages for beginners because it is easy to learn and has a large and active support community.

Beginners can quickly find:

Tutorials
Documentation
Videos
Forums
Community support

Python is also a valuable career skill because it is versatile and used in many industries beyond data analytics.

In data analytics specifically, Python helps analysts:

Clean data
Organize information
Analyze trends
Visualize insights
Present findings clearly

This makes Python an important tool for turning raw data into useful business decisions.

Conclusion

Python has become one of the most important tools in modern data analytics because of its simplicity, flexibility, and powerful libraries.

With tools such as Pandas, Requests, Matplotlib, and Seaborn, analysts can clean, analyze, and visualize large amounts of data efficiently.

More importantly, Python helps businesses transform raw information into meaningful insights that support smarter and faster decision-making.

Understanding Subqueries and CTEs in SQL

Tom Chege — Sun, 19 Apr 2026 12:58:22 +0000

After getting comfortable with joins and window functions, I’m now diving into more advanced query structuring techniques.

Subqueries felt like a natural extension of filtering and aggregation. CTEs, on the other hand, introduced a cleaner way of breaking down complex logic into readable, step-by-step components.

This guide provides a practical comparison of both concepts using a simple student exam results dataset, highlighting when and why to choose one over the other.

What is a Subquery?

A subquery is a query nested inside another query. It runs first, and its result is used by the outer (main) query.

Subqueries can be placed in:

SELECT clause
FROM clause
WHERE or HAVING clause

Why Use Subqueries?

Subqueries are useful when you need to:

Perform intermediate calculations
Apply dynamic filtering
Break down complex logic without creating temporary tables
Keep queries self-contained

1. Subqueries in the WHERE Clause

This is the most common use case.

Example: Students scoring above the overall average

SELECT 
    student_id,
    marks
FROM exam_results
WHERE marks > (
    SELECT AVG(marks)
    FROM exam_results
);

What’s happening:

The inner query calculates the average marks across all students.
The outer query filters for students who scored above that average.

Subqueries in the WHERE clause with IN and EXISTS

Using IN (for value comparison)

SELECT student_id
FROM exam_results
WHERE student_id IN (
    SELECT student_id
    FROM exam_results
    WHERE marks > 80
);

Using EXISTS (for row existence check)

SELECT DISTINCT e1.student_id
FROM exam_results e1
WHERE EXISTS (
    SELECT 1
    FROM exam_results e2
    WHERE e2.student_id = e1.student_id
    AND e2.marks > 80
);

Key Difference:

IN → Compares values against a list
EXISTS → Checks whether at least one matching row exists (usually more efficient with large datasets)

2. Subqueries in the SELECT Clause

Used to compute additional values for each row.

Example: Show each student’s marks alongside their personal average

SELECT 
    e1.student_id,
    e1.marks,
    (
        SELECT AVG(e2.marks)
        FROM exam_results e2
        WHERE e2.student_id = e1.student_id
    ) AS student_avg
FROM exam_results e1;

Note: This is a correlated subquery — it runs once for every row in the outer query, which can impact performance on large tables.

3. Subqueries in the FROM Clause (Derived Tables)

Creates a temporary result set that can be treated like a table.

Example: Average marks per student

SELECT *
FROM (
    SELECT 
        student_id,
        AVG(marks) AS avg_marks
    FROM exam_results
    GROUP BY student_id
) AS student_avg;

Important: Derived tables must have an alias.

Types of Subqueries

Scalar Subquery: Returns a single value
Column Subquery: Returns multiple rows (usually one column)
Correlated Subquery: References columns from the outer query and runs for each row

What is a CTE (Common Table Expression)?

A Common Table Expression (CTE) is a temporary named result set defined using the WITH clause. It makes queries more readable by breaking them into logical steps.

Think of it as creating a named mini-table that you can reference later in the same query.

Why Use CTEs?

Improved Readability — Break complex logic into named, logical steps instead of deeply nested subqueries
Reusability — Define a result set once and reference it multiple times in the same query
Easier Debugging — You can SELECT * from any CTE individually to test and verify intermediate results
Better Organization — Complex queries become a series of clear building blocks rather than one giant nested statement
Logical Flow — You can build your query step by step, making it easier to understand the thought process
Reduced Repetition — Avoid repeating the same subquery logic multiple times

CTEs are especially powerful when you need to perform multiple transformations or aggregations on the same dataset before joining everything together.

Basic CTE Example: Student averages

WITH student_avg AS (
    SELECT 
        student_id,
        AVG(marks) AS avg_marks
    FROM exam_results
    GROUP BY student_id
)
SELECT *
FROM student_avg;

Types and Use Cases of CTEs

1. Simple CTE

WITH high_scores AS (
    SELECT *
    FROM exam_results
    WHERE marks > 80
)
SELECT * FROM high_scores;

2. Multiple CTEs

WITH student_avg AS (
    SELECT 
        student_id,
        AVG(marks) AS avg_marks
    FROM exam_results
    GROUP BY student_id
),
top_students AS (
    SELECT *
    FROM student_avg
    WHERE avg_marks > 85
)
SELECT * FROM top_students;

3. Recursive CTE

Used for hierarchical or recursive data (e.g., organizational charts, bill of materials). Advanced topic — not ccovered in this article.

Subqueries vs CTEs

Aspect	Subqueries	CTEs (Common Table Expressions)
Readability	Can become hard to read when deeply nested	Much cleaner and more structured
Reusability	Logic must be repeated if used multiple times	Defined once and can be reused multiple times
Performance	Simple subqueries are fast; correlated ones can be slow	Often better with modern optimizers; depends on usage
Debugging	More difficult	Easier (you can `SELECT` from each CTE separately)
Scope	Limited to the immediate query part	Available throughout the entire query

When to Use Each

Use Case	Recommended Choice	Reason
Simple filtering or single value	Subquery	Quick and concise
Complex, multi-step logic	CTE	Better organization and readability
Reusable intermediate results	CTE	Defined once, used multiple times
Row-by-row comparisons	Correlated Subquery	Runs for each row and can reference outer query values
Readability & maintainability	CTE	Easier to read, debug, and maintain

Conclusion

Both subqueries and CTEs help break down complex SQL problems, but they do so in different ways:

Subqueries are great for quick, inline logic that fits naturally inside another clause.
CTEs shine when your query becomes more complex and you want maximum readability and maintainability.

As you progress, the goal shifts from “writing queries that work” to “writing queries that are clear, efficient, and easy to maintain.”

Happy querying!

Understanding SQL Window Functions

Tom Chege — Fri, 17 Apr 2026 12:53:52 +0000

Introduction

This week I’ve been learning a range of SQL concepts. Some clicked quickly, others took a bit more effort to fully grasp.

Joins, for example, were relatively intuitive. Once I understood how tables connect, it became easier to reason about how data flows between them. Window functions, on the other hand, were a different story.

I found them harder to wrap my head around. Not because they are overly complex, but because they require a shift in how you think about data — from grouping and reducing rows to enriching each row with additional context.

That challenge is what led me to write this article. To keep things grounded, I’ll use a simple student exam results dataset throughout the article.

What Are Window Functions?

Window functions perform calculations across a set of rows related to the current row, without collapsing the dataset.

Unlike GROUP BY, which reduces multiple rows into a single summary row, window functions retain every row and add additional insights alongside the existing data.

1. Assigning Order with ROW_NUMBER()

Requirement

Assign a unique rank to each exam result from highest to lowest marks.

SELECT 
    result_id, 
    student_id, 
    marks,
    ROW_NUMBER() OVER (ORDER BY marks DESC) AS row_num
FROM exam_results;

How it works

The data is sorted by marks in descending order
SQL assigns numbers sequentially (1, 2, 3…)

Key behaviour

Every row gets a unique number
Ties are ignored
Ordering strictly follows the defined rule

Example

marks	row_num
95	1
90	2
90	3
85	4

Line everyone up based on a rule, then count them one by one

2. Handling Ties: RANK() vs DENSE_RANK()

SELECT 
    marks,
    RANK() OVER (ORDER BY marks DESC) AS rank_marks,
    DENSE_RANK() OVER (ORDER BY marks DESC) AS dense_rank
FROM exam_results;

Example

marks	rank	dense_rank
95	1	1
90	2	2
90	2	2
85	4	3

Rank Behaviour

Equal values share the same rank
Gaps appear after ties

Dense_Rank Behaviour

Equal values share the same rank
No gaps in ranking

RANK counts positions, DENSE_RANK counts distinct values

3. Grouping into Buckets with NTILE()

How it works

Rows are sorted by marks
Data is divided into equal groups as much as possible

Key behaviour

Groups may not be equal
Extra rows go to top groups first
Ties are ignored

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

Example

Group	Rows
1	4
2	3
3	3

Divide the list into equal buckets, then fill from the top in case of any remainders

4. Adding Context with AVG() OVER()

Requirement

Show each exam result alongside the student’s average mark.

Query

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

How it works

Data is grouped per student using PARTITION BY
Average is calculated per group
Result is attached to each row

Key behaviour

Rows are not collapsed
Averages repeat per student
Original detail is preserved

Example

student_id	marks	student_avg
1	80	85.00
1	90	85.00
2	70	70.00

Why not GROUP BY?

SELECT student_id, AVG(marks)
FROM exam_results
GROUP BY student_id;

Returns one row per student
Loses exam-level detail

Calculate once per group, attach results everywhere

Summary

Function	What it answers
ROW_NUMBER()	Exact order of rows
RANK()	Position with gaps
DENSE_RANK()	Grouping without gaps
NTILE()	Bucket distribution
AVG() OVER()	Context per row

Conclusions

Window functions are not just about computation — they change how you think about data.

Instead of reducing rows, they allow you to:

Add meaning
Compare within context
Build richer analysis directly in SQL

Once that clicks, everything else becomes much easier, or does it?

Understanding SQL: DDL, DML, and data manipulation

Tom Chege — Sat, 11 Apr 2026 06:20:44 +0000

What are DDL and DML?

SQL commands are grouped based on what they act on.

Think of a database as a house:

DDL builds the house (structure)
DML furnishes and maintains the house (data inside it)

DDL (data definition language) — building the structure

DDL is used to define and manage the structure of your database.

It answers questions like:

What tables exist?
What columns do they have?
What type of data goes into them?

Common DDL commands

CREATE -> create new tables
ALTER -> modify existing tables
DROP -> delete tables

Example: creating a table

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    age INT,
    city VARCHAR(50)
);

Key idea

DDL works on the entire structure, not individual rows.

DML (data manipulation language) — working with the data

DML is used to interact with the actual data inside the tables.

It answers questions like:

What data do we have?
How do we add, update, or remove it?

Common DML commands

SELECT -> retrieve data
INSERT -> add data
UPDATE -> modify data
DELETE -> remove data

Examples of DML in action

Inserting data

INSERT INTO students (student_id, first_name, age, city)
VALUES (1, 'Alice', 14, 'Nakuru');

Retrieving data

SELECT * FROM students;

Updating data

UPDATE students
SET city = 'Nairobi'
WHERE student_id = 1;

Deleting data

DELETE FROM students
WHERE student_id = 1;

DDL vs DML — key differences

aspect	DDL (data definition language)	DML (data manipulation language)
Purpose	defines and manages database structure	works with the data inside tables
Focus	tables, schemas, columns	rows and records
Level of impact	affects entire objects (tables, schemas)	affects specific rows
Common commands	`CREATE`, `ALTER`, `DROP`	`SELECT`, `INSERT`, `UPDATE`, `DELETE`
Transaction behaviour	auto-committed (usually cannot rollback)	transactional (can rollback before commit)
Use of WHERE	not used (operates on full objects)	heavily used to filter specific rows
Real-world analogy	building or modifying a house	adding, changing, or removing furniture
Risk level	high (can remove entire structures)	moderate (usually scoped to selected data)

Filtering data with the where clause

The WHERE clause helps you target specific rows instead of working on everything.

Think of it as a filter.

Examples

Equality '='

SELECT * FROM students
WHERE city = 'Nairobi';

greater than '>'

SELECT * FROM exam_results
WHERE marks > 80;

between(inclusive of the numbers in the range)

SELECT * FROM exam_results
WHERE marks BETWEEN 60 AND 79;

in (multiple values)

SELECT * FROM students
WHERE class IN ('Form 2', 'Form 3');

like (pattern matching)

SELECT * FROM students
WHERE first_name LIKE 'A%';

Transforming data with CASE WHEN

The CASE WHEN statement is a powerful tool for adding conditional logic to queries, transforming raw data into more meaningful information.

For instance, I used it to convert numerical marks into a simpler way that shows the performance at a glance.

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

Reflection

This week, I found the distinction between DDL and DML to be the most interesting.

The simplest way to think about SQL:

DDL builds the system
DML brings it to life

If DDL creates the classroom,

DML is the students, lessons, and exams happening inside it.

Let's see what the next lessons bring.

How to publish a Power BI report and embed it into a website

Tom Chege — Sun, 05 Apr 2026 09:43:14 +0000

Introduction

Power BI is a tool used for data cleaning, analysis, and presentation. Once you build your report locally, the next step is making it accessible to others.

In this guide, I will walk you through the process step by step. The end goal is to generate a shareable dashboard that can be viewed in a browser using an embedded iframe.

Items Needed

Your organization’s Power BI credentials: https://app.powerbi.com
A completed dashboard report available locally on your machine
A text editor such as VS Code or Sublime

Once you have the above, you can follow through the process to completion.

Step 1: Open Your Dashboard Report

Open your Power BI report on your local machine.

Click on Sign in on the far right of the screen as shown in the image below.

Step 2: Sign In to Power BI

Proceed and provide the necessary credentials.

Note: Power BI Service requires an organization email. Personal emails such as Gmail will not work.

Once successful, your account will appear on the top right.

Step 3: Select or Create a Workspace

On the left navigation bar in the Power BI Service:

Click on Workspaces
Either:
- Create a new workspace, or
- Use an existing one provided by your organization

Workspaces are where your reports are stored after publishing.

Step 4: Publish the Report

On Power BI Desktop:

Click Publish on the top navigation
Choose your workspace
Select from the dropdown or search for it

Step 5: Confirm Publishing

Once the workspace is selected, proceed to publish.

You will receive a confirmation screen with a link indicating that the report has been successfully published.

Step 6: Generate the Embed Code

Go to the Power BI Service in your browser:

Refresh your workspace
Open the report you just uploaded
Click File → Embed report

You will see the following options:

SharePoint Online
Website or portal
Publish to web (public)
Developer playground

For this tutorial, select Website or portal.

Copy the iFrame code that is generated.

Step 7: Create an HTML File

Open your text editor and create a new file such as index.html.

You can use a basic HTML boilerplate such as the one from:

https://www.w3schools.com/html/default.asp

Example:

<!DOCTYPE html>
<html>
<head>
<title>Sample Dashboard</title>
</head>
<body>

<h1>My First Power BI Dashboard Website</h1>

<iframe title="Kenya crop data" width="1140" height="541.25" src=
"https://app.powerbi.com/reportEmbed?reportId=YOUR EMBEDDED LINK" 
frameborder="0" allowFullScreen="true"></iframe>

</body>
</html>

Paste your copied iFrame into the HTML file.

Step 8: Open and Test

Save the file.

Right-click on index.html and open it with your browser.

It should render a page similar to the image below:

You may be prompted to sign in to Power BI to access the dashboard.

Conclusion

You have successfully:

Published a Power BI report to the cloud
Generated an embedded link
Displayed the report in a web page using an iFrame

This approach allows you to share dashboards in a more interactive and accessible way.

Notes

Users will need Power BI access to view the embedded report
For public sharing, Publish to web can be used, but this should be handled carefully since it exposes your data
Ensure the right workspace permissions are set if other users cannot access the report

Congratulations — you have now built and shared your first embedded Power BI dashboard.

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

Tom Chege — Sun, 29 Mar 2026 18:10:19 +0000

Introduction

Working with raw data often introduces challenges such as duplication, inconsistency, and difficulty in extracting meaningful insights.

Data modelling in Power BI addresses these challenges by structuring data into related tables, enabling efficient querying, accurate calculations, and scalable reporting.

This article explores:

SQL joins and their behaviour
Power BI relationships and how they differ from joins
Data modelling schemas
Practical implementation in Power BI

We’ll use a simple school dataset to ground these concepts.

1. Sample Dataset

We will work with three core tables:

Students (Dimension Table)

StudentID	Name	Age	Class
101	Alice	14	Form 2
102	Brian	15	Form 3
103	Catherine	14	Form 2
104	David	16	Form 4

Test Scores (Fact Table)

ScoreID	StudentID	Subject	Score
1	101	Math	85
2	101	English	78
3	102	Math	92
4	105	Science	88

⚠️ Note: Student 105 does not exist in the Students table—this represents a referential integrity issue.

📊 View Dataset Online

Explore the dataset directly: 👉here!

Teachers (Dimension Table)

TeacherID	Name	Subject
T1	Mr. Kim	Math
T2	Ms. Asha	English
T3	Mr. Otieno	Science

Data Roles

Fact Table: Test Scores (transactional/measurable data)
Dimension Tables: Students, Teachers (descriptive attributes)

2. What is Data Modelling?

Data modelling is the process of:

Organizing data into structured tables and defining relationships that enable efficient querying and accurate analysis.

In analytical systems like Power BI, this typically involves:

Separating facts from dimensions
Establishing keys (e.g., StudentID)
Designing a schema optimized for performance

3. Joins vs Relationships

Joins (Power Query Layer)

A join is a data transformation operation that combines rows from two or more tables based on a matching condition.

Key Characteristics:

Produces a new table
Data is physically merged
Can introduce duplication

Example:

Joining Students and Test Scores results in repeated student records for each score.

Relationships (Model Layer)

A relationship is a logical link between tables based on matching columns.

Key Characteristics:

Tables remain separate
Data is combined at query time
Enables efficient filtering and aggregation via DAX

Critical Difference

Aspect	Join	Relationship
Type	Physical transformation	Logical connection
Location	Power Query	Model View
Data duplication	Yes	No
Performance impact	Higher memory usage	Optimized

4. SQL Joins Explained (with Examples)

Joins determine how rows from two tables are combined based on a matching condition.

We will use:

Students table (left table)
Test Scores table (right table)

INNER JOIN

Definition

Returns only rows where a match exists in both tables.

SQL Example

SELECT s.StudentID, s.Name, t.Subject, t.Score
FROM Students s
INNER JOIN TestScores t
ON s.StudentID = t.StudentID;

Result

StudentID	Name	Subject	Score
101	Alice	Math	85
101	Alice	English	78
102	Brian	Math	92

Explanation

Student 103 and 104 are excluded (no scores)
Score with StudentID 105 is excluded (no matching student)

LEFT JOIN

Definition

Returns all rows from the left table, with matching rows from the right.

SQL Example

SELECT s.StudentID, s.Name, t.Subject, t.Score
FROM Students s
LEFT JOIN TestScores t
ON s.StudentID = t.StudentID;

Result

StudentID	Name	Subject	Score
101	Alice	Math	85
101	Alice	English	78
102	Brian	Math	92
103	Catherine	NULL	NULL
104	David	NULL	NULL

Explanation

All students are included
Missing scores appear as NULL
Useful for identifying missing activity

RIGHT JOIN

Definition

Returns all rows from the right table and matching rows from the left.

SQL Example

SELECT s.StudentID, s.Name, t.Subject, t.Score
FROM Students s
RIGHT JOIN TestScores t
ON s.StudentID = t.StudentID;

Result

StudentID	Name	Subject	Score
101	Alice	Math	85
101	Alice	English	78
102	Brian	Math	92
NULL	NULL	Science	88

Explanation

Score for StudentID 105 appears
Missing student info is NULL
Highlights data inconsistencies

FULL OUTER JOIN

Definition

Returns all rows from both tables, matched where possible.

SQL Example

SELECT s.StudentID, s.Name, t.Subject, t.Score
FROM Students s
FULL OUTER JOIN TestScores t
ON s.StudentID = t.StudentID;

Result

StudentID	Name	Subject	Score
101	Alice	Math	85
101	Alice	English	78
102	Brian	Math	92
103	Catherine	NULL	NULL
104	David	NULL	NULL
NULL	NULL	Science	88

Explanation

Combines LEFT and RIGHT JOIN behavior
Shows all matched and unmatched records

LEFT ANTI JOIN

Definition

Returns rows from the left table with no match in the right.

SQL Equivalent

SELECT s.StudentID, s.Name
FROM Students s
LEFT JOIN TestScores t
ON s.StudentID = t.StudentID
WHERE t.StudentID IS NULL;

Result

StudentID	Name
103	Catherine
104	David

Explanation

These students have no test scores
Useful for identifying inactivity

RIGHT ANTI JOIN

Definition

Returns rows from the right table with no match in the left.

SQL Equivalent

SELECT t.StudentID, t.Subject
FROM TestScores t
LEFT JOIN Students s
ON s.StudentID = t.StudentID
WHERE s.StudentID IS NULL;

Result

StudentID	Subject
105	Science

Explanation

This score has no valid student
Indicates a data quality issue

Key Insight

Each join answers a specific business question:

Join Type	Business Question	Example from Our Dataset
INNER	What data exists in both tables?	Which students have recorded test scores? → Alice and Brian
LEFT	What records are missing related data?	Which students are in the system but have not taken any tests? → Catherine and David
RIGHT	What data exists on the secondary side?	Are there any test scores without valid student records? → Score for StudentID 105
FULL	What does the complete dataset look like?	What is the full picture of students and scores, including missing or unmatched data?
LEFT ANTI	What records in the main table have no matches?	Which students have no test scores at all? → Catherine and David
RIGHT ANTI	What records are invalid or inconsistent?	Which test scores do not belong to any student? → StudentID 105

5. Power BI Relationships

Cardinality

Defines how rows relate between tables.

Type	Description
1:M	One dimension record relates to many fact records
M:M	Multiple matches on both sides
1:1	Unique mapping

👉 Best practice:
Use 1:M from Dimension → Fact

Cross-Filter Direction

Defines how filters propagate between tables.

Type	Behaviour
Single	One-way filtering
Both	Bi-directional filtering

⚠️ Overusing bi-directional filtering can introduce ambiguity.

Active vs Inactive Relationships

Only one relationship can be active between two tables
Inactive relationships can be used via DAX (USERELATIONSHIP)

6. Fact vs Dimension Tables

Fact Table

Contains measurable data
Often large
Example: Test Scores

Dimension Table

Contains descriptive attributes
Used for filtering and grouping
Example: Students

7. Schemas

Star Schema

          Students
             |
Teachers — Test Scores — Subjects

Characteristics:

Central fact table
Denormalized dimensions
Optimized for performance

Snowflake Schema

Students
   |
Address
   |
Region
   |
Test Scores

Characteristics:

Normalized dimensions
Reduced redundancy
Increased complexity

Flat Table (Denormalized Model)

All data stored in one table.

Trade-offs:

Simpler structure
Poor scalability
High duplication

8. Implementation in Power BI

Power Query (Data Preparation)

Clean data
Perform joins
Handle missing values

Model View (Data Modelling)

Create relationships
Define cardinality
Configure filters

Best Practice Workflow

Load data
Clean in Power Query
Avoid unnecessary joins
Build relationships in Model View
Validate using visuals

9. Common Modelling Issues

Data duplication from excessive joins
Incorrect cardinality
Ambiguous filter paths
Missing or inconsistent keys

Conclusion and Key Takeaways

Data modelling is the foundation of effective analysis in Power BI. It defines how data is structured, interpreted, and ultimately how insights are derived.

Throughout this article, we’ve seen how:

Joins are used to reshape and prepare data by combining tables in Power Query
Relationships allow Power BI to connect tables logically, enabling efficient analysis without duplication
Schemas, particularly the star schema, provide a scalable structure for organizing fact and dimension tables
Data integrity (e.g., handling missing or invalid records like StudentID 105) directly impacts the quality of insights

Practical Takeaways

Use Power Query for data cleaning and controlled joins
Use Model View to define relationships and control how data interacts
Prefer 1:M relationships from dimension to fact tables
Default to single-direction filtering unless there is a clear need for bi-directional filtering
Adopt a star schema for most analytical scenarios
Treat missing or unmatched data as valuable signals, not just errors

How Excel is Used in Real-World Data Analysis

Tom Chege — Tue, 24 Mar 2026 06:13:39 +0000

Working with real-world data is rarely clean. Unlike textbook examples, datasets often contain inconsistencies like text mixed with numbers, duplicate values, missing fields, and ambiguous identifiers.

Over the past week, I’ve been exploring key Excel concepts such as error handling, aggregation, lookup functions, and data cleaning. Each of these plays a critical role in transforming raw, messy data into reliable insights.

In this article, I’ll focus specifically on error handling which is a foundational skill that helps ensure your formulas remain robust even when your data isn’t.

What are Excel errors and why do they matter?

Excel errors are often seen as problems to eliminate, but they are actually signals. Each error tells you something specific about your data or logic:

#DIV/0! → You’re dividing by zero or an empty cell
#N/A → A lookup couldn’t find a match
#VALUE! → The data type is incorrect (e.g., text instead of a number)
#NAME? → Excel doesn’t recognize part of your formula

Ignoring these errors or masking them without understanding their cause can lead to misleading results. Instead, they should be treated as indicators of underlying data issues.

The role of IFERROR

One of the most commonly used tools for handling errors in Excel is the IFERROR function. It allows you to return an alternative result when a formula fails.

Example:

=IFERROR(Bonus/Salary, 0)

This ensures that if Salary is missing or zero, the formula does not break but instead returns 0.

⚠️ While this improves usability, it introduces a new responsibility: ensuring that the fall back value does not distort the meaning of your analysis.

The risk of hiding errors

A common mistake is using IFERROR to suppress errors without considering their implications.

=IFERROR(Salary * 0.1, 0)

If Salary is missing, this formula returns 0, which may incorrectly suggest that the calculated value is genuinely zero.

A more transparent approach would be:

=IF(ISBLANK(Salary), "Missing Salary", Salary * 0.1)

This makes the issue visible and preserves the integrity of your data.

Handling specific errors more intelligently

Not all errors should be treated the same. In many cases, it is better to target specific error types.

For example, when working with lookups:

=IF(ISNA(VLOOKUP(A2, A:B, 2, FALSE)), "Not Found", VLOOKUP(A2, A:B, 2, FALSE))

This distinguishes between a missing value (#N/A) and other potential issues, allowing for more precise handling.

Combining logic and error handling

Robust formulas often combine conditional logic with error handling to cover multiple scenarios.

=IFERROR(
  IF(ISBLANK(A2), "No Input", XLOOKUP(A2, A:A, B:B)),
  "Not Found"
)

This structure:

Checks for empty input -- ISBLANK
Performs a lookup when valid -- XLOOKUP
Returns a clear message if the lookup fails -- "No Input"

Such layered logic makes your formulas more resilient and easier to interpret.

What I have learnt about error handling

Treat errors as signals, not just problems
Avoid blindly replacing errors with default/placeholder values
Use targeted checks such as ISNA or ISBLANK where appropriate
Ensure default/placeholder values do not distort analysis
Design formulas with real-world data imperfections in mind

Final takeaway:

Errors are feedback. By understanding the types of errors, applying functions like IFERROR thoughtfully, and combining them with logical checks, you can build Excel models that are both reliable and transparent.

Creating a windows server VM on Azure

Tom Chege — Thu, 19 Mar 2026 06:51:07 +0000

Some problems in software are complex and then there are the other peculiar problems.

The ones that appear simple until they quietly refuse to cooperate.

Recently, I needed to spin up a virtual machine on Microsoft Azure. Nothing fancy. Just a small server running Windows Server 2025 Datacenter for a bit of experimentation.

In theory, creating a VM in the cloud should be one of the most straightforward tasks in modern computing. Click a few buttons, choose an operating system, wait a moment, and somewhere in a distant datacenter a machine materializes.

Reality, as it often does, had a slightly different plan.

The deployment failed with a message that looked innocuous but carried just enough ambiguity to slow things down:

The selected image is not valid for the specified location.

At first glance, it reads like a configuration mistake. Choose a different option and try again. But cloud systems rarely fail randomly; they fail for reasons that are usually hidden one layer deeper than the interface suggests.

In this case, the issue was geographical. I discovered this after tinkering with various images and location settings.

Azure operates dozens of data centers across the world. Each region has slightly different hardware, capacity, and image availability. When you select an operating system image, you are implicitly asking a very physical question: Does this datacenter actually have that image available right now?

Sometimes the answer is no.

Once that realization clicks, the error message suddenly makes perfect sense.

With that small puzzle resolved, I thought it would be useful to write down the exact process for creating a VM in Azure. If nothing else, it may spare someone else a few minutes of head scratching.

Creating a Virtual Machine in Azure

The starting point is the Azure portal.

Open the dashboard and log in:

https://portal.azure.com

This interface is essentially the command center for everything running on Microsoft’s cloud infrastructure.

Once inside, use the search bar and type Virtual Machines. Select the service and click:

Create → Azure Virtual Machine

That opens the configuration wizard where the real work begins.

Basic Configuration

The first section defines the environment where the VM will live.

Start by creating or selecting a resource group. Resource groups act as containers that hold related infrastructure components.

Example:

vm-lab-rg

Next, give the machine a name:

my-first-vm

Naming may seem trivial, but good naming conventions save time later when environments grow larger.

Choosing the Right Region

Azure requires you to pick a geographic region where the VM will run.

Some commonly reliable regions include:

West Europe
UK South
UAE North

The region choice affects latency, pricing, and as I learned sometimes which operating system images are available.

Selecting the Operating System

This is the step where many deployments quietly succeed or fail.

A widely compatible option is:

Windows Server 2025 Datacenter: Azure Edition – x64 Gen2

This image integrates well with Azure’s virtualization environment and is generally available across most regions.

Choosing a VM Size

Next comes the machine size. In cloud terms, this defines CPU power, memory, and cost.

For experimentation or lightweight workloads, something modest works well.

Choosing a VM size is essentially selecting how powerful you want your rented computer to be.

Administrator Credentials

Azure then asks you to create an administrator account for the machine.

Example:

Username: azureadmin
Password: ********

If you are creating a Windows machine, enable the option that allows Remote Desktop (RDP) access. This allows you to connect to the server after deployment.

Storage and Networking

For most use cases, the default settings here work perfectly well.

Azure automatically provisions:

a virtual network
a subnet
a public IP address
a network security rule for RDP

For storage, the default Standard SSD disk is usually a good balance between performance and cost.

Deployment

With the configuration complete, click Review + Create.

Azure validates the settings and then begins the deployment process.

Within a couple of minutes the platform allocates compute resources, attaches storage, configures networking, and boots the virtual machine.

Connecting to the Machine

Once the deployment completes, the next step is actually getting into the server.

Azure provides a few connection options through the portal, but the simplest approach is to connect using Microsoft Remote Desktop.

After the VM is created, Azure automatically assigns a public IP address to the machine. You can find it in the VM’s overview page inside Microsoft Azure.

In my case, I simply copied that public IP and opened a connection through the Microsoft Remote Desktop client.

The process looks roughly like this:

Host: <public-ip-address>

When prompted, enter the administrator credentials you created during the VM setup.

Username: azureadmin
Password: ********

If everything is configured correctly and the RDP port was allowed during deployment the connection should establish within a few seconds.

At that point, you are no longer looking at the Azure portal. You are looking at the desktop of a Windows machine running somewhere inside Microsoft’s global datacenter network.

Yet there it is, responding to your keyboard and mouse as if it were sitting right in front of you.

A Small Reflection

Cloud computing often feels magical. Servers appear and disappear on command. Storage scales endlessly. Networks stretch across continents.

But every now and then the illusion cracks just enough to remind you what is happening underneath.

And sometimes, all it takes to see that reality is a small error message about a missing image in chosen location.