DEV Community: Sharon-nyabuto

Making Decisions Within Decisions in Python

Sharon-nyabuto — Wed, 15 Jul 2026 06:11:48 +0000

Taking Conditional Logic Further with Nested if Statements

Previously, we learnt how to use if, elif, and else to make decisions in Python. Those allowed our programs to choose between different paths depending on whether a condition evaluated to True or False.

Most of the real world decisions depend on more than one condition.

Think about logging into your bank account to withdraw money. Entering the correct PIN is only the first step. Once you're in, the system still needs to check whether you have sufficient funds before approving the withdrawal.
How do we build that kind of layered decision-making in Python?
That's exactly what we'll explore in this article as we learn about nested if statements.

Nested `if` Statements

A nested if statement is simply an if statement placed inside another if statement. It allows a program to evaluate another condition only after the first condition has been met.

Syntax

if condition_1:
    if condition_2:
        # Code to execute

The inner if statement is only evaluated if the outer if statement is True.

Example:

Suppose you're analysing survey responses for a scholarship programme. Applicants must first be at least 18 years old. If they meet that requirement, then you can check whether their household income qualifies them for financial assistance.

age = 21
monthly_income = 18000

if age >= 18:
    if monthly_income < 30000:
        print("Eligible for the scholarship.")
    else:
        print("Age requirement met, but income is above the eligibility threshold.")
else:
    print("Applicant does not meet the minimum age requirement.")

Here's what happens step by step:

Python stores the value 21 in the variable age and 18000 in the variable monthly_income.
It evaluates the first condition: age >= 18.
Since 21 is greater than or equal to 18, the first condition evaluates to True.
Because the first condition is True, Python moves to the nested if statement and evaluates the second condition: monthly_income < 30000.
Since 18000 is less than 30000, the second condition also evaluates to True.
Python executes the nested if block and prints:

  Eligible for the scholarship.

Because both conditions were satisfied, the two else blocks are skipped and the program ends.

Notice the order of the checks. Python only evaluates the applicant's income after confirming they meet the minimum age requirement.
If the applicant were under 18, the income would never be checked because the program would immediately execute the outer else block. This is what makes nested ifstatements useful; they allow one decision to depend on the outcome of another.

Here are a few other examples, that demonstrate how multiple conditions can be evaluated one after another.

# Scenario 1: Job Application Screener

# Check whether the applicant has tertiary education. If they do, ask for their years of experience. 
# If they have at least 3 years of experience, ask for their expected salary. If the salary expectation is Ksh 80,000 or less, shortlist them for an interview; otherwise, indicate that their salary expectation is too high. 
# If they have fewer than 3 years of experience, print "Minimum 3 years of experience required." If they do not have tertiary education, print "Higher education is required for this position."

tertiary_education= input("Do you have a diploma, degree or higher?").lower()

if degree == "yes":
    experience = int(input("How many years of experince"))
    if experience >= 3:
        salary = float(input("Enter your expected salary"))
        if salary <= 80000:
            print("Shortlisted for interview")
        else:
            print("Salary expectation too high")
    else:
        print("Minimum 3 years experience required")
else:
    print("Higher education is required for this position")

# Scenario 2: Mobile Money Transfer Validator

# Write a program to validate a mobile money transfer. 
# If the user has an account, check that they have sufficient funds and that the transfer amount does not exceed the daily limit of Ksh 150,000. 
# If the transfer is successful, display the amount sent and the remaining balance. If the user does not have an account, print "Please register first."

mobile_checker = input("Do they have an active account? (Yes/No)")

if mobile_checker == "yes":
    acc_balance = float(input("What is your account balance?"))
    if acc_balance > 0:
        amount_to_send = float(input("How much do you want to send??"))
        if amount_to_send <= acc_balance:
            new_balance = acc_balance - amount_to_send
            if amount_to_send > 150000:
                print("Amount exceeded daily limit")
            else:
                print("Successful transfer")
                print(f"Amount sent: Ksh., {amount_to_send}, Your account balance is: Kshs., {new_balance}")
        else:
            print(f"Insufficient funds: Kshs., {acc_balance}")
    else:
        print("You have a low balance")
else:
    print("Please register first.")

# Scenario 3: School Report Card Generator

# Write a program that generates a student's report card. Ask for the student's name and marks (out of 100) in Mathematics, English, Kiswahili, Science, and Social Studies. For each subject, assign a grade and remark using the grading scale provided. Then calculate the total marks (out of 500), the average score, and the overall grade based on the average using the same grading scale.
# Next, determine the student's promotion status: students with an average of 75 or above are promoted, those with an average between 50 and 74 are promoted but advised to work harder, while those below 50 should repeat the class.
# Finally, check whether the student received a grade E in any subject. If so, display a warning indicating that remedial lessons are required for the relevant subject(s). 

# Print a neatly formatted report showing the student's name, subject grades, total marks, average, overall grade, promotion status and any remedial warning.

subjects = ["Maths", "English", "Kiswahili", "Science", "Social Studies"]
student_name = input("What is your name?")
print(f"{student_name} : Performance Summary")

print("==================================")
total_marks = 0
failed = ""
for subject in subjects:
    marks = int(input(f"What was your score in {subject}"))
    if 75<= marks <= 100:
        grade = "  A  - Excellent"
    elif 60<= marks <= 74:
        grade = "  B  - Very Good"
    elif 50<= marks <= 59:
        grade = "  C  - Good"
    elif 40<= marks <= 49:
        grade = "  D  - Pass"
    else:
        grade = "  E  - Need Improvement"
    total_marks += marks
    print(f"{subject} | Score: {marks} | Grade:{grade}")
    if grade == "  E  - Need Improvement":
        failed += subject + " "  
print(f"Total marks: {total_marks} / 500")
if failed: 
    print(f"NOTE: Remedial lessons required in {failed}")
    print()

average = total_marks / len(subjects)
print(f"Average Score: {average}")
if average >= 75:
    print("PROMOTED to next class")
elif 50 <= average <= 74:
    print("PROMOTED -  work harder next term")
else: 
    print("REPEAT - see your class teacher")

Result:

Mary Nancy : Performance Summary
==================================

Maths          | Score: 43 | Grade:  D  - Pass
English        | Score: 23 | Grade:  E  - Need Improvement
Kiswahili      | Score: 56 | Grade:  C  - Good
Science        | Score: 89 | Grade:  A  - Excellent
Social Studies | Score: 67 | Grade:  B  - Very Good
Total marks: 278 / 500

NOTE: Remedial lessons required in English 

Average Score: 55.6
PROMOTED - Work harder next term

Don't stop with the examples in this article. Modify them, build your own, and experiment with different scenarios, because the best way to master programming is by writing and testing your own code.
As you continue practicing, you may find yourself using nested conditionals whenever a problem requires multiple levels of decision-making.

Bonus Tip: f-Strings

Before we wrap up, there's one small feature you've probably noticed throughout the examples in this article. Many of the print() statements begin with the letter f, like this;

print(f"{subject} | Score: {marks} | Grade:{grade}")

The f stands for formatted string (or f-string). It allows you to insert the value of a variable directly into a string by placing it inside curly braces {}.

Without an f-string, you would have to write:

print(subject, " | Score: ", marks, " | Grade: ", grade)

While both approaches work, f-strings are easier to read and become especially useful as your programs grow longer and your output includes multiple variables.

As we continue through this series, we'll use f-strings regularly to produce cleaner and more readable output.

Teaching Python to Make Decisions

Sharon-nyabuto — Mon, 13 Jul 2026 21:11:20 +0000

Understanding if, elif, and else

In the previous article, we explored what Python is and why it's such a valuable tool for data analysts. We also wrote a few simple programs that followed instructions exactly as we wrote them.

In this article, we will explore how to teach Python to make decisions using conditional statements. By the end, you'll understand how to use if, elif, and else to control the flow of your programs and hopefully solve simple real-world problems.

So what is a conditional statement?

We make decisions all the time in our lives. If it rains, we carry an umbrella. If we're hungry, we eat. If an assignment is due tomorrow, we probably shouldn't leave it until midnight. Programs need to make decisions too, that is what conditional statements are for.

Conditional Statements allow a program to evaluate conditions and choose what to do based on whether those conditions are True or False.
The three keywords you'll most commonly use when writing conditional statements are if, elif, and else.

Comparison Operators

Before we can make decisions in Python, we need a way to compare values.
Comparison operators evaluate two values and return either True or False, which are the outcomes that drive if, elif, and else statements. Common comparison operators are:

