DEV Community: Daniel Mutwiri Mbabu

Data Cleaning Broken Down

Daniel Mutwiri Mbabu — Wed, 07 Oct 2026 08:01:19 +0000

Data Cleaning: The Bane of Our Existence and the Reason Analysis Works.

Introduction

Why the duality, you might ask?

There is a strange irony in the world of data: the work that nobody wants to do is often the work that matters most. Data cleaning is arguably the bane of every data professional's existence. We spend hours chasing missing values, hunting down duplicates, fixing inconsistent dates, correcting misspelled categories, and asking why a column that should contain numbers is suddenly full of text. It is repetitive, frustrating, and rarely the glamorous part of data analysis. Yet, beneath all that frustration lies its greatest importance: clean data is the foundation of trustworthy analysis. The most sophisticated dashboard, the most advanced SQL query, or the most powerful machine-learning model cannot compensate for fundamentally flawed data. Thus, before any clean or meaningful insight, data cleaning is inevitable.

1. Then, What Is Data Cleaning?

Data cleaning is the process of detecting, correcting, removing, or appropriately handling inaccurate, incomplete, inconsistent, duplicated, or improperly formatted data. It's literally cleaning data. Only that, in this case, we don't use water and soap. We use logic, formulas, and repetitive, annoying little adjustments here and there.

What makes data dirty? There are several potential problems when dealing with any data; I will name a few:

Different Casing for the same item.

Dates in different formats.

Missing values

Ambiguous values, such as a negative sale, which may be invalid or may represent a legitimate refund. Or an end date that comes before the start date.

Duplicates, and again I say duplicates.

Inconsistent data types... I am already tired. In a nutshell, there are so many reasons a data set can be dirty.

2. The importance of Cleaning Data

I will quote the famous principle:

Garbage in, garbage out.

If incorrect data enters an analytical process, even a sophisticated model can produce misleading results.

Data cleaning is important because it improves:

Accuracy - Corrects erroneous values and records.
Consistency - Ensures that the same concepts are represented in the same way.
Completeness - Identifies missing information and determines how it should be handled.
Reliability - Makes analytical results more trustworthy.
Efficiency - Clean and standardized data is easier to query, transform, visualize, and analyze.
Decision-making - Business decisions based on inaccurate data can result in incorrect conclusions, financial losses, operational problems, or poor strategic decisions.

3. How do we clean data becomes the next question.

Tools will differ, but I would like to show the general data cleaning process. Whichever tool you may be using, these steps form the skeleton of data cleaning.

I would follow these steps for any data cleaning task:


1. Understand the data (Context) 
2. Profile the data  
3. Identify the problems 
4. Decide on a uniform and standard way of correcting the data  
5. Clean the data
6. Validate the data 
7. Document

Step 1: Understanding the Data

Before changing anything, we need to understand what the data represents. Understand the context of the data and the final purpose of cleaning the data. This step seems unnecessary, but it's very crucial. It will help you make the right decisions, know which data to keep, understand the dynamics of the purpose of analysis, and even make your work easier

Here are a few questions you can ask to understand the data:

What does each row represent?
What does each column represent?
What is the source of the data?
What are the expected data types?
What values are considered valid?
What are the business rules?
Which fields are mandatory?
What constitutes a duplicate?
Are negative values allowed?
What date range should the data cover?

Step 2: Profile the Data

Data profiling means examining the dataset before cleaning it. This is a little different from Step 1. The main goal of this step is to categorize the data for a uniform kind of cleaning.

Look at:

Number of rows
Number of columns
Data types
Missing values
Unique values
Duplicate records
Minimum and maximum values
Average and median
Frequency distributions
Outliers
Invalid values

For example, if an Age column contains -5 and 150, this should immediately raise questions. But also does not mean you automatically delete them.

Note: A value that looks unusual may actually be valid.

Step 3: Identify Data Quality Problems

Identify common data-quality problems systematically. They include:

Missing values
Duplicate records
Incorrect data types
Inconsistent formatting
Invalid values
Outliers
Spelling inconsistencies
Incorrect dates
Leading and trailing spaces
Inconsistent categorical values
Broken relationships between tables
Incorrect units
Calculation errors

Step 4: Decide on a uniform and standard way of correcting the data

Missing Values

How do you treat unavailable information?

Rule of Thumb: Different data sets with missing values may need different handling of missing data.

A Missing value can be in the form of: NULL, Blank, Empty string, N/A, NA, Unknown,-0

Please note carefully that these CANNOT be treated as equivalents. For Instance: 0 means the value is actually zero.

Common missing values handling approaches include:

1. Remove

