DEV Community: Charles

Connecting PostgreSQL to Power BI for Data Analysis

Charles — Tue, 14 Apr 2026 21:39:04 +0000

Introduction: Power BI and the Role of SQL Databases

Power BI is a tool from Microsoft that turns raw data into interactive dashboards and reports. You know those beautiful charts and graphs that managers love? Power BI makes them. But here’s the thing – Power BI on its own doesn’t store data. It needs to connect to something that does store data. That’s where databases come in.

A database is just an organised collection of information. Think of it like a giant, super‑fast Excel file that many people can use at the same time. Databases are divided into two main types:

SQL databases (also called relational databases) – like PostgreSQL and MySQL – use tables with rows and columns, and you talk to them using the SQL language."
NoSQL databases (like MongoDB) – they store data in other formats like JSON documents.

Connecting Power BI to a local PostgreSQL database

When we say a local database, we simply mean the database is installed and running on your own computer – not on a server somewhere else.

What you need before you start

Power BI Desktop – free download from Microsoft.
PostgreSQL installed and running on your machine.
A database with some tables already created – for example, a simple sales database with tables like customers, products, sales, and inventory.
The PostgreSQL connection driver – when you first try to connect from Power BI, it will usually detect that the driver is missing and offer to download it for you. Just follow the prompts and say Yes or Install.

Step-by-step to connecting Power BI to a locally hosted Database

Step 1. Open Power BI Desktop. You’ll see a blank canvas with a ribbon at the top.

Step 2. On the Home ribbon, click Get Data (it’s on the left). A dropdown appears – choose More… if you don’t see PostgreSQL right away.

Step 3. In the search box, type “PostgreSQL”. Select PostgreSQL Database and click Connect.

Step 4. A small window asks for two things:

Server: type localhost (that’s the special name your computer uses for itself).
Database: type the name of your database that contains the data. If you’re not sure, check in pgAdmin – right‑click on the database name.

Step 5. You can leave Data Connectivity mode as Import . Click OK.

Step 6. Power BI will ask for your PostgreSQL username and password.

Username is usually postgres.
Password is whatever you set when you installed PostgreSQL. Choose Database as the authentication method, then click Connect.

Step 7. After a few seconds, the Navigator window appears. It shows all the schemas and tables in your database. Select the tables you wish to work on and load the data.

Step 8. You have two buttons at the bottom: Load and Transform Data.

Load brings the tables straight into Power BI.
Transform Data opens the Power Query Editor where you can clean or reshape the data first.

Step 9. Once the cleaning is done you can create a relationship between the tables. Open the model view and inspect the relationship between the tables.

Connecting Power BI to a cloud PostgreSQL database (Aiven)

In the real world, databases are rarely on your laptop. They live in the cloud – AWS, Google Cloud, or a service like Aiven. Connecting to a cloud database is similar to local, but with one extra step: SSL certificates.

In a nutshell, what is SSL certificates?
Think of it this way – when you share data across the internet, you don't want some random person snooping on your passwords or your sales numbers. SSL is just a technology that encrypts (secures) everything so only you and the database can read it. Cloud providers like Aiven won't even let you connect without an SSL certificate. It's a small file you download and install on your computer – it's like a digital ID badge.

Step 1. Download and install the certificate from Aiven

Log into your Aiven and go to your PostgreSQL service.
On the Overview tab, scroll down to Connection Information.
Click Download CA Certificate. You’ll get a file named ca.pem.
Rename it file from ca.pem to ca.crt.
Double‑click the ca.crt file. A window pops up – click Install Certificate.

Choose Local Machine → Next.

Select Place all certificates in the following store → Browse.
Choose Trusted Root Certification Authorities → OK → Next → Finish.

Step 2. Get your connection details

Still in the Aiven Overview page, copy these values:

Host (looks like pg-1a2b3c4d-aivencloud.com)
Port (a five‑digit number, e.g., 12345)
Database name (often defaultdb)
Username (usually avnadmin)
Password (the one you set or was generated)

Step 3. Connect from Power BI

In Power BI, click Get Data → PostgreSQL Database (same as the local host connection).

In the Server field, combine the host and port like this: pg-1a2b3c4d-aivencloud.com:12345

Enter the Database name (e.g., defaultdb).
Click OK. Enter your Aiven username and password.

The Navigator appears – same as before. Select your tables and click Load or Transform Data.

Loading tables and building relationships

Once your tables are loaded (either from local or cloud), you need to tell Power BI how they relate to each other.

The model view Click the Model icon on the left sidebar – it looks like three connected boxes. You’ll see your four tables floating.

Power BI sometimes guesses relationships automatically. But here we are going to;

Create these relationships (drag and drop):

customers (customer_id) → sales (customer_id) → One‑to‑Many
products (product_id) → sales (product_id) → One‑to‑Many
products (product_id) → inventory (product_id) → One‑to‑One

Now when you build a chart, you can drag product_name from products and total_amount from sales onto the same visual – Power BI automatically joins them.

Without these relationships, filters won't work correctly.

What is data modeling?

Data modeling is basically how you decide your tables relate to each other. You can think of it as setting up a blueprint for your data. In Power BI, this is what makes everything work together — when you click on a chart, the rest of the report updates because those relationships are already defined.

Why Relationships Matter

Relationships are what make your data useful. Without them, your tables just sit there as separate pieces of information.

For example, a sales report won’t be able to show the customer’s name, filtering by product category won’t affect your sales numbers, and in some cases, you might even end up double-counting values.

That’s how wrong insights happen — not because the data is bad, but because the connections aren’t set properly.

Types of relationships

Type	What it means	Example
One-to-Many	One record in Table A links to many in Table B	One customer, many sales
One-to-One	One record links to exactly one other	One product, one inventory record
Many-to-Many	Many records on both sides (use carefully)	Multiple products in multiple orders

Bottom line: Power BI is the kitchen. SQL is the prep cook who chops and cleans the ingredients first. Do the heavy lifting in SQL, and your dashboards will be faster and simpler.

Why SQL skills still matter

You might be thinking: "If Power BI already has drag-and-drop features, why do I still need SQL?"

The simple answer is this: Power BI is great at showing data, but SQL is better at preparing data.

Lets see some situations where SQL will be useful.

1. Loading Only What You Need

Instead of importing entire tables, you can write a SQL query in the Advanced Options when connecting.

SELECT 
    c.customer_id,
    c.first_name,
    SUM(s.total_amount) AS total_spent
FROM customers c
JOIN sales s ON c.customer_id = s.customer_id
GROUP BY c.customer_id, c.first_name;

Here, Power BI only loads a summarized dataset — which is much faster.

2. Filter at the Source
Filtering data in SQL happens before the data is even sent to Power BI.

SELECT * 
FROM sales 
WHERE sale_date >= '2024-01-01';

This reduces the size of the dataset and improves refresh performance.

3. Use Advanced SQL Features
Some operations are easier in SQL than in Power BI.

For example, functions like RANK() or LAG() are straightforward in SQL but can get complicated in DAX.

4. Cleaning your Data Prior
It’s always better to fix data issues at the source.

SELECT 
    customer_id,
    UPPER(TRIM(first_name)) AS first_name,
    COALESCE(email, 'no-email@example.com') AS email
FROM customers
WHERE customer_id IS NOT NULL;

This helps you avoid unnecessary cleaning steps inside Power BI.

Conclusion

We walked through connecting PostgreSQL to Power BI – whether it's on your own laptop or up in the cloud with something like Aiven.

What surprised me is that the connection part is actually the easiest step. The real work starts after that: loading the right tables, cleaning stuff up, getting the relationships right. If you skip that part, your charts will look weird and you'll be chasing problems forever.

I was working with a pretty small dataset – just a few tables, nothing crazy. But even then, I could see the pattern. It's not about which tool you use. It's about whether you bothered to prepare your data properly before dragging things onto the canvas.

Get the structure right, and Power BI feels almost too easy. Get it wrong, and you'll spend hours fixing filters and wondering why your totals don't match. I've been there. It's not fun.

So yeah – take the extra time to model things correctly. You'll have fun and enjoy every bit.

Machine Learning Basics: What is ML? Supervised vs Unsupervised, Features vs Labels

Charles — Mon, 13 Apr 2026 12:09:25 +0000

In this article, we will cover:

What ML really is
The difference between supervised and unsupervised learning
What features and labels are – and why they matter

What is machine learning?

Machine learning (ML) is the subset of artificial intelligence (AI) focused on algorithms that can learn the patterns of training data and, subsequently, make accurate inferences about new data. This pattern recognition ability enables machine learning models to make decisions or predictions without explicit, hard-coded instructions.

Examples of Machine Learning.

1. Personal assistants and voice assistants.
ML powers popular virtual assistants like Amazon Alexa and Apple Siri. It enables speech recognition, natural language processing (NLP), and text-to-speech conversion. When you ask a question, ML not only understands your intent but also searches for relevant answers or recalls similar past interactions for more personalized responses.

2. Email Filtering and Management.
ML algorithms in Gmail automatically categorize emails into Primary, Social, and Promotions tabs while detecting and moving spam to the spam folder. Beyond basic rules, ML tools classify incoming emails, route them to the right team members, extract attachments, and enable automated personalized replies.

3. Transportation and Navigation.

Machine Learning has transformed modern transportation in several ways:

Google Maps uses ML to analyze real-time traffic conditions, calculate the fastest routes, suggest nearby places to explore, and provide accurate arrival time predictions.
Ride-sharing apps like Uber and Bolt apply ML to match riders with drivers, dynamically set pricing (surge pricing), optimize routes based on live traffic, and predict accurate ETAs.
Self-driving cars (e.g., Tesla) rely heavily on computer vision and unsupervised ML algorithms. These systems process data from cameras and sensors in real-time to understand their surroundings and make instant driving decisions.

Types of machine learning

Machine Learning generally falls into two main learning paradigms: Supervised Learning and Unsupervised Learning. These differ based on the type of data they use and the objective they aim to achieve.

1. Supervised Learning

Supervised learning trains a model using labeled data — where every input example is paired with the correct output (label). The goal is to learn the mapping between inputs and outputs so the model can accurately predict outcomes on new, unseen data.

Common Tasks:

Classification — Predict discrete categories (e.g., spam/not spam, cat/dog, approve/reject loan)
Regression — Predict continuous values (e.g., house price, temperature, sales forecast)

How it works:
In supervised learning, the model learns from examples where the answers are already known. It is given inputs (features) together with the correct outputs (labels), and over time it identifies patterns in the data. As it trains, it continuously adjusts itself to reduce the difference between its predictions and the actual answers.

