DEV Community: Mary Nyambura

Joins and Window Functions Made Super Simple

Mary Nyambura — Mon, 02 Mar 2026 11:57:48 +0000

Joins and window functions are two powerful tools in SQL that help you combine data and do smart calculations.

Joins let you bring together information from two or more tables.
Window functions let you add extra information like averages, ranks, or “previous” values without losing any rows.

How they work with simple, everyday examples.

Joins: Stitching Tables Together
Imagine you have two paper lists:

Employees list

Columns: name, salary, department_id
Departments list
Columns: id, name

We want to see each employee’s name and salary plus their department name. Since that information is split between two lists, we use a join to connect them using the department_id.

Example 1.
Basic join (employees + departments)
sql
SELECT employees.name,
departments.name AS department_name,
employees.salary
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;
This returns only employees who have a matching department.
For each employee, the department name is pulled from the second table.

Example 2.
Left join (keep all employees)
sql
SELECT employees.name,
departments.name AS department_name,
employees.salary
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;
Every employee appears in the result.
If there is no matching department, department_name will be blank (NULL).

Window Functions: Adding Helper Columns
A window function is like adding helper columns that show extra information “around” each row, such as:

“What is the average salary in this department?”
“How high is this employee’s salary compared to others in the same department?”

The beauty of a window function is that it keeps every original row and just adds this extra information.

Example 3.
Show average salary per department beside each employee
sql
SELECT employees.name,
departments.name AS department_name,
employees.salary,
AVG(employees.salary) OVER (PARTITION BY departments.id) AS avg_dept_salary
FROM employees
JOIN departments
ON employees.department_id = departments.id;
AVG(employees.salary) calculates the average salary.

OVER (PARTITION BY departments.id) means: “for each department, compute this average.”

Every employee row still appears, and next to their name you see the average salary for their department.

Example 4.
Rank employees by salary within each department
sql
SELECT employees.name,
departments.name AS department_name,
employees.salary,
ROW_NUMBER() OVER (PARTITION BY departments.id ORDER BY employees.salary DESC) AS row_number,
RANK() OVER (PARTITION BY departments.id ORDER BY employees.salary DESC) AS rank_value,
DENSE_RANK() OVER (PARTITION BY departments.id ORDER BY employees.salary DESC) AS dense_rank_value
FROM employees
JOIN departments
ON employees.department_id = departments.id;
PARTITION BY departments.id: do the ranking separately for each department.

ORDER BY employees.salary DESC: sort from highest to lowest salary.

ROW_NUMBER(): gives each row a unique number (1, 2, 3, ...) even if salaries are the same.

RANK(): ties get the same rank, then it skips the next number (1, 1, 3).

DENSE_RANK(): ties get the same rank, but no gaps (1, 1, 2).

Key Points Summary
Joins are like stitching two lists together using a shared ID (like department ID).

INNER JOIN: only show rows that match in both lists.
LEFT JOIN: keep all rows from the first list, even if there is no match in the second.

Window functions are like adding helper columns that calculate things per group without losing any rows.

You can compute averages, sums, or rankings for each group while still seeing every individual row.

The OVER (PARTITION BY ... ORDER BY ...) tells SQL: “do this calculation for each group, in this order.”

There are three main families of window functions:

Aggregate window functions like SUM, AVG, COUNT, MIN, MAX.
Ranking window functions like ROW_NUMBER, RANK, DENSE_RANK, NTILE.
Value/offset window functions like LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE (you can learn these later).

The Concepts
Joins are like taking two separate lists and combining them using a shared ID.

INNER JOIN is like “only show people who appear in both lists.”
LEFT JOIN is like “show everyone from the first list and add matching info from the second where possible.”
Window functions are like putting sticky notes on each row that say things like “the average salary in this department” or “this employee is number 3 by salary here.” They don’t hide or remove anything; they just add extra information next to each row.
OVER (PARTITION BY ... ORDER BY ...) is how you tell SQL which group to use and in what order.

Simple Analogy
Think of:

Joins as merging two spreadsheets by a common column (like employee ID).
Window functions as adding extra columns in that merged sheet that show things like “average per group”, “ranking per group”, or “value from the row above”.

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

Mary Nyambura — Sun, 08 Feb 2026 18:46:57 +0000

In today's fast-paced business world, companies collect vast amounts of data every second. But raw data is like a noisy crowd speaking different languages, it's hard to understand, and even harder to use for making decisions. This is where data analysts, armed with Power BI, step in as crucial translators. They turn this data chaos into clear, actionable insights that directly impact business success.

Let's break down how analysts achieve this translation, taking us from raw information to smart business moves.

Taming the Mess: Power Query (The Data Cleaner)
Imagine you're handed a giant box of LEGOs. Before you can build anything cool, you first need to sort them, separate the colors, find all the flat pieces, and toss out any broken ones. This "sorting and cleaning" in the data world is done using Power Query in Power BI.

