DEV Community: Brian O. Njagi

SQL Joins and Window Functions

Brian O. Njagi — Tue, 10 Mar 2026 08:50:58 +0000

1. JOINS

Sometimes it is very difficult to look through data to get specific things. For example, you want the name of customers who made purchases as well as the name of the products they bought.
You have a sales table which only has customer_id and product_id, the product names are in a different table say the products table and the names of the customers are found in the customers table.
As a normal person you would open the sales table and note down the Product IDs and the Customer IDs then move to the customers table and get the names of the customers represented by the specific customer ID then finally move to the products table and do the same.
That's so hectic!
Makes you wonder....what if there was a way to link all these tables and see whatever I want without having to open each table alone?

Well my friend, you are lucky!
I introduce to you the concept of JOINS.
Joins provide a way that enables us connect multiple tables so that you can SELECT the columns you want to see from those tables and display all of them simultaneously. It literally joins tables as long as there exists a common column in the tables being joined together. The most IMPORTANT thing to remember is that there must be a common column.
Lets see how joins work.

Inner Join

Inner Join displays something that is present in all tables. If say a 1 corresponds to cup (1 = cup) in table A and table B has only digit 1. Then inner join identifies that digit 1 in table B matches digit 1 in table A and they both can be said to correspond to cup.

We will use the tables below for examples.

create schema joins
set search_path to joins;

CREATE TABLE employees
(employee_id SERIAL PRIMARY KEY, 
name VARCHAR (100) NOT NULL, 
department_id INT, 
manager_id INT,
salary NUMERIC (8, 2)
) ;
INSERT INTO employees (name, department_id, manager_id, salary) 
VALUES 
('Alice', 1, NULL, 50000),
('Bob',2, 1, 45000),
('Charlie', 1, 1, 47000), 
('Diana', 3, NULL, 60000),
('Eve',NULL, NULL, 40000);
select * from employees;

CREATE TABLE departments 
( department_id INT PRIMARY KEY,
department_name VARCHAR (100));

INSERT INTO departments (department_id, department_name) 
VALUES 
(1, 'Engineering'),
(2,'Sales'),
(3,'Marketing'),
(4, 'Finance');

select * from departments;

CREATE TABLE projects 
( project_id SERIAL PRIMARY KEY, 
project_name VARCHAR (100),
 employee_id INT
) ;


INSERT INTO projects (project_name, employee_id) 
VALUES 
('Redesign Website', 1),
('Customer Survey',2),
('Market Analysis',NULL),
('Mobile App Dev', 3),
('Budget Planning', NULL);

select * from projects;

**Inner join example**

select name, project_name from employees e
inner join projects p
on e.employee_id = p.employee_id;

Explanation: The query joins the projects and employees tables and only selects the names of employees and projects assigned to them.
NOTE the common column here is employee_id on both tables. It establishes the relationship between the two tables.

Left Join

For left join and right join I need you to picture two tables.
Let us call one the primary table and the other a secondary table.
The primary table is our main point of reference meaning that this table remains constant and its contents are sort of static. You are the one to choose which table will be primary or secondary.

Look at the statement below and try to understand it.
select name, project_name from primary_table p
left join secondary_table s
on s.common_column = p.common_column;

Now below is an example of a real query

select name, project_name from employees e
left join projects p
on e.employee_id = p.employee_id;

Explanation: Look at the results the column on the left side shows names of employees selected from employees table. In this example the employees e is the PRIMARY table. Note that there are no NULLS in the primary table but on the project_name column we have two NULLS this is because this column comes from a secondary table called projects p
In the left join the primary table is the first table you selected your columns from in this case the 'employees e' table.
The primary table displays all the things it contains based on column selected. The secondary table appears after the keyword join.
Note that that is for left joins.
Simply, the left table is the primary table

Right Joins

Right joins are the total opposites of left joins.
Look at this general syntax note the position of primary and secondary tables

select name, project_name from secondary_table s
right join primary_table p
on s.common_column = p.common_column;

select name, project_name from employees e
right join projects p
on e.employee_id = p.employee_id;

Look at where the NULLS went to. The secondary table.
So the primary table in this case in on our right hand.
Here NULL shows that there were projects not assigned to any employees.

Full Outer Join

It joins data from both left and right join here both tables are treated as equals

select name, project_name from employees e
full outer join projects p
on e.employee_id = p.employee_id;

See how NULLS appear on both sides. Shows projects not assigned to employees and employees not assigned any projects.

Natural Join

Natural join unites two tables using a common column without you having to use ON keyword.
It just checks for common columns between the two tables and uses them to establish a relationship
For example

select name, project_name from employees e
natural join projects p;

Note the results are similar to inner join. However, natural joins need to be used with caution. It is better not to use it if you are not sure about columns in the tables.

2. Window Functions

Window functions enable us to do calculations on rows. There is some sort of relationship between the rows which we will talk about at the end.
Some of the window functions include: Rank(), Dense_Rank(), lag(), lead(),row_number(), ntile()

Rank() and dense_rank()

They both assign a rank to each row. Just like in a race where we have the race leader, others in between and finally the last person.
So we could rank rows based on total amount spent, total quantity sold etc..
For example rank departments by the number of employees they contain

select department_name, count(*),
rank() over (order by count(*)desc) as rank_
from departments d
join employees e
on e.department_id = d.department_id
group by department_name;

The difference between the dense_rank and rank is that rank skips a number if there is a tie i.e 1,1,1,4 while dense_rank does not skip a number i.e 1,1,1,2