Real-world examples:

Spam detection,
Image classification,
Credit risk scoring.

Analogy:
Think of a student learning with a teacher. The teacher shows examples and clearly labels them — “this is a cat,” “this is a dog.” Over time, the student begins to recognize the differences and can correctly identify new animals on their own.

2. Unsupervised Learning

Unsupervised learning works with unlabeled data. The model must discover hidden patterns, structures, or groupings on its own — without any “correct answers” provided.

Common tasks:

Clustering — grouping similar data points together (e.g., customer segmentation)
Association — finding relationships in data (e.g., people who buy X also buy Y)
Dimensionality reduction — simplifying data while keeping the most important information

Real-world examples:

Customer segmentation in retail (grouping shoppers based on buying habits),
Fraud detection in mobile money or banking (flagging unusual transactions),
Product recommendations on e-commerce sites (suggesting items similar to what you’ve viewed),
Music or movie suggestions based on what you like (Spotify, Prime Video).

Supervised vs Unsupervised Learning

Aspect	Supervised Learning	Unsupervised Learning
Data used	Labeled (features + answers)	Unlabeled (just features, no answers)
Goal	Predict an output / category	Find hidden patterns or groupings
Task types	Classification & regression	Clustering, association, dimensionality reduction
How hard to evaluate	Easy – you have ground truth to compare	Trickier – no "right answer" to check against
Real‑world examples	Spam detection, price prediction	Customer segments, fraud detection
Complexity	Generally simpler	More complex (no teacher to guide)

Key Takeaway:

Use Supervised Learning when you have labeled historical data and want to make predictions.
Use Unsupervised Learning when you have lots of raw data and want to discover insights or patterns you didn’t already know.

Modern systems often combine both. For example, many Large Language Models (LLMs) use self‑supervised learning during pre‑training, followed by supervised fine‑tuning and RLHF (reinforcement learning from human feedback).

Features vs Labels

If you're doing supervised learning, you'll run into two terms constantly: features and labels. Here's what they actually mean.

What is a Feature?
A feature is any piece of information you feed the model – a clue that helps it make a prediction. Features are also called independent variables, predictors, or attributes

Examples of Features:

In house price prediction: square footage, number of bedrooms
In spam detection: length of email, number of capital letters

Features can be numerical (age, price), categorical (gender, color), or text-based.

What is a Label?

A label is the answer the model tries to guess – the output or correct answer. Also called target or dependent variable

Examples of Labels:

House price prediction --> Actual sale price (Kshs)
Spam detection --> “Spam” or “Not Spam”

Labels are only available in supervised learning because they represent the ground truth.

Features vs Labels – Quick Comparison

Aspect	Features (the inputs)	Label (the answer)
What it is	What the model uses to learn	What the model tries to guess
Other names	Independent variables, predictors	Target variable, dependent variable
Do you always have it?	Yes – in any dataset	Only in supervised learning
House price example	Size, bedrooms, location	The price tag

Key Takeaway:

Features = clues. Label = the answer.
When preparing data for a supervised model, split it into X (features) and y (label).
Garbage in --> garbage out: bad features or wrong labels will ruin your model.

Conclusion

Machine Learning lets computers learn from data without hard‑coded rules.
Supervised learning uses labeled data to predict outcomes (spam detection, prices).
Unsupervised learning finds hidden patterns in unlabeled data (customer segments, fraud).
Features are the clues you feed the model. Labels are the answers you want to predict.

Data lives scattered across tables—customers here, products there, sales everywhere. This article is your guide to bringing it all together. Follow along with a real online store dataset as we build queries that rank top customers, spot sales trends, and u

Charles — Sat, 07 Mar 2026 17:31:11 +0000

Mastering SQL Joins and Window Functions: A Practical Guide with an E‑commerce Dataset

Charles ・ Mar 7

#sql #database #softwaredevelopment #datascience

Mastering SQL Joins and Window Functions: A Practical Guide with an E‑commerce Dataset

Charles — Sat, 07 Mar 2026 17:23:41 +0000

Introduction & The Dataset

If you're working with SQL, you've probably used basic SELECT statements, filtered with WHERE, and maybe even done some simple aggregations. But the real power of SQL unfolds when you start combining data from multiple tables and performing complex calculations across rows without losing detail. That's where JOINs and Window Functions come in.

In this article, we'll dive deep into these advanced concepts using a realistic e‑commerce dataset.
By the end, you'll be able to:

Combine data from multiple tables using different types of joins.
Perform rankings, running totals, and comparisons across rows with window functions.
Organize complex queries using Common Table Expressions (CTEs).
Solve practical business questions that real data analysts face every day.

We'll use a consistent dataset throughout – so you can follow along, try the queries yourself, and truly master these techniques.

Meet Our Data

Imagine we're running an online store. Our database has three main tables:

customers – people who registered on our site.
products – items we sell, with prices and stock.
sales – records of purchases, linking customers to products.

Here are the table structures (simplified for clarity) and a few sample rows.

1. Customers

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone_number VARCHAR(50),
    registration_date DATE,
    membership_status VARCHAR(10)  -- 'Bronze', 'Silver', 'Gold'
);

customer_id	first_name	last_name	email	phone_number	registration_date	membership_status
1	Karen	Molina	gonzalezkimberly@glass.com	(728)697-1206	2020-08-27	Bronze
2	Elizabeth	Archer	tramirez@gmail.com	778.104.6553	2023-08-28	Silver
3	Roberta	Massey	davislori@gmail.com	+1-365-606-7458	2024-06-12	Bronze

2. Products

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10, 2),
    supplier VARCHAR(100),
    stock_quantity INT
);

product_id	product_name	category	price	supplier	stock_quantity
1	Laptop	Electronics	999.99	Dell	50
2	Smartphone	Electronics	799.99	Samsung	150
3	Washing Machine	Appliances	499.99	LG	30
4	Headphones	Accessories	199.99	Sony	100

3. Sales

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    quantity_sold INT,
    sale_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

sale_id	customer_id	product_id	quantity_sold	sale_date	total_amount
1	1	1	1	2023-07-15	999.99
2	2	2	2	2023-08-20	1599.98
3	3	3	1	2023-09-10	499.99
4	4	4	3	2023-07-25	599.97

What We'll Build

We'll use these tables to answer questions like:

"Which customers have never placed an order?"
"What is the total revenue per product category?"
"Who are the top 3 customers by spending, and how do they rank within their membership tier?"
"How does each sale compare to the previous sale for the same customer?"

By the time you finish reading, you'll be able to write queries that combine these tables and apply window functions like a pro.

SQL Joins – Bringing Data Together

In a well‑designed database, data is split across multiple tables to avoid redundancy. For example, we store customer details in one table, product information in another, and sales transactions in a third. To answer real‑world questions, we need to join these tables back together.

INNER JOIN

An INNER JOIN returns only the rows that have matching values in both tables. Rows without a match are excluded.

Use case: Find all customers who have placed orders, along with their order details.

Example 1: List customers and their orders

SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    s.sale_id,
    s.sale_date,
    s.total_amount
FROM customers c
INNER JOIN sales s ON c.customer_id = s.customer_id;

Result:

customer_id	first_name	last_name	sale_id	sale_date	total_amount
1	Karen	Molina	1	2023-07-15	999.99
2	Elizabeth	Archer	2	2023-08-20	1599.98
3	Roberta	Massey	3	2023-09-10	499.99
4	Jacob	Adams	4	2023-07-25	599.97
5	Cynthia	Lowery	5	2023-06-18	1200.00

What happened?

The INNER JOIN combined rows from customers and sales wherever customer_id matched. Customers who have never placed an order (like some of our 50 customers) do not appear in the result.

Example 2: Add product details to the orders
We can join more than two tables. Let's include the product name and price.

SELECT 
    c.first_name,
    c.last_name,
    s.sale_date,
    p.product_name,
    s.quantity_sold,
    s.total_amount
FROM customers c
INNER JOIN sales s ON c.customer_id = s.customer_id
INNER JOIN products p ON s.product_id = p.product_id;

Result:

first_name	last_name	sale_date	product_name	quantity_sold	total_amount
Karen	Molina	2023-07-15	Laptop	1	999.99
Elizabeth	Archer	2023-08-20	Smartphone	2	1599.98
Roberta	Massey	2023-09-10	Washing Machine	1	499.99
Jacob	Adams	2023-07-25	Headphones	3	599.97
Cynthia	Lowery	2023-06-18	Refrigerator	1	1200.00

Key point: Each INNER JOIN adds another table, filtering out rows that don't match the join condition. Only orders with valid customer and product IDs appear.

LEFT JOIN (or LEFT OUTER JOIN)

A LEFT JOIN returns all rows from the left table, and matching rows from the right table. If there's no match, the right‑table columns are filled with NULL.

Use case: Show all customers, even those who have never placed an order, and include any orders they may have.

SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    s.sale_id,
    s.sale_date,
    s.total_amount
FROM customers c
LEFT JOIN sales s ON c.customer_id = s.customer_id
ORDER BY c.customer_id;

Result (first few rows):

customer_id	first_name	last_name	sale_id	sale_date	total_amount
1	Karen	Molina	1	2023-07-15	999.99
2	Elizabeth	Archer	2	2023-08-20	1599.98
3	Roberta	Massey	3	2023-09-10	499.99
4	Jacob	Adams	4	2023-07-25	599.97
5	Cynthia	Lowery	5	2023-06-18	1200.00
6	Emily	King	NULL	NULL	NULL
7	Linda	Larsen	NULL	NULL	NULL

Notice: Customers 6 and 7 (Emily King, Linda Larsen) have no sales, so the sale_id, sale_date, and total_amount are NULL. This is a great way to find customers who haven't purchased anything.

RIGHT JOIN

A RIGHT JOIN is the mirror of LEFT JOIN: it returns all rows from the right table, and matching rows from the left. Unmatched left‑table columns become NULL.
Because you can always rewrite a RIGHT JOIN as a LEFT JOIN by swapping the table order, many developers rarely use it. But it's good to know it exists.

Use case: List all products and show which ones have been sold (if any).

SELECT 
    p.product_id,
    p.product_name,
    s.sale_id,
    s.sale_date,
    s.total_amount
FROM sales s
RIGHT JOIN products p ON s.product_id = p.product_id
ORDER BY p.product_id;

Result (first few rows):