Operator	Meaning
`==`	Equal to
`!=`	Not equal to
`>`	Greater than
`<`	Less than
`>=`	Greater than or equal to
`<=`	Less than or equal to

Example:


age = 25

print(age == 25)   # equal to        -> True
print(age != 18)   # not equal to    -> True
print(age > 18)    # greater than    -> True
print(age < 18)    # less than       -> False
print(age >= 25)   # greater or equal -> True
print(age <= 20)   # less or equal    -> False

Note: The = operator assigns a value to a variable, (like age = 25), while == checks whether two values are equal. This is one of the most common mix-ups I had when I started out.

The IF statement

The if statement is what lets your code actually make a decision : It runs a block of code only if a condition is True.

Example:

age = 25
if age >= 18:
    print("You're an adult.")

Here's what happens step by step:

age = 25 - Python stores the value 25 in the variable age.
age >= 18 - It evaluates the condition.
Since 25 is greater than 18 the condition is True
The indented line is executed, and the output prints:

You're an adult.

The colon : shows the end of the condition, and indicates everything after is what needs to be done if the condition is met.

The indentation (usually 4 spaces) is how Python knows which lines of code belong inside the if statement.

The ELSE statement

In an if statement, you specify what needs to be done if the condition is True.
You can also tell Python what to do if the condition is not met, using else. The statement then becomes;

Example:

score = 68
if score >= 70:
    print("You have passed")
else:  
    print("Please retake the test")

Here's what happens step by step:

Python stores the value 68 in the variable score.
It evaluates the condition score >= 70.
Since 68 is less than 70, the condition evaluates to False.
Because the if condition is False, Python skips the if block.
Python then executes the else block and prints:

Please retake the test

The else block acts as a fallback, and is only executed when the condition in the if statement evaluates to False.

The if-else statement is always used when you have two possible outcomes.

The ELIF statement

Suppose there are more than two possible outcomes?
The if-else statements will not sufficiently represent all of them. This is where the elif statement becomes useful

elif ("else if") lets you check additional conditions in order.

Example:

age = 34

if age >= 40:
    print("Older Adults")
elif age >= 20:
    print("Youth")
else:
    print("Teens")

Here's what happens step by step:

Python stores the value 34 in the variable age.
It evaluates the first condition: age >= 40.
Since 34 is less than 40, the first condition evaluates to False, so Python moves to the next condition.
Python evaluates the elif condition: age >= 20.
Since 34 is greater than 20, the elif condition evaluates to True.
Python executes the elif block and prints:

Youth

Python then skips the else block and the conditional statement ends.

Think of elif as Python saying, "If the first condition wasn't true, check this one instead."
The conditions are evaluated from top to bottom and only the first condition that evaluates to True is executed.

Putting Conditionals into Practice

Now that we've covered the basics of if, elif, and else, it's time to see how they work together.
The following examples are a little more practical, and demonstrate how Python can make decisions based on multiple conditions.

Examples

# Q1. A cinema allows entry if: age >= 18 AND has_ticket == True. Write the condition and test it with different values.

age = int(input("How old are you?")) # Ask for age
has_ticket = True

if age >= 18 and has_ticket == True:
    print("Welcome to the Movies!")
else: 
    print("Restricted")

# Q2. Build an electricity bill calculator. Units used: 0–50 → Ksh 12/unit, 51–200 → Ksh 15/unit, 201+ → Ksh 18/unit. Ask for units used and print the total bill.

electricity_units = int(input("How many units have you used?"))
print(electricity_units)
if electricity_units <= 50:
    print("Your bill is ",electricity_units * 12)
elif electricity_units <= 200:
      print("Your bill is ",electricity_units * 15)
else:
     print("Your bill is ",electricity_units * 18)

# Q3. Ask for a student's marks (0–100). Print their KCSE grade: A (75+), B (60–74), C (50–59), D (40–49), E (below 40).
student_marks = int(input("What were your marks?"))
if student_marks >= 75:
    print("A")
elif student_marks >= 60:
    print("B")
elif student_marks >= 50:
    print("C")
elif student_marks >= 40:
    print("D")
else:
    print("E")

Don't worry about counting your elif statements! You can have as many as your program needs, as you can see in the last example. Python simply checks each condition in order and stops as soon as it finds one that's True.

Your turn, try it yourself!

The examples in this article are just a starting point. Experiment with different values, create your own conditions, and see how changing the logic affects the output. Programming is one of those skills that's learned best by doing.

What's Next?

We've now learned how to make Python choose between different paths. Next, we'll build on that foundation with nested if statements, where one condition is evaluated inside another. It's a small step in syntax, but a big step towards writing more realistic programs.

Thanks for reading, and I hope you'll join me for the next part of the series. Until then, happy coding!

Python for Data Analysts: Getting Started

Sharon-nyabuto — Sun, 12 Jul 2026 14:44:43 +0000

Understanding what Python is, why it matters, and the concepts every beginner will encounter.

A month ago, if you'd asked whether I could write Python, the answer would have been no, simply because Python just looked intimidating.

As a research and data analyst however, I do love a challenge, and I asked myself the practical question: How could I actually use Python in my day-to-day work?
A dive into Python revealed one certainty: Python is not easy, but it's nowhere near as intimidating as I'd built it up to be. This is a look back at my first week learning Python: the assumptions I walked in with, and how quickly most of them fell apart.

So what is Python and why does it matter in Data analysis?

Python is a programming language: a way of giving a computer clear, step-by-step instructions to perform tasks. The tasks can be as simple as basic calculations or as involved as cleaning data, analysing thousands of data entries, automating repetitive tasks, or building applications.

One of the reasons Python is so popular among both beginners and data analysts is its readable syntax. It often resembles plain English, making it easier to learn while remaining powerful enough for real-world data analysis.

For example, one of the first programs most people write looks like this:

print("Hello, World!")

It simply tells Python to display Hello, World! on the screen. It's a tiny program, but it marks the beginning of almost every programmer's journey, including mine.

Why does Python matter?

If you've spent enough time working with spreadsheets, you've probably thought, "There has to be an easier way to do this." Whether it's cleaning data, repeating the same steps every week, or working with increasingly large datasets, there comes a point where spreadsheets begin to show their limits. That's where Python comes in.

In data analytics, Python is particularly useful for:

Automating repetitive tasks
- Tasks you'd normally repeat manually (cleaning files, renaming columns, merging spreadsheets) can be scripted once and reused.
Scale
- As datasets grow, spreadsheets can become slow, cumbersome, or even unresponsive. Python is designed to handle much larger amounts of data efficiently. We'll look at the tools that make this possible including Python libraries, in a later article.
Reproducibility
- Every step of your analysis is written into a script. That means you can run the same code tomorrow, next month, or even next year and reproduce the same results, without having to memorize or manually repeat every step.
Collaboration
- Because each step of the analysis is written into the script, colleagues can review, understand, and build on your work without having to guess what was done. This makes teams work seamlessly on the same project.
Flexibility in connectivity
- Python can pull data directly from a database, an API, or a folder of raw files, instead of relying on someone exporting a CSV for you.
Specialized Tools
- One of Python's greatest strengths is its ecosystem of libraries. Libraries are tools created by the Python community that extend what Python can do, without you having to build everything from scratch. (We'll introduce some of them as we progress through the series).

Learning why Python matters gave me the motivation to begin. Learning how to use it, however, came with a few surprises. Looking back, many of the things that intimidated me at first weren't really difficult, but simply unfamiliar.

Common Terms You'll Encounter in Python - Getting familiar with the Python Environment

Far from jumping into data analysis, my first few days were spent learning the basic concepts and getting familiar with common terms, most of which had names that were new to me.

Here are five that stood out to me when I was getting started;

Terminal: I assumed one wrong command could break my computer, only to realize it's really just another way of talking to your machine, and it stops feeling like a 'black box' after a handful of basic commands.
Integrated Development Environment/IDE: This is where you actually write your code. Think of it as a text editor built specifically for writing and running programs, with extra features like error highlighting, autocomplete, and a built-in terminal. VS Code is one of the most popular choices.
Script: This is the file containing Python code, usually with a .py extension. Instead of typing commands one at a time into the terminal, you write them all into a script and run the whole thing at once.
Library: Pre-written code that someone else has already built, so you don't have to write everything from scratch. Need to work with dates, analyse data, or make charts? There's very likely a library for that — you just import it into your script and use it. Pandas is one of the most common ones for data analysis.
Indentation: Indentation isn't cosmetic in Python, it's part of the syntax, defining which lines belong together. It took me a couple of errors and examples before fully comprehending, but once that clicked, it stopped feeling confusing.