Remove records when the missing information makes the record unusable.

2. Impute

Replace the missing value using a reasonable estimate such as the mean, the median, or the mode for text. But always remember to document the logic used.

Mean - for data with a normal distribution (no outliers)
Median - for data with outliers(extremes)
Mode - the most recurring text

For example:

10, 12, NULL, 14

and

10, 1000, NULL, -20

The missing value could potentially be replaced with the mean or median, respectively, depending on the situation.

3. Use a default value

Some of the default values that can be used are: NULL, "Unknown", "Not Provided"

Ensure the default value is uniform across a common set of data.
.

4. Leave it missing

Sometimes the best decision is to keep the missing value. The correct approach depends on the business meaning and analytical objective.

Duplicate Data

This is when the same record appears more than once.

Example:

Order ID	Customer	Sales
1001	John	500
1002	Mary	700
1001	John	500

If each order should have a unique Order ID, the second occurrence of order 1001 may be a duplicate.

It is, however, important to investigate duplicate-looking records before deleting.

For example:

John | Kenya | 500
John | Kenya | 500

could represent:

A duplicate transaction
Two legitimate transactions
Two separate orders with missing identifiers

Therefore:

Never remove duplicates simply because two rows look identical. Understand the business rules first.

Standardizing Text

Text inconsistencies are extremely common, especially casing problems when representing the same category. For example: Kenya, kenya, KENYA, Kenya

Cleaning text involves:

Removing leading spaces
Removing trailing spaces
Standardizing casing (Lower/Upper/Proper cases)
Correcting spelling
Standardizing abbreviations

Data Types

Every field should have its appropriate data type. An Integer for an Integer, Decimal for Decimal...

Correct data types are essential for:

Calculations
Sorting
Filtering
Aggregation
Joins
Time-series analysis

Consider this case:

"1500"

Although it looks like a number, it may actually be stored as text. This creates problems when calculating:

1500 + 500

Similarly:

"2026-05-01"

may be stored as text instead of a date.

Date and Time Cleaning

Dates are particularly problematic because different systems use different formats.

For example:

01/05/2026
2026-05-01
2026/05/01
May 1, 2026

These may represent the same date.

However:

01/05/2026

could mean:

1 May 2026
January 5, 2026

depending on the regional format.

Dates should therefore be standardized and converted to an actual date data type whenever possible.

Outliers

An outlier is a value that is unusually different from other observations.

Suppose sales values are:

50,000 is potentially an outlier.

But an outlier isn't automatically an error.

It could represent:

A large legitimate transaction
A special customer
A data-entry error
Fraud
A one-time event

Therefore:

Identify outliers; don't automatically delete them.

Step 5: Data Validation

After cleaning, the data should be validated.

Ask:

Are duplicates gone?
Are required fields populated?
Are data types correct?
Are values within acceptable ranges?
Are categories standardized?
Are dates valid?
Are relationships between tables correct?
Did the cleaning process accidentally remove valid data?

Validation ensures that cleaning didn't create new problems.

Conclusion

Data Cleaning is the foundation we build all our data systems upon. Get it right, and the pipelines, the analytics, and the business decisions become seamless and valuable; get it wrong, and we miss the mark!

The Paradox of Git and GitHub: Version Control

Daniel Mutwiri Mbabu — Wed, 12 Aug 2026 13:40:18 +0000

Often we get caught in the crossfire of big technological terminology, and we have so little to do but get blasted. For me, and I bet many others, Git and GitHub must be one of those! But for how long can we sit by and feel absolutely clueless, yet we can armour up at our own will? That's what I intend to do with this article. Let's break down this paradox together!

So What is Git and GitHub?

Are they branches of the same Franchise that chose different names? Certainly not.

Term	Definition
Git	It's the tool that tracks your changes to your projects and runs locally
GitHub	It's a website that stores your Git history in the cloud for Collaboration

Git

Git is a version control system that runs on your local machine, tracking every change you make to your code over time. It allows you to go back to any previous state, and lets multiple people work on the same project at the same time without overwriting each other.

Without Git, teams share code by emailing files or editing the same folder. Both approaches lead to the same outcome, but in this case, someone's work gets lost. Git solves this permanently.

Every developer has the full project history on their own machine - not just the latest version.

GitHub

GitHub is the website that stores your git history/git repositories in the cloud. (It does not run locally on your machine). It stores repositories online to enable collaboration.

Mainly used to share code, review changes, manage issues, and Continuous Integration and Continuous Delivery (CI/CD)

In a nutshell
Git is like MS Word that tracks all the changes, while GitHub is the Google Drive where you upload the Word document for collaboration.