product_id	product_name	sale_id	sale_date	total_amount
1	Laptop	1	2023-07-15	999.99
2	Smartphone	2	2023-08-20	1599.98
3	Washing Machine	3	2023-09-10	499.99
4	Headphones	4	2023-07-25	599.97
5	Refrigerator	5	2023-06-18	1200.00
6	Smart TV	NULL	NULL	NULL
7	Microwave	NULL	NULL	NULL

Products 6 (Smart TV) and 7 (Microwave) haven't been sold – their sale details are NULL.

FULL OUTER JOIN

FULL OUTER JOIN returns all rows from both tables, matching where possible. Unmatched rows from either side are filled with NULL. It's like a combination of LEFT and RIGHT joins.

Use case: See every customer and every product side by side – which is not very common in practice, but useful for finding orphans.

SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    p.product_id,
    p.product_name,
    s.sale_id
FROM customers c
FULL OUTER JOIN sales s ON c.customer_id = s.customer_id
FULL OUTER JOIN products p ON s.product_id = p.product_id
LIMIT 10;  -- just to keep the output manageable

Result (truncated):
You'll get a mix of customers without sales, products without sales, and actual transactions. Many NULL cells appear.

Because FULL OUTER JOIN can produce huge result sets, it's used less often, but it's indispensable for certain data reconciliation tasks.

CROSS JOIN

A CROSS JOIN returns the Cartesian product of two tables – every row from the first table paired with every row from the second. There's no ON condition.

Use case: Generate all possible combinations, e.g., for creating product‑customer promotional lists.

SELECT 
    c.customer_id,
    c.first_name,
    p.product_id,
    p.product_name
FROM customers c
CROSS JOIN products p
WHERE c.customer_id <= 3 AND p.product_id <= 3  -- limit for readability
ORDER BY c.customer_id, p.product_id;

Result:

customer_id	first_name	product_id	product_name
1	Karen	1	Laptop
1	Karen	2	Smartphone
1	Karen	3	Washing Machine
2	Elizabeth	1	Laptop
2	Elizabeth	2	Smartphone
2	Elizabeth	3	Washing Machine
3	Roberta	1	Laptop
3	Roberta	2	Smartphone
3	Roberta	3	Washing Machine

Every combination of the selected customers and products appears. This is handy for generating base data for analysis.

SELF JOIN

A SELF JOIN is when a table is joined with itself. It's useful for hierarchical data or comparing rows within the same table.

Use case: Find pairs of customers who have the same membership status.

SELECT 
    c1.customer_id AS customer1,
    c1.first_name AS first1,
    c1.last_name AS last1,
    c2.customer_id AS customer2,
    c2.first_name AS first2,
    c2.last_name AS last2,
    c1.membership_status
FROM customers c1
JOIN customers c2 ON c1.membership_status = c2.membership_status
WHERE c1.customer_id < c2.customer_id
ORDER BY c1.membership_status, customer1;

Result (few rows):

customer1	first1	last1	customer2	first2	last2	membership_status
1	Karen	Molina	3	Roberta	Massey	Bronze
1	Karen	Molina	8	Angela	Hanson	Bronze
1	Karen	Molina	9	Whitney	Wilson	Bronze
2	Elizabeth	Archer	5	Cynthia	Lowery	Silver

We use c1.customer_id < c2.customer_id to avoid duplicate pairs (e.g., (1,3) and (3,1)) and to exclude a customer paired with themselves.

Joining Multiple Tables – Putting It All Together

Often you need to join more than two tables. Here's a query that combines customers, sales, and products to give a complete picture of each transaction.

SELECT 
    c.first_name,
    c.last_name,
    s.sale_date,
    p.product_name,
    p.category,
    s.quantity_sold,
    s.total_amount
FROM customers c
JOIN sales s ON c.customer_id = s.customer_id
JOIN products p ON s.product_id = p.product_id
WHERE c.membership_status = 'Gold'   -- only Gold members
ORDER BY s.sale_date;

Result (few rows):

first_name	last_name	sale_date	product_name	category	quantity_sold	total_amount
James	Rodriguez	2023-07-15	Gaming Console	Electronics	1	350.00
Steven	Burnett	2023-08-01	Microwave	Appliances	1	180.00

This kind of multi‑table join is the backbone of reporting and analytics.

Now that you've seen all join types, you're ready to combine data from any set of related tables. In the next section, we'll explore Window Functions – a whole new level of analytical power.

Window Functions – Analyzing Data Without Losing Detail

Window functions are a game‑changer. Unlike regular aggregate functions (GROUP BY) that collapse multiple rows into one, window functions perform calculations across a set of rows related to the current row while keeping each row intact. This makes them perfect for rankings, running totals, moving averages, and comparing values between rows.

Basic Syntax

function_name (expression) OVER (
    [PARTITION BY column(s)]
    [ORDER BY column(s)]
    [frame_clause]
)

function_name: e.g., ROW_NUMBER(), RANK(), SUM(), LAG()
PARTITION BY: divides rows into groups (optional) – the function is applied separately to each group.
ORDER BY: defines the order within each partition.
frame_clause: specifies which rows to include (for aggregate window functions).

We'll use our sales table, sometimes joined with customers or products, to illustrate.

1. ROW_NUMBER() – Unique Sequential Number

Assigns a unique integer to each row within a partition, starting at 1. Ties are broken arbitrarily.

Example: Number all sales by date

SELECT 
    sale_id,
    customer_id,
    sale_date,
    total_amount,
    ROW_NUMBER() OVER (ORDER BY sale_date) AS row_num
FROM sales;

Result:

sale_id	customer_id	sale_date	total_amount	row_num
5	5	2023-06-18	1200.00	1
1	1	2023-07-15	999.99	2
4	4	2023-07-25	599.97	3
2	2	2023-08-20	1599.98	4
3	3	2023-09-10	499.99	5

Use case: Pagination, assigning a unique identifier to each row in a result set.

2. RANK() and DENSE_RANK() – Ranking with Ties

RANK() gives the same rank to equal values, then skips the next rank(s) (e.g., 1,1,3).
DENSE_RANK() also gives the same rank to ties, but does not skip numbers (e.g., 1,1,2).

Example: Rank customers by total spending (including those with no purchases).

WITH customer_spending AS (
    SELECT 
        c.customer_id,
        c.first_name,
        c.last_name,
        COALESCE(SUM(s.total_amount), 0) AS total_spent
    FROM customers c
    LEFT JOIN sales s ON c.customer_id = s.customer_id
    GROUP BY c.customer_id, c.first_name, c.last_name
)
SELECT 
    customer_id,
    first_name,
    last_name,
    total_spent,
    RANK() OVER (ORDER BY total_spent DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY total_spent DESC) AS dense_rank
FROM customer_spending
ORDER BY total_spent DESC
LIMIT 10;  -- just show top 10 for brevity

Result (first few rows):

customer_id	first_name	last_name	total_spent	rank	dense_rank
2	Elizabeth	Archer	1599.98	1	1
6	Emily	King	1500.00	2	2
1	Karen	Molina	999.99	3	3
5	Cynthia	Lowery	1200.00	4	4
4	Jacob	Adams	599.97	5	5
3	Roberta	Massey	499.99	6	6
7	Linda	Larsen	0.00	7	7
8	Angela	Hanson	0.00	7	7
9	Whitney	Wilson	0.00	7	7

Notice how RANK and DENSE_RANK handle ties differently: when multiple customers have zero spending, RANK skips from 7 to 10 (depending on how many zeros), while DENSE_RANK assigns the same rank to all zeros and then increments by 1.

3. SUM() as a Window Function – Running Totals

Using aggregate functions like SUM with OVER allows you to compute a running total without collapsing rows.

Example: Calculate a running total of sales amounts ordered by date.

SELECT 
    sale_id,
    sale_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
ORDER BY sale_date;

Result:

sale_id	sale_date	total_amount	running_total
5	2023-06-18	1200.00	1200.00
1	2023-07-15	999.99	2199.99
4	2023-07-25	599.97	2799.96
2	2023-08-20	1599.98	4399.94
3	2023-09-10	499.99	4899.93

Each row's running_total is the sum of all total_amount from the earliest sale up to that row.

With partitioning: You can reset the running total for each customer.

SELECT 
    customer_id,
    sale_id,
    sale_date,
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY sale_date) AS customer_running_total
FROM sales
ORDER BY customer_id, sale_date;

This would show, for each customer, the cumulative amount they've spent over time.

4. LAG() and LEAD() – Accessing Other Rows

LAG() lets you access data from a previous row, while LEAD() accesses a following row – great for comparing values.

Example: For each sale, show the previous sale amount (by date).

SELECT 
    sale_id,
    sale_date,
    total_amount,
    LAG(total_amount) OVER (ORDER BY sale_date) AS prev_sale_amount,
    total_amount - LAG(total_amount) OVER (ORDER BY sale_date) AS difference_from_prev
FROM sales
ORDER BY sale_date;

Result:

sale_id	sale_date	total_amount	prev_sale_amount	difference_from_prev
5	2023-06-18	1200.00	NULL	NULL
1	2023-07-15	999.99	1200.00	-200.01
4	2023-07-25	599.97	999.99	-400.02
2	2023-08-20	1599.98	599.97	1000.01
3	2023-09-10	499.99	1599.98	-1099.99

The first row has NULL because there's no previous sale.

5. NTILE() – Dividing into Buckets

NTILE(n) divides rows into n roughly equal groups (buckets) based on the ordering.

Example: Divide customers into 4 quartiles based on total spending.

WITH customer_spending AS (
    SELECT 
        c.customer_id,
        c.first_name,
        c.last_name,
        COALESCE(SUM(s.total_amount), 0) AS total_spent
    FROM customers c
    LEFT JOIN sales s ON c.customer_id = s.customer_id
    GROUP BY c.customer_id, c.first_name, c.last_name
)
SELECT 
    customer_id,
    first_name,
    last_name,
    total_spent,
    NTILE(4) OVER (ORDER BY total_spent DESC) AS spending_quartile
FROM customer_spending
ORDER BY total_spent DESC;

Result (sample):

customer_id	first_name	last_name	total_spent	spending_quartile
2	Elizabeth	Archer	1599.98	1
6	Emily	King	1500.00	1
1	Karen	Molina	999.99	1
5	Cynthia	Lowery	1200.00	1
4	Jacob	Adams	599.97	2
3	Roberta	Massey	499.99	2
7	Linda	Larsen	0.00	3
8	Angela	Hanson	0.00	3
9	Whitney	Wilson	0.00	4
10	Angela	Atkins	75.00	2

Quartile 1 contains the top 25% of spenders, quartile 4 the bottom 25%.

