Forem: Robert Njuguna

SQL Joins and Window Functions: A Practical Guide.

Robert Njuguna — Fri, 06 Mar 2026 13:28:54 +0000

SQL is an efficient language that is used to manage and analyze database data contained in relational databases. Joins and window functions are considered to be among the most significant tools of high-level data querying.

SQL Joins - Fusion of Different Tables.

The SQL used to put together rows of two or more tables is a JOIN in SQL. It is done by joining 2 or more tables using a common column in both tables being joined. Joins are important to work with normalized databases, in which data is distributed across a number of tables to minimize redundancy.

Types of Joins

INNER JOIN

Returns only the rows that have matching values in both tables.

Sample: Selecting the names of the employees and the name of the department in which they work. There will only be the employees in a department; employees who have no department or departments that have no employees will not be returned.

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

LEFT JOIN (or LEFT OUTER JOIN)

Gives back all the rows of the left table and the matching rows from the right table. In case no match is found, NULLs are shown.
Sample: Giving a list of all the employees, including those who are not assigned to any department.

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;

RIGHT JOIN (or right outer join)

Returns all rows from the right table and the matching rows from the left table.
Example: Selecting all departments, even those that have no workers/employees.

SELECT e.name, d.department_name
FROM departments d
RIGHT JOIN employees e
ON e.department_id = d.department_id;

FULL JOIN (full outer join)

Returns everything from the joined tables.

Example: Combining the employees and department tables.

SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

CROSS JOIN

Gets the Cartesian product of two tables. Each row of the first table is multiplied by each row of the second one. Essentially, if table one has 5 rows and row 2 has 4 rows, you would expect a total of 20 rows in the cross-joined table.

Example: Generating the combinations of employees and departments.

SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;

One thing about cross joins, it doesn't need the on statement.

Window Functions: Row-Level Calculations

Window functions allow you to perform calculations across a set of rows that are related to the current row.

Common Window Functions

ROW_NUMBER()

Assigns a unique sequential integer to rows within a partition.
Example: Ranking employees by salary within each department.

SELECT e.name, d.department_id, department_name, e.salary, ROW_NUMBER() OVER (PARTITION BY department_name ORDER BY salary) AS salary_rank
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

RANK() and DENSE_RANK()

RANK gives a sequence to rows, but in the case of rows with a similar value, it skips ranks based on ties, while DENSE_RANK doesn't skip ranks even in a tie.

SELECT name, department_id, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

SUM(), AVG, MIN, and MAX as Window Functions

Carries out aggregate inspections on a window of rows.
Example: Calculating the total salary expense per department while still showing individual employees.

SELECT name, department_id, salary, SUM(salary) OVER (PARTITION BY department_id) AS total_salary
FROM employees;

LEAD() and LAG()

Accesses subsequent or previous row values within a partition.

Example: Comparing the salary of each of the employees with both the past and the future salary in the dataset.

SELECT name, salary,
LAG(salary, 1) OVER (ORDER BY salary) AS previous_salary,
LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary
FROM employees;

TIP

Figuring out how to rank, do totals, calculate moving averages, and make comparisons between current and previous values, window functions are best suited for analytics efforts where row-level information cannot be lost.

The Joins and Window Functions Combination.

Joins and window functions tend to be used together when it comes to more intricate analytics. For instance, you could join employees with departments, then rank salaries within each department:

SELECT e.name, d.department_name, e.salary, RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS salary_rank
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

This query provides a ranked view of salaries for each department, combining relational data retrieval with advanced row-wise computation.

Conclusion

Joins and window functions are highly applicable in SQL. Joins make it easy and effective to combine data from several tables. Windows functions allow one to compute calculations of rows without losing any detail. A combination of the two will provide more powerful insights on your datasets. They are useful in ranking, totals, comparisons, and analytics. The practice of these functions will make your SQL queries strong. Working with data involves working with learning joins as well as window functions, which is a crucial part of the work of any data professional.

How Analysts Translate Messy Data, DAX, and Dashboards into Action Using Power BI

Robert Njuguna — Mon, 09 Feb 2026 15:14:31 +0000

Data is rarely received in an ideal state. In the physical world it is incomplete, contradictory, replicated, and dispersed among various systems. However, business remains keen to find such straight answers: What is driving costs? Where are we losing revenue? How can performance improve? An analyst is supposed to fill that gap. Power BI offers the arsenal, which enables analysts to transform sloppy data into dependable information and, eventually, into action.