Common terminologies used in the Git/GitHub Environment...

Term	What it means
Repository (repo)	It's a Git Project Folder that Git is tracking.
Commit	This is a saved snapshot/state of your project at a specific point in time.
Branch	It's a parallel version of the project - you experiment here without touching the main code.
Remote	It's the online stored copy of the repo, usually on GitHub.
Push	It's to send your commits/changes to GitHub
Pull	It's to bring others' commits down to your local machine.

So how do you get started on Git and GitHub?...

Installing Git

Linux (Ubuntu)

Run the commands below:

sudo apt update
sudo apt install git -y
git --version          # confirm: git version 2.x.x

Windows

Download from https://git-scm.com/download/win

Always run Git commands inside Git Bash CLI, not on other CLIs such as Command Prompt or PowerShell.

macOS

xcode-select --install    # installs Git via Xcode CLI tools
# OR if you have Homebrew:
brew install git

Note: After the installation. A first-time configuration is necessary to connect your local Git to GitHub (Remote).

Opening Git Bash CLI for Windows and first-time configuration.

Create a New Folder (Location? Upto you)

Right-click the newly created Folder to select more options if you cannot view "Open Git Bash Here".

The Git Bash will open, and you have to initialize Git to do any Configurations. To initialize Git, run the command: git init

Assuming you already have a GitHub account set up, run these commands in Git Bash for the first-time configuration.

git config --global user.name  "Your Full Name"
git config --global user.email "you@example.com"

Where: "Your Full Name" = "GitHub Account Name"
"you@example.com" = "Email address to the GitHub Account Name"

Creating Your GitHub Account

Go to https://github.com and click Sign up
Choose a professional username
Verify your email address
Add a profile picture and a short bio

Quick Question

After configuring Git, how does the actual information flow between local (Git) and remote (GitHub)?

Answer.

SSH / HTTPS

The Communication Channels.

SSH (Secure Shell Protocol) uses a key pair to prove your identity without ever sending a password across the network.
HTTPS is an application layer protocol responsible for securely transporting the Git communication between your computer and GitHub.

SSH vs HTTPS.

Which one do I use? Both are secure communication methods. You should not think SSH is secure and HTTPS is not. With HTTPS, GitHub uses a personal access token (PAT) or a credential manager rather than your GitHub account password.

Ideally:

Use SSH if it's your own machine, and you use GitHub regularly.
Use HTTPS if you're on a machine/network where SSH setup is inconvenient, restricted, or temporary.

SSH	HTTPS
Authentication Automatic - key pair handles it silently	Requires a Personal Access Token (not your password - GitHub removed that in 2021)
Setup effort 15 minutes once per machine	Token needed for each machine; expires and must be refreshed
Daily use Push and pull with no prompts at all	Prompted for credentials unless you store the token
Best for Personal machines, servers, daily work	One-off cloning; environments where SSH port is blocked

THE CORE FLOW

                   YOUR COMPUTER
            ┌─────────────────────┐
            │                     │
            │   Working Directory │
            │          ↓          │
            │        git add      │
            │          ↓          │
            │   Staging Area      │
            │          ↓          │
            │       git commit    │
            │          ↓          │
            │   Local Repository  │
            │                     │
            └─────────┬───────────┘
                      │
                   git push
                      │
                      ▼
            ┌─────────────────────┐
            │       GitHub        │
            │                     │
            │ Remote Repository   │
            └─────────────────────┘
                      │
                   git pull
                      │
                      ▼
            ┌─────────────────────┐
            │   Local Repository  │
            └─────────────────────┘

To break this down,

1. Create/change a file locally
Suppose your GitHub repository is:

JCARS

You clone it onto your computer:
git clone git@github.com:Danielmutwiri/JCARS.git

You will have all the files related to JCARS, e.g., the README.md file, etc

Git creates a local repository containing the project's history and also remembers where the remote repository is.

To verify,
git remote -v

You will see

origin git@github.com:Danielmutwiri/JCARS.git (fetch)
origin git@github.com:Danielmutwiri/JCARS.git (push)

Origin is the conventional name given to a GitHub repository.

2. You modify a file
Suppose you modify:

README.md

Git will see README.md (Modified), but GitHub knows nothing about this change yet. Changing a file does not automatically change your Git repository or GitHub.

3. git add moves the change to staging

git add README.md

The change moves from the working directory to the staging area
It's like saying:

"Git, I want this particular change to be included in my next commit."

To verify, use this command:

git status

4. git commit stores the change locally
git commit -m "Update README.md"

Git creates a commit in your local repository. But GitHub still hasn't received the commit yet.

5. git push sends your commits to GitHub
git push origin main