6. Window Functions with PARTITION BY – Per‑Group Rankings

PARTITION BY restricts the window to rows with the same value in a column.

Example: Rank products by price within each category.

SELECT 
    product_id,
    product_name,
    category,
    price,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank_in_category
FROM products
ORDER BY category, price_rank_in_category;

Result (first few rows):

product_id	product_name	category	price	price_rank_in_category
5	Refrigerator	Appliances	1200.00	1
3	Washing Machine	Appliances	499.99	2
7	Microwave	Appliances	180.00	3
8	Blender	Appliances	50.00	4
6	Smart TV	Electronics	1500.00	1
1	Laptop	Electronics	999.99	2
2	Smartphone	Electronics	799.99	3
9	Gaming Console	Electronics	350.00	4
12	Monitor	Electronics	250.00	5

Now you can see the most expensive product in each category at a glance.

Key Takeaways

Window functions do not collapse rows – they add calculated columns while preserving detail.

PARTITION BY is like GROUP BY within the window.
ORDER BY inside OVER controls the order for functions like ROW_NUMBER, RANK, and running totals.
Use RANK vs DENSE_RANK depending on whether you want gaps after ties.
LAG and LEAD are invaluable for time‑series and comparative analysis.
NTILE is great for segmenting data into percentiles or quartiles.

In the next section, we'll explore Common Table Expressions (CTEs) – a powerful way to organize complex queries, especially when combined with window functions.

Common Table Expressions (CTEs) – Organizing Complex Queries

A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. Think of it as a named subquery that makes your SQL more readable, reusable, and easier to debug.

Basic Syntax

WITH cte_name AS (
    -- CTE query
    SELECT ...
)
-- Main query that uses the CTE
SELECT ...
FROM cte_name;

You can also define multiple CTEs in one WITH clause by separating them with commas:

WITH 
cte1 AS (SELECT ...),
cte2 AS (SELECT ...)
SELECT ...
FROM cte1 JOIN cte2 ON ...;

CTEs are especially valuable when you need to:

Break a complex query into simple, logical steps.
Reference the same subquery multiple times in a query.
Create recursive queries (though we'll focus on non‑recursive here).

Example 1: Simple CTE – Total Spending per Customer

Suppose we want a list of customers with their total spending, but we also need to use that result in further calculations. Instead of repeating the aggregation, we can define a CTE.

WITH customer_spending AS (
    SELECT 
        c.customer_id,
        c.first_name,
        c.last_name,
        COALESCE(SUM(s.total_amount), 0) AS total_spent
    FROM customers c
    LEFT JOIN sales s ON c.customer_id = s.customer_id
    GROUP BY c.customer_id, c.first_name, c.last_name
)
SELECT 
    customer_id,
    first_name,
    last_name,
    total_spent
FROM customer_spending
ORDER BY total_spent DESC
LIMIT 5;

Result (top 5 spenders):

customer_id	first_name	last_name	total_spent
2	Elizabeth	Archer	1599.98
6	Emily	King	1500.00
5	Cynthia	Lowery	1200.00
1	Karen	Molina	999.99
4	Jacob	Adams	599.97

Now customer_spending can be reused in the same query or even in subsequent CTEs.

Example 2: Chaining Multiple CTEs

You can build one CTE on top of another. This is great for step‑wise transformations.
Task: Find customers who spent more than the average spending of all customers.
We'll first compute each customer's total, then the overall average, then filter.

WITH customer_spending AS (
    SELECT 
        c.customer_id,
        c.first_name,
        c.last_name,
        COALESCE(SUM(s.total_amount), 0) AS total_spent
    FROM customers c
    LEFT JOIN sales s ON c.customer_id = s.customer_id
    GROUP BY c.customer_id, c.first_name, c.last_name
),
overall_avg AS (
    SELECT AVG(total_spent) AS avg_spent
    FROM customer_spending
)
SELECT 
    cs.customer_id,
    cs.first_name,
    cs.last_name,
    cs.total_spent,
    oa.avg_spent
FROM customer_spending cs
CROSS JOIN overall_avg oa
WHERE cs.total_spent > oa.avg_spent
ORDER BY cs.total_spent DESC;

Result (sample):

customer_id	first_name	last_name	total_spent	avg_spent
2	Elizabeth	Archer	1599.98	287.65
6	Emily	King	1500.00	287.65
5	Cynthia	Lowery	1200.00	287.65
1	Karen	Molina	999.99	287.65
4	Jacob	Adams	599.97	287.65
3	Roberta	Massey	499.99	287.65

(Here the average is around 287.65 – you can see which customers are above it.)

Example 3: CTEs with Window Functions

CTEs and window functions are a match made in heaven. The CTE prepares the data, and the window function adds analytics.
Task: Rank customers by total spending and show their quartile.

WITH customer_spending AS (
    SELECT 
        c.customer_id,
        c.first_name,
        c.last_name,
        COALESCE(SUM(s.total_amount), 0) AS total_spent
    FROM customers c
    LEFT JOIN sales s ON c.customer_id = s.customer_id
    GROUP BY c.customer_id, c.first_name, c.last_name
)
SELECT 
    customer_id,
    first_name,
    last_name,
    total_spent,
    RANK() OVER (ORDER BY total_spent DESC) AS spending_rank,
    NTILE(4) OVER (ORDER BY total_spent DESC) AS spending_quartile
FROM customer_spending
ORDER BY total_spent DESC;

Result (first few rows):

customer_id	first_name	last_name	total_spent	spending_rank	spending_quartile
2	Elizabeth	Archer	1599.98	1	1
6	Emily	King	1500.00	2	1
5	Cynthia	Lowery	1200.00	3	1
1	Karen	Molina	999.99	4	1
4	Jacob	Adams	599.97	5	2
3	Roberta	Massey	499.99	6	2
7	Linda	Larsen	0.00	7	3
8	Angela	Hanson	0.00	7	3
9	Whitney	Wilson	0.00	7	4
10	Angela	Atkins	75.00	10	2

Notice how RANK handles ties (customers with zero spending share rank 7) and NTILE distributes rows into four buckets as evenly as possible.

Example 4: CTEs for Readability – Multi‑Step Analysis

Imagine you need to produce a report that shows, for each product category, the top‑selling product and its sales. Without CTEs, the query could become a tangled mess of subqueries. With CTEs, it's clean:

WITH category_sales AS (
    SELECT 
        p.category,
        p.product_name,
        SUM(s.total_amount) AS product_sales
    FROM products p
    LEFT JOIN sales s ON p.product_id = s.product_id
    GROUP BY p.category, p.product_name
),
ranked_products AS (
    SELECT 
        category,
        product_name,
        product_sales,
        RANK() OVER (PARTITION BY category ORDER BY product_sales DESC) AS rank
    FROM category_sales
)
SELECT 
    category,
    product_name,
    product_sales
FROM ranked_products
WHERE rank = 1;

Result:

category	product_name	product_sales
Appliances	Refrigerator	1200.00
Electronics	Smart TV	1500.00
Accessories	Headphones	599.97

(Assuming only a few sales; your actual data may vary.)

Key Takeaways

CTEs improve readability by letting you name intermediate results.
They are not materialized (in most databases) – they're just syntax, but they make complex queries easier to write and debug.
You can use them with joins, window functions, and aggregations.
Multiple CTEs can be chained to build up a solution step by step.
They are essential for recursive queries (though we didn't cover that here).

Now that you've seen how CTEs can organize even the most intricate logic, you're ready to tackle advanced analytical queries that combine all these concepts.

In the final section, we'll bring everything together with some real‑world analytical problems that use joins, window functions, and CTEs in harmony.

Putting It All Together – Real‑World Analytical Problems

Now that we've covered joins, window functions, and CTEs individually, let's combine them to solve realistic business questions. These examples show how these tools work in harmony to extract valuable insights from our e‑commerce data.

Example 1: Top 3 Customers by Spending in Each Membership Tier

Business question: For each membership tier (Bronze, Silver, Gold), who are the top 3 customers by total spending?

Approach:

CTE to compute total spending per customer, including their membership status.
Window function RANK() partitioned by membership status to rank customers within their tier.
Filter to keep only ranks 1–3.

WITH customer_spending AS (
    SELECT 
        c.customer_id,
        c.first_name,
        c.last_name,
        c.membership_status,
        COALESCE(SUM(s.total_amount), 0) AS total_spent
    FROM customers c
    LEFT JOIN sales s ON c.customer_id = s.customer_id
    GROUP BY c.customer_id, c.first_name, c.last_name, c.membership_status
),
ranked_customers AS (
    SELECT 
        customer_id,
        first_name,
        last_name,
        membership_status,
        total_spent,
        RANK() OVER (PARTITION BY membership_status ORDER BY total_spent DESC) AS rank_in_tier
    FROM customer_spending
)
SELECT 
    membership_status,
    rank_in_tier,
    customer_id,
    first_name,
    last_name,
    total_spent
FROM ranked_customers
WHERE rank_in_tier <= 3
ORDER BY membership_status, rank_in_tier;

Result (sample from our data):

membership_status	rank_in_tier	customer_id	first_name	last_name	total_spent
Bronze	1	1	Karen	Molina	999.99
Bronze	2	3	Roberta	Massey	499.99
Bronze	3	8	Angela	Hanson	0.00
Gold	1	4	Jacob	Adams	599.97
Gold	2	17	James	Rodriguez	350.00
Gold	3	18	Steven	Burnett	180.00
Silver	1	2	Elizabeth	Archer	1599.98
Silver	2	5	Cynthia	Lowery	1200.00
Silver	3	6	Emily	King	1500.00

This query combines a LEFT JOIN (to include customers with no purchases), a CTE for the base aggregation, a window function for ranking, and finally a filter on the rank. The result gives us a quick view of the best customers in each tier.

Example 2: Product Performance vs. Category Average

Business question: For each product, show its total sales and how much it differs from the average sales of products in the same category. Which products are outperforming their category?

Approach:

First CTE computes total sales per product with category.
Second CTE computes average sales per category.
Main query joins the two and calculates the difference.

WITH product_sales AS (
    SELECT 
        p.category,
        p.product_id,
        p.product_name,
        COALESCE(SUM(s.total_amount), 0) AS product_total
    FROM products p
    LEFT JOIN sales s ON p.product_id = s.product_id
    GROUP BY p.category, p.product_id, p.product_name
),
category_avg AS (
    SELECT 
        category,
        AVG(product_total) AS avg_in_category
    FROM product_sales
    GROUP BY category
)
SELECT 
    ps.category,
    ps.product_name,
    ps.product_total,
    ca.avg_in_category,
    ps.product_total - ca.avg_in_category AS difference_from_avg
FROM product_sales ps
JOIN category_avg ca ON ps.category = ca.category
ORDER BY ps.category, difference_from_avg DESC;

Result (sample):

category	product_name	product_total	avg_in_category	difference_from_avg
Accessories	Headphones	599.97	99.99	499.98
Accessories	Wireless Mouse	0.00	99.99	-99.99
Accessories	Keyboard	0.00	99.99	-99.99
Appliances	Refrigerator	1200.00	244.99	955.01
Appliances	Washing Machine	499.99	244.99	255.00
Appliances	Microwave	180.00	244.99	-64.99
Appliances	Blender	0.00	244.99	-244.99
Electronics	Smart TV	1500.00	424.99	1075.01
Electronics	Laptop	999.99	424.99	575.00
Electronics	Smartphone	1599.98	424.99	1174.99
Electronics	Gaming Console	350.00	424.99	-74.99
Electronics	Monitor	0.00	424.99	-424.99
Electronics	External Hard Drive	0.00	424.99	-424.99
Electronics	Tablet	0.00	424.99	-424.99
Electronics	Smartwatch	0.00	424.99	-424.99

Here, joins link products to sales, CTEs structure the intermediate calculations, and the final ORDER BY highlights top performers. We can immediately see which products are stars and which are lagging in their category.

Example 3: Customers Who Made Consecutive Purchases Within 7 Days

Business question: Identify customers who have made purchases within 7 days of a previous purchase – a sign of engaged shoppers.

Approach:

Use LAG() window function to get the previous purchase date for each customer.
Compute the date difference.
Filter where the gap is ≤ 7 days.

WITH customer_purchases AS (
    SELECT 
        customer_id,
        sale_date,
        LAG(sale_date) OVER (PARTITION BY customer_id ORDER BY sale_date) AS prev_purchase_date
    FROM sales
)
SELECT DISTINCT
    c.customer_id,
    c.first_name,
    c.last_name,
    cp.sale_date AS purchase_date,
    cp.prev_purchase_date,
    (cp.sale_date - cp.prev_purchase_date) AS days_between
FROM customer_purchases cp
JOIN customers c ON cp.customer_id = c.customer_id
WHERE cp.prev_purchase_date IS NOT NULL
  AND (cp.sale_date - cp.prev_purchase_date) <= 7
ORDER BY c.customer_id, cp.sale_date;

Result (In respect to our data)

customer_id	first_name	last_name	purchase_date	prev_purchase_date	days_between
1	Karen	Molina	2023-07-15	2023-07-10	5
5	Cynthia	Lowery	2023-06-18	2023-06-12	6

This query uses a window function (LAG) within a CTE, then joins with the customers table to get names, and finally filters. It demonstrates how window functions can identify sequential patterns.

Example 4: Monthly Sales Trends – Running Total and Month‑over‑Month Change

Business question: Show monthly sales totals, a running total, and the percentage change from the previous month.

Approach:

CTE to aggregate sales by month.
Window functions for running total (SUM() OVER) and previous month's sales (LAG).
Compute the change.

WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', sale_date)::DATE AS month,
        SUM(total_amount) AS monthly_total
    FROM sales
    GROUP BY DATE_TRUNC('month', sale_date)
)
SELECT 
    month,
    monthly_total,
    SUM(monthly_total) OVER (ORDER BY month) AS running_total,
    LAG(monthly_total) OVER (ORDER BY month) AS prev_month_total,
    CASE 
        WHEN LAG(monthly_total) OVER (ORDER BY month) IS NOT NULL 
        THEN ROUND(100.0 * (monthly_total - LAG(monthly_total) OVER (ORDER BY month)) / LAG(monthly_total) OVER (ORDER BY month), 2)
        ELSE NULL 
    END AS pct_change
FROM monthly_sales
ORDER BY month;

Result;

month	monthly_total	running_total	prev_month_total	pct_change
2023-06-01	1200.00	1200.00	NULL	NULL
2023-07-01	1599.96	2799.96	1200.00	33.33
2023-08-01	1599.98	4399.94	1599.96	0.00
2023-09-01	499.99	4899.93	1599.98	-68.75

Here, the CTE aggregates data, and window functions add running total and previous‑month comparison. This gives a concise view of sales momentum.

Conclusion

Throughout this article, we've explored three powerful SQL concepts—joins, window functions, and CTEs—using a practical e‑commerce dataset. As a data professional, knowing when and how to apply these tools is essential for building efficient, readable, and maintainable queries.

Here's a quick reference guide based on what we've learned:

Use Window Functions When:

You need to perform calculations across rows without collapsing the result set (e.g., running totals, moving averages).
You're working with rankings or percentiles (e.g., top customers by spending, quartiles).
You need to compare values between rows (e.g., previous sale amount, month‑over‑month change).
Your analysis requires retaining detail while still gaining aggregate insights.

Use CTEs When:

You're breaking down a complex query into simpler, logical steps.
The same intermediate result needs to be referenced multiple times in a query.
You want to improve readability and make your code easier to debug.
You're preparing data for window functions or further transformations.

Use Joins When:

You need to combine data from multiple related tables (e.g., customers with their orders).
Your database is normalized and information is spread across tables.
You're building reporting datasets that require fields from different sources.
You need to understand relationships between entities in your data.

By mastering these advanced SQL concepts, you're not just writing queries—you're building a toolkit for solving real business problems. Whether it's identifying top‑performing products, understanding customer behavior, or tracking sales trends over time, the techniques you've learned here will serve you across industries and datasets.

Data Analysis Step by Step;

1st Read: Git & Github Beginner's guide

If you’re also learning version control with Git, you can read my Git & GitHub beginner’s guide here:
https://dev.to/charles_ndungu/git-for-data-scientists-data-engineers-my-very-first-beginner-guide-git-bash-github-3952

2nd Read: Mastering Excel

After mastering Git basics, you can learn how to analyze data using Microsoft Excel here:
https://dev.to/charles_ndungu/ms-excel-for-data-analytics-a-friendly-practical-guide-for-beginners-hjn

3rd Read: Data Modelling & Schemas

This article dives into data modelling in Power BI, covering star and snowflake schemas, fact and dimension tables, relationships, and why good modelling is essential for accurate insights and fast reports.
https://dev.to/charles_ndungu/the-backbone-of-power-bi-a-deep-dive-into-data-modeling-schemas-1o1l

4th Read: Data Analysis Steps in Power BI

This article reveals how Power BI analysts act as data translators, bridging the gap between messy data and clear business action. We break down their essential three-step process: cleaning raw information, encoding logic with DAX, and designing dashboards that drive real decisions.
https://dev.to/charles_ndungu/from-raw-data-to-real-action-the-analysts-journey-as-a-data-translator-in-power-bi-2gl6

5th Read: From Tables to Insights – A SQL Masterclass

Data lives scattered across tables—customers here, products there, sales everywhere. This article is your guide to bringing it all together. Follow along with a real online store dataset as we build queries that rank top customers, spot sales trends, and uncover hidden patterns. By the end, you won't just write SQL—you'll tell stories with data.
https://dev.to/charles_ndungu/mastering-sql-joins-and-window-functions-a-practical-guide-with-an-e-commerce-dataset-ejg

Repo

https://github.com/Charles-Ndungu/excel-for-data-analytics

From Raw Data to Real Action: The Analyst's Journey as a Data Translator in Power BI

Charles — Sun, 08 Feb 2026 18:54:55 +0000

In today's organizations, a critical gap persists. On one side, executives and managers demand clear, immediate answers to urgent questions: "Are we on track to hit our quarterly targets?" "Which product line is underperforming and why?" On the other side lies the reality of modern data: a sprawling, chaotic landscape of spreadsheets, databases, and legacy systems—each with its own inconsistencies, errors, and obscure logic.

Bridging this gap is the fundamental role of the data analyst. But to call them mere "number crunchers" is a profound understatement. A more apt description is that of a translator. An analyst's core skill is not just proficiency with tools, but the ability to interpret the raw, technical "language" of disparate systems and translate it into the clear, actionable "language" of business decisions.

This translation is a disciplined, three-act process. It begins with taming chaos into a trusted foundation, moves to encoding complex business logic into dynamic calculations, and culminates in designing a compelling narrative that drives action.

Deciphering the Chaos – Translating Raw Data into a Trusted Foundation

The analyst's first task is to confront the "source text": the raw data. This is rarely clean. It's more likely a collection of CSV files with different date formats, a Salesforce report with merged header cells, and a SQL table where the "Region" column suddenly changed from "EMEA" to "Europe & Middle East."

The Translator's First Tool: Power Query

This is where Power Query, Power BI's data transformation engine, moves from being a feature to being a philosophy. Its purpose is not to apply a one-time fix, but to build a single, reproducible source of truth. Every step you record—removing a column, splitting a field, merging a table—is saved as a recipe. The next time data refreshes, the recipe runs automatically, ensuring consistency and freeing you from manual, error-prone cleaning.

Here’s how a translator thinks within Power Query:

Filter at the Source, Not the End: A common rookie mistake is to load 10 years of historical data only to analyze the last quarter. A skilled translator uses Power Query's "Filter Rows" step early in the process to load only the necessary data. This dramatically improves performance and model refresh times.

Pivot and Unpivot Thoughtfully: Data often arrives in a "wide" format convenient for human reading but terrible for analysis. A sales report might have columns for Jan_Sales, Feb_Sales, Mar_Sales. A translator "unpivots" these into two columns: Month and Sales. This long format is what Power BI's relationships and calculations need to work efficiently.

Leverage Custom Columns for Logic: Need to categorize customers based on purchase frequency or flag orders that exceed a certain threshold? Instead of doing this later in DAX (which can hurt performance), create a Conditional Column in Power Query during the data prep phase. This logic becomes part of your stable data foundation.

The output is no longer just "data." It is a structured, reliable, and analysis-ready dataset. The chaos has been translated into order, setting the stage for the next phase: adding intelligence.

Embedding Intelligence – Translating Business Questions into DAX

With clean tables related in a star schema, the analyst now faces the core translation challenge: turning stakeholder questions into calculated answers. This is the realm of Data Analysis Expressions (DAX), the formula language of Power BI.

DAX is more than a collection of functions; it is the syntax for expressing business rules. A question like "What were our sales this month compared to the same month last year, but only for our premium product segment?" requires a precise translation.

Moving Beyond Basic Aggregation: The Art of Context
The power of DAX lies in its understanding of context. A simple measure Total Sales = SUM(Sales[Amount]) behaves differently depending on where it's used. Put it in a card visual, it shows the grand total. Put it in a table sliced by Region, it automatically shows the total per region. This is filter context in action.

The translator uses advanced functions to manipulate this context and answer complex questions:

Time Intelligence for Trend Translation: Questions about growth and trends are fundamental. DAX provides dedicated time intelligence functions to translate them.

Sales PY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
Sales Growth % = DIVIDE([Total Sales] - [Sales PY], [Sales PY])

This code seamlessly calculates prior year sales and the percentage growth, regardless of whether the user is looking at a day, month, or quarter.

The CALCULATE Function: The Master Translator: CALCULATE is the most important function in DAX. It modifies the context of a calculation. It's how you answer "what if" questions.

Sales for Premium Products =CALCULATE([Total Sales],'Product'[Segment] = "Premium")

This measure translates the question "What are sales, but only for premium products?" into a dynamic calculation that respects all other filters on the report.

Writing for Readability: The VAR Keyword: Good translators make complex logic understandable. In DAX, the VAR (variable) statement is essential for this.

Profit Margin % =
VAR TotalProfit = SUM(Sales[Profit])
VAR TotalRevenue = SUM(Sales[Revenue])
RETURN DIVIDE(TotalProfit, TotalRevenue, 0)

This breaks the calculation into logical steps, making it easier to debug, modify, and explain to others.

The output is a suite of dynamic measures. The dataset is now imbued with business logic, capable of answering nuanced questions interactively. The data is intelligent, but it is not yet a story.

Narrating for Action – Translating Insights into Compelling Dashboards

The final and most critical translation is from insight to action. A dashboard is not a data dump; it is a visual argument and a guidance system. Its success is measured not by how many charts it contains, but by how quickly it leads a user to a confident decision.

Design Principles for the Decision-Maker

A translator designs with empathy for the audience:

The Five-Second Rule: The primary objective of the entire page should be understood within five seconds. This is achieved through a clear visual hierarchy: a prominent KPI header at the top, supporting trend charts in the middle, and detailed breakdowns at the bottom.

Guided Interactivity, Not Just Features: Slicers, cross-filtering, and drill-throughs are powerful, but they must serve the narrative. A well-designed dashboard uses bookmarks to create "guided analytical stories"—clicking a button might reset filters, highlight a key trend, and bring a specific detail page to the forefront, leading the user down a pre-defined analytical path.

Leverage the Full Ecosystem: Power BI is more than a canvas. The translator uses Data Alerts to proactively notify stakeholders when a KPI crosses a threshold, turning a passive report into an active monitoring tool. They enable the Q&A feature, allowing users to ask questions in natural language ("show me sales by region last quarter"), fostering a conversational relationship with the data.

The Virtuous Cycle of Informed Action

The journey of the data translator in Power BI is a continuous, virtuous cycle: Chaos → Structure → Logic → Narrative → Action.

Each decision made from a well-crafted dashboard generates new data and new questions, which flow back to the analyst. This starts the translation process anew, creating a resilient loop of increasingly informed decision-making.

The true power of an analyst, therefore, lies not in memorizing every DAX function or mastering every visualization, but in architecting and sustaining this cycle. It is the deep understanding that their role is to be the essential, human link between the raw potential of data and the tangible progress of the business. By embracing the discipline of translation, they move from being reporters of the past to becoming indispensable guides to the future.

Data Analysis Step by Step;

1st Read: Git & Github Beginner's guide

2nd Read: Mastering Excel

After mastering Git basics, you can learn how to analyze data using Microsoft Excel here:
https://dev.to/charles_ndungu/ms-excel-for-data-analytics-a-friendly-practical-guide-for-beginners-hjn

3rd Read: Data Modelling & Schemas

4th Read: Data Analysis Steps in Power BI

5th Read: From Tables to Insights – A SQL Masterclass

Repo

https://github.com/Charles-Ndungu/excel-for-data-analytics

The Backbone of Power BI: A Deep Dive into Data Modeling & Schemas

Charles — Sat, 31 Jan 2026 15:38:07 +0000

https://images.unsplash.com/photo-1551288049-bebda4e38f71?ixlib=rb-4.0.3&auto=format&fit=crop&w=1200&q=80

Introduction

Power BI is a powerful tool for business intelligence, enabling users to connect to various data sources, visualize data, and share insights across an organization. At the heart of any Power BI project is the data model, a crucial component that defines how data is organized, related, and utilized for analysis. Understanding data models is key to unlocking the full potential of Power BI.

This article explains what data models are, why they matter in Power BI, and how to design clean, fast, and correct models using the star schema (recommended) and the snowflake schema (when appropriate).

What is a Data Model?

A data model in Power BI is a collection of tables, relationships, and calculations that represent the underlying structure of your data. It defines how data is stored, how different data entities relate to each other, and how calculations (such as measures and calculated columns) are performed. The data model serves as the foundation for creating reports and dashboards, enabling you to analyze and visualize your data in meaningful ways.

Key Components of a Data Model

Tables: Fact vs. Dimension - The Critical Distinction
In Power BI, tables are the basic building blocks, but understanding their purpose is crucial:

Fact Tables (The "What Happened" Tables):

Contain measurable, quantitative data (metrics/KPIs)
Examples: Sales amounts, quantities, counts, durations
Typically have many rows (millions/billions)
Store foreign keys that link to dimension tables

-- Example Fact Table Structure
Sales_Fact
----------
SalesKey (Primary Key)
DateKey (Foreign Key to Date dimension)
ProductKey (Foreign Key to Product dimension)
CustomerKey (Foreign Key to Customer dimension)
SalesAmount (measure)
Quantity (measure)
Profit (measure)

Dimension Tables (The "Context" Tables):

Contain descriptive attributes and categories
Examples: Product details, customer demographics, date hierarchies
Typically have fewer rows (thousands)
Provide context for analysis (the "who, what, when, where")

-- Example Dimension Table Structure
Product_Dim
-----------
ProductKey (Primary Key)
ProductName
Category
Brand
Color
Size
PriceRange

Relationships: The Connective Tissue

Relationships define how tables are connected in a data model. They establish links between columns in different tables, allowing you to perform cross-table calculations and create complex data visualizations.

Power BI supports different relationship types:

One-to-many (1:*) - Most common (dimension → fact table)
Many-to-one (*:1) - Reverse of above
One-to-one (1:1) - Rare, often indicates design issues
Many-to-many ()** - Requires careful handling with bridge tables

Measures and Calculated Columns
Measures are calculations used to aggregate data in a model, created using DAX (Data Analysis Expressions). They are computed at query time and don't store data:

Total Sales = SUM(Sales[SalesAmount])
Average Price = AVERAGE(Products[Price])

Calculated Columns are custom columns created in your tables using DAX. They store data at the row level and are computed during data refresh:

Profit Margin = 
DIVIDE(Sales[Profit],Sales[SalesAmount],0)

Hierarchies

Hierarchies organize data into multiple levels, making it easier to drill down and analyze data at different granularities. For example, a date hierarchy might include levels for year, quarter, month, and day.

Data Modeling Schemas: Star vs. Snowflake

The Star Schema: Power BI's Gold Standard

The star schema is the most recommended structure for Power BI because of its simplicity and performance benefits. It's called a "star" because when visualized, it resembles a star with a central fact table connected to surrounding dimension tables.

Why Star Schema Excels in Power BI:

Performance: Simple relationships mean faster query execution
DAX Simplicity: Clear context transition for calculations
User-Friendly: Intuitive for report consumers to understand
Optimized Storage: Columnstore indexing works optimally

The Snowflake Schema: When Normalization Matters

A snowflake schema is a normalized version of the star schema, where dimension tables are broken down into multiple related tables. This creates a more complex, "snowflake" pattern.

When to Consider Snowflake Schema:

Your source data is already heavily normalized
You need to minimize data redundancy for storage efficiency
Complex dimensions with multiple hierarchical levels
Integration with existing normalized databases

The Trade-off: While snowflake schemas reduce data redundancy, they introduce complexity that can impact Power BI performance. More tables mean more relationships, which can slow down calculations.

Pro Tip: In Power BI, you can often "flatten" snowflaked dimensions back into a star schema using Power Query transformations, giving you the best of both worlds.

Why Data Models Matter in Power BI

A well-designed data model is essential for effective data analysis and reporting. Here are a few reasons why data models are important:

Performance: The Speed Difference Is Dramatic
Good modeling can make reports 10-100x faster. Here's why:

-- With star schema (optimized)
Total Sales = SUM(Sales[Amount])
-- Simple, fast filter propagation

-- With poor modeling (slow)
Total Sales = CALCULATE(SUM(Transactions[Value]),CROSSFILTER(Products[ID],Transactions[ProdID], BOTH),USERELATIONSHIP(Dates[Date], Transactions[TransactionDate]))
-- Multiple cross-filters = performance penalty

Real Impact:

Star schema report: 3-second load time
Poorly modeled equivalent: 30-second load time
User adoption difference: 80% vs. 20%

Accuracy: Trustworthy Numbers or Guesswork?
Bad modeling leads to wrong numbers through:

Fan traps: Many-to-many relationships without proper bridging
Chasm traps: Missing relationships causing undercounting
Ambiguous contexts: Multiple active paths between tables

Example: Without proper date dimension relationships, time intelligence functions like TOTALYTD() or SAMEPERIODLASTYEAR() will return incorrect results.

Scalability: Future-Proofing Your Solutions
As your data grows from thousands to millions of rows, a well-structured model:

Handles increased data volume gracefully
Maintains consistent performance
Allows easy addition of new data sources
Supports row-level security implementation

Maintainability: Reducing Technical Debt
A clean model is easier to:

Update with new requirements
Troubleshoot when issues arise
Hand over to other developers
Document and understand months later

Creating a Data Model in Power BI

Creating a data model in Power BI involves several steps, from importing data to defining relationships and calculations. Let's walk through the process:

1. Import and Transform Data
Start by importing data from your sources. Power BI supports a wide range of data connectors. Once imported, use Power Query Editor to clean and transform your data.

Key Transformation for Modeling:

Create a proper Date table (never use fact table dates directly)
Flatten normalized structures into star schema when possible
Ensure consistent data types across related columns

2. Define Relationships and Choose Schema

After loading your data, define relationships between tables. Remember:
Aim for star schema whenever possible
Set proper cross-filter direction (usually single direction)
Use integer keys for relationships (faster than text)

3. Create Measures and Calculated Columns

With your schema established, create business logic:
Measures for aggregate calculations (sums, averages, ratios)
Calculated columns for row-level categorization
Time intelligence measures using date dimension

4. Organize and Document Your Model

Group related tables into folders
Use clear, consistent naming conventions
Hide unnecessary fields from report view
Add descriptions to tables and columns

5. Optimize for Performance

Remove unnecessary columns
Reduce cardinality where possible
Optimize DAX calculations
Use Performance Analyzer to identify bottlenecks

Best Practices for Data Modeling in Power BI

To ensure that your data model is robust and efficient, consider the following best practices:

1. Always Start with Star Schema Design
Plan your model with star schema as the default choice. Deviate only when you have specific normalization requirements that justify the complexity.

2. Implement a Proper Date Dimension
Every fact table needs a dedicated date dimension table with continuous dates and time intelligence columns.

// Create comprehensive Date table
Date = ADDCOLUMNS(CALENDAR(DATE(2020,1,1), DATE(2025,12,31)),"Year", YEAR([Date]),"Quarter", "Q" & FORMAT([Date], "Q"),"Month", FORMAT([Date], "MMM"),"Weekday", FORMAT([Date], "dddd"),"IsWeekend", IF(WEEKDAY([Date],2) > 5, TRUE, FALSE))

3. Use Integer Keys for Relationships
Integer keys (especially surrogate keys) perform better than text keys in relationships.

4. Avoid Circular References and Both-Direction Filtering
Circular relationships and both-direction filtering can cause performance issues and incorrect results. Use single-direction filtering as the default.

5. Prefer Measures Over Calculated Columns
Measures are more flexible and don't increase model size. Use calculated columns only when you need row-level categorization for filtering or grouping.

6. Implement Row-Level Security Early
Design your model with security in mind from the beginning, not as an afterthought.

7. Regularly Test and Validate
Test with sample data before full implementation

Validate calculations against source systems
Use Performance Analyzer to identify bottlenecks
Get user feedback on report responsiveness

Conclusion: The Foundation of Power BI Success

Data models are indeed the backbone of any Power BI project. They provide the structure and logic needed to transform raw data into actionable insights. By understanding the critical distinction between fact and dimension tables, implementing the appropriate star or snowflake schema, and following proven best practices, you create a foundation that ensures:

Blazing Performance: Reports that load in seconds, not minutes
Unquestionable Accuracy: Numbers stakeholders can trust
Effortless Scalability: Models that grow with your business
Sustainable Maintenance: Solutions that don't become technical debt

The choice between star and snowflake schema isn't just academic—it's a practical decision with real consequences for report performance and user experience. While star schema is generally the better choice for Power BI, understanding both approaches allows you to make informed decisions based on your specific requirements.

Remember: In Power BI, the model is the report. Beautiful visualizations cannot compensate for a broken foundation. Invest time in proper data modeling, and every report you build will be faster, more accurate, and more maintainable.

Data Analysis Step by Step;

1st Read: Git & Github Beginner's guide

2nd Read: Mastering Excel

After mastering Git basics, you can learn how to analyze data using Microsoft Excel here:
https://dev.to/charles_ndungu/ms-excel-for-data-analytics-a-friendly-practical-guide-for-beginners-hjn

3rd Read: Data Modelling & Schemas

4th Read: Data Analysis Steps in Power BI

5th Read: From Tables to Insights – A SQL Masterclass

Repo

https://github.com/Charles-Ndungu/excel-for-data-analytics

MS Excel for Data Analytics — A friendly, practical guide for beginners

Charles — Sun, 25 Jan 2026 15:44:24 +0000

Introduction

Microsoft Excel is often the first tool people use when they start learning data analytics — and for good reason. It is easy to use, widely available, and powerful enough to turn raw data into useful insights.

At its core, data analytics is about answering questions using data. For example:

How much did we sell?
Which product performed best?
Are sales increasing or decreasing over time?

Excel helps answer these questions by allowing you to organize data in tables, perform simple calculations, summarize large datasets, and create visual charts — all in one place.

This article is a step-by-step introduction to using Microsoft Excel for basic data analysis. No prior experience is required. We will start with simple concepts and gradually build up, showing how Excel can be used to clean data, calculate key values, and create clear summaries that support decision-making.

What Is Data Analytics?

Data analytics is simply the process of looking at data to find meaning, patterns, and answers.

In everyday life, data analytics helps answer questions like:

How much money did we make this month?
Which product is selling the most?
Are sales improving or declining over time?

The “data” can be anything — sales records, survey responses, attendance lists, or expenses. On its own, raw data is often messy and hard to understand. Data analytics helps turn that raw data into useful information.

Microsoft Excel is one of the easiest tools to use for data analytics. With Excel, you can:

Organize data into rows and columns
Perform calculations automatically
Clean and fix messy data
Summarize large datasets
Create charts to visualize trends

This is why Excel is commonly used as a starting point before learning more advanced tools like Power BI, SQL, or Python.

Getting started with Excel (Rows, Columns & Cells — super simple)

Excel stores information in a big grid. The grid has rows, columns, and cells — that’s it. Think of a spreadsheet like a paper chart: each row is a single item (one sale, one product, one quantity), each column is a type of information about that item (product, quantity, price), and each cell is the box where one piece of information sits (for example: the price of one sale).

Why structure matters: good data analysis needs neat, predictable data. That means:

One header row at the top (column names like Date, Product, Quantity)
No blank rows inside your data block
No mixed data in a column (don’t put text and numbers in the same column)

When your data follows those simple rules, Excel can easily:

Sort and filter it,
Run basic calculations,
Summarize it with PivotTables,
And make charts that actually tell a story.

Quick tip: Select your data and press Ctrl + T (or Insert → Table). Turning your range into a Table makes formulas, filters, and charts behave more reliably — and it’s the one small habit that makes Excel feel professional fast.

Excel looks like a big grid. That’s it. Behind the grid you’ll do all your data work.

Think of the grid like a paper ledger or a notebook:

Row — a horizontal line (1, 2, 3…). Each row usually represents one record — for example, one product’s sales in our February table.
Column — a vertical line (A, B, C…). Each column stores one type of information — for example: Product, Unit Price, Quantity, Total.
Cell — the single box where a row and column cross (A1, B2, C3). A cell holds one piece of information: text (e.g., Microwave), a number (e.g., 160), a date, or a formula.

Why this simple structure matters for analysis

When your data is tidy — one header row, no blank rows, one type of value per column — Excel can:

Sort and filter quickly,
Run formulas that fill down correctly,
Summarize with PivotTables,
And build charts that actually make sense.

If your data is not tidy (mixed values, empty headers, blank rows) Excel still works — but you’ll waste time untangling problems. Good structure = faster answers.

Real example

Look at the row for Air Conditioner:

Product	Unit Price	Quantity	Total
Air Conditioner	850	40	34,000

Here:

One row Air Conditioner record.
The Unit Price column always has prices.
The Total cell is the product of Unit Price × Quantity.

In Excel you would put a formula like =C2*D2 in the Total cell and then drag the fill handle down to copy that formula for every row. Excel updates cell references automatically — that’s called relative referencing and it makes repetitive calculations easy.

One tiny pro tip for beginners

Select your full range and press Ctrl + T (or Insert → Table).
Turning your range into an Excel Table:

Adds filter arrows automatically,
Makes formulas easier ([@unit Price] style),
Auto-fills formulas for new rows,
Keeps charts and pivots stable.

This small habit makes your workbook feel professional instantly.

Quick practice (30 seconds)

Click any cell with a number and type =ISNUMBER(A2) in a blank cell — it returns TRUE if A2 is a real number.
Put =C2*D2 in the first Total cell, press Enter, then drag the fill handle down. See the totals fill? You did calculation automation.
Select the range and press Ctrl + T to make it a table.

Hands-on: Basic calculations, quick cleaning, and your first PivotTable

We’ll do three small things in order:
Add simple formulas (Total, SUM, AVERAGE, COUNT, IF)
Quick data-cleaning checks
Build your first PivotTable to summarize the table

Add simple formulas/Functions (Total per row + a running summary)

A — Total per row (you already saw this idea):

Click the Total cell for the first product (e.g., cell E2 if your table columns are A:SlNo, B:Product, C:Unit Price, D:Quantity, E:Total).
Type =[@unit Price] * [@Quantity] or if you prefer cell references (example for row 2):=C2*D2
Press Enter. If your data is an Excel Table, Excel auto-fills the formula for all rows.

B — Grand Total (SUM)

Below the table, pick a blank cell and type:=SUM(SalesData[Total])
or, if not a table:=SUM(E2:E7)
Press Enter → this gives total sales for February.

C — Average & Count

Average Unit Price:=AVERAGE(SalesData[Unit Price])

Number of product rows: =COUNTA(SalesData[Product])

D — Simple IF check (flag low-selling items)

Add a new column LowSales and use:=IF([@total] < 10000, "Low", "OK")

This flags products with Total < 10,000 for review.

Tiny practice for easier understanding;

Change the Quantity for Television to 100 and watch the Grand Total update automatically. You just saw reactive calculation.

Quick data-cleaning checks

These checks help with the following;

Removing Duplicates and Irrelevant Observations: Identifying and deleting repeated records or data points that do not fit the specific problem you are trying to solve, which makes analysis more efficient.
Fixing Structural Errors: Standardizing inconsistent formats (e.g., converting "st." to "Street") and ensuring uniform naming conventions across the dataset.
Handling Missing Data: Addressing gaps in the data through methods like imputation (filling in values) or deletion to maintain the integrity of the dataset.
Managing Outliers: Detecting and handling extreme values that could skew results and lead to inaccurate conclusions.
Ensuring Data Consistency and Formatting: Scaling or normalizing values to a specific range (e.g., 0 to 1) so that algorithms can process them more effectively.
Validating Accuracy: Checking for human errors such as typos, misspelled names, or incorrect numerical values against known lists or standards.
Enhancing Data Compliance: Maintaining accurate and valid records to minimize legal risks and ensure compliance with data regulations.

1) Are numeric cells real numbers?