A Power BI analyst is the intermediary between the source systems and decision-makers. Through this process, the application of organized data cleaning, dimensional modelling, data aggregation functions, and intentional visualization can allow the analysts to transform initial information into trustworthy knowledge that drives decision-making.

Power Query and the ETL Foundation.

Analysis life cycle commences in Power Query, where Extract, Transform, Load (ETL) standardizes the raw inputs.

Examples include hospital and pharmacy data, which usually include:

The dates are recorded as a text rather than a date.
Different systems use different names of drugs.
Blank values, negative values (where they should not be negative, like transaction costs, cost of medicine or even quantity of medicine given to patients).

Duplicate invoice numbers

The analyst uses transformation steps to impose data types, trim and clean text, eliminate duplicates, substitute nulls, and derive attributes. It is possible to break down a single Date column into Year, Quarter, Month, and Day to expand the time intelligence in the future.

In power Bi the Time intelligence functions are mainly used to clean the Date column

For instance:

Year (Date)

Month (Date)

Day (Date)

These Time intelligence functions returns only the Year, month number, or Day number of the month respectively of the desired date on a separate column.

Star Schema and Dimensional Modeling.

Once prepared, the analyst models the data with the dimensional modeling methods, the star schema being the most common.

A transactional record may be contained in a central fact table like

Prescription ID
Patient ID
Product ID
Date
Quantity
Unit Price
Cost

Dimensions tables surrounds the facts table and give descriptive information:

Dim Patient - age, gender, type of insurance.
Dim Product—name of drug, brand name, therapeutic classification.
Dim Date—fiscal periods, weekdays, and months.
Dim Department - ward, facility, region.

The relationships are generally one-to-many, where the dimensions flow to the facts. The structure lessen redundancy as well as make aggregations predictable. In the absence of this design, totals can be counted twice, filters can fail, and there will be worse performance.

DAX: Encoding Business Logic

DAX is applied to develop standardized metrics with an appropriate model.

The analyst defines it instead of computing revenue in several ways.
For instance:

Total Revenue = SUMX(FactSales, FactSales[Quantity] * FactSales[Unit Price])

Profitability may be defined as

Gross Profit = TOTAL Revenue [Cost] - SUM (FactSales[Cost])

Since DAX works in a filter environment, analysts can immediately observe the performance by month, department, or type of drug without needing to rewrite formulas.

This guarantees one version of the truth in the organization.

Architecture and Visualization.

Good dashboards are not created but designed. The layouts created by analysts are built in accordance with information hierarchy: KPIs are on top, diagnostics are in the middle, and details are at the bottom.

For instance:

There are cards showing Total Revenue, Total Prescriptions, and Gross Margin.

Line charts depict trends of utilization on a daily or monthly basis.

The bar charts are used to compare products (say products utilization per departments) or (departments found in each county).

Color logic (e.g., red decline, green improvement) is used to provide the user with a quick way to understand performance. Date, facility, or Location (county) slicers allow the presenter to control the interactivity without making the audience feel bombarded.

It is aimed at rapid thinking and directed investigation.

Between the Insight and Operational Action.

A dashboard may indicate that some drugs are in large stock but not being utilized. By cutting further, the analyst can find that demand declined following a treatment regimen alteration.

The same understanding can stimulate the following procedures, like changing the quantities of procurement, renegotiating contracts with suppliers, or reallocating inventory between locations.

In this case, analytics transforms the reporting of the past to the future strategy.

Conclusion

Power BI allows an analyst to combine ETL, dimensional modeling, DAX computation, and visualization into one decision system. The layers are based on each other: clean data is used to come up with good models and good visuals, good models are used to come up with good measures, and good measures and visuals are used to make good decisions. When properly implemented, dashboards are working tools that minimize uncertainties, enhance productivity, and generate quantifiable business performance. This is the how chaotic data is brought into action.

Schemas and Data Modeling in Power BI

Robert Njuguna — Tue, 03 Feb 2026 18:55:09 +0000

Schemas and Data Modeling in Power BI