Raw data often arrives looking like this:

Inconsistent: "New York," "NY," and "N.Y." all mean the same city.
Incomplete: Missing sales dates or customer information.
Scattered: Sales data in one Excel file, product info in another, and customer details in a database.

Power Query helps analysts:

Standardize: Make sure "New York," "NY," and "N.Y." are all simply "New York."
Combine: Bring together sales, product, and customer information into one easy to use table.
Clean Up: Remove duplicate entries, fill in missing data, or fix incorrect values.

Real world Impact: If a sales report shows "NY" and "New York" as separate regions, the total sales for that city would be wrong. Power Query ensures accuracy from the start, so decisions aren't based on flawed numbers.

Defining Logic: DAX (The Data Brain)
Once the data is clean and organized, it's still just a collection of numbers. To make these numbers meaningful, to turn them into actual business insights, analysts use DAX (Data Analysis Expressions). Think of DAX as the brain of your data model. It allows you to create smart calculations and metrics that answer specific business questions.

Here's how DAX brings data to life:

-Creates Key Performance Indicators (KPIs): Instead of just seeing raw "sales amounts," DAX lets you calculate "Profit Margin," "Customer Retention Rate," or "Sales Growth Year over Year."

Example DAX Formula: Total Sales = SUM(Sales[Amount])
Example DAX Formula: Profit Margin = DIVIDE([Total Profit], [Total Sales])

-Time Intelligence functions: DAX has powerful functions to compare performance over different time periods.

Example DAX Formula: Sales Last Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

This formula automatically looks back exactly one year to get sales for the same period.

Real world Impact: A business needs to know if sales are actually improving. A simple "Total Sales" number might be high, but DAX can reveal that "Sales Last Year" were even higher, meaning current performance is actually down. This critical context helps managers understand if they're truly succeeding.

The Visual Story: Dashboard Design (The Easy to Read Report)
Now that the data is clean and smart, it needs to be presented in a way that anyone from a sales manager to the CEO can quickly understand. This is where dashboard design comes in. A Power BI dashboard is like a well designed newspaper: it quickly gives you the headlines, then lets you dive into details if you want.

Key elements analysts use:
Big, Clear KPIs: Prominently display key numbers like "Total Revenue: $8.2M" or "Profit Margin: 3.7%." These are the "headlines" telling you instantly how things are going.
Interactive Charts: Use visuals like bar charts to compare sales across different product categories, or line charts to show trends over time. Users can click on these to filter the whole report.
Slicers & Filters: These allow users to personalize the view. Want to see only sales from "California" or for the "last quarter"? Just click a button.
Drill throughs: If a manager sees a sudden drop in sales for a specific region, they can click on that region in the chart and "drill through" to a detailed page showing individual transactions for that area, helping them pinpoint the exact problem.

Real world Impact: Instead of sifting through hundreds of rows in a spreadsheet, a sales manager can open a dashboard, see that "Sales in the West Region are down 15% this month," click on the West Region, and immediately see that a particular product line is underperforming, prompting them to investigate further.

The Final Translation: From Insight to Action
The ultimate goal of any data analysis is to drive action. A beautiful dashboard is only valuable if it leads to better business decisions. Analysts don't just show what happened; they help answer: "What should we do about it?"

This involves:
Highlighting Anomalies & Key Drivers: Power BI can automatically point out unusual spikes or drops in data and even identify the biggest factors causing them. For example, "The recent drop in customer satisfaction is mostly due to shipping delays."
Setting Up Alerts: Analysts can configure Power BI to send automatic emails or messages when a key metric crosses a certain threshold (e.g., "Alert: Customer churn rate exceeded 5%"). This ensures that critical issues are addressed immediately.
Providing Recommendations: Good analysts often summarize the key findings from a dashboard and offer clear, actionable recommendations. This might be a 3-bullet point email:

Sales in Region X are significantly down.
Highest customer churn is for Product Z.
Action: Implement a targeted discount campaign for Product Z in Region X next week.

Real world Impact: By providing direct, actionable recommendations based on solid data, analysts empower decision makers to react quickly to problems, seize opportunities, and ultimately improve the company's bottom line. The conversation shifts from "What do the numbers say?" to "What's our next strategic move?"

Power BI Data Modeling: Your Guide to Faster, Less Frustrating Reports

Mary Nyambura — Sun, 01 Feb 2026 14:27:56 +0000

Have you ever created a Power BI dashboard that felt sluggish, showed inconsistent numbers, or was just plain confusing to maintain? You're not alone. I've been there too. The problem often isn't your visuals or DAX formulas it's what happens behind the scenes in your data model.

Think of data modeling as the foundation of your report. A strong foundation means everything built on top works better, loads faster, and gives you accurate results every time.