Early on, I felt like I had to fully understand every new term, variables, functions, loops, modules, packages, libraries, before I could write code. Turns out you only need to understand the concept you're using today. The rest will fall into place as you keep writing code.

My first few lines of Python

A variable is just a name that stores information:

name = "Sharon"
print(name)

name , nationality, city = "Sharon", "Kenyan", "Kisumu" 
print("My name is ",name,". I am a ",nationality," and I live in ",city)

Python can do calculations for you:

income = 25000
expenses = 18000
print(income - expenses)

Or take input from the user:

name = input("What is your name? ")
print("Welcome, ", name)

While these may not be impressive on their own, together they show programming isn't about writing something complex from day one, but about stacking small blocks to solve a bigger problem.

Biggest lessons so far

Progress comes from consistent practice.
Errors are part of the workflow, not a verdict on your ability.
Typing code yourself teaches more than copy-pasting it.
Small projects teach more than another hour of tutorials.
Looking things up is normal, sometimes even the best analysts do it.

Programming isn't about knowing every answer. It's about knowing how to find one. This is the first article in my Python learning series, documenting the process as I go.

In the next article, we'll look at how Python uses if statements nested if statements to make choices based on different conditions. This i think is one of the biggest steps towards writing programs that actually think before they act.

Final thoughts

If you're on the fence, don't wait until you feel "ready." Write one line of code and trust that understanding builds with reps.

I'm still learning too, and that's exactly what this series is for.

Cleaning Messy Phone Numbers in PostgreSQL Using REGEXP_REPLACE

Sharon-nyabuto — Mon, 18 May 2026 22:34:00 +0000

Introduction

The first thing anyone that has worked with real world data will tell you is that rarely does data come in the clean, uniform format you expect. Phone numbers are one of the biggest culprits.

One person enters +254712345678, another writes 0712345678, someone else throws in 254-712-345-678, another (254)-712345678 and another 254 712 345 678. Before you know it, your database has a different version of phone numbers in every row.

In this article, we will explore how to fix that, using a powerful PostgreSQL function called regexp_replace.

By the end, you will understand what it does, how to build the pattern, and how to apply it to actually clean your data.

First, What Even Is `regexp_replace`?

First things first, we need to understand what this function is and what it does.

regexp_replace is a PostgreSQL function that finds a pattern inside a string and replaces whatever matches that pattern with something else you specify. The word regexp is short for regular expression.

The syntax looks like this:

regexp_replace(source, pattern, replacement_string, flag)

Let us break each part down simply:

Part	What It Means
`source`	The string you are searching inside. In our case, this is the column `passenger_phone`
`pattern`	What you are looking for : What do you want to find and remove or replace?
`replacement_string`	What do you want to put in place of what you found? If you just want to delete it, you pass an empty string `''`
`flag`	Controls how the matching behaves. The two you will use most are `i` for case-insensitive matching and `g` for global, meaning it applies the replacement to everything it finds in the string, not just the first match

Now that we have the hang of it, let us move on to building the pattern.

Understanding the Special Characters in Patterns

A pattern is basically a set of rules that tells PostgreSQL what to look for. It is like saying "find any character that is not a digit" or "find this pattern, but only at the beginning" etc.

To write these rules/instructions, there are special characters that are used.

Here are the ones we will use today:

The `^` character

Depending on where it is placed, this will do either of 2 things;

When ^ is at the beginning of a pattern it means: start matching from the very beginning of the string.

^'hello'

This means: only match hello if it appears at the start of the string.

^[0-9]

This means: Only the first character of a string is checked and matched if it is a digit. Anything after that is ignored.

When ^ is inside square brackets [ ], it means: NOT these characters.

[^0-9]

This means: Every character in the string is checked, any one that is not a digit gets matched.

Same symbol, two different purposes depending on its position.