In a blank cell type: =ISNUMBER(C2)
Replace C2 with a Unit Price cell. TRUE = good.

2) Trim spaces

If Product names look odd, create helper:=TRIM([@Product])

Copy the trimmed values → Paste Values → replace original Product column.

3) Remove exact duplicates

Select the table → Data → Remove Duplicates → check all columns → OK.

4) Find blanks quickly

Select ALL Ctrl + A, Press Ctrl + F → Leave search blank? (Instead use filter): click any column filter → (Blanks) to show missing values.

Build your first PivotTable (summarize Total by Product)

Pivot Tables help you summarize large data quickly without complex formulas.

You can answer questions like:

Total sales per product
Sales per month
Quantity sold by category

Key Functions

Summarization: Automatically calculates sums, averages, counts, or other statistics without requiring manual formulas.
Data Grouping: Organizes information into categories, such as sales by region, employee, or time period.
Trend Identification: Helps spot patterns, outliers, and relationships within complex datasets.
Interactive Exploration: Uses a drag-and-drop interface to rearrange fields, apply filters, and drill down into specific details instantly.

The Four Main Components
When building a pivot table, you typically assign your data fields to four areas:

Rows: Fields placed here appear as labels down the left side of the table.
Columns: Fields placed here appear as headers across the top.
Values: This is where numerical data is calculated (e.g., "Sum of Sales" or "Count of Orders").
Filters: Allows you to isolate specific subsets of data, such as a single year or a specific department.