This is where the actual communication with GitHub occurs. Git transfers the necessary Git commit information to the remote repository. GitHub then updates its remote branch, and now they are in sync.

6. What happens if some other developer changes GitHub?
On their computer, a change has happened, say 'change A'. Also, GitHub is updated, but your local PC is lagging. Use git fetch/git pull to retrieve information from GitHub and get your local PC up to speed.

git fetch origin

Git will contact GitHub and retrieve information about changes.

fetch is relatively safe: it downloads information but does not automatically modify your working files.git pull however, goes one step further.

git pull origin main

Git will perform: git fetch + git merge. Your local branch is then brought up to date. Modern Git can also be configured to use rebase instead of merge.

Finally

GitHub is not the "master copy" of Git itself. Your local repository contains the full Git history too. GitHub is a remote repository that provides collaboration, backup, hosting, and pull requests. Now you have the power to go and do some version control.

Connecting Power BI to a SQL Database

Daniel Mutwiri Mbabu — Thu, 23 Jul 2026 08:53:03 +0000

Introduction

Oftentimes we need to clean, model, and report insights from data stored in databases. Sometimes the data can be stored in a local SQL database or in a cloud-based SQL database. Today we will dive into the nitty-gritty of this endeavor using Power BI as your reporting tool and a SQL database as our storage.

Understanding Data Connectivity Modes

To connect a reporting and analysis tool such as Power BI to a SQL Database, understanding how it connects to the database is crucial. There are three primary connection modes. Each serves a different purpose depending on your reporting needs.

Import Mode

Direct Query

Live Connection

1. Import Mode

How it works
Power BI imports all selected data into its in-memory VertiPaq engine.
Reports query the imported data instead of the SQL database.

Ideal when

Data changes daily or a few times per day.
You need high-performance dashboards.
Complex calculations and relationships are required.

Example
A sales database updates overnight, and management reviews reports each morning.

2. DirectQuery Mode

How it works
No data is stored in Power BI (except metadata and optional cache).
Every filter, slicer, or visual generates a SQL query against the database.

Ideal when

Data changes every few minutes.
Databases contain hundreds of millions of rows.
Data cannot be copied due to compliance or storage constraints.

Example
A call center dashboard showing live customer support activity.

Performance Tips

Create indexes on frequently filtered columns.

Use star schema design.

Minimize calculated columns in Power BI.

Reduce visual complexity.

3.Live Connection

How it works
Instead of connecting directly to SQL, Power BI connects to:

SQL Server Analysis Services (SSAS)
Azure Analysis Services (AAS)
Another Power BI semantic model

The semantic model handles calculations, relationships, and security.

Ideal when
An organization has a centralized BI team managing enterprise models.
Multiple reports should use the same governed data model.

Scenario	Recommended Mode
Fast executive dashboards	Import
Real-time operational dashboards	DirectQuery
Very large databases	DirectQuery
Enterprise semantic model already exists	Live Connection
Need both speed and fresh data	Composite Model

Connecting Power BI to a Local SQL Database

A local SQL Database runs on your local machine. Therefore, connecting to it is easy!
Prerequisites

SQL Server installed (e.g., SQL Server Express, Developer, or Standard Edition)
SQL Server service is running
You know:
Server name
Database name
Authentication method (Windows or SQL Server Authentication)
Power BI Desktop is installed

Step 1: Open Power BI Desktop

Launch Power BI Desktop.
Click Get Data on the Home ribbon.

Select SQL Server. (In this case, we are using a Postgres Server)

Click Connect.

Step 2: Enter Connection Details

You'll be prompted for:

Server and Database information.
For a local connection, input the server credentials as: 127.0.0.1:5432
where th 5432 is the port number

Step 3: Choose Connectivity Mode

You'll have two options:

Import (recommended)
DirectQuery

Step 4: Authentication

Windows Authentication (Most Common)
Uses your Windows login credentials.

Select: Windows > Then click Connect.

SQL Server Authentication (Authentication details you input while installing your local SQL server)

Choose: Database > Enter: Username and Password

Click Connect.

Step 5: Select Tables

Power BI will display all tables and views. Select the tables you need. Click Load to import them or Transform Data to clean them in Power Query before loading.

Connecting Power BI to a Cloud-based SQL Database

For this, the cloud service provider is Aiven.

Cloud databases like Aiven PostgreSQL offer scalable and flexible data storage but require secure internet-based connections. To protect data in transit, they use encryption. Before Power BI can connect, the local machine must trust the cloud provider's Certificate Authority (CA), allowing it to verify the database server's identity and establish a secure connection.