The `\` character (backslash)

The backslash is used to tell PostgreSQL: treat the next character as a character, not as a special operator.

For example, the + sign in regular expressions has a special meaning (we will cover it in a moment). So if you want to find a plus sign + in your data, like +254, you write it as \+ to tell PostgreSQL "I mean the actual plus sign, not the operator."

Square brackets `[ ]`

Square brackets let you define a group of characters to match. For example:

[0-9] means match any digit from 0 to 9
[a-z] means match any lowercase letter
[^0-9] means match anything that is NOT a digit (the ^ inside means NOT, as we've mentioned above)

The `+` operator

When used outside brackets, + means: keep matching one or more of the preceding character or group.

So [0-9]+ means: Match the digits as long as they are all next to each other with no other characters in between. (Without the + , it stops after the first match)

The `$` character

Just like ^ marks the start of a string, $ marks the end. When you write $ at the end of your pattern, you are saying: the string must end here, nothing else allowed after this.

Putting them together: `^[0-9]+$`

Now that you know each piece, read this pattern again:

^[0-9]+$

^ → start from the beginning of the string
[0-9] → the characters must be digits
+ → keep going, there can be one or more of them
$ → and this must be the end of the string, nothing else after

All together: the entire string, from start to finish, must contain only digits.

This is the pattern we will use to identify which phone numbers are already clean and which ones need fixing.

Our Problem: What Does the Messy Data Look Like?

Let us say when we run this:

SELECT passenger_phone FROM dirty_safari_data;

We get back a mix of things like:

+254712345678
254712345678
0712345678
0712-345-678
+254 712 345 678
(0) 712345678

The goal is to standardize all of these into the local format starting with 0, containing only digits, like:

0712345678

Two problems to solve:

Some numbers start with +254 or 254 instead of 0
Some numbers have extra characters like brackets, spaces, dashes, or plus signs

Step 1: Replace `+254` or `254` at the Start With `0`

The first thing we want to do is find any phone number that starts with +254 or 254 and swap that prefix out for 0.

SELECT regexp_replace(passenger_phone, '^(\+254|254)', '0', 'g')
FROM contacts;

Let us read the pattern ^(\+254|254) carefully:

^ → look at the start of the string only
( ) → group what is inside together
\+254 → literally the characters +254 (the backslash makes + a literal character)
| → OR
254 → literally the characters 254

So the full pattern says: at the start of the string, find either +254 or 254.

The replacement is '0', so wherever that prefix is found, replace it with 0.

The flag is 'g' for global.

After this step, +254712345678 becomes 0712345678 and 254712345678 also becomes 0712345678.

Step 2: Remove Any Character That Is Not a Digit

Some numbers still have spaces, dashes, or other characters sitting in them. The second regexp_replace cleans all of that up.

SELECT regexp_replace(passenger_phone, '[^0-9]', '', 'g')
FROM contacts;

The pattern [^0-9] means: any character that is NOT a digit.

The replacement is '' (an empty string), so we are just deleting those characters.

The 'g' flag means do this for every non-digit character found in the string, not just the first one.

So 0712-345-678 becomes 0712345678 and 0712 345 678 also becomes 0712345678.

Step 3: The `WHERE` Clause — Only Update What Needs Fixing

We do not want to touch phone numbers that are already clean. That is where our ^[0-9]+$ pattern comes in handy. We use it in the WHERE clause to filter for records where the phone number is not already a clean string of digits.

You might be tempted to write it like this:

WHERE passenger_phone != '^[0-9]+$'

But this is not correct. The != operator just compares strings literally, so this would check if the phone number is literally equal to the text ^[0-9]+$, which is not what we want.

In PostgreSQL, to check whether a string does NOT match a regular expression, you use the !~ operator. The corrected version should be:

WHERE passenger_phone !~ '^[0-9]+$'

This now correctly says: only return (or update) rows where the phone number does not match the pattern of being purely digits from start to finish.

Putting It All Together

The SELECT query (to preview your cleaned data before changing anything)

Always preview before you update. This is a good habit.

SELECT 
    passenger_phone AS original,
    regexp_replace(
        regexp_replace(passenger_phone, '^(\+254|254)', '0', 'g'),
        '[^0-9]', '', 'g'
    ) AS cleaned_phone
FROM contacts
WHERE passenger_phone !~ '^[0-9]+$';

Notice how we nested one regexp_replace inside another. The inner one runs first (fixing the prefix), and then the outer one runs on the result (removing non-digits). It is like running two cleaning steps in one go.

The UPDATE query (to actually apply the changes)

Once you are happy with the preview, run the update:

UPDATE contacts
SET passenger_phone = regexp_replace(
    regexp_replace(passenger_phone, '^(\+254|254)', '0', 'g'),
    '[^0-9]', '', 'g'
)
WHERE passenger_phone !~ '^[0-9]+$';

This updates only the rows that have messy phone numbers, leaving the already clean ones untouched.

Quick Recap

Here is a summary of everything covered:

Pattern	What It Does
`^` at the start of a pattern	Anchors the match to the start of the string
`^` inside `[ ]`	Means NOT — match anything except what follows
`\+`	Treat `+` as a literal plus sign, not an operator
`[0-9]`	Match any digit from 0 to 9
`[^0-9]`	Match any character that is NOT a digit
`+`	One or more of the preceding character/group
`$`	Anchors the match to the end of the string
`^[0-9]+$`	The entire string must be digits only, nothing else
`g` flag	Apply the replacement globally (every match, not just the first)

Final Thoughts

Regular expressions can look intimidating and confusing at first, but once you understand what each character means and why it is there, they start making a lot of sense. The key takeaway here is to build your pattern step by step, rather than trying to write the whole thing at once.

Also, always test with a SELECT before running an UPDATE. You can never be too careful when modifying data directly in a table.

SQL Subqueries vs. CTEs: A Guide to Writing Better Queries

Sharon-nyabuto — Tue, 21 Apr 2026 19:54:31 +0000

When you first learn SQL, queries are usually straightforward: you SELECT some columns from a table and apply a WHERE filter to narrow down the results.

As your data questions become more complex, however, you realize sometimes you need the answer to one question before you can answer another.
For example: "Which employees earn above the company average?", requires calculating the average salary first, then use the result to filter employees.

To solve this, SQL provides two incredibly powerful tools: Subqueries and Common Table Expressions (CTEs).

This guide will break down these tools and cover:

Part 1: Subqueries
- What subqueries are and how they work
- The different types of subqueries
- When to use subqueries
Part 2: CTEs (Common Table Expressions)
- What CTEs are and why they matter
- The different types of CTEs
- When to use CTEs
Part 3: A clear comparison of Subqueries vs. CTEs

PART 1: SUBQUERIES

Also known as an inner query or a nested query, a subquery is a query embedded within another SQL query.

Subqueries are typically found nested inside standard SQL clauses such as SELECT, FROM, WHERE, or HAVING.

SQL follows a strict order of execution:

The innermost subquery is always executed first. Its result is then passed on to be used by the outer query (the containing query).

Subqueries allow you to use a single query rather than multiple queries, making your code more flexible and dynamic. They are categorized either by their output or by execution.

Subqueries Categorized by Output

One way to understand subqueries is by looking at the result they return.
A subquery can return a single value, a list of values, or an entire table, depending on the query.

Scalar Subquery

Returns exactly one value (one row, one column).

Scalar subqueries are used with single value comparison operators; =, <, >, <= & >=

For example: "Which students scored above the class average on the math test?" requires a scalar subquery.

(Assuming the class average is 80.)

SELECT student_name, score
FROM students
WHERE score > (SELECT AVG(score)
               FROM students);

Result:

student_name	score
Awino Judy	95
Wanjiku Stacy	88
Kiprop Bryan	82

Note: The scalar subquery SELECT AVG(score) FROM students returns a single value (80), then the outer query uses this value to filter students who scored above the class average

Multi-row Subquery (List Subquery)

Returns a column of values, i.e. multiple rows but one column.
It is often used with IN and NOT IN operators to filter results against a list of values.

For example: "Which students registered for the math exam?" requires a multi-row subquery

SELECT student_id,student_name
FROM students
WHERE student_id 
IN (SELECT student_id 
  FROM exam_registrations 
  WHERE exam_subject = 'Math');

Result:

student_id	student_name
1	Wanjiku Stacy
2	Kiprop Bryan
3	Awino Judy
4	Mutua Henry

The list subquery first generates a list of student IDs from the exam_registrations table, then the outer query uses IN to filter the students table against that list.

Table Subquery (Derived Table)

Returns multiple rows and columns, acting like a temporary table.
This subquery is used in the FROM clause, and must always have an alias.

For example: "Which students scored more than 160 total points in all 3 math tests?" requires a table subquery.

SELECT student_name, total_score
FROM 
(SELECT student_name, 
SUM(score) AS total_score
  FROM exam_results
  GROUP BY student_name) AS student_totals
WHERE total_score > 160;

Result;

student_name	total_score
Awino Nancy	185
Wanjiku Stacy	170
Kiprop Bryan	165

The table subquery ran first to calculate everyone's total score, creating a temporary virtual table which we aliased (student_totals). The outer query used this table to filter the results to get the students with more than 160 marks.

Subqueries Categorized by Execution

Subqueries can also be categorized based on how they work with the rest of the query.

Correlated Subquery

A correlated subquery is dependent on data from the outer query.
It runs for every row in the outer query, using values from the outer query to filter its result.

Uncorrelated (Independent) Subquery

An uncorrelated subquery runs once, and is fully independent of the outer query.
This means that it evaluates its logic exactly once, gets its result, and simply hands that result over to the main query to complete the task.

If you're ever uncertain as to whether a subquery is correlated (dependent) or uncorrelated (independent), you can use the highlight test on your SQL editor.

Highlight just the subquery and run it:

If it returns a result: It is an uncorrelated subquery. It can stand on its own.

If it throws an error: It is a correlated subquery. It depends on information from the outer query to make sense and run correctly.

When to use Subqueries

Subqueries are useful when;

Calculating metrics like averages or totals on the fly, without hardcoding numbers.
Filtering results using a dynamically generated list of values i.e. (List subqueries).
Using row-specific logic
There's need to aggregate data before filtering.
Working with older legacy databases that don't support more modern features.

Subqueries are excellent for simple logic, but when they are deeply nested, they become difficult to read and more difficult to maintain. This is where Common Table Expressions (CTEs) come in.

PART 2: The CTE (COMMON TABLE EXPRESSION)

A CTE is a temporary, named result set defined at the top of a query using the WITH clause, that exists only for the duration of your query.

Once it is defined, you can query and reference the CTE as you would a normal table in your database.

Because a CTE is defined at the top of your script, it allows you to read the code logically from top-to-bottom

The Basic Syntax of a CTE

No matter how complex a query gets, almost every CTE follows this simple, two-part structure:

Define the CTE using the WITH clause at the top.
Use the CTE in your main query.

Syntax;

WITH cte_name AS (
    -- Step 1: Write your query here. 
    -- This creates your temporary, virtual table.
    SELECT column1, column2
    FROM table1
    WHERE condition
)
-- Step 2: Write your main query here, reading from the CTE!
SELECT column1
FROM cte_name;

Now that we know the basic syntax, we can dive into the two types of CTEs commonly encountered: Recursive and Non-Recursive (based on whether or not they reference themselves).

Non-Recursive CTEs
They are temporary result sets that run once and don’t reference themselves.
They’re commonly used for breaking complex queries into modular blocks.

The CTE syntax is;

WITH cte_name AS (
  -- Your SQL logic here (SELECT, JOIN, GROUP BY, etc.)
)
SELECT * FROM cte_name;

Use Case: Replacing nested subqueries for better readability and reusability.

Earlier when we wanted to find students who scored a total of more than 160 points across all their maths tests, We solved it using a Table Subquery in the FROM clause.

Let's try the exact same question, using a CTE.
"Which students scored more than 160 total points in all 3 math tests?"

WITH student_totals AS (
  SELECT student_name, SUM(score) AS total_score
  FROM exam_scores
  GROUP BY student_name
)
SELECT student_name, total_score
FROM student_totals
WHERE total_score > 160;

Result;

student_name	total_score
Awino Nancy	185
Wanjiku Stacy	170
Kiprop Bryan	165

This CTE achieves the same result as our Table Subquery Example, in a cleaner, more readable way.

Recursive CTEs

Recursive CTEs are temporary result sets that reference themselves to explore and map out hierarchical (tree-like) data (e.g., organizational charts, category trees).
Syntax;

WITH RECURSIVE cte_name AS (
  -- Base case: Start with the root of the hierarchy
  SELECT column1, column2 FROM table WHERE parent_id IS NULL
  UNION ALL
  -- Recursive case: Reference the CTE itself to traverse child rows
  SELECT t.column1, t.column2 FROM table t
  JOIN cte_name ON t.parent_id = cte_name.id
)
SELECT * FROM cte_name;

Use Case: Exploring hierarchical or tree structured data (e.g., reporting structures or category hierarchies).

For example: "Alice is a manager at a Nairobi tech startup. Who are the employees in her team hierarchy?" requires a recursive CTE.

WITH RECURSIVE team_hierarchy AS (
  SELECT employee_id, name, manager_id FROM employees WHERE name = 'Alice'
  UNION ALL
  SELECT e.employee_id, e.name, e.manager_id FROM employees e
  JOIN team_hierarchy th 
ON e.manager_id = th.employee_id
)
SELECT * FROM team_hierarchy;

Result;

employee_id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Charlie	1
4	Diana	2
5	Eve	2

This result shows Alice, her direct reports (Bob, Charlie), and Bob’s direct reports (Diana, Eve)—the full team hierarchy.
The line JOIN team_hierarchy th ON e.manager_id = th.employee_id is where the recursion happens.
team_hierarchy is the CTE itself, we are joining the query to its own result. It takes every employee already in the CTE (e.g., Alice) and finds all employees who report to them. This repeats in a loop until no more reports are found.

When to use CTEs

Subqueries are useful when;

A query has multiple logical steps
You need to use the same data multiple times
Querying hierarchical data
You temporarily want to replace a view.
When replacing nested subqueries.

PART 3: SUBQUERIES VS CTEs

Here is a table comparison of subqueries and CTEs, based on architecture, readability and performance.

Feature	Subqueries	CTEs
Readability & Flow	Reads inside-out. Prone to creating "spaghetti code" if nested multiple times.	Reads top-to-bottom. Keeps code clean and highly modular.
Reusability	Single-use only. If you need the same data twice, you have to type the whole subquery again.	Highly reusable. Can be referenced multiple times in the main query
Recursion	Cannot handle self-referencing or hierarchical data.	Perfect for hierarchical/tree-structured data using Recursive CTEs.
Performance	Fast for basic filters. Correlated Subqueries can be slow on large datasets because they loop row-by-row.	Generally the same performance (optimizers treat standard CTEs and subqueries equally). However, CTEs can be temporarily cached in memory in databases like PostgreSQL for speed boosts.
Best Use Case	Quick, single-step math (Scalar), checking lists (`IN` / `EXISTS`), or creating simple derived tables.	Multi-step data transformations, chaining queries, building org charts, or acting as temporary views.

SQL is not just about writing queries, it’s about writing queries that others can easily understand.

When in doubt, choose the option that is easiest for the next developer to understand.

If you only remember one thing from this guide:

Use Subqueries for quick, single-step tasks and simple filters.
Use CTEs for multi-step queries, reusability, and keeping your code readable from top to bottom.

SQL is all about practice. Don’t get discouraged if subqueries and CTEs take a few tries to master, with consistent practice, they get and feel more intuitive.

If you found this guide helpful, don't forget to save it for later.

Do you prefer using subqueries or CTEs in your work?
Let me know in the comments below. Happy querying!

📘SQL JOINs Explained Simply - A Beginner's Guide

Sharon-nyabuto — Sun, 19 Apr 2026 03:47:31 +0000

Introduction

SQL JOINs are used to combine data from two or more tables into a single result based on a shared column.

They allow you to retrieve related data that is stored separately.
For example, a database might have customer names in one table and purchase details in another.

A Join bridges the tables, giving insights that one table alone may not provide.

In this article, we’ll break down the different types of SQL JOINs and when to use them. Specifically, we’ll cover:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
CROSS JOIN
SELF JOIN

🔗 INNER JOIN

An INNER JOIN combines records based on a related column and returns only matching rows from both tables.

Syntax

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Example; If the customers table and the orders table share a column - the customer_id, an INNER JOIN is used to identify the customers who made orders;

SELECT customers.customer_id, first_name, last_name, order_id
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

Result;

customer_id	first_name	last_name	order_id
1	john	doe	1
2	mary	wanjiku	2
3	peter	otieno	3
1	john	doe	4
4	lucy	njeri	5

An INNER JOIN can be used to combine more than two tables.

For example, to find out the customers, books they purchased and the corresponding order IDs;

SELECT orders.order_id, first_name, last_name, title
FROM customers
INNER JOIN orders 
ON customers.customer_id = orders.customer_id
INNER JOIN books b 
ON books.book_id = orders.book_id;

Result;

order_id	first_name	last_name	title
1	john	doe	Learning SQL
2	mary	wanjiku	Data Analytics Basics
3	peter	otieno	Python for Data Science
4	john	doe	Data Analytics Basics
5	lucy	njeri	Advanced SQL Queries

⬅️ LEFT JOIN/LEFT OUTER JOIN

The LEFT JOIN returns all records from the left table, and the matching records from the right table. Where no match exists, a NULL value is returned.

In a LEFT JOIN, the table specified in the FROM clause is treated as the left table.

Syntax

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

To show all the books and the customers who ordered them, a LEFT JOIN is used;

SELECT title,first_name,order_id
FROM books 
LEFT JOIN orders 
ON books.book_id = orders.book_id
LEFT JOIN customers 
ON orders.customer_id = customers.customer_id;

Result;

title	first_name	order_id
Learning SQL	john	1
Data Analytics Basics	mary	2
Python for Data Science	peter	3
Data Analytics Basics	john	4
Advanced SQL Queries	lucy	5
Machine Learning Intro	NULL	NULL

Notice that the row with Machine Learning Intro has NULL values because it was not ordered.

➡️ RIGHT JOIN/RIGHT OUTER JOIN

The RIGHT JOIN works like the LEFT JOIN, but it keeps all the rows from the right table and matching rows from the left table.

Syntax;

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

To show every book in the library, whether it was ordered or not;

SELECT books.book_id, title, order_id
FROM orders
RIGHT JOIN books
ON books.book_id = orders.book_id;

Result;

book_id	title	order_id
1	Learning SQL	1
2	Data Analytics Basics	2
3	Python for Data Science	3
2	Data Analytics Basics	4
4	Advanced SQL Queries	5
5	Machine Learning Intro	NULL

⚠️ Note: For both LEFT and RIGHT outer joins, the order in which you write your tables determines which one returns all of its values. You need to be keen about which table you put on the left and which goes on the right to get your intended result.

🔄 FULL JOIN/FULL OUTER JOIN

A FULL JOIN returns all rows from both tables.
As is the case in both the right and Left outer joins, for the rows that do not have a match, NULL is returned.

Syntax;

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

To retrieve all the customers and all the books, whether ordered or not, a FULL JOIN will be used;

SELECT order_id, first_name, last_name, title
FROM orders 
FULL JOIN customers  
ON customers.customer_id = orders.customer_id 
FULL JOIN books
ON orders.book_id = books.book_id;

Result;

order_id	first_name	last_name	title
1	john	doe	Learning SQL
2	peter	otieno	Python for Data Science
3	lucy	njeri	Advanced SQL Queries
2	mary	wanjiku	Data Analytics Basics
NULL	david	kimani	NULL
NULL	NULL	NULL	Machine Learning Intro

To retrieve only the matched or un-matched records, you can filter with the WHERE clause.

❌ CROSS JOIN

A CROSS JOIN returns the combination of each row from one table with each row from another table.

Syntax;

SELECT *
FROM table1
CROSS JOIN table2;

A CROSS JOIN does not have an ON clause, because we are not looking for matching rows.

If you have 2 tables; t-shirts color and t-shirt size tables, and wanted to find all possible combinations, a CROSS JOIN is used.

SELECT 
  tshirt_colors.color_name, 
  tshirt_sizes.size_name
FROM tshirt_colors
CROSS JOIN tshirt_sizes;

Result;

color_name	size_name
Black	Small
Black	Medium
Black	Large
Black	Extra Large
White	Small
White	Medium
White	Large
White	Extra Large
Navy	Small
Navy	Medium
Navy	Large
Navy	Extra Large

🔁 SELF JOIN

A SELF JOIN is used when comparing rows within the same table.

When using self joins, it is essential to use Aliases, to distinguish the two "roles" of the same table for exact execution.
AS is used to introduce an alias

Syntax;

SELECT column name (s) as alias
FROM employees A
JOIN employees B
ON A.column = B.column;

To best illustrate self joins, we will use the employees table below;

employee_id	name	department_id	manager_id	salary
1	Alice	1	NULL	50000
2	Bob	2	1	45000
3	Charlie	1	1	47000
4	Diana	3	NULL	60000
5	Eve	NULL	NULL	40000
6	Brian	2	4	50000
7	Joy	3	4	35000
8	Luke	5	1	45000

To show employees and their managers, we use a SELF JOIN as follows;

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m 
ON e.manager_id = m.employee_id;

Result;

employee	manager
Bob	Alice
Charlie	Alice
Brian	Diana
Joy	Diana
Luke	Alice

I hope this guide made joins less intimidating!
If you found this beginner's guide helpful, save it for later or share it with a friend who's just starting their SQL journey! Happy learning!!

Are there any joins I left out? Do you have any join tricks you want to share?
Let me know in the comment section below, I’d love to hear from you!

Applying SQL Concepts: DDL, DML, Filtering, and Data Transformation

Sharon-nyabuto — Sun, 12 Apr 2026 18:25:54 +0000

Structured Query Language (SQL) is the standard language used to interact with relational databases.
SQL is a command type language. Whether you want to create, delete, update or read data, SQL provides commands to perform these operations.
SQL commands are categorized based on their specific functionalities, and allow for the creation, manipulation, retrieval and control of data and database structures.
Among the command categories include;

Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Query Language (DQL)
Data Control Language (DCL)
Transaction Control Language (TCL)

In this article, we will look at Data Definition Language (DDL) and Data Manipulation Language (DML).

DDL (DATA DEFINITION LANGUAGE)

Data Definition Language (DDL) is a set of commands used to define and manage the structure of a database.
It focuses on creating and modifying database objects such as tables, schemas, and indexes.
DDL uses commands like CREATE, ALTER, and DROP, to determine how data is organized and stored within the database.
These commands affect the structure of the database and not the data itself.
DDL operations are often automatically committed, therefore changes take effect immediately without needing a manual COMMIT.

DML (DATA MANIPULATION LANGUAGE)

Data Manipulation Language (DDL) is a set of SQL commands used to manage and manipulate the data _stored within database tables.
Unlike DDL, which focuses on defining the structure of the database, DML is concerned with performing operations on the data itself.
Common DML commands include INSERT, UPDATE, and DELETE, which are used to add new records, modify existing data, and remove records from a table respectively.
In most database systems, DML changes are not automatically committed, meaning they can be rolled back if necessary before being finalized.

Below are some SQL commands and how they are used;

CREATE
DDL command used to create an object in a database, e.g a table

The syntax;
CREATE TABLE table_name(
column1 datatype constraints,
column2 datatype constraints,
column3 datatype constraints
);
For example, to create a schema in a database for a school, and add a table called students in the Schema you use CREATE

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) 
);

INSERT

This command is used to add a new record in a database.

The syntax;
INSERT INTO table_name (column1, column2, column3)
VALUES
(value1, value2, value3),
(value1, value2, value3);

To add student details to the students table created above, you use INSERT

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','2007-07-25','Form 4','Mombasa'),
(3,'Cynthia','Mutua','F','2008-11-05','Form 3','Kisumu'),
(4,'David','Kamau','M','2007-02-18','Form 4','Nairobi'),
(5,'Esther','Akinyi','F','2009-06-30','Form 2','Nakuru');

UPDATE
The update command is used to modify existing records in a database.

The syntax;
UPDATE table_name
SET column1 = value1,
WHERE condition;

In the students table created, if student id number 2, moved to Nairobi from Mombasa, UPDATE is used.

update students
set City = 'Nairobi'
where student_id = 2;

DELETE

This deletes one or more records from a database object.
It is used together with the _WHERE _clause to ensure that only specific rows are deleted.

The syntax;

DELETE FROM table_name
WHERE condition;

In the students table, if student_id 4 is no longer a student there, his records are removed using DELETE

delete 
from students
 where student_id = 4;

FILTERING WITH WHERE

The WHERE clause filters rows based on one or more conditions, so your query only modifies the records that match.
It is often used across SELECT, UPDATE, and DELETE statements.

The Where clause is used with logical and comparison operators such as equal to (=), greater than (>) and less than (<).

It can also be used with other operators including;

BETWEEN

Where clause is used with the BETWEEN operator, to filter records within a specified range.
When using between, the result will include both the start and end values of the range.

For example, to find students that scored between 55 and 95 marks from a results table, the values returned will include 55 and 95

select result_id,student_id,subject_id,marks,grade
from exam_results 
where marks between 55 and 95;

The WHERE clause can be used with the IN operator to filter records based on multiple values.
It makes queries shorter and easier to understand, instead of writing multiple conditions using the (=) operator combined with OR.

Syntax;

SELECT column1, column2
FROM table_name
WHERE column_name IN (value1, value2, value3);

To find students from different cities in our students table, we use IN

select first_name, last_name, city
from students 
where city in ('Nairobi','Nakuru','Mombasa');

LIKE

WHERE clause is used with the LIKE operator when searching for a particular pattern.
When using LIKE, a wildcard is used;
‘%’- signifies any number of characters including 0.
‘_’ wildcard is used for a specific number of characters.

Syntax is;
SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';

For example, to find the list of subjects that contain the word 'studies' in a subjects table, LIKE is used.

select subject_name
from subjects
where subject_name like '%Studies%';

CASE WHEN STATEMENT

The CASE WHEN statement is used to add conditional logic inside queries, similar to an if-else structure.
It checks conditions one by one and returns a value as soon as a matching condition is found.
It helps categorize data dynamically and allows creation of new categories based on the specific conditions within a query.

The syntax is;

select column name,
case
when condition1 then 'result 1'
when condition2 then 'result 2'
else 'default result
end as new column_name

For example, CASE WHEN can be used to group marks into a new column called Performance, in the results table, as shown below.

select result_id, student_id, subject_id,marks,
    case
        when marks >= 80 then 'Distinction'
        when marks >= 60 then 'Merit'
        when marks >= 40 then 'Pass'
        else 'Fail'
    end as Performance
from exam_results;

REFLECTION

Learning Databases and SQL can seem difficult, but once you get into it, it becomes interesting especially if you are curious to see how you can create and modify your database along the way.

The most interesting thing with SQL is that there are some rules for consistency and functionality across databases. Some of those rules are;

Always end your statements with a semicolon (;)
SQL commands and key words such as SELECT and INSERT are not case-sensitive.
Spaces and new lines are allowed for better readability.
Do not use SQL keywords as names, if you have to, write them in quotes.
To include comments in your script, begin single line comments with --- and for multi-line comments you use /* at the beginning and */ at the end.
Commands like DROP and DELETE should be used with caution as they could result in permanent data loss without proper use of transactions or backups.

A Comprehensive Guide to Publishing and Embedding Power BI Reports on the Web with IFrames

Sharon-nyabuto — Mon, 06 Apr 2026 23:25:01 +0000

Microsoft PowerBI is one powerful tool when it comes to analyzing and visualizing data.

Power BI allows users to connect to different data sources, transform data, build interactive dashboards and share insights with others.

I have spent the last two weeks learning how to build queries, clean data on power query, use DAX functions, data modelling and relationships, as well as building dashboards and reports in Power BI.

Reports and dashboards in Power BI
Reports and dashboards are important for visualizing data.

While dashboards **are often a one-page summary of key insights, **reports are multiple pages and with a detailed analysis.
In the day to day operations, reports have to be shared with managers, teams and other stakeholders.
Instead of sending ‘.pbix’ files, which require Power BI desktop, embedding reports enables viewing them in browsers, interaction with filters and accessing live and updated data.

Embedding reports is a necessary move from static reports, to interactive web-based dashboards, that no one has a challenge accessing.

In this article, we will learn step by step how to;

Create a Workspace in Power BI Services
Upload and Publish a Power BI report from Power BI desktop
Generate an Embed code
Embed the report on a web page using an iframe.

Brief Overview of Power BI and the Publishing Process
Power BI consists of two components;

- Power BI Desktop– Where the data is loaded, cleaned and transformed, relationships and visuals are built and reports designed.
- Power BI Service- Where the reports are published, workspaces and embedded links are created, dashboards are shared and work is done in collaboration with others.

Creating a Workspace in Power BI Services

A Workspace is an online folder, that is dedicated to storing and organizing your dashboards and reports.

The default workspace is always ‘My Workspace’ which is often just for personal testing, and as a result, you cannot get a working public embed code if you generate your report here. Therefore, do NOT use My Workspace.

How to create a Workspace step-by-step;

Open your browser and go to https://app.powerbi.com
Log in using your Microsoft account
On the left menu, click Workspaces
Click the New Workspace button at the top right
Enter a Workspace Name, e.g. Electronics Sales
Click Apply

Once this is complete, your Workspace is ready. You can now publish your report.

Uploading and publishing a Power BI report from Power BI desktop.

Now we will upload the .pbix file to the workspace we just created.

How to publish a report to the Workspace step-by-step;

Open your report in Power BI desktop.
On the top right corner of the home ribbon, click ‘Publish’.
Select the workspace you just crated, (e.g. Electronic Sales) from the list of Workspaces that Power BI will display.
Click Select
Power BI will now upload your report. It may take a few seconds to 2 minutes, so give it some time.
You will see a message that shows you have successfully published your report to Power BI Services.

Until you have confirmed that the report has been published, do not close your Power BI Desktop.
If you close and there’s something wrong, you may have to start the process all over again.

To confirm if the report has been uploaded to the Power BI Service;

Go back to https://app.powerbi.com
Open your Workspace and refresh. You should see your report

Generating the Embed Code.

Embedding allows the report to be displayed inside a website.
Power BI provides an embed code, that can be copied and pasted in a website.

How to generate a code, step-by-step;

Open your report in the Workspace.
Click ‘File’ at the top of the page.
Select ‘Embed Report’, then click on Website or portal
Click on create Embed code.
Then click Confirm.
Copy the embed code to be used later.

You will now see a pop-up window with the link to embed, and a box with the embed code needed, this is the only correct place to get your embed code from.

The embed code, is an Iframe (inline frame) which is a HTML tag that allows you to display another webpage inside your webpage.

A typical iframe will look like this;

<iframe title="Electronic_Data" width="1140" height="541.25" src="https://app.powerbi.com/reportEmbed?reportId=1e766837-7d75-4a37-bc3d-3e0ea1f56073&autoAuth=true&ctid=c95984e8-6a70-4512-a440-1c79bca9cc37" frameborder="0" allowFullScreen="true"></iframe>

The width and height parts of the code are flexible, and you can change those to whatever you like. All the other parts however, should remain exactly as they are.

A common mistake often made is using the Share button to embed a report.
The share button generates links accessible only to those that have accounts in your organization. It will therefore not generate an appropriate embed code.

Embedding the report on a webpage

Embedding codes allow integration into websites.

How to do it, step-by-step;

Open any plain text editor, such as VS code or Notepad. Do not use Microsoft Word.
Create an empty .html file.
Add basic HTML structure;

html
<!DOCTYPE html>
<html>
<head>
<title>Title of report e.g. Electronic Sales Data</title>
</head>
<body>
<h1>Heading e.g. Electronic Sales Dashboard</h1>
<Power BI iframe goes here>
</body>
</html>

Click file > Save as, and name the file Index.html

That is it, if you double click on the saved html file, it will open on any browser and you can load your report, and see all the interactive report running perfectly.

Troubleshooting common Issues

If something is not working, it could be one of the following;

Problem: Report requires users to log in
Fix: New workspaces are sometimes set to private by default. Go back to your workspace, click manage access, and add Everyone as Viewer.

Problem: Blank Screen after the publishing and embedding
Fix: Give it time. This is a normal occurrence, as Power BI can take a few minutes for the embed code to work, after publishing.

Problem: The report works on your end but no one else can see it.
Fix:It is possible you used ‘Share’ Button, when embedding, instead of generating the embed code. You might need to generate the embed code anew.

Key Insights and Conclusion

Now that you have learnt this process, you can go ahead and use it for your student projects, portfolios, and for work, absolutely free.

You do not need a paid premium license to embed your Power BI reports.
Every singe feature of your report will work exactly as it does on Power BI Desktop, the slicers, filters and interactions will work just fine inside the Iframe.

In conclusion, you could build the best Power BI report, but if you cannot publish it and share it with other people, it's use may not be maximized, and a lot of insight could be lost.

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

Sharon-nyabuto — Sat, 04 Apr 2026 17:24:06 +0000

Ever tried to build a puzzle where half the pieces were in a different room?
That is how it feels working with raw data before understanding Data Modelling.

In the real world, data is not stored in one sheet, it is scattered across dozens different files. A major chain for example, has separate files for their stores, customers, sales and products. If you tried to cram all that information into one massive table, managing it would be difficult to manage!

So what is Data Modelling?

Data modelling is the process of organizing data into structured tables and defining how those tables relate to each other. In professional environments, data is organized into 2 main types;

Facts tables - These store measurable events (the metrics). A sales table for instance, is a fact because it records every transaction, indicating how much was sold and when. Fact tables are usually long and full of numbers.

2.Dimensions tables - These provide descriptive contexts, such as customer names, color and product category. They are relatively smaller, and give meaning to the facts.

Tables are connected using Joins (in Power Query) or Relationships (in the model view).

In this article, we will look at;

SQL Joins in Power Query
Power BI Relationships
Schemas
Role Playing Dimensions
Common Modelling Issues to watch out for

SQL JOINS IN POWER QUERY

Before data gets to the model, it has to be cleaned, this happens in Power Query using Joins.

A Join is a way of combining data from two tables based on a common column. It is a rule, that tells the computer how to merge two tables into one physical table.

Our scenario; An online retail store with two lists;

• Table A (Customers) – With Customer Name, Customer ID and Customer Location
• Table B (Orders) - Order ID, Customer ID, Order Amount, Order Date

There are 6 main kinds of Joins;

How to do it Step-By-Step (The steps are common for ALL joins.)
In Power Query:

Click your main table (e.g. Customer List)
Click Merge Queries in the Home Ribbon
Select your second table, (e.g. Orders List in our case)
Click on the matching columns in both tables (e.g. Customer ID)
Choose your Join Kind from the dropdown.

POWER BI RELATIONSHIPS

Relationships define how tables are connected in a data model, so they can interact with each other in reports. Relationships connect tables while keeping them separate, and are created in the Model View.

Cardinality (The Match Ratio)

Cardinality describes the nature of the relationship between two tables. It answers the question How many

**_One-to-Many (1:M)_**

One record in one table relates to many records in another. For example; where one customer has multiple orders or where one bank account has multiple transactions.
Many-to-Many (M:M)
Multiple records in both tables relate to each other. These can cause double-counting and incorrect numbers. Use bridge tables if necessary.
One-to-One (1:1)
Where one record matches exactly one other in another table. This is pretty rare for relationships, and sometimes means that two tables should be merged instead.

Direction and Activity
Cross filter directions: Determines how filters flow between tables.

Single: Filters flow one way, from dimension to fact. A good example is how filtering customers will affect Orders, but orders won’t filter customers.
Both: Filters flow both ways. It is useful but can easily create confusion or incorrect results.

Active Vs Inactive Relationships
In Power BI, you can only have ONEactive relationship between two tables at a time.

Active filters are the connections that are always on.

Inactive filters are the connections that are always off, unless they are required for a certain calculation.

PowerBI allows you to have one active relationship, and one or more inactive relationships at any given time.

How to do it step-by-step

Go to model view
Click a column in your dimension table and drag it to the matching column in your fact table; A line appears
Double click the line to check Cardinality and Direction
You can make the relationship active or inactive

Differences Between Joins and Relationships

SCHEMAS

Now that we know how to connect tables, it is important to also know how to organize them. In data modelling, tables follow a layout, i.e. a Schema.
Every good model will separate data into either fact tables or dimensions tables.

There are three main table designs;

A Flat Table (DLAT)

This is where both your fact and dimension tables are in one place, as would appear in Excel.
While this could be used for one off analysis or very small datasets, it is not advisable on big models, because as data grows, the table becomes very slow and very hard to manage.
It also has a higher probability of duplicate data.

How to get it;
Use the Merge Queries in Power Query to merge all your tables together into one single, massive table.

The Star Schema
This is a model with one fact table at the center, and dimension tables surround it.
It is the industry standard, and results in a clean, fast and simple layout.
It is easy to understand and hard to break, and preferred in over 90% of reports.
It is advisable to use this, unless you have a reason not to.

How to get it;

Load your Fact Table and Dimension Tables separately into Power Query.
Enter Model View: Click the _Model View _icon on the far left sidebar..
Click and drag a unique ID from your Dimension table (e.g., Product ID) onto the matching column in your Fact table.
Drag the Fact table to the center and pan out your Dimension tables around it.
Check for cardinality.

Snowflake Schema

This is a star schema, whose dimensions are further broken down to other dimensions. This is called normalization.

It is used when there are complex relationships such as Country>County>Sub-county.
While it can save on space due to less repetition, it becomes more complicated and reports are slower.

How to get it;

Start Like a Star: Follow the steps above for your main Fact table.
Split a Dimension for example instead of loading Products directly into the Fact link, load Products and then a separate Categories table.
Connect hierarchically, i.e. Fact → Products, then Products → Categories.

ROLE PLAYING DIMENSIONS

Sometimes a single dimension table has to play more than one role. A great example is a date table, that you use to filter both Order dates, and Shipping Dates. In this case, the problem is handled using ‘inactive relationships.
Managing relationships this way allows for a clean model, as it lets you slice data differently, and there's no need to clone the table twice.

COMMON MODELLING ISSUES TO WATCH OUT FOR.

As you start building, here are a few issues to avoid;

Many-to-Many relationships (M:M) - This happens when you try to connect two tables that don't have a unique "one" side. It causes Power BI to get confused and often results in numbers that look correct but are actually completely wrong.
Blanks in Keys– If your ID column has empty cells, they could break the relationship silently. That is why you should clean your data in Power Query before loading.
Flat tables- Don’t try to keep everything in one table just because it feels like excel, you may have a hard time navigating or getting anything done.

How Excel is used in Real World Data Analysis

Sharon-nyabuto — Mon, 30 Mar 2026 06:24:23 +0000

A month ago, I challenged myself to learn data analysis, so I went shopping across the internet for a good place to start and the consensus was unanimous; check out good old Excel.

So what exactly is Excel?

Far from just being a digital ledger, excel is a powerful data engine. It is used in turning observations into trends in Field Research, helps businesses track sales and predict growth while in finance it is used to ensure every cent is accounted for.

Learning Excel felt very intimidating at first, the aggregate and lookup functions were all too new to me! Once I stopped being anxious however, I have been enjoying the journey of discovering this ability excel has! Here are a few things I have learned and use often;

COUNTIF () FUNCTION

This simply counts how many times something appears in your list. In its simplest form, it asks:
=COUNTIF(Where do you want to look?, What do you want to look for?)

IF() FUNCTION

This operator allows excel to ‘think’. It checks if a condition is true, and gives you one result if it is, and another if it is not.

Syntax =IF(logical_test, value_if_true, value_if_false)

You can have as many conditions as you need, all you have to do is properly nest them!

=IF(X3<2, "Poor Score",IF(X3<3,"Average Score",IF(X3<4,"Good Score","Excellent Score")))

Lookup Functions

In the real world, all the data will not necessarily be in the same place always. The sales and customer names may be in different sheets, and the lookup functions are the connectors!

VLOOKUP

It searches down the first column, to find a value and pulls information from a different column on the right.

 Syntax: =VLOOKUP(lookup_value, table_array, col_index_num,[range_lookup])

Translation:=VLOOKUP(What are you looking for?, Where is the whole table?, Which column number has the answer?, FALSE)

The FALSE at the end ‘tells’ excel that you want a perfect match

=VLOOKUP(10010, A2:F13, 5, FALSE)

(This simlpy says; Find ID 10010 in this table, and give me the value from the 5th column. The result will be 82107)

Because it relies on a fixed column number, adding a new column to your table can break its logic. If you insert a new column before Column 5, it becomes Column 6, but VLOOKUP will still look at Column 5! Since the 5th column is no longer where the salary ‘lives’, it might return the wrong data, without giving you any error message.

XLOOKUP

A relatively newer version that beats these limitations is the XLOOKUP, and it does not require one to count the columns! XLOOKUP works, regardless whether other columns are added or deleted.

Syntax: =XLOOKUP(Lookup_Value, Lookup_Array, Return_Array)

=XLOOKUP("P-102", A:A, C:C)
(This simply says "Find P-102 in Column A, and give me the corresponding value from Column C."

XLOOKUP only works on Office 365, Excel 2021, or later.

(INDEX + MATCH)

What if you’re working on an older model and still need to bypass VLOOKUP's limitations? You use INDEX and MATCH.

MATCH finds the position of a value, and INDEX grabs the value from that position.

Syntax:=INDEX(Return_Column, MATCH(Lookup_Value, Lookup_Column, 0))

(Note: We use 0 for an exact match every time!)

=INDEX(G2:G13, MATCH(10010, A1:A13, 0))

Translation: Find ID 10010 in Column A, then give me the value from the matching row in Column G.

This combo is powerful because unlike VLOOKUP, it can look to the left or right without breaking!

PIVOT TABLES

Before embarking on excel, these used to look like Martian tech to me! Now, I realize they are effective summary machines! In a few clicks, you have a clean summary of your data. They take data and tell me the averages, totals and counts in less than a minute!

You highlight all your data.
Click Insert
Select Pivot Table.
Drag a field to Rows.
Drag a different field to Values.

And just like that, you have every sum, average, count that you need.

REFLECTION

It’s only been two weeks, but my relationship with data has shifted. There is a massive sense of empowerment in looking at a messy sheet and having a good idea how to fix it, realizing i have the tools to make my data talk to me!

Since I’m still very much in the learning phase and have only just scratched the surface, I’m curious:

What was that one surprisingly simple Excel trick that completely broke your brain when you first saw it?

If you're a seasoned pro, what is a golden tip you wish you knew when you were just starting out in excel?

Please drop your tips, shortcuts, or aha! moments, I’m all ears and ready to learn!

DEV Community: Sharon-nyabuto

Making Decisions Within Decisions in Python

Taking Conditional Logic Further with Nested if Statements

Nested if Statements

Syntax

Bonus Tip: f-Strings

Teaching Python to Make Decisions

Understanding if, elif, and else

So what is a conditional statement?

Comparison Operators

The IF statement

The ELSE statement

The ELIF statement

Putting Conditionals into Practice

Your turn, try it yourself!

What's Next?

Python for Data Analysts: Getting Started

Understanding what Python is, why it matters, and the concepts every beginner will encounter.

So what is Python and why does it matter in Data analysis?

Why does Python matter?

Common Terms You'll Encounter in Python - Getting familiar with the Python Environment

My first few lines of Python

Biggest lessons so far

Final thoughts

Cleaning Messy Phone Numbers in PostgreSQL Using REGEXP_REPLACE

Introduction

First, What Even Is regexp_replace?

Understanding the Special Characters in Patterns

The ^ character

The \ character (backslash)

Square brackets [ ]

The + operator

The $ character

Putting them together: ^[0-9]+$

Our Problem: What Does the Messy Data Look Like?

Step 1: Replace +254 or 254 at the Start With 0

Step 2: Remove Any Character That Is Not a Digit

Step 3: The WHERE Clause — Only Update What Needs Fixing

Putting It All Together

The SELECT query (to preview your cleaned data before changing anything)

The UPDATE query (to actually apply the changes)

Quick Recap

Final Thoughts

SQL Subqueries vs. CTEs: A Guide to Writing Better Queries

PART 1: SUBQUERIES

Subqueries Categorized by Output

Scalar Subquery

Multi-row Subquery (List Subquery)

Table Subquery (Derived Table)

Subqueries Categorized by Execution

Correlated Subquery

Uncorrelated (Independent) Subquery

When to use Subqueries

PART 2: The CTE (COMMON TABLE EXPRESSION)

The Basic Syntax of a CTE

When to use CTEs

PART 3: SUBQUERIES VS CTEs

📘SQL JOINs Explained Simply - A Beginner's Guide

Introduction

🔗 INNER JOIN

⬅️ LEFT JOIN/LEFT OUTER JOIN

➡️ RIGHT JOIN/RIGHT OUTER JOIN

🔄 FULL JOIN/FULL OUTER JOIN

❌ CROSS JOIN

🔁 SELF JOIN

Applying SQL Concepts: DDL, DML, Filtering, and Data Transformation

DDL (DATA DEFINITION LANGUAGE)

DML (DATA MANIPULATION LANGUAGE)

FILTERING WITH WHERE

CASE WHEN STATEMENT

REFLECTION

A Comprehensive Guide to Publishing and Embedding Power BI Reports on the Web with IFrames

Creating a Workspace in Power BI Services

Uploading and publishing a Power BI report from Power BI desktop.

Generating the Embed Code.

Embedding the report on a webpage

Troubleshooting common Issues

Key Insights and Conclusion

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

So what is Data Modelling?

Nested `if` Statements

First, What Even Is `regexp_replace`?

The `^` character

The `\` character (backslash)

Square brackets `[ ]`

The `+` operator

The `$` character

Putting them together: `^[0-9]+$`

Step 1: Replace `+254` or `254` at the Start With `0`

Step 3: The `WHERE` Clause — Only Update What Needs Fixing