Steps (exact Hacks)

Click anywhere inside your SalesData table.
Go to Insert → PivotTable. Excel will suggest the table range — leave defaults and choose New Worksheet, then OK.
In the PivotTable Fields pane:
Drag Product into Rows.
Drag Total into Values (it defaults to Sum).
The Pivot shows Sum of Total by Product — great!

Drag LowSales into Filters to quickly filter only low sellers.

PivotField Layout

Pivot Results

Charts in Excel

In Microsoft Excel, charts are visual representations used to make complex data easier to interpret. They allow you to illustrate trends, comparisons, and relationships between different data series graphically.

Common Chart Types

Excel offers several standard chart types, each suited for different data analysis goals:

Column Charts: Best for comparing items across categories using vertical bars.
Line Charts: Ideal for showing trends over time or continuous data.
Pie Charts: Used to show proportions and how parts contribute to a whole for a single data series.
Bar Charts: Function like column charts but use horizontal bars, making them better for long category labels.
Scatter (XY) Charts: Used to show relationships between sets of values, common in scientific and engineering data.
Combo Charts: Combine two or more chart types (e.g., a column and a line chart) to highlight different types of information in one view.

Make a quick PivotChart

With the PivotTable selected, click PivotTable Analyze → PivotChart → choose Column.
Place the chart on the Pivot sheet or copy it to a Dashboard sheet.
Add a clear title: Revenue by Product — February.