Data modeling is an important step in Power BI as it defines the way of organizing, associating, and analyzing data. An effective data model enhances the performance of a report, guarantees correct output, and simplifies it to create meaningful visualizations for the users. The major ideas in the Power BI data modeling are schemas, fact and dimension tables, and relationships.

Star Schema

The most commonly used and most recommended data model in Power BI is the star schema. A star schema has one central fact table and several dimension tables that link to the fact table, forming a star.

The fact table is filled with quantifiable values like the amount of sales made, the number of patients, or the yield.

Example

The information stored in dimension tables is descriptive and includes dates, locations, products, hospitals, or the type of crops.

All the dimension tables are directly related to the fact table through a one-to-many relationship. Star schemas are simple to maintain and comprehend and are very effective for the in-memory engine of Power BI. The number of joins is reduced; therefore, reports load quicker, and DAX calculations are more effective.

Snowflake Schema

The snowflake schema is a variation of the star schema that is more advanced. The use of this model entails the normalization of dimension tables into several related tables. As an illustration, a location dimension can be divided into country, region, and city tables.

Example

Although snowflake schemas minimize redundancy in data, they add more relationships and complexity. In Power BI, this has the ability to slow performance and complicate DAX formulas to write and debug. Owing to this reason, snowflake schemas are neither popular nor usually sought-after except when the dataset is large or already structured in that manner.

Fact and Dimension Tables

To master good data modeling, it is important to understand the distinction between fact and dimension tables:

Fact tables

Essentially consists of numerical values (metrics or measures).

Have many rows
Examples: admissions, sales transactions, and crop production records.

Dimension tables

Hold descriptive characteristics.
Have fewer rows.
Examples: date, hospital, department, crop type, county.

Common measures in Power BI are totals, averages, and ratios generated in fact tables, whereas dimensions are used to slice and filter the data.

Relationships in Power BI

Relationships indicate the connection between tables. Power BI has a one-to-many relationship, the most prevalent type of relationship.

Good practices for relationships include:

Single-direction (dimension to fact filtering).
It is better to avoid many-to-many relationships.
Ensuring Matching data types and clean keys.

Distorted or complicated associations may cause incorrect totals, undesirable filtering, and inefficient execution.

Significance of Good Data Modeling.

Good data modeling is important for the following reasons:

Performance—Properly modeled models, in particular star schemas, save memory and increase report response time.
Precision—Relationships are clear so that the measures are calculated, and filters act as expected.
Usability—Clean models allow easier report building as the user is not confused.
Scalability—A well-structured data model is capable of adding more data and data measures without significant redesign.

In practical data like hospital records or agricultural data of Kenyan crops, it is good to model the data so that the analysts can make credible information to aid in decision-making.

Conclusion

Effective Power BI reporting depends on data modeling. With the help of star schemas, well-defined fact and dimension tables, and straightforward relationships, one obtains better results in terms of performance and accuracy of the analysis. The snowflake schema has a place, but Power BI suits best with clean and intuitive models. One of the ways to guarantee quality dashboards and reliable business intelligence results is investing in suitable data modeling.

Introduction to Excel For DATA Analysis

Robert Njuguna — Tue, 27 Jan 2026 17:02:17 +0000

Introduction

The most common data analytics tool that is popular with beginners is Microsoft Excel. It is simple to operate, versatile, and strong enough to carry out simple data analysis functions without possessing any programming skills.

In this article, I present MS Excel as an analytics tool used in data analysis and how it may be utilized to analyse data using straightforward examples. This tutorial will assist the beginners who are new to Excel and wish to know how it can assist in the analysis of data.

Data Analysis

Data analysis is the process of collecting, cleaning, analysing data to discover important information, trends, and patterns in order to make informed decision.

Data Collection

Data collection is the process of obtaining both primary and secondary data for the purpose of analysis. This can either be in the field (primary) or already stored data from databases (Secondary data).

Once collected, data is stored in a tool(Excel) for analysis. This is called data entry. For example, say you are a HR analyst, and you wish to analyse employee data using Excel. You have to first obtain the employee data from the relevant databases and store it in an excel file as shown below:

Data Cleaning

Data cleaning involves ridding data of any inconsistencies which would otherwise negatively impact the analysis process. This can involve:

Data formatting
Getting rid of duplicates (Mainly done in the unique identifier Column)
Handling Outliers
Handling Blanks/Missing Values

Data Formatting