Lead() and lag()

Lead will show what the next value i.e the value in the row that follows.

For example, show the salaries for the next employee on the table.

select name, salary,
lead (salary) over (order by salary) as next_salary 
from employees e;

For lead, the last value is always a null and vice versa for lag

Example show the salary of the preceding employee

select name, salary,
lag (salary) over (order by salary) as next_salary 
from employees e;

Row_number()

Assigns a unique number to each row.

select name, employee_id,
row_number() over (order by employee_id) as row_no 
from employees;

NTILE()

Distributes data into specific segments/groups based on a certain criteria.

For example, distribute employees based on their salary into 3 different groups.

select name, salary,
ntile(3) over (order by salary desc) as tile 
from employees e;

From the above the ones earning highest are placed in tier 1 and lowest in tier 3.

Conclusion

Window functions enable us to carry our calculations per row. As seen above you do notice the relationship between rows that we said. For rank it compares the value in every row and the current row and assigns a number to that row in relation to other rows.
Lead and lag depend on the next row and the preceding row respectively to give an output.
Window functions are useful especially in live data when you want to get the customer who bought first or the customer with the highest purchases etc..

Now let's practice!!!

Getting Started with SQL subqueries. A complete beginner guide with examples.

Brian O. Njagi — Tue, 10 Mar 2026 08:38:40 +0000

Introduction

Imagine a family unit (parent(s) and children). Every child has their own unique traits i.e height, weight, voice, complexion etc...
Now children are to parent(s) what subqueries are to a main query.
So in other words subqueries are the main query's child/children.
And when children grow up they become independent humans where they can do things without involving their parent(s) this too happens with certain subqueries meaning some subqueries can run without depending on the outer main query. These type of subqueries are called non-correlated subqueries while those that are fully dependent on the main query are called correlated subqueries.

Subqueries can be associated with clauses; the WHERE, FROM and SELECT clauses. We shall look at each clause with some examples.

Subqueries In the SELECT clause.

The SELECT clause helps us choose specific columns where we want our data to be retrieved and displayed.
This means that by placing a subquery in a SELECT clause you are sort of adding a new column that will display something you want.

For starters, let us create the necessary schema and tables that we will use for the examples. Create a database and run the queries below.

create schema subqueries;
set search_path to subqueries;

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)
);
-- CREATE Products table in the subqueries schema
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
);
-- CREATE Sales table in the subqueries schema
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 assignment.customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES assignment.products(product_id)
);

-- CREATE Inventory table in the subqueries schema
CREATE TABLE inventory (
    product_id INT PRIMARY KEY,
    stock_quantity INT,
    FOREIGN KEY (product_id) REFERENCES assignment.products(product_id)
);