Short notes (best-practice tips)

Use Tables (Ctrl + T) — they auto-fill formulas and make Pivots easier.
Avoid mixing data types in a column (numbers + text).
Keep original raw data untouched — do cleaning in a copy or new sheet.
When Pivot doesn’t update after changing data, click Refresh (PivotTable Analyze → Refresh).

Building a Simple Excel Dashboard (Charts, KPIs & Slicers)

Excel dashboards are interactive, visual summaries of key data, using charts, tables, and KPIs in a single view for quick analysis and decision-making.

In this section, we’ll turn our PivotTable into a clean, interactive dashboard that anyone can understand at a glance.

By the end, you’ll have:

A summary chart
Clear KPI numbers
An interactive slicer to filter results instantly

No advanced Excel. Just smart setup.

Create a Dashboard Sheet

Click the + icon to add a new worksheet
Rename it to Dashboard

This sheet will only contain visuals and summaries — no raw data.

Best practice:

Raw Data → one sheet
PivotTables → one sheet
Dashboard → one clean sheet

Add Key KPI Numbers (Top Summary)

From your PivotTable sheet, identify these values:

Total Sales (February)
Number of Products
Average Sales per Product

A — Total Sales

Copy the Grand Total value from your PivotTable
Paste it at the top of the Dashboard

Label it clearly:

Total Sales – February
Increase font size, make it bold.

B — Number of Products Sold
C — Average Revenue per Product

Use borders or shapes (Insert → Shapes → Rectangle) to make these KPIs look like cards.

Add a Slicer (Make It Interactive)

This is where the dashboard comes alive.

Click anywhere inside the PivotTable
Go to PivotTable Analyze → Insert Slicer

Select:

Product
(Optional) LowSales

Click OK

Now:

Drag the slicer onto the Dashboard
Resize it neatly on the side

Clicking a product instantly updates:

Chart
KPIs
PivotTable values

This is real-time analysis.

Clean Up the Dashboard (Very Important)

Make it executive-friendly:

Remove gridlines
View → Uncheck Gridlines
Align everything evenly
Use consistent fonts
Avoid clutter — less is more

Optional polish:

Light background color for KPI cards
Bold headers
No unnecessary decimals

Wrap-Up (What You Built)

Created a dedicated dashboard sheet
Displayed KPI metrics clearly
Visualized revenue by product
Added slicers for instant filtering
Designed a clean, professional layout

This is the foundation of business reporting in Excel.

KPI cards

Chart on dashboard

Product slicer

Full dashboard view

Data Analysis Step by Step;

1st Read: Git & Github Beginner's guide

2nd Read: Mastering Excel

After mastering Git basics, you can learn how to analyze data using Microsoft Excel here:
https://dev.to/charles_ndungu/ms-excel-for-data-analytics-a-friendly-practical-guide-for-beginners-hjn

3rd Read: Data Modelling & Schemas

4th Read: Data Analysis Steps in Power BI

5th Read: From Tables to Insights – A SQL Masterclass

Repo

https://github.com/Charles-Ndungu/excel-for-data-analytics

Git for Data Analysts: My First Beginner Guide (Git Bash + GitHub Step-by-Step)

Charles — Sat, 17 Jan 2026 13:49:25 +0000

Hi, I’m Charles Ndungu.

I recently started learning data science and analytics, and one tool that kept appearing everywhere was Git. At first, Git felt intimidating — lots of commands, confusing terminology, and unclear workflows.

So I decided to learn it step by step and document my experience.

This article walks through my very first Git workflow, from creating a local project to pushing it to GitHub.

If you're a beginner in data science, analytics, or engineering, this guide should help you get started.

Practice repository
Repo: https://github.com/Charles-Ndungu/GIT-basics-practice

Why Git Matters (Especially for Data Work)

Even if you're mainly working with Python, SQL, Power BI, or Excel, Git quickly becomes essential.

Here’s why:

Reproducibility - You can return to any previous version of your project.
Collaboration - Multiple people can work on the same project safely.
Safety - You can experiment without losing working code.
Professional Standard - Most modern data teams expect Git knowledge.

The Simple Mental Model

Before touching commands, this mental model helped me a lot.

Concept	Meaning
Local folder	Your project on your computer
Git	The system that tracks changes
Commit	A saved snapshot of your project
Remote	The online copy (GitHub)
Push	Upload your work
Pull	Download updates

The basic workflow is:

init → add → commit → push → pull

Once you understand this cycle, Git becomes much easier.

What You’ll Need

To follow along:
• Windows computer (this guide uses Git Bash)
• GitHub account
https://github.com
• Git installed
https://git-scm.com/downloads
• (Optional) VS Code or another editor

Step 1 — Install Git

Download Git from:
https://git-scm.com/downloads

Run the installer and accept the default settings.
Once installed, open Git Bash from the Start Menu.

Step 2 — Configure Git (One Time Only)

Git needs your identity for commits.
Open Git Bash and run:

git --version

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

git config --list

This sets your global configuration.

Step 3 — Create Your First Local Git Project

These are the exact commands I used.

# create a project folder
mkdir ~/git-basics-practice

# enter the folder
cd ~/git-basics-practice

# create a simple file
echo "hello from git bash" > hello.txt

# initialize git
git init

# check project status
git status

# stage the file
git add hello.txt

# create first commit
git commit -m "Add hello.txt"

# view history
git log --oneline

At this point, Git is tracking your project locally.

Step 4 — Create a GitHub Repository

Now we connect the project to GitHub.

On GitHub:

Click + → New Repository
Repository name:

git-basics-practice

Important:
Do NOT initialize with README or .gitignore.

Copy the repository URL.
Example:

https://github.com/your-username/git-basics-practice.git

Step 5 — Push the Project to GitHub

Back in Git Bash:

git remote add origin https://github.com/your-username/git-basics-practice.git

git branch -M main

git push -u origin main

If successful, your project will now appear on GitHub.

Important Authentication Note

GitHub no longer allows account passwords when pushing from the command line.
You must use either:

• Personal Access Token (PAT)
• SSH authentication

Personal Access Token (PAT)

Generate a token from GitHub and use it instead of your password.
or

SSH Keys (Recommended)

Generate an SSH key:

ssh-keygen -t ed25519 -C "you@example.com"

Then add the public key to:

GitHub → Settings → SSH and GPG keys

Step 6 — Pulling Changes

If someone updates the repository, or you edit directly on GitHub, download the changes using:

git pull origin main

Git Command Cheat Sheet

These commands are enough to start using Git comfortably.

git init                 start tracking a folder
git status               check project state
git add <file>           stage file
git commit -m "message"  save snapshot
git log --oneline        view commit history
git remote -v            show remote repositories
git push -u origin main  upload commits
git pull origin main     download changes
git checkout -b name     create new branch

A Real Beginner Mistake I Made

When I first pushed my repository, Git said the push was successful.
But when I opened the repo in the browser I got a 404 error.
After some debugging, I discovered the issue:
My remote URL accidentally had an extra dot at the end:

GIT-basics-practice..git

Git accepted the push, but the browser link didn’t match the repository name.
If you encounter a 404 error, check your remote using:

git remote -v

Small typos in URLs can cause confusing errors.

Key Takeaways

If you remember only three things about Git:
• git init starts tracking a project
• git commit saves a snapshot
• git push uploads your work to GitHub

Once you understand this cycle, Git becomes much easier.

Data Analysis Learning Path

1. Git & Version Control  
2. Excel for Data Analysis  
3. Data Modeling  
4. Power BI Dashboards  
5. SQL for Data Analysis

Data Analysis Step by Step;

1st Read: Git & Github Beginner's guide

2nd Read: Mastering Excel

After mastering Git basics, you can learn how to analyze data using Microsoft Excel here:
https://dev.to/charles_ndungu/ms-excel-for-data-analytics-a-friendly-practical-guide-for-beginners-hjn

3rd Read: Data Modelling & Schemas

4th Read: Data Analysis Steps in Power BI

5th Read: From Tables to Insights – A SQL Masterclass

Repo

https://github.com/Charles-Ndungu/excel-for-data-analytics