Understanding Fact and Dimension Tables
At its core, data modeling in Power BI revolves around two types of tables:

Fact Tables are where your measurable events, live the numbers you want to analyze. Think of them as recording "what happened" in your business.

Dimension Tables provide the context, they describe "who, what, when, and where." These are your reference tables that give meaning to the numbers in your fact tables.

The Star Schema: Why It's the Gold Standard
The star schema is the most recommended structure for Power BI models. Here's why:

Imagine a central fact table (your numbers) surrounded by dimension tables (your descriptions). Each dimension connects directly to the fact table, creating a simple, star-like pattern. This structure is simple, efficient, and easy for both Power BI and your team to understand.

How It Works in Practice:
When you ask Power BI to "show me Q1 sales by product category," here's what happens with a star schema:

It filters your date dimension for Q1 dates
That filter flows to your fact table
It joins with your product dimension to group by category
It returns aggregated results quickly and accurately

The Snowflake Schema: When Things Get Complicated
Sometimes you might encounter a "snowflake" structure where dimensions are normalized into multiple related tables. While this might look organized, it forces Power BI to navigate through multiple relationships for every query, slowing everything down.

Unless you have specific technical requirements, stick with the simpler star schema.

Why Good Modeling Makes a Real Difference
Performance: A well structured model can turn a 30 second report into a 3 second one. I've seen this transformation repeatedly same data, better structure, dramatically faster results.

Accuracy: Nothing erodes trust faster than inconsistent numbers. Proper relationships in your model prevent double counting, ensure filters work correctly, and make time-based calculations reliable.

Maintainability: Clean models are easier to explain to colleagues, simpler to update when business needs change, and more consistent across all your reports.

Building Your Model: A Practical Approach

Start with a Date Table Every good model needs a proper date table. This isn't optional Power BI's time intelligence functions depend on it. Here's a simple way to create one:

dax
DateTable =
ADDCOLUMNS(
CALENDAR(DATE(2020,1,1), DATE(2030,12,31)),
"Year", YEAR([Date]),
"Month", FORMAT([Date], "MMMM"),
"Quarter", "Q" & TRUNC((MONTH([Date])-1)/3)+1
)

Structure Your Dimensions Create separate tables for each logical grouping:

Date dimensions (from step 1)
Product/service information
Customer/client details
Location data
Employee/team structures
Design Your Fact Table
Your fact table should:
Contain numeric measures (sales, quantities, costs)
Include foreign keys linking to your dimension tables
Maintain the lowest useful level of detail

Establish Clean Relationships
Connect each dimension to your fact table, setting the filter direction to flow from dimension to fact. In most cases, use single direction filtering for better performance.
Validate with Real Questions
Test your model with common business questions:
Does "sales by month" calculate correctly?
Do filters properly narrow results?
Are year-over-year comparisons accurate?

Common Modeling Mistakes (And How to Avoid Them)
The Mega-Table: Don't mix facts and dimensions in one massive table. Keep them separate for clarity and performance.

Relationship Overkill: Avoid setting all relationships to filter both directions. Use single direction filtering as your default.

Date Confusion: Never use transaction dates directly for time intelligence. Always build a proper date table.

Ignoring Granularity: Ensure all facts in a table share the same level of detail. Mixing granularities leads to incorrect aggregations.

When to Break the Rules
While the star schema should be your starting point, there are valid exceptions:

Role playing dimensions (using the same date table for order date, ship date, due date)
Many to many relationships (requiring bridge tables)
Extremely large dimensions that benefit from normalization
Start simple with a star schema, then add complexity only when you have a clear, justified need.
Your Model Health Checklist
Is your model easy to explain to a colleague?
Do filters propagate correctly through relationships?
Are common reports loading in under 5 seconds?
Do calculations match source system totals?
Can you easily add new measures or dimensions?

If you answered "no" to any of these, your model might benefit from some restructuring.

The Takeaway
Good data modeling in Power BI isn't about complexity it's about clarity. A clean star schema with proper relationships gives you faster reports, more accurate numbers, and happier stakeholders. It's the difference between fighting with your data and letting it work for you.

Start with a solid foundation, keep it simple, and watch your reports transform from frustrating to fantastic.

Microsoft Excel for Absolute Beginners

Mary Nyambura — Sun, 25 Jan 2026 19:32:52 +0000

Introduction
Microsoft Excel is one of the most widely used tools for organizing, analyzing, and visualizing data. Whether you’re tracking personal expenses, managing small business records, or handling school projects, Excel can make complex tasks simple and efficient.

Even if you’ve never used Excel before, learning the basics will help you:
Organize information clearly and efficiently
Automate calculations to save time
Visualize data with charts and tables
Build a foundation for advanced tools like PivotTables and dashboards