Step 1: Collect Aiven Connection Details

Host (e.g., pg-12345-project.aivencloud.com)

Port (usually 12345 or 5432)

Database name

Username

Password

CA Certificate

Step 2: Install the PostgreSQL ODBC/Npgsql Driver (if required)

Power BI uses PostgreSQL drivers to connect.
Download and install the latest PostgreSQL (Npgsql) driver.
Restart Power BI Desktop after installation.

Step 3: Trust the Aiven CA Certificate

Since Aiven uses SSL encryption, Windows must trust the Certificate Authority. This helps Power BI trust the Aiven Postgres server.

NOTE: To connect Power BI to the Database without the certificate, this error occurs.

Locate the downloaded ca.pem file.
Import it into the Windows Trusted Root Certification Authorities:

Click Import> In the import wizard, click Next> Browse for the downloaded CA file

Add Certificates for the Computer Account.

Step 4: Open Power BI Desktop ( Same as for local connection steps 1 to 3)
Select Home → Get Data → Choose PostgreSQL database.

Step 5: Enter the Server Details

Fill in the fields as per the data on your Aiven service page:
Server: hostname:port (pg-12345-project.aivencloud.com:12345)
Database: Database name (defaultdb)

Click OK.

Step 6: Authenticate - if connecting first time

Choose Database Authentication: Username > Password

Click Connect.

Step 8: Select Tables

Power BI will display the PostgreSQL schema. Choose the required tables or views.

Load to import the data or Transform Data to clean it in Power Query before loading.

Parting Shot...

Every dashboard begins with a connection. This is where the strength of Power BI comes in: Interconnectivity! Whether your data resides on a local server or in the cloud, mastering secure database connectivity equips you to spend less time troubleshooting and more time discovering the stories hidden within your data. After all, data only becomes valuable when it is transformed into meaningful insights.

How Excel is Used in Real-World Data Analysis

Daniel Mutwiri Mbabu — Sun, 07 Jun 2026 14:58:01 +0000

The eagle first learns to walk before it masters the sky. In the world of data analysis, Excel is often the starting point of that journey. It helps us clean, organize, analyze, and visualize data in ways that reveal valuable patterns and opportunities. What begins as simple rows, columns, and calculations soon transforms into a powerful analytical tool that enables users to uncover trends, solve problems, and make informed decisions based on data rather than assumptions.

I recently joined an official Microsoft Excel class. Although I had used Excel before and was required to learn it on the job due to its importance in business, I quickly discovered that there is always something new to learn. I joined the class believing I had a solid understanding of Excel, but I soon realized that beneath its simplicity lies remarkable sophistication. This balance between simplicity and power is what makes Excel such an indispensable tool.

One of the key lessons I have learned is that data analysis is not just about mastering formulas and features; it is also about understanding the business environment and the problem being solved. A good data analyst should possess both technical and business knowledge. Understanding what questions to ask is just as important as knowing how to manipulate and analyze the data.

Excel plays a vital role in several real-world applications.** In business decision-making,** organizations collect large amounts of data from sales, operations, customers, and employees. Analysts use Excel to identify trends, determine which products generate the most revenue, evaluate branch performance, and forecast future demand. These insights help businesses make informed decisions regarding inventory management, staffing, and investments.
Excel is also widely used in financial reporting. Organizations use it to compare actual spending against budgets, forecast revenues, evaluate investments, and identify potential financial risks. These analyses provide management with the information needed to make sound financial decisions.
In marketing, Excel helps measure the effectiveness of advertising campaigns, social media activities, and customer engagement efforts. By analyzing metrics such as website traffic, conversion rates, and customer acquisition costs, organizations can determine which strategies are delivering the best results and allocate resources more effectively.

One of the most valuable skills I have learned is data cleaning. Clean data forms the foundation of reliable analysis. Excel provides numerous tools and functions that support this process, including

COUNTIFS(), TRIM(), VALUE(), DATEDIF(), YEAR(), TODAY(), NOW(), IFERROR(), and SUMIFS().

Features such as Find and Replace, Conditional Formatting, Data Validation, Multi-Sort, and handling blanks help ensure data accuracy and consistency. Data cleaning also involves checking whether data is realistic and free from errors, such as identifying impossible employee ages or inconsistent entries.

Learning Excel has greatly increased my appreciation for data-driven decision-making. Whether in business, finance, marketing, or personal projects, I now recognize the importance of relying on data rather than assumptions. Excel has transformed the way I see information by showing me how raw data can be organized, analyzed, and converted into actionable insights. As I continue learning, I appreciate even more how a tool that appears simple on the surface can become a powerful means of solving complex real-world problems.