-- Inserting data into customers table
INSERT INTO customers 
(customer_id, first_name, last_name, email, phone_number, registration_date, membership_status) 
VALUES
(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-7458x399', '2024-06-12', 'Bronze'),
(4, 'Jacob', 'Adams', 'andrew72@hotmail.com', '246-459-1425x462', '2023-02-10', 'Gold'),
(5, 'Cynthia', 'Lowery', 'suarezkiara@ramsey.com', '001-279-688-8177x4015', '2020-11-13', 'Silver'),
(6, 'Emily', 'King', 'igoodwin@howard.com', '(931)575-5422x5900', '2021-05-01', 'Silver'),
(7, 'Linda', 'Larsen', 'pware@yahoo.com', '289-050-2028x7673', '2021-08-20', 'Silver'),
(8, 'Angela', 'Hanson', 'zanderson@gmail.com', '+1-403-917-3585', '2023-03-17', 'Bronze'),
(9, 'Whitney', 'Wilson', 'norma70@yahoo.com', '001-594-317-6656', '2024-01-27', 'Bronze'),
(10, 'Angela', 'Atkins', 'burnsjorge@medina.org', '344.217.5788', '2025-02-05', 'Silver'),
(11, 'Gary', 'Lucero', 'ssnyder@hotmail.com', '001-842-595-7853', '2024-10-08', 'Silver'),
(12, 'Matthew', 'Romero', 'jennifer22@gmail.com', '556.328.91896', '2022-04-07', 'Bronze'),
(13, 'Ronald', 'Thompson', 'hramos@hayes.biz', '298-487-2483', '2023-07-31', 'Bronze'),
(14, 'Suzanne', 'Anderson', 'michaelcole@ruiz-ware.com', '+1-018-029-7257', '2023-11-02', 'Bronze'),
(15, 'Mary', 'Kelly', 'matthewmurphy@gmail.com', '(845)934-9x286', '2021-01-20', 'Bronze'),
(16, 'John', 'George', 'burnettlauren@gmail.com', '+1-708-200-4286', '2022-05-17', 'Bronze'),
(17, 'James', 'Rodriguez', 'brownbrian@blair-sanford.com', '8826047658', '2022-11-25', 'Gold'),
(18, 'Steven', 'Burnett', 'zblackburn@yahoo.com', '(055)912-6726x1246', '2020-01-28', 'Gold'),
(19, 'Jonathan', 'White', 'millsseth@choi-kelly.org', '755-979-1934x772', '2022-02-06', 'Bronze'),
(20, 'Christopher', 'Santiago', 'heidimaddox@hotmail.com', '118-589-6973x058', '2021-10-16', 'Silver'),
(21, 'John', 'Diaz', 'gsmith@hotmail.com', '369.915.4337', '2022-09-17', 'Gold'),
(22, 'Curtis', 'Rose', 'ryanmartinez@moore.com', '(921)461-2128', '2021-12-14', 'Bronze'),
(23, 'Charles', 'Hughes', 'jonesangela@frank-lynn.com', '(152)603-5387x8994', '2024-07-29', 'Silver'),
(24, 'Sarah', 'Cooke', 'whitedennis@tucker.org', '(641)830-6756x56741', '2024-12-15', 'Bronze'),
(25, 'Luis', 'Harrison', 'melvin70@gmail.com', '516.509.9493', '2021-08-19', 'Silver'),
(26, 'Annette', 'Greene', 'aaron68@hall.com', '(733)734-1847x1078', '2025-04-12', 'Bronze'),
(27, 'Melissa', 'Jacobson', 'becklarry@gmail.com', '562-245-7784x4729', '2023-04-28', 'Bronze'),
(28, 'Julie', 'Gardner', 'adamsrodney@hall.com', '+1-014-029-3206x188', '2024-03-31', 'Gold'),
(29, 'Margaret', 'Taylor', 'lfuller@hotmail.com', '(299)340-8900x297', '2021-09-06', 'Bronze'),
(30, 'Erika', 'Mckee', 'wsmith@gmail.com', '(160)040-7321', '2021-05-25', 'Silver'),
(31, 'Donna', 'Whitney', 'justinnicholson@gmail.com', '7086491657', '2022-08-07', 'Gold'),
(32, 'Kristina', 'Wade', 'ashley30@richards-young.com', '603-604-2831x303', '2024-03-16', 'Silver'),
(33, 'Joshua', 'Green', 'ihartman@yahoo.com', '988-232-8285x00933', '2024-05-14', 'Silver'),
(34, 'John', 'Leblanc', 'herickson@green.info', '229.016.2527x20209', '2022-12-24', 'Silver'),
(35, 'Nicholas', 'Campbell', 'ghernandez@hotmail.com', '(982)215-6626', '2022-06-06', 'Gold'),
(36, 'Christopher', 'Hicks', 'ryan48@gmail.com', '884.881.7758', '2021-04-03', 'Silver'),
(37, 'Craig', 'Miller', 'scampbell@johnson.net', '390-328-7286x021', '2024-04-30', 'Silver'),
(38, 'Jennifer', 'Bailey', 'dwright@hotmail.com', '001-992-011-9250', '2022-09-07', 'Silver'),
(39, 'Emma', 'Davis', 'lisalester@hotmail.com', '911.706.3025', '2021-06-04', 'Gold'),
(40, 'Michael', 'Wilson', 'lmerritt@wallace-wang.com', '462.021.3233', '2025-01-14', 'Bronze'),
(41, 'Sarah', 'Church', 'deniseramos@gmail.com', '(840)285-3653x61868', '2021-03-14', 'Silver'),
(42, 'Carolyn', 'Stevenson', 'george62@garrison.net', '040.179.1155', '2024-07-26', 'Silver'),
(43, 'Sarah', 'Cole', 'amandamartin@hotmail.com', '481-651-5206x4800', '2024-07-27', 'Silver'),
(44, 'Jeremiah', 'Lozano', 'bethany38@lopez.net', '846-327-7426', '2023-01-02', 'Bronze'),
(45, 'Leslie', 'Boyd', 'cartermorgan@scott-franco.com', '+1-583-786-3525', '2022-10-22', 'Silver'),
(46, 'Carrie', 'Anderson', 'stevenlivingston@yahoo.com', '+1-086-709-5530x6149', '2024-08-23', 'Gold'),
(47, 'Jared', 'Davis', 'mooretodd@cook.com', '001-069-544-8807x2397', '2022-08-29', 'Bronze'),
(48, 'James', 'Soto', 'patriciaburns@yahoo.com', '129.857.8193x421', '2023-01-27', 'Gold'),
(49, 'Cody', 'Kline', 'bradfordleslie@hotmail.com', '+1-710-706-3703x7998', '2022-06-28', 'Bronze'),
(50, 'Jennifer', 'Perkins', 'austinowens@hill.info', '762.009.1882', '2020-10-19', 'Silver');`

`select * from customers;`

-- Inserting data into products table
`INSERT INTO products 
(product_id, product_name, category, price, supplier, stock_quantity) 
VALUES
(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),
(5, 'Refrigerator', 'Appliances', 1200.00, 'Whirlpool', 40),
(6, 'Smart TV', 'Electronics', 1500.00, 'Samsung', 20),
(7, 'Microwave', 'Appliances', 180.00, 'Panasonic', 75),
(8, 'Blender', 'Appliances', 50.00, 'Ninja', 200),
(9, 'Gaming Console', 'Electronics', 350.00, 'Sony', 60),
(10, 'Wireless Mouse', 'Accessories', 25.00, 'Logitech', 300),
(11, 'Keyboard', 'Accessories', 49.99, 'Logitech', 250),
(12, 'Monitor', 'Electronics', 250.00, 'Acer', 120),
(13, 'External Hard Drive', 'Electronics', 80.00, 'Seagate', 90),
(14, 'Tablet', 'Electronics', 400.00, 'Apple', 70),
(15, 'Smartwatch', 'Electronics', 199.99, 'Apple', 120);

select * from assignment.products;

-- Inserting data into sales table
INSERT INTO sales 
(sale_id, customer_id, product_id, quantity_sold, sale_date, total_amount) 
VALUES
(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),
(5, 5, 5, 1, '2023-06-18', 1200.00),
(6, 6, 6, 1, '2023-10-05', 1500.00),
(7, 7, 7, 1, '2023-08-01', 180.00),
(8, 8, 8, 2, '2023-09-02', 100.00),
(9, 9, 9, 1, '2023-10-10', 350.00),
(10, 10, 10, 3, '2023-11-12', 75.00),
(11, 11, 11, 2, '2023-12-01', 100.00),
(12, 12, 12, 1, '2023-12-07', 250.00),
(13, 13, 13, 1, '2024-01-15', 80.00),
(14, 14, 14, 1, '2024-02-05', 400.00),
(15, 15, 15, 1, '2024-01-05', 199.99);

-- Inserting data into inventory table
INSERT INTO inventory 
(product_id, stock_quantity) 
VALUES
(1, 50),
(2, 150),
(3, 30),
(4, 100),
(5, 40),
(6, 20),
(7, 75),
(8, 200),
(9, 60),
(10, 300),
(11, 250),
(12, 120),
(13, 90),
(14, 70),
(15, 120);

Example
Show how many customers bought each product

select product_name, (select count(*) from sales s
where s.product_id = p.product_id) as no_of_customers
from products p;

Explanation: The outer query displays the names of employees while the inner query counts total projects per employee.
A new column called total_projects is created and each row from outer query is calculated separately to give an answer in the new column.

Subqueries in the WHERE clause

WHERE clause is used for filtering results so that only those that are required are displayed.
In simple terms this subquery acts as a filter.
Example
Show products that are priced higher than the average price of all products

select product_name from  products
where price > (select avg(price) from products);

Explanation: The outer query displays product names from products table while the inner query only displays average price. Now WHERE in this case makes the outer query to only display names of products of whose price is more than the average price of all products. NOTE that no new column was created in this case.

WHERE clause can be used with IN, EXIST or NOT EXISTS

WHERE plus IN

Example

select product_name from products
where product_id in (select product_id from sales 
where price > 500);

Explanation: This is like a join since the two queries are referencing two different tables i.e The outer query provides details from the products table while the inner query provides details from the sales table. However it is important to note that the common column is the product_id column. So we are selecting product IDs in the sales table and comparing to those in product tables and checking which meets the criteria stated (i.e price > 500)

WHERE plus EXISTS or NOT EXISTS

The EXISTS clause just like a filter only displays things present inside a certain category or categories with at least something inside them.
From our database say you want to show product categories where at least one product was sold. So we link the sales table and the products table using the product_id.
So the results will show us the product category for each product sold. In our case every row of a single sale will appear a total of 15 sales and the product categories displayed.

select category from products p
where  exists(select 1
from sales s
where p.product_id = s.product_id);

Note that in order to get single category without duplication, we add the DISTINCT key word as shown below

select distinct category from products p
where exists(select 1
from sales s
where p.product_id = s.product_id);

NOT EXISTS is the opposite of EXISTS so it will only display categories where no product was sold.
For this we can use the example below

select first_name, c.membership_status from customers c
where not exists (select 1 from sales s
where c.customer_id = s.customer_id);

Explanation: This query will display first_name and the membership_status of customers who did not make a single purchase.

Subqueries in the FROM clause

Subqueries in the FROM clause create a temporary table where you can select your data from.
Just like a normal select statement
select column1, column2 from table1
So instead of 'table1', we introduce a subquery there.

Example

select first_name, last_name, total_quantity
from (select customer_id, sum(quantity_sold) as total_quantity
from sales s
group by customer_id) as quantity_summary
join customers c
on c.customer_id = quantity_summary.customer_id
where total_quantity >=2;

Explanation: In the above query, we created a temporary table called quantity_summary in the from clause with two columns (customer_id and total_quantity). This table was derived from the sales table.
So what happens when we run the query is that the outer query selects two columns from the customers table and one column from our derived 'quantity_summary' table.
We use a join to connect the customers table and the new derived table (quantity_summary). NOTE that there must be a common column between the two tables and it is paramount for you to select the common column in the subquery i.e we selected the customer_id in the quantity_summary table.
Remember that the derived table is treated as a completely new entity so if you do not select the customer_id then a join will not work.

Conclusion

Subqueries are important when you need to simplify complex logic, to filter data and to create temporary tables for specific purposes. It saves a lot of time.
Subqueries can appear in the SELECT, FROM and WHERE clause
SELECT - Adds new column (hence it handles every row)
FROM - Creates a temporary derived table (sort of a summary)
WHERE - Displays data that meets certain criteria (a filter)

There you go!! You are all set with subqueries now practice practice practice!

Converting confusing data into actionable insights using DAX and dashboards in power BI.

Brian O. Njagi — Mon, 09 Feb 2026 14:12:20 +0000

Introduction

As a data analyst in a company most of the data you receive is always mixed up. Some missing values, wrongly formatted data and some are just weird and don't make sense. Worst part is that the data contains thousands of rows and tens of columns.
It is paramount that as a data analyst you find your way around this. That is where Power BI comes in.
The most important reason why we use Power BI is because it can be used to manipulate large datasets.
Once we load the data, this is the most common pathway most analysts use to analyze data.

Data Cleaning ---> Data Enrichment ---> Data Visualization

Data Cleaning

Once your data has been loaded into power BI we transform the data and start cleaning.
Cleaning involves: - Removing duplicates
- Removing empty cells
- Replacing some data
- Formatting columns correctly
- Data consistency checks

Data cleaning is one of the most important steps because if you skip this step, it will lead to data breaks later on, difficulties building relationships and also some arithmetic calculations may occur unnecessarily. For example a column may have numbers but you do not want to add those numbers i.e month number (nobody wants to add the month number we just want to know if it is the eighth month or not).

Data Enrichment

This is the addition of columns and deriving new columns from other existing columns to make the data easy to understand and analyze.
We can add new columns using the "new column" function and new measures using the "new measure" function shown below.

In power BI, this is where something called DAX comes in.

What is DAX?
DAX stands for data analysis expressions which is a library of functions and operators that can be combined to build formulas and expressions in Power BI.
DAX is what functions and operators are in excel, just simpler and easier to understand and visualize.

There are many DAX functions.
The most basic and common ones are these three:
1. Aggregation functions- These functions calculate a value such as count, sum, average, minimum, or maximum for all rows in a column or table as defined by the expression. These are the most basic DAX functions and are very useful.

2. Date and time functions - These functions in DAX are similar to date and time functions in Microsoft Excel. However, DAX functions are based on the datetime data types used by Microsoft SQL Server.

3. Filter functions - These functions help you return specific data types, look up values in related tables, and filter by related values. Lookup functions work by using tables and relationships between them. Filtering functions let you manipulate data context to create dynamic calculations.

Below are some of the examples:

Total Sales = SUM(Sales[Amount]) - gives sum of sales in the sales column Total Orders = COUNTROWS(Sales) - give total number of rows in the sales column(assuming every cell = 1 sale) Avg Order Value = DIVIDE([Total Sales], [Total Orders]) divide total sales by total orders

There are filter functions which control rows of data that are included when a calculation is performed.
We use CALCULATE to filter.

Kiambu Planted Area = CALCULATE(SUM('Kenya_Crops_Power BI DATASET'[Planted Area (Acres)]), 'Kenya_Crops_Power BI DATASET'[County]= "Kiambu") This statement means: you are calculating the sum of planted area in acres in Kiambu county. It just adds the planted areas for Kiambu County only.

If you are trying to filter multiple things then
Total Profit Potatoes Meru = CALCULATE(SUM('Kenya_Crops_Power BI DATASET'[Profit (KES)]),'Kenya_Crops_Power BI DATASET'[County]="Meru", 'Kenya_Crops_Power BI DATASET'[Crop Type]="Potatoes", 'Kenya_Crops_Power BI DATASET'[Market Price (KES/Kg)]>100)

All the above require you to select the "new measure function".

However, some will require you to make new tables in order to see specific data sets.
In such cases we use the FILTER function

Select new table on ribbon --> Then write a function

Meru Data = FILTER('Kenya_Crops_Power BI DATASET','Kenya_Crops_Power BI DATASET'[County]="Meru"
For this a new table with be created showing only data from Meru County as shown below

If you want to apply multiple filters we use '&&' as shown below:

Meru Data = FILTER('Kenya_Crops_Power BI DATASET','Kenya_Crops_Power BI DATASET'[County]="Meru" && 'Kenya_Crops_Power BI DATASET'[Crop Type] = "Cassava")
This filters for both Meru and Cassava as shown below.

Data enrichment just involves adding new things that will make the data more insightful and useful.

Data Visualization

After all the above is done, we need to communicate the important findings to key stakeholders.
This is where visualization comes in. Data visualization is the graphical representation of information and data. By using visual elements like charts, graphs, and maps, data visualization tools provide an accessible way to see and understand trends, outliers, and patterns in data.
This is what the dashboard contains.

The dashboard

A dashboard is a way of displaying various types of visual data in one place. Usually, a dashboard is intended to convey different, but related information in an easy-to-digest form. And oftentimes, this includes things like key performance indicators (KPI)s or other important business metrics that stakeholders need to see and understand at a glance.

There are important things that need to be observed in order to make a comprehensive dashboard. Below is the checklist.

Ensure main message is understood in a few seconds
Every visual must have a clear purpose.
It must be well organized and easy to understand data, most important KPIs top left, interactivity, supports decision making.
Make it interactive using slicers.
Use a single theme(avoid using too many colours).

Below is a sample of dashboard note how it is simple and only the key metrics have been captured.

Conclusion
Data analysts must always convert inconsistent and incomplete data into a useful form and visualize it in a way that non-analysts can understand. It is not only about how much you can clean and enrich data but also what insights can you communicate that significantly influence decisions?

Mastering Data Modeling in Power BI: Schemas, Tables, and Relationships for Optimal Performance

Brian O. Njagi — Mon, 02 Feb 2026 06:07:30 +0000

Currently, the world has become inseparable from data and that means we have to adopt and align ourselves too to survive. In order for us to survive we need to know what data is and what tools are used to manipulate data in the most efficient ways.
Enterprises all over are moving towards data, everybody wants to know what data is needed, where do we get it from and why is it important?
Well I joined data science to get these answers too, and when I began the course were introduce to a tool called Power BI.

So what is POWER BI??
In simple terms, this is a Business Intelligence tool-this is what "BI" in "power BI" stands for.
The term ‘Business Intelligence’ ascribes to technologies, software, and processes that gather unprocessed business data and transform it into informative data. Power BI has user-friendly exploitation and effectiveness that gives every person, data savvy or not, even the CEO, the opportunity to extract their full potential out of their data. You just gotta learn a few things about it first.

However, the true magic of Power BI lies not just in its visualization capabilities(which are superb btw) but in the underlying data model. Effective data modeling ensures that reports are accurate, performant, and scalable. This article delves into key concepts like star and snowflake schemas, fact and dimension tables and relationships in Power BI, while explaining why good modeling is essential for good reporting.

What is a schema?
It refers to the structure and organization of data within a data model. Schemas define how data is connected and related within the model, influencing the efficiency and performance of data queries and reports. Understanding schemas helps in designing best data models.

What is data modelling?
Data modeling is the process of identifying, organizing and defining the types of data a business collects and the relationships between them. It uses diagrams, symbols and textual definitions to visually represent how data is captured, stored and used. A well-designed data model helps to understand data requirements, ensure proper structure for reporting, align with business goals and maintain data integrity.

FACT TABLES VS DIMENSION TABLES

These two are the building blocks of data models in power BI.

Fact Tables

Are the heart of the model and store quantitative data i.e the data you want to analyze such as sales amounts, transaction counts. They contain figures. Fact tables are often large, containing huge chunks of data.

Dimension Tables

These provide context to the facts, describing the "who, what, when, where, and why" of your data. They contain descriptive attributes, such as product names, customer demographics, or date hierarchies (year, quarter, month). Dimension tables are usually smaller than fact tables.

The separation of these two highly increases efficiency in data querying.
It is from these two tables that different types of schemas have been developed such as the star and snowflake schema.

1. STAR SCHEMA

This Schema organizes data into a central fact table linked to multiple dimension tables, forming a layout that visually resembles a star. This structure makes analytical queries fast, simple, and efficient making it one of the most commonly used data modeling techniques in data warehousing.

Fact Table: The fact table sits at the center of the schema and stores the measurable, quantitative data used for analysis such as Sales amount, Units sold, Discount and profit.
NB: Each record in a fact table represents a business event (e.g., a sales transaction).
Dimension Tables: Dimension tables surround the fact table and contain descriptive attributes that add context to the facts such as Product details, Customer details and Employee or store information
These tables allow users to filter, and group the fact data for analysis (e.g., sales by region or by month or by product category).
Also note that the star schema does not have to be the shape of a star.
The picture below is a star schema as well, though, not in the shape of a star.

2. SNOWFLAKE SCHEMA

Now imagine if the dimension tables had branches. This means we have a primary source-the fact table- then the dimension tables then other branches attached to dimension tables making it look like the respiratory system or a root. This is the snowflake schema.
It is mainly used when hierarchies are so complex that a star schema would not be able to filter efficiently. This helps reduce data redundancy and develop a more detailed model compared to star schema.

Dimension tables are normalized into multiple related tables, creating a hierarchical or "snowflake" structure.

Fact table is still located at the center of the schema, surrounded by the dimension tables. However, each dimension table is further broken down into multiple related tables, creating a hierarchical structure that resembles a snowflake.

RELATIONSHIPS
This is the glue, the bond that hold everything together i.e the fact table connection to the dimension table and dimension table to others....
They define how tables interact in power BI. This can be done manually or through auto-detect function.
There are different types of table relationships namely:

1. One-to-One Relationship: Each row in the first table is related to only one row in the second table.
2. Many-to-One Relationship: **Many rows in the first table are related to one row in the second table.
**3. One-to-Many Relationship: One row in the first table is related to one or more rows in the second table.
4. Many-to-Many Relationship: Each row in the first table can be related to multiple rows in the second table. This type requires an intermediate table to link the two tables.

**THE IMPORTANCE OF GOOD MODELLING*

1. Reducing Redundancy: Normalized schemas like the snowflake schema help reduce data redundancy, leading to more efficient storage utilization.
2. Managing Large Datasets: Efficient schema design is crucial for managing and storing large volumes of data without compromising performance.
3. Optimizing Queries: Well-designed schemas improve query performance by reducing the complexity of joins and filtering operations.
4. Retrieval speed: Proper indexing of key columns in fact and dimension tables enhances data retrieval speed.

References

Introduction to MS Excel for Data Analytics

Brian O. Njagi — Sun, 25 Jan 2026 16:30:51 +0000

Microsoft Excel is a powerful tool widely used in data analytics. Its user-friendly interface and dynamic features make it an essential application for professionals in various fields. This article will cover the key components of Excel, basic data operations, and advanced functions such as pivot tables and dashboards that can enhance your data analysis skills!!!
So let's get into it future analysts!

1. Overview of Excel Interface

When you open Excel, you are welcomed by a well-organized interface comprising several key components:

The Ribbon: Located at the top, the ribbon contains various tabs (Home, Insert, Page Layout, etc.) that house tools and commands. Each tab is organized into groups for ease of use.
Worksheet Area: This is where you enter and manipulate your data. Each worksheet is made up of rows and columns, forming cells where data is stored.
Formula Bar: Situated above the worksheet area, the formula bar displays the content of the selected cell, allowing you to edit formulas and data. It gives the true value of a cell!!
Status Bar: At the bottom, the status bar shows information about the current state of the worksheet, including calculations and view options.

Below is a photo showing the parts of ms excel.

2. Basic Data Operations

Sorting and Filtering Data

Sorting and filtering are essential for managing large datasets. Here’s how you can do both:

Sorting:
1. Select the range of data you want to sort.
2. Go to the Data tab on the Ribbon.
3. Choose Sort A to Z or Sort Z to A for ascending or descending order.
Filtering:
Filtering enables you to see only the data that you want i.e you only want to see data for employees working in Nairobi.
1. Click on the Data tab and select Filter.
2. Use the dropdown arrows in the header row to filter data based on specific criteria.

Data Validation

Data validation helps ensure the accuracy of data entered into your worksheet; in that if the cell only allows certain numbers you cannot input any other different thing.

Select the cell or range where you want to apply validation.
Go to the Data tab and click on Data Validation.
Set the criteria (e.g., whole numbers, dates) to restrict data entry.

Mathematical Operators

Excel supports a variety of mathematical operators:

Addition (+): =A1 + A2
Subtraction (-): =A1 - A2
Multiplication (*): =A1 * A2
Division (/): =A1 / A2

A1 and A2 represent a specific cell. Example A(column) 1(row) together the cell is A1.

3. Essential Functions

Number and Text Functions

Excel has a variety of functions for manipulating numbers and text:

SUM: =SUM(A1:A10) adds all the numbers in a range.
AVERAGE: =AVERAGE(B1:B10) calculates the average.
CONCATENATE: =CONCATENATE(A1, " ", B1) combines text from multiple cells.

Date and Time Functions

Working with dates and times is straightforward in Excel:

TODAY: =TODAY() returns the current date.
NOW: =NOW() returns the current date and time.
DATEDIF: =DATEDIF(A1, B1, "D") calculates the difference between two dates in days.

4. Advanced Features

Pivot Tables

Pivot tables are powerful tools for summarizing data:

Select your data range.
Go to the Insert tab and click on PivotTable.
Choose where to place the PivotTable and click OK.
Drag and drop fields into the Rows, Columns, and Values areas to analyze your data.

The photo below shows how a pivot table looks like

Lookup Functions

Excel provides functions for searching data:

VLOOKUP: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) searches for a value in the first column of a range and returns a value in the same row from a specified column.
HLOOKUP: Similar to VLOOKUP but searches in rows.
INDEX: =INDEX(array, row_num, [column_num]) returns the value of an element in a specified array.

Creating Dashboards

Dashboards provide a visual representation of your data, this is very important as it is the an intersection between data analysts and non data analysts, this helps people see what the data means without having to go through each data set. Just a summary of almost everything and any useful insights.

Data Preparation: Organize your data and create summaries using functions and pivot tables.
Charts and Graphs: Use the Insert tab to create visualizations such as bar charts, line graphs, and pie charts.
Layout: Arrange your charts and tables in a single worksheet for a cohesive look.
Interactivity: Use slicers and timelines to allow users to filter data dynamically (make sure to connect the slicers to the charts - right click the slicer and check all boxes under "report connections")

Below is an example of a dashboard I created. So cool right?!
Far left are the slicers we are talking about. Slicers behave like filters.

Getting Started with Git: A Beginner's Guide to Version Control, Tracking Changes, Pushing, and Pulling Code

Brian O. Njagi — Sun, 18 Jan 2026 07:53:28 +0000

If you're new to coding or just dipping your toes into collaborative projects, you've probably heard the term "Git" thrown around. But what is it really? And why does everyone seem to be so devoted to it for managing code? In this article, we'll break it down step by step in a beginner-friendly way. No prior experience required
We'll cover the basics of version control, how to track changes in your code, and how to push and pull code from remote repositories.

What Is version Control and Why do we need it?

You ever changed something and wished you could back to how it was initially? Version control helps you see what you had before you made the changes. It is like the memory you thought you never needed. With version control you can always go back and forth making changes until you get the perfection you so desire.
One of the most popular version control systems is Git. And the good part is that it is free!!

Install Git and connect it to your github: Read this article I wrote

How to set up your First Git Repository

Open Git bash and make a new directory, say "my-first-project" and navigate into it by running the following commands
mkdir my-first-project
cd my-first-project

Initialize Git: This enables git to start tracking this folder.
git init
There is a hidden .git folder created. Here is where git does its thing!

Now the repo is ready and we can add a few things such as a html file or a python file

Tracking Changes: Staging, Committing, and Viewing History

Open VS code => file => open folder
Then select the folder you just created "my-first-project" and create a new file in this folder by selecting "new file" on VS code.
Name the file "hello.py" and add this line of code inside it print("Hello Git!")

Check repository status

git status - the output will show no commits and untracked files.

Staging changes

In order for git to track files, the files must first be staged.
git add hello.py - stages a specific file
If more than one file needs staging then you just name it after the first one as below
git add hello.py index.html (another file etc...)
To stage all changes
git add .
Check what has been staged
git status

Commit Changes

A commit records the staged changes with a message describing what you did.
git commit -m "name of file created"
To see your commit history
git log

Remote Repositories: Pushing and Pulling Code

All the above actions happen locally in your computer thus, we created a local repository. This means you can only access the files and track them only on your computer.
Now let us go a little further.
When we want to access our codes in a remote/different computer we have to create remote repositories which are going to be hosted on Github.
We create them locally and then push them to github. Pushing code, in a simpler term just means uploading while pulling is just downloading.

1. Create a Remote Repo: Sign up or log in to your github account and create a new repository and name it my-first-project, finally give it a description . Do not initialize with a README yet. Copy the repo URL.

2. Link Local to Remote: In your git bash terminal
git remote add origin https://github.com/yourusername/my-first-project.git
NB paste the URL (https/ssh) from your github in the place of "https://github.com/yourusername/my-first-project.git" in the above code.

Verify connection git remote -v

Push to Github

git push -u origin main for the first push
then git push for subsequent uploads.

NB: Git will prompt you to log in to your github account to authenticate it is you.

Pulling from github

Say you are collaborating with someone and they make changes, Pulling enables you to download the changes and continue from where they left off.
git pull origin main

INSTALL GIT BASH ON WINDOWS AND LINK IT TO YOUR GITHUB ACCOUNT.

Brian O. Njagi — Wed, 14 Jan 2026 13:30:24 +0000

STEP 1 - INSTALLING GIT BASH

Open this link and then select your operating system, in this case windows and then install.

STEP 2 - SETTING UP GIT BASH

Open the downloaded file.
Click on next button to select components (to be safe make sure all components are ticked). Then on clicking next select the default editor (Visual Studio is recommended -I could be biased {inserts laughing emojis}).
Click on next until you get to the install button.

STEP 3 - CONNECTING GIT BASH TO YOUR GITHUB ACCOUNT

Open git bash (duuh!)

VERSION CHECK

Run the command git --version

CONFIGURATION

First, configure the username by running the command
git config --global user.name "john doe"

Then, configure the email by running the command
git config --global user.email "johndoe@gmail.com"
NB Ensure this email is the one linked to your github account.

Now we have to confirm whether the configuration was indeed successful (yk the drill).
Run the command
git config --list

You should see your username and email somewhere in the output.

GENERATING SSH KEY

Now we need to generate an SSH key which will be very 'key'/important to create and agent that will connect us to our github account.
Now run this command
ssh-keygen -t ed25519 -C "johndoe@gmail.com" (ensure it's your user email).

Now we have generated an SSH key yaay!

CREATING THE AGENT

Run this command
eval "$(ssh-agent -s)"
Voila! We have the agent.

Now what's remaining is to add our identity to gitbash and then connect it to github.

ADDING IDENTITY TO GIT BASH

This identity is specific to your email, which, remember is connected to your github account.
Run this command
ssh-add ~/.ssh/id_ed25519

CONNECTING TO GITHUB ACCOUNT

There is the SSH key that we generated up there. Now this key is linked to your user email, we need that key for this step.
You can get it in two ways:

Run the command
cat ~/.ssh/id_ed25519.pub
And copy that long code given as the output
You could also do it manually by following the path given when this command ssh-keygen -t ed25519 -C "johndoe@gmail.com" was run.
You will find the path somewhere in there and it looks like this
Your public key has been saved in /c/Users/john/.ssh/id_ed25519.pub

Follow that path and open the "id_ed25519.pub" file with a code editor and copy that long text/code.

Now that we have this key fingerprint, we are set!

Open github and log in => user navigation => settings => SSH and GPG keys => New SSH key => paste the key fingerprint text under "key" => add SSH key.

And we are doooonnnneeeee!!!!!!!

DATA SCIENCE BEGINNER'S ROAD MAP 2023

Brian O. Njagi — Sun, 01 Oct 2023 17:42:58 +0000

Introduction

Data is important in almost every field nowadays. This means that data scientists have become a fundamental part of a lot of companies. Data science is a rapidly growing field with multiple potentials and its applications are increasing everyday. As it is a promising field, plenty of people are definitely going to be interested in joining this world. It is thus expected that an increase in data scientists might occur soon. However just like other careers, data science is also quite challenging when proper planning is not done, and that is why I am here. So here I will explain what you need to know as you plan on joining this path. So basically this is me helping you with a roadmap.

What is Data Science?

Data Science is basically the use of data to understand and explain patterns and events. It is a multidisciplinary field that combines maths, statistics, machine learning and other basic computer science knowledge to analyse data.

Why is Data Science Needed?
We need data science for predictive purposes, for example predicting what the price of a product could be in 5 years time.
It is also used to predict success of a product factoring in trends and its usage in the population. In medical field it is used to predict the expected increase or decrease in occurrence of a disease in the future as well as data driven approaches in certain medical procedures such as vaccinations and surgeries.
In the current data driven state of the world, data science provides meaningful insights from this data, enabling better decision making and improved operations.

Data science is used for analysis. This is obvious since there's application of statistical knowledge to extract hidden patterns.

What You Need to Know
You need to know that you need to have a computer. 😉

Basic programming skills
Programming skills such as python, R, scala and SQL.
SQL is used to manipulate and access data.
Python is a programming language used for very many functions such as communication with databases. Python is simple and fast, this is the reason why it's preferred.
Python is also used for machine learning.

Learning GitHub
GitHub is the largest host of source code, it is basically a place where you upload your code and people can access it. It is like a linkedin for developers and your code is your CV.

Machine Learning
Machine learning is making a computer learn from studying data. It is used in Artificial intelligence. It is machine learning that is used for predictive purposes.
This means that for machine learning, you need basic knowledge of statistics such as mean, standard deviation, probability, data distribution etc...

Data Visualization
This is the representation of data in visual terms using graphs, pie charts, histograms etc..
Data visualization is important since it helps communicate information faster from just a glimpse. It thus helps reduce the use of words.

Below is a pictorial representation of what I'm talking about.
NB: I might have not mentioned everything, make sure to check on the extra information.

Other things you need to know is basic problem solving skills and project building skills.

Also, read about the life cycle of a data science project.

Wrapping Up
This journey is fun and challenging.
Self motivation is important and remember you are not alone in this.
Remember, no knowledge is bad knowledge. 😁
All the best as you begin something new. 🎉🎉
You've got this!!