This tutorial will guide you through the core features of Excel in a beginner-friendly, step-by-step approach. By the end, you’ll be comfortable working with data in Excel and ready to explore more advanced topics.

this is the dataset that were going to use

this is the dataset before formatting

Preliminary Step: Ensure All Data is Visible
Before diving into Excel, make sure every piece of information is fully readable. Columns should be wide enough so that text doesn’t spill over into neighboring cells, and headers should be clear and distinct. This prevents confusion when entering formulas, sorting data, or creating charts.

How to do it:
click between 1 and A to highlight the whole dataset the go to formats and select AutoFit column width click

your dataset should look like this with spaced columns and width

You also need to freeze your top row and your first column by going to D2 go to view-freeze panes-click the drop down then freeze panes

Why this step matters:
Making sure all information is visible and easy to read avoids mistakes later when you apply formatting, formulas, or create charts. Think of it as setting up a clean workspace before starting any project.
Salary Formatting

Select the Salary column.
Go to Home → Number → Currency.

Your results should look like this.

Date Formatting
Select the Joining Date column.
Go to Home → Number → Short Date.

Simple Formulas
Sum of Salaries: =SUM(E2:E877) → total payroll

Average Salary: =AVERAGE(E2:E877) → average employee pay

Maximum Salary: =MAX(E2:E877) → identify highest-paid employee

Conditional Formatting
Highlight salaries above 100,000.
Highlight cell under home go to conditional formatting, you will see greater than then you write 100,000 enter.

Highlight employees joined before 2020

Using IF Statements
Identify high-salary employees
=IF(E2>100000, "High", "Normal")
so you create a new cell between salary and hire date and name it, after getting the answer in E2 autofill the column to see for the rest.

Lookup Example (VLOOKUP/XLOOKUP)

Scenario: Find department of a specific employee
=VLOOKUP("Williams",C2:D877,2,FALSE)

SUMIF / SUMIFS Example
Total salary by department:
=SUMIF(D2:D877,"Sales",E2:E877)

Total salary of all employees in Sales department AND Office Location = Nairobi (for example).
=SUMIFS(E2:E877,D2:D877,"Sales",Q2:Q877,"Nairobi")

Creating pivot table
Here click anywhere in your Dataset under insert go to pivot tables click new worksheet

Creating pivot table
i also created a Donut chart of average salary by department

Beginner's Guide to Git and GitHub: Version Control Made Simple

Mary Nyambura — Sat, 17 Jan 2026 09:47:25 +0000

Introduction
Git and GitHub are essential tools for developers, data scientists, and anyone working with code. This guide will help beginners understand how to track changes, push and pull code, and use version control effectively.

What is Git
Git is a version control system that acts like a time machine for your code. *It tracks every change you make to your files*, so you can:

Go back to a previous version if something breaks.
Track what changes were made.
Work safely on multiple projects at the same time.

Example:
Imagine you wrote a program yesterday, but it stopped working today. Git lets you revert to yesterday’s version quickly.

What is GitHub?
GitHub is an online platform for storing Git projects. It allows you to:

Share your code with others
Collaborate on projects
Backup your work online

Think of GitHub as a *cloud version of your Git projects*

What is Version Control?
Version control helps you track changes in your files over time. It’s useful because:

You never lose your work
You can see who made changes and when
You can safely work with others on the same project.

Git Workflow for Beginners

Check Git Version: Before starting, make sure Git is installed: Bash code git --version The double hyphen (--version) is required Shows which version of Git is installed on your computer.
Check the Status of Your Project: To see which files have changed or are ready to be saved: Bash code git status Shows files that are modified, untracked, or staged for commit. Helps you understand what Git is tracking, an essential part of version control.
Add Files to Be Tracked: Bash code git add. The . tells Git to stage all files in the folder Staging files prepares them to be saved in your project history
Commit Your Changes: Bash code git commit -m "Describe your change here" The -m flag (single hyphen) is for your commit message Commits save a snapshot of your project locally Each commit is part of version control, so you can revert if needed.
Push Changes to GitHub: Bash code git push Uploads your committed changes to your GitHub repository Keeps your work backed up online Makes your project available to collaborators.
Pull Changes from GitHub: Bash code git pull Downloads the latest changes from GitHub to your local project Ensures your work is up to date before making new changes
See the History of Your Project: Bash code git log

Shows all previous commits with messages
Let's you review the history of changes
A key part of understanding version control in action

Beginner Friendly Daily Workflow
Here’s a simple workflow that combines all the above commands:
Bash code
git --version # Check Git version
git status # See changes
git add . # Stage files for commit
git commit -m "Describe what you changed" # Save snapshot locally
git push # Upload changes to GitHub
git pull # Download latest changes from GitHub
git log # Review history
Every command above is part of version control, tracking your files, saving snapshots, sharing and reviewing history.