This is the process of correcting any formatting problem.
For instance in the HR dataset, the Hire date Column the date data doesn't currently fit in the Hire date column. See Below:

This is fixed by extending the Hire date Column. This is done by right clicking the + sign at the furthest end of the column.

The number formatting also comes into play, For example, the Employee ID column in the HR dataset is initially formatted into number format rather than text format.

How do you know this? - In excel, text is always aligned to the left, while Date and number formats are always aligned to the right for each cell in a column.

To fix this, the Format option is utilized in excel. Select the Employee_ID column Go to:

Home>Font>Click the furthest Arrow>Format Cells>Text

Getting rid of duplicates

This is done by first conditional formatting the Unique Identifier column, where you highlight the duplicates. Once the duplicates are visualized you can use logical arguments to suggest how to deal with duplicates.

Select the Unique Identifier Column:
Home>Styles>Conditional Formatting>Highlight Cell Rules>Duplicate Values
and then highlight with your preferred colour.

Once highlighted, the next step if applying you criteria. For instance, In our example for the HR dataset, we can choose to keep the oldest Unique Identifier (Basically, the first ID entry and delete subsequent duplicate entries).

Handling Blanks/Missing Values

Missing Values can be as a result of data entry problems, or unavailable data for that particular Cell. Say for example you have Sales data set that contains countries and their respective cities. If the a particular country doesn't have data in the city column. How do we handle this?

We can fill the empty cells with unavailable to prevent adding incorrect values/Cities for a particular set of data.

Next, for values in a particular column, we can fill empty cells using the averages, median or mode of other columns. This is decided by what the data represents. E.g., ratings(median), Salary(average, median) e.t.c.

This is done by first computing the arithmetic's of these particular columns:

= Average ([Cell range])
= Median ([Cell range])
= Mode ([Cell range])

Handling Outliers

1. Identify Outliers

Boxplots (most common)
Z-scores (values > ±3 are often outliers)
IQR method

Lower bound = Q1 − 1.5 × IQR

Upper bound = Q3 + 1.5 × IQR

2. Verify the Outliers

Before removing anything, check:
Is it a data entry error? (extra zero, wrong unit)
Is it a valid but extreme value?

3. Remove Outliers

Use this only if the value is an error or irrelevant.
Delete the row
Filter values outside acceptable ranges

4. Transform the Data

Reduce the impact instead of removing:
Log transformation
Square root transformation

Data Analysis

This is the process of obtaining imortant information/insights from cleaned and transformed data

1. Descriptive Statistics (Start Here)

This summarizes your data so you understand it.

In Excel:

Assume your cleaned prices are in Column B.

Mean (Average)

=AVERAGE(B:B)

Median

=MEDIAN(B:B)

Minimum & Maximum

=MIN(B:B)
=MAX(B:B)

Standard Deviation

=STDEV(B:B)

Interpretation example:
“The average product price is KSh 1,666, with most prices clustered around the mean, as indicated by a standard deviation of …”

2. Identify Outliers (IQR Method – Practical)

This shows whether any prices are unusually high or low.

Step-by-step:

Q1

= QUARTILE(B:B,1)

Q3

= QUARTILE(B:B,3)

IQR

= Q3-Q1

Lower Bound

=Q1-1.5*IQR

Upper Bound

=Q3+1.5*IQR

Any price below the lower bound or above the upper bound is an outlier.

Pivot Tables and Visualizations

PIVOT TABLES

Pivot tables are used to summarize data in excel. Pivot tables come in hand when one wants to group dataset using a particular criteria or to aggregate a given set of grouped data. For example, You may want to calculate the total revenue by a given region, or the average salaries of employees by Region. This can be easily done using pivot tables.

To create one, you select the entire data range>Insert> Pivot table>From table Range.

This inserts the pivot table in a new worksheet or a pre-created worksheet

The next steps involves summarizing the desired data criteria. For example, Average Salary by departments as shown below:

Visualization

This can either be done by raw data, or data from the pivot table. From raw data, a column or columns is/are selected, and a chart is inserted.

This are some of the charts that can be created using raw data in excel:

The charts can also be created using the pivot tables, by heading to:

Pivot table analyze > pivot charts,

and selecting the desired chart.

Dashboards

Lastly, once the visualizations are complete, one can decide to present the entire visual, KPI's on one panel. This is called a dashboard. The charts are clearly and systematically arranged to tell a story about the data. The dashboards are also important since all the required information about your data can be placed in one place which makes it easier for presentation:

See an example below:

The above dashboard clearly and visually presents a story about the employees data.

Conclusion

To sum up, Microsoft Excel is an effective and easy-to-use data analysis tool. It enables one to gather, clean, analyze, and visualize information without having to know how to program. Excel is useful because it allows users to discover valuable insights and trends through its data formatting capabilities, missing values, duplicates, descriptive statistics, pivot tables, and charts. Dashboards also improve knowledge as they display critical information in a single transparent display. Altogether, Excel is a great place to start with when someone wishes to learn more about data analysis and how to use simple and practical tools to make an informed decision.

Understanding GIT: Version Control, Push and Pull Code, and Tracking changes

Robert Njuguna — Sun, 18 Jan 2026 10:15:09 +0000

1. This article tackles:

What is Version Control
How Git Tracks Changes
How to Push and Pull code

1.1 What is Version Control

For instance say you write an essay and there are different versions, e.g., Final_doc_1.docx, final_doc_2.docx. This can quickly become messy.

When it comes to GIT, Version control solves this by automatically tracking and updating changes without saving two different versions. Git helps in:

Saving each change Automatically
Letting one go back to previous versions
Seeing who changed any part of the document and when.

1.1.1 Saving each change Automatically

This is done through:

git add .
git commit -m "saved changes"

1.1.2 Letting one go back to previous versions

To see previous versions of commit, one can view through:

git log

This displays the log of the commit, giving the commit Id, name of the author and the Date of the commit. For instance:

commit: abc3wef
Author: Robert
Date: Mon Jan 15 10:30:00 2026

Therefore, in order to go back to a specific version of the commit, we use:

git checkout <commit-id>

Moreover, when one wants to get the most recent version of the commit, we use the more general version:

git checkout main

1.1.3 Showing who changed what and when

Showing who changed what and when is done using the code:

git log --oneline

This displays a short, easy-to-read summary of your project’s commit history.

Next, to see what was changed the command:

git show

is used to display all the exact changes done to the files.

Last, but not least, the code:

git blame filename.txt

is used to show line by line history of the changes made(who changed each line)

1.2 How Git Tracks Changes

Git does not automatically save every change you make. Instead, it tracks changes using three simple stages. Understanding these stages is the key to using Git correctly.

The Three Stages of Git

1.2.1 Working Directory

This is where you edit your files.

You write code
You delete or modify files
Git notices the changes but does not save them yet

To check changes, one uses:

git status

Git gives you a clear summary of what’s happening in your repository.
Example output:

On branch main
Changes not staged for commit:
  modified: index.html

Untracked files:
  script.js

1.2.3 Staging area

This is where you tell Git the changes you want to save. This is done through:

git add .

Basically, you are preparing Git for a commit and telling Git the changes you wish to keep.

1.2.4 Repository (Commit History)

This is where Git saves the staged files Once you commit. The commit is done by:

git commit -m "New files added"

Once this is done, the committed staged files are now permanently saved in Git's history.

Git knows what has changed by comparing the last commit with the current files. This can be displayed in Git using the code:

git diff

This displays the added or removed lines, or any modified code.

In simple terms, think of Git like a camera, where the Working Directory is the scene you are setting up in order to capture a picture, Staging Area is what you frame in the photo and Commit is the photo you save.

1.3 How to Push and Pull Code in Git

Basically, when one is working with Git the code you are writing exists in two places Local repository(your computer) and Remote Repository (GitHub). Essentially, pushing and pulling keeps these two locations in sync.

1.3.1 What is push ?

When you are done committing any changes on a file, pushing basically saves the files into the Remote Repository(GitHub). This is done by using the code:

git push

In short, this is like uploading your saved work.

1.3.2 What does "Pull" mean?

Pull basically downloads the saved files from the Remote Repository into your Local Repository(into your computer). This is done if you are occasionally collaborating and others may have committed changes in the last version you interacted with.

This is done using the code:

git pull

In short, this is like downloading updates. If you don't pull first before pushing Git may block your push or You may see a merge conflict. That’s why pulling first is a good habit.

Fun twist: The name Git comes from British slang meaning “someone who is stubborn”—which is fitting, because Git never forgets your changes.