Forem: Mariam Turnesh

How to connecting Power BI to SQL Database

Mariam Turnesh — Sat, 04 Apr 2026 16:59:32 +0000

Introduction

At some point in your data journey, you build something in a database and realise that the people who actually need to use that data are managers, clients, the rest of the team who are never going to open a SQL editor. They need charts. They need something they can click through, filter, and read without writing a single query.
That's where Power BI comes in.

Power BI is Microsoft's business intelligence and data visualisation tool. It takes data from various sources and turns it into interactive reports and dashboards, the kind that update automatically, respond to filters and can be shared across an entire organisation. Companies use it to track sales performance, monitor inventory, analyse customer behaviour, measure KPIs and make decisions based on what's actually happening in their data rather than what someone guesses is happening.

But Power BI doesn't create data on its own. It connects to where the data already lives and more often than not, that place is a SQL database.
SQL databases like PostgreSQL are built for exactly this kind of work. They store large volumes of structured data reliably, organise it into tables with clear relationships between them and allow multiple systems to read from and write to the same source at the same time. A business might have thousands of customer records, hundreds of thousands of transactions and a product catalogue that changes daily, all stored in one place, always current, always consistent.

This is precisely why connecting Power BI to a SQL database matters so much. Instead of exporting data to a spreadsheet, building a chart, and then repeating the whole process when the data changes, a direct database connection means your reports are always reading from the live source. When the database updates, the dashboard reflects it ,no manual steps, no stale numbers.
In this article, we'll walk through how to make that connection work: first with a local PostgreSQL database, then with a cloud-hosted one on Aiven.

Part 1 : Connecting Power BI to a Local PostgreSQL Database

A local database is one running on your own computer. If you've been learning PostgreSQL and building databases on your machine, this is where you start.

Step 1 — Open Power BI and click Get Data

Open Power BI Desktop and start with a blank report. On the Home ribbon at the top, click Get Data.

Get Data is on the Home ribbon, that's your starting point for any connection

Step 2 — Search for PostgreSQL

A window pops up listing every data source Power BI can connect to, there are a lot of them. Don't scroll. Type PostgreSQL in the search bar, select PostgreSQL Database from the results, and click Connect.

Search saves you from scrolling through the entire list

Step 3 — Enter your server and database details

A connection dialog appears asking for two things: the server name and the database name.

Server:    localhost
Database:  assignment

localhost tells Power BI that the database is on your own machine. The database name is whatever you named your database when you created it ,mine is assignment.

Below that, you'll see a Data Connectivity mode option with two choices:

Import — Power BI copies the data into its own model. Faster to work with, great for most use cases.
DirectQuery — Power BI queries the database live every time you interact with a report. Slower, but useful when you're working with very large or frequently-changing data.

For most projects at this level, Import is the right choice.

Enter localhost as the server, your database name, then choose Import

Step 4 — Enter your credentials

Power BI will ask for a username and password to authenticate. These are the same credentials you use when logging into pgAdmin, the PostgreSQL username (usually postgres) and the password you set during installation.

Enter them and click Connect.

Use the same username and password you'd use in pgAdmin

Step 5 — Select your tables and load

Once connected, the Navigator window opens. It shows every table in your database. Select the ones you want in my case, customers, products, sales and inventory and click Load.

Power BI imports them and they appear in the Data pane on the right side of the screen. Your local connection is done.

Part 2 : Connecting Power BI to Aiven (Cloud PostgreSQL)

Cloud databases work the same way in principle, but your connection details come from the cloud provider's dashboard rather than your local machine.

Aiven is a managed cloud database service, they host your PostgreSQL database on their infrastructure, so you don't have to worry about running it yourself. A lot of teams use this in production.

Step 1 — Get your connection details from Aiven

Log into console.aiven.io, open your PostgreSQL service, and go to the Overview tab. You'll find everything you need listed there:

Host — the address of the server (a long .aivencloud.com URL)
Port — the port number Power BI will connect through
Database name — for Aiven this is typically defaultdb
Username — avnadmin by default
Password — visible from the Overview tab

Everything you need for the connection lives on the Aiven Overview tab

Keep this tab open you'll need to copy from it.

Step 2 — Enter the connection details in Power BI

Back in Power BI, go through the same flow: Get Data → PostgreSQL Database → Connect.

In the connection dialog, enter your Aiven host in the Server field and defaultdb (or your database name) in the Database field. Keep the mode on Import and click OK.

Paste the host directly from the Aiven console, it's a long string, easy to mistype

Step 3 — Enter your Aiven credentials

When the credentials screen appears, enter avnadmin as the username and the password from your Aiven Overview tab.

Use the credentials exactly as shown in your Aiven console

Click Connect. Power BI will authenticate and open the Navigator window, just like the local connection.

A note on SSL certificates

Aiven requires all connections to be encrypted using SSL (Secure Sockets Layer). SSL is a security protocol that encrypts the data travelling between your computer and the cloud server, so that even if someone intercepted it, they couldn't read it.

When you connect Power BI to Aiven, it needs to verify that it's talking to a legitimate server, not something pretending to be one. That verification is done using a CA certificate (Certificate Authority certificate), which you can download from your Aiven Overview tab.

If Power BI rejects the connection with an SSL error, download the certificate from Aiven, rename it from .pem to .crt, and install it into your Windows Trusted Root Certification Authorities store. After a Power BI restart, the connection will go through. You only ever need to do this once per machine.

Part 3 : Loading Tables and Building the Data Model

Once you're connected whether locally or via Aiven the next steps are the same.

Loading your tables

In the Navigator, select all the tables you need and click Load. Power BI imports them and they show up in the Data pane on the right. You can click into any table to see its columns and values.

Setting up relationships in Model view

Here's where a lot of beginners stop too early. Loading tables into Power BI is not the same as telling Power BI how those tables relate to each other. Without relationships, Power BI is just holding four separate spreadsheets, it can't combine them.

Click the Model icon on the left sidebar. You'll see your tables displayed as boxes, each showing their columns. This is where you define how they connect.

For a retail database like mine, the relationships look like this:

From table	To table	Connecting column
`sales`	`customers`	`customer_id`
`sales`	`products`	`product_id`
`inventory`	`products`	`product_id`

You can create these by dragging a column from one table onto the matching column in another. Power BI will often detect some relationships automatically, but always check them manually.

The Model view shows how your tables link to each other the sales table sits at the centre

Why this matters

Once relationships are in place, Power BI knows how to cross-reference data across tables. If you build a chart showing total sales by customer name, Power BI can look up customer_id in the sales table, match it to the customers table and pull the right name automatically, every time.

Without the relationship, that lookup simply doesn't happen.

This pattern one central fact table (like sales) connected to surrounding dimension tables (like customers and products) is called a star schema. It's the standard structure for analytical data models and the reason Power BI reports can filter and slice data so fluidly.

Part 4: Why SQL Skills Matter for Power BI Analysts

Power BI is a capable tool, but there's something it can't do on its own: it can't make your data better than it arrives. Whatever goes into the model is what the dashboards are built on. If the data is messy, duplicated or incomplete when it lands in Power BI, the reports will reflect that and no amount of dragging visuals around will fix it.

That's the first reason SQL matters. Before data even touches Power BI, an analyst with SQL skills can clean it, shape it and make sure only the right rows are coming through.

Pulling only what you need

Loading an entire table into Power BI when you only need six months of transactions is wasteful. It makes the model heavier and the report slower. SQL lets you filter at the source, before anything is imported:

SELECT * FROM assignment.sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

This is a simple example, but in a real dataset with millions of rows, this kind of query is the difference between a report that loads in two seconds and one that takes twenty.

Aggregating before loading

Power BI can calculate totals and averages after data is loaded, but there are times when it makes more sense to do that work in SQL first. If a stakeholder only ever wants to see total sales per product, there's no reason to bring in every individual transaction:

SELECT p.product_name, SUM(s.total_amount) AS total_sales
FROM assignment.sales s
JOIN assignment.products p ON s.product_id = p.product_id
GROUP BY p.product_name
ORDER BY total_sales DESC;

This query does the heavy lifting in the database and hands Power BI one clean, summarised row per product. The model stays lean and the report stays fast.

Joining tables before they reach Power BI

While Power BI has its own relationship system, there are cases where it makes more sense to join tables in SQL and load the result as a single, ready-to-use dataset. This is especially true when the logic behind the join is complex or when you want to be deliberate about which columns are exposed to the report layer.

Catching problems before they become dashboard problems

An analyst who knows SQL can inspect the data before loading it check for nulls, duplicates, unexpected values, mismatched IDs. These issues are much easier to handle in a SQL query than they are to track down after a dashboard is built and a stakeholder is asking why a number looks wrong.

-- Check for sales with no matching customer
SELECT s.sale_id
FROM assignment.sales s
LEFT JOIN assignment.customers c ON s.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

This kind of query takes thirty seconds to write and can save hours of confusion later.

Using the Advanced Options SQL field in Power BI

When connecting to PostgreSQL, Power BI has an Advanced Options section in the connection dialog that includes a SQL statement field. You can write a query there and Power BI will load the result instead of the full table. This gives you all the control of SQL with all the visualisation power of Power BI, working together from the start.

The bottom line is this: Power BI tells the story. SQL decides which story is worth telling. Analysts who understand both are the ones who can build dashboards that people actually trust because they know what's in the data, not just what it looks like on a chart.

Conclusion

Connecting Power BI to PostgreSQL is one of those things that seems technical until you do it once and then it becomes second nature. The local connection is a few clicks: localhost, your database name, your pgAdmin credentials and you're in. The Aiven connection follows the same steps with a cloud host address instead.

The part that actually requires thought is what comes after the connection. Getting relationships right in the model, making sure your tables are linked the way the data actually works, understanding why a star schema is the right structure that's where the analysis either holds up or falls apart.

And underneath all of it is SQL. Not as a replacement for Power BI, but as the layer that makes everything Power BI receives worth working with. The best dashboards aren't built by people who are good at dragging visuals around. They're built by people who understood the data long before it reached the screen.

Something worked differently on your setup? Drop a comment below.

SQL Joins and Window Functions

Mariam Turnesh — Mon, 02 Mar 2026 20:08:34 +0000

Introduction

In relational databases, data is stored in separate tables to keep it organized, reduce duplication, and improve efficiency. For example, employee details might be stored in one table while department information is stored in another.

When you need information from both tables at the same time, you use a JOIN. A JOIN combines rows from two tables using a related column, typically a primary key in one table and a foreign key in another. This relationship defines how the records are connected.

The ON clause specifies the exact condition used to match rows between the tables. SQL evaluates this condition to determine which records relate to each other. The result is a single, combined dataset that brings together information that was originally stored separately.

This guide walks through both Joins and Window Functions clearly, with examples you can follow from start to finish.

Dataset

We will use two tables throughout this entire article so the examples stay consistent and easy to follow.

CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    first_name    VARCHAR(50),
    last_name     VARCHAR(50),
    country       VARCHAR(50)
);

INSERT INTO customers VALUES
(1, 'Alice',   'Mensah',   'Kenya'),
(2, 'Brian',   'Okonkwo',  'Nigeria'),
(3, 'Carol',   'Wanjiku',  'Kenya'),
(4, 'Diana',   'Asante',   'Ghana'),
(5, 'Edward',  'Kimani',   'Kenya');

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,
    product      VARCHAR(100),
    amount       DECIMAL(10,2),
    order_date   DATE
);

INSERT INTO orders VALUES
(101, 1, 'Laptop',     900.00, '2024-01-10'),
(102, 1, 'Mouse',       25.00, '2024-02-14'),
(103, 3, 'Keyboard',    75.00, '2024-03-01'),
(104, 2, 'Monitor',    300.00, '2024-03-15'),
(105, 6, 'Headphones', 120.00, '2024-04-02');

Note: customer_id = 6 in the orders table has no matching customer. Diana (customer_id = 4) and Edward (customer_id = 5) have never placed an order. These details will matter as we go through each join type.

A Quick Note on Aliases

You will notice queries using short names like c and o instead of the full table name. These are called aliases.

-- Without aliases
SELECT customers.first_name, orders.product
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

-- With aliases — same result, much cleaner
SELECT c.first_name, o.product
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

You assign an alias by writing a short name right after the table name in the FROM or JOIN clause. Aliases are especially useful when table names are long or when the same table appears more than once in a query. Every example from here on uses them.

Part 1 — SQL Joins

1. INNER JOIN

Returns only the rows where a match exists in both tables. Rows with no match on either side are excluded.

SELECT c.first_name, c.last_name, o.product, o.amount, o.order_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

first_name	last_name	product	amount	order_date
Alice	Mensah	Laptop	900.00	2024-01-10
Alice	Mensah	Mouse	25.00	2024-02-14
Brian	Okonkwo	Monitor	300.00	2024-03-15
Carol	Wanjiku	Keyboard	75.00	2024-03-01

Alice, Brian and Carol matched so they appear. Diana and Edward have no orders so they are excluded. Order 105 (customer_id = 6) has no matching customer so it is also excluded.

2. LEFT JOIN

Returns all rows from the left table and matching rows from the right. Where there is no match, the right-side columns return NULL.

The "left" table is whichever comes after FROM.

SELECT c.first_name, c.last_name, o.product, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

first_name	last_name	product	amount
Alice	Mensah	Laptop	900.00
Alice	Mensah	Mouse	25.00
Brian	Okonkwo	Monitor	300.00
Carol	Wanjiku	Keyboard	75.00
Diana	Asante	NULL	NULL
Edward	Kimani	NULL	NULL

All five customers appear. Diana and Edward have no orders so their product and amount columns are NULL. Order 105 is still excluded because it has no matching customer in the left table.

The LEFT JOIN is also known as the LEFT OUTER JOIN.

3. RIGHT JOIN

Returns all rows from the right table and matching rows from the left. Where there is no match, the left-side columns return NULL.

SELECT c.first_name, c.last_name, o.product, o.amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

first_name	last_name	product	amount
Alice	Mensah	Laptop	900.00
Alice	Mensah	Mouse	25.00
Brian	Okonkwo	Monitor	300.00
Carol	Wanjiku	Keyboard	75.00
NULL	NULL	Headphones	120.00

All five orders appear. Order 105 (Headphones) has no matching customer so first_name and last_name are NULL. Diana and Edward are gone because they live in the left table and have no matching order.

The RIGHT JOIN is also known as the RIGHT OUTER JOIN.

💡 Most developers rewrite RIGHT JOIN as a LEFT JOIN by swapping the table order as it reads more naturally. These two queries return identical results:
-- As RIGHT JOIN
SELECT c.first_name, o.product
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

-- Same result as LEFT JOIN (tables swapped)
SELECT c.first_name, o.product
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id;

4. FULL OUTER JOIN

Returns all rows from both tables. Where there is no match on either side, NULL fills in the gap. No row from either table is left out.

SELECT c.first_name, c.last_name, o.product, o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

first_name	last_name	product	amount
Alice	Mensah	Laptop	900.00
Alice	Mensah	Mouse	25.00
Brian	Okonkwo	Monitor	300.00
Carol	Wanjiku	Keyboard	75.00
Diana	Asante	NULL	NULL
Edward	Kimani	NULL	NULL
NULL	NULL	Headphones	120.00

Every customer and every order appears, matched or not.

⚠️ MySQL does not support FULL OUTER JOIN. Use a UNION to emulate it:

SELECT c.first_name, o.product
FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id
UNION
SELECT c.first_name, o.product
FROM customers c RIGHT JOIN orders o ON c.customer_id = o.customer_id;

5. SELF JOIN

A SELF JOIN joins a table to itself. It is used when rows in a table relate to other rows in the same table. The most common example is an employee table where each employee has a manager_id that points to another employee in the same table.

We will use a separate employees table for this example:

CREATE TABLE employees (
    emp_id      INT PRIMARY KEY,
    name        VARCHAR(100),
    manager_id  INT
);

INSERT INTO employees VALUES
(1, 'Alice',  3),
(2, 'Brian',  3),
(3, 'Carol',  4),
(4, 'Diana',  NULL);

Alice and Brian report to Carol (emp_id = 3). Carol reports to Diana (emp_id = 4). Diana is at the top so she has no manager.

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

employee	manager
Alice	Carol
Brian	Carol
Carol	Diana
Diana	NULL

Notice the aliases e and m. Because we are joining the employees table to itself, aliases are what tell SQL which copy of the table is which. Without them the query would be ambiguous and SQL would not know which name column to use. Diana returns NULL because she has no manager.

6. CROSS JOIN

A CROSS JOIN returns every possible combination of rows from both tables. There is no ON clause because you are not matching rows, you are pairing every row from one table with every row from the other.

Using our existing tables:

SELECT c.first_name, o.product
FROM customers c
CROSS JOIN orders o;

With 5 customers and 5 orders, the result has 5 x 5 = 25 rows. Every customer is paired with every product regardless of whether they ordered it.

first_name	product
Alice	Laptop
Alice	Mouse
Alice	Keyboard
Alice	Monitor
Alice	Headphones
Brian	Laptop
Brian	Mouse
...	...

This is useful for generating all possible combinations such as product variants, scheduling slots, or test data.

⚠️ On large tables, CROSS JOIN grows very fast. Two tables with 1,000 rows each produce 1,000,000 rows.

Join Types Summary

Join	What It Returns
`INNER JOIN`	Only rows with a match in both tables
`LEFT JOIN`	All rows from the left table + matching rows from the right
`RIGHT JOIN`	All rows from the right table + matching rows from the left
`FULL OUTER JOIN`	All rows from both tables
`SELF JOIN`	A table joined to itself
`CROSS JOIN`	Every possible combination of rows from both tables

Part 2 — Window Functions

What Are Window Functions?

Window functions perform calculations across a set of rows without collapsing them. That is what separates them from regular aggregate functions.

With GROUP BY, every group gets summarised into a single row. With window functions, every row stays in the result and the calculation is added as an extra column alongside it.

They operate over a window of rows defined by the OVER() clause.

Syntax

SELECT column_name,
       function() OVER (
           PARTITION BY partition_column
           ORDER BY     order_column
       ) AS output_column
FROM table_name;

Part	What It Does
`function()`	The window function e.g. `RANK()`, `SUM()`, `LAG()`
`OVER()`	Tells SQL this is a window function and defines the window
`PARTITION BY`	Divides rows into independent groups. The function restarts for each group
`ORDER BY`	Defines the order of rows within each partition
`AS output_column`	Names the result column

Important: Window functions run after WHERE, GROUP BY, and HAVING, so you cannot filter on a window function directly in a WHERE clause. Wrap it in a CTE or subquery instead.

For the window function examples we will use this employees table:

CREATE TABLE employees (
    emp_id      INT PRIMARY KEY,
    name        VARCHAR(100),
    department  VARCHAR(50),
    salary      DECIMAL(10,2)
);

INSERT INTO employees VALUES
(1, 'Alice',   'Engineering', 72000),
(2, 'Brian',   'Marketing',   58000),
(3, 'Carol',   'Engineering', 65000),
(4, 'Diana',   'Marketing',   71000),
(5, 'Edward',  'Finance',     91000),
(6, 'Faith',   'Engineering', 72000);

Types of Window Functions

Window functions fall into three categories:

Aggregate: SUM(), AVG(), COUNT(), MIN(), MAX()

Ranking: ROW_NUMBER(), RANK(), DENSE_RANK(), PERCENT_RANK()

Value: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

1. ROW_NUMBER()

Assigns a unique sequential number to each row within a partition, ordered by the column you specify.

SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

name	department	salary	row_num
Alice	Engineering	72000.00	1
Faith	Engineering	72000.00	2
Carol	Engineering	65000.00	3
Diana	Marketing	71000.00	1
Brian	Marketing	58000.00	2
Edward	Finance	91000.00	1

The numbering restarts for each department because of PARTITION BY department. Alice and Faith earn the same salary but still get different row numbers because ROW_NUMBER is always unique.

2. RANK()

Assigns ranks within a partition. When rows tie, they receive the same rank and the next rank skips to account for the tie.

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

name	department	salary	rank
Alice	Engineering	72000.00	1
Faith	Engineering	72000.00	1
Carol	Engineering	65000.00	3
Diana	Marketing	71000.00	1
Brian	Marketing	58000.00	2
Edward	Finance	91000.00	1

Alice and Faith both earn 72,000 so they both get rank 1. The next rank jumps to 3 because rank 2 is skipped to account for the tie.

3. DENSE_RANK()

Works like RANK() but without the gaps. Tied rows share the same rank and the next rank continues without skipping.

SELECT name, department, salary,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

name	department	salary	dense_rank
Alice	Engineering	72000.00	1
Faith	Engineering	72000.00	1
Carol	Engineering	65000.00	2
Diana	Marketing	71000.00	1
Brian	Marketing	58000.00	2
Edward	Finance	91000.00	1

Alice and Faith are still both rank 1 but now Carol is rank 2 not 3. No numbers are skipped.

Comparing ROW_NUMBER, RANK, and DENSE_RANK

Using the Engineering department where Alice and Faith both earn 72,000:

name	salary	ROW_NUMBER	RANK	DENSE_RANK
Alice	72000.00	1	1	1
Faith	72000.00	2	1	1
Carol	65000.00	3	3	2

ROW_NUMBER always unique, no ties recognised
RANK ties get the same rank, next rank skips
DENSE_RANK ties get the same rank, next rank does not skip

4. SUM() — Running Total

When you use SUM() as a window function with ORDER BY, it accumulates a running total as it moves down the rows.

SELECT name, department, salary,
       SUM(salary) OVER (
           PARTITION BY department
           ORDER BY emp_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM employees;

name	department	salary	running_total
Alice	Engineering	72000.00	72000.00
Carol	Engineering	65000.00	137000.00
Faith	Engineering	72000.00	209000.00
Brian	Marketing	58000.00	58000.00
Diana	Marketing	71000.00	129000.00
Edward	Finance	91000.00	91000.00

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW tells SQL to include every row from the start of the partition up to the current row. The total resets for each department because of PARTITION BY.

5. AVG() — Department Average Per Row

Without ORDER BY, a window aggregate runs across the entire partition at once, giving you the group value on every individual row.

SELECT name, department, salary,
       ROUND(AVG(salary) OVER (PARTITION BY department), 2) AS dept_avg
FROM employees;

name	department	salary	dept_avg
Alice	Engineering	72000.00	69666.67
Carol	Engineering	65000.00	69666.67
Faith	Engineering	72000.00	69666.67
Brian	Marketing	58000.00	64500.00
Diana	Marketing	71000.00	64500.00
Edward	Finance	91000.00	91000.00

Every employee keeps their individual row and the department average sits right next to their salary. This is the query that GROUP BY simply cannot do.

6. LAG() and LEAD()

LAG() looks back at the value of a previous row. LEAD() looks ahead at the value of a future row. Both are used for period-over-period comparisons.

SELECT
    order_date,
    amount,
    LAG(amount, 1)  OVER (ORDER BY order_date) AS prev_order,
    LEAD(amount, 1) OVER (ORDER BY order_date) AS next_order
FROM orders
WHERE customer_id = 1;

order_date	amount	prev_order	next_order
2024-01-10	900.00	NULL	25.00
2024-02-14	25.00	900.00	NULL

The first row returns NULL for LAG because there is no row before it. The last row returns NULL for LEAD because there is no row after it. This is expected behaviour, not an error.

Getting the Top Earner Per Department

This is one of the most common window function patterns. It requires a CTE because you cannot filter on a window function in a WHERE clause directly.

WITH ranked AS (
    SELECT name, department, salary,
           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn = 1;

name	department	salary
Alice	Engineering	72000.00
Diana	Marketing	71000.00
Edward	Finance	91000.00

We use ROW_NUMBER() here instead of RANK() so that ties produce exactly one result per department. The CTE materialises the window function result first and then the outer WHERE filters on it.

Conclusion

Joins connect data that lives in separate tables. The join type you pick decides what happens to rows with no match. INNER JOIN drops them, LEFT JOIN keeps the left side, FULL OUTER JOIN keeps everything.

Window Functions bring group-level calculations down to the row level without losing any individual rows. PARTITION BY creates the groups, ORDER BY inside OVER() controls the order within them, and functions like RANK(), SUM(), and LAG() do the work.

Master these two and most real-world SQL analysis becomes a lot more straightforward.

Have a question about a specific join or function? Drop it in the comments.

How Analysts Turn Messy Data into Action Using Power BI

Mariam Turnesh — Fri, 06 Feb 2026 14:39:31 +0000

Introduction

When I first started learning Power BI, I thought data analysis was mostly about building charts and dashboards.
I was wrong.
What I quickly realized is that most of the work happens before the visuals. Real-world data is messy, inconsistent and often confusing and Power BI is only powerful when you know how to translate that mess into something meaningful.

This article explains how analysts (especially beginners like me) use Power BI to move from messy data, through DAX and finally into dashboards that actually drive action.

The Reality Check Nobody Prepared Me For

You open that CSV file or connect to that database feeling optimistic. You're ready to build some dashboards, create insights, change the world. Then you see it: dates formatted as text, customer names spelled seventeen different ways, empty cells scattered around like landmines, numbers mixed with currency symbols, and columns labeled "Field_47" with zero documentation.

My first real dataset had all of this plus duplicate records of the same transactions. I remember staring at it thinking, "Did I get bad data? Should I ask for a cleaner version?"

Then I learned the truth: this is normal. This is the job. Messy data isn't bad data,it's just raw. And if you can't translate raw data into something usable, none of the fancy dashboard skills matter.

What You're Actually Looking For (And Why It Matters)

When I first crack open a messy dataset, I'm hunting for specific problems. Here's what trips up most analyses before they even start:

Inconsistent Formatting

This drives me nuts but it's everywhere. You'll have one column with "New York," "NY," "new york," and "N.Y." all meaning the same thing. Power BI doesn't know they're the same, so your visuals will treat them as four separate categories. Your boss asks why sales are split across four different locations and you look like you don't know what you're doing.

Missing Values

These are trickier than they seem. Sometimes a blank means "we don't know." Sometimes it means "zero." Sometimes it means "this doesn't apply." You have to figure out which one it is, because filling in blanks with zeros when they actually mean "unknown" will completely screw up your averages and totals.

I learned this the hard way when my average order value calculation was totally wrong because I'd replaced nulls with zeros.

Data Types That Make No Sense

This is a constant headache. Dates stored as text, numbers stored as text, percentages stored as whole numbers (so 85% shows up as 85.00 instead of 0.85). Power Query's type detection helps, but you can't just trust it blindly.

I once built an entire time series analysis before realizing my dates were still text and none of my date hierarchies worked.

Duplicate Records

These happen more than you'd think. Maybe the same transaction got logged twice. Maybe someone entered the same customer with slightly different info. You need to spot these and decide whether to keep both records, merge them or delete the duplicates entirely.

This step completely changed how I viewed data analysis. Instead of rushing to build visuals, I learned to slow down and ask: Can this data be trusted? If the answer is no, everything that follows becomes unreliable.

Power Query: Where the Translation Actually Happens

In Power BI, the translation process starts in Power Query. This is where analysts prepare data so Power BI can analyze it correctly.

Here's my actual workflow when I'm cleaning data:

First, I use Power Query to get a feel for what I'm working with. I turn on the column quality, column distribution and column profile options. These show you at a glance how many nulls you have, how many distinct values, whether you have outliers. It's like getting an X-ray of your data before you operate on it.

Then I start fixing things systematically:

Standardizing text - I use TRIM and CLEAN to get rid of weird spacing. I use Replace Values for common inconsistencies. For the "New York" problem I mentioned earlier, I create a conditional column or use Replace Values to make everything consistent.

Fixing data types - I manually set every single column type because auto-detect has burned me too many times. Date/Time for dates, Decimal Number for currency, Whole Number for counts, Text for IDs (even if they look like numbers trust me on this one).

Handling missing data intentionally - Sometimes I filter out incomplete rows. Sometimes I replace nulls with "Unknown" or "Not Provided" for categorical data. For numbers, it depends on context, sometimes zero makes sense, sometimes you need to leave it null, sometimes you calculate an average to fill it in.

Removing junk - Duplicate records go away. Irrelevant columns get deleted. Rows that are clearly data entry errors get filtered out (like that one time I found a customer age of 247).

Splitting and combining - I split columns so each one represents a single piece of information. A "Full Name" column becomes "First Name" and "Last Name." A messy address becomes separate fields for street, city, state, zip.

Renaming everything - I rename columns to clearly describe what they contain. "Col_17" becomes "Customer_Acquisition_Date." Future me always thanks past me for this.

This is where you earn your money as an analyst. Nobody sees this work, but it's the foundation of everything.

Data Modeling: Teaching Power BI How Things Connect

Once the data is clean, the next challenge is structuring it properly. Power BI doesn't just display tables, it uses relationships between them to filter and calculate data correctly.

As a beginner, the biggest concept I learned was the difference between fact tables and dimension tables:

Fact tables: Store what actually happened; sales transactions, website visits or support tickets. Each row represents an event, which is why these tables usually have a lot of rows.

Dimension tables: Provide context around those events. They describe customers, products, dates or locations. Instead of growing longer, these tables tend to grow wider, with more descriptive columns.

By connecting these tables using clear one-to-many relationships (one customer can have many sales, one product can appear in many transactions), Power BI can filter visuals correctly, respond properly to slicers, and produce accurate aggregations.

Honestly, I didn’t get why this mattered at first. Then I built a dashboard without proper relationships. Someone clicked a region slicer, half the visuals updated, half didn’t and the numbers were all over the place. Lesson learned.

A good data model doesn't look exciting, but it makes dashboards behave the way users expect. Without it, even clean data produces confusing results.

DAX: Defining What Numbers Actually Mean

DAX was the part I found most intimidating at first. All those functions and syntax rules felt like learning a new programming language.

But over time, I realized that DAX isn't just about formulas,it's about defining meaning.

Instead of asking "How do I write this formula?", analysts ask:

What exactly does "total sales" mean for our business?
Should it include returns? Discounts? Tax?
How should this number change when someone filters by date or region?
How do we compare current results to last year's performance?

Using DAX measures, Power BI allows calculations to adapt dynamically to filters, dates and user interaction. This is what transforms static numbers into insights.

A simple example: when I create a measure for Total Revenue, it's not just SUM(Sales[Amount]). I have to think about whether cancelled orders should be excluded. Whether I need to account for currency conversion. Whether partial refunds affect the number.

DAX is the bridge between raw numbers and real business questions. It's where you translate data into the language your stakeholders actually care about.

The Stuff That's Hard to Learn From Tutorials

Here's what trips people up: knowing when to clean in Power Query versus when to handle it in DAX measures.

Generally, if it's structural (fixing data types, removing duplicates, standardizing categories), do it in Power Query. If it's calculation-based (like handling division by zero in a ratio, or computing year-over-year growth), do it in DAX.

Why? Power Query transformations happen once when you refresh your data. DAX calculations happen every time someone interacts with your report. If you try to do heavy transformations in DAX, your dashboards will be slow and painful to use.

Another thing documentation is not optional. When I create a Power BI file, I keep notes about what I cleaned and why. Months later when someone asks why you excluded certain records or how you defined "active customer," you need to have an answer. I use the Description field in Power Query steps and add comments to complex DAX measures.

Also, always keep a copy of your original raw data. Never overwrite source files. I learned this the hard way when I spent a day cleaning a dataset, saved over the original, then realized I'd filtered out records I actually needed.

Dashboards: When Translation Finally Becomes Visible

Only after data is cleaned, modeled and measured does the dashboard truly matter.

Here's what I learned about dashboards: they're not meant to show everything,they're meant to guide attention. Early on, I tried cramming every possible metric onto one page. It looked impressive to me but confused everyone else.

Effective Power BI dashboards do a few things really well:

They highlight key metrics

Using KPI cards or scorecards. The three or four numbers that actually matter get prominent placement. Everything else is secondary.

They show trends instead of isolated values

A single number like "450 sales this month" means nothing without context. But a line chart showing whether that's up or down from last month? That tells a story.

They allow exploration through slicers and filters

Users can slice by date, region, product category, whatever makes sense. This turns a static report into an interactive tool.

They keep layouts simple and focused

White space is your friend. Clear labels matter more than fancy visuals. If someone has to squint or ask what they're looking at, you've failed.

When dashboards are designed well, decision-makers don't need to ask for new reports. They can explore the data themselves and act immediately. That's when analysis turns into action.

What Success Actually Looks Like

You know you've translated your messy data successfully when you can build a visual and it just works. You don't have to keep going back to Power Query to fix "one more thing." Your slicers show the categories you expect. Your date hierarchies make sense. Your totals actually total correctly.

The goal isn't perfection, there's always another tweak you could make. The goal is reliable data that answers business questions without creating new confusion.

That's what this whole job is about, really. Taking information that's scattered and inconsistent and broken and turning it into something someone can actually use to make a decision.

The dashboards and visualizations are just the final 10% that everyone sees. The real work happens in Power Query, in those transformation steps nobody ever asks about.

From Learning Power BI to Actually Thinking Like an Analyst

Learning Power BI taught me something important: data analysis is not about tools,it's about thinking.

Power BI simply provides the environment where analysts:

Translate messy data using Power Query
Structure it with proper modeling
Define meaning using DAX
Communicate insights through dashboards

Once I understood this workflow, Power BI stopped feeling overwhelming and started making sense. The tool just facilitates the process. The real skill is knowing what questions to ask at each stage.

If you're just starting out, focus less on perfect visuals and more on understanding this process. Master the unglamorous parts first:the data cleaning, the relationships, the proper data types. Everything else becomes easier once the foundation is right.

And honestly? Once you get good at this part, the actual analysis becomes almost fun. Because you're working with data you trust, data you understand, data that's finally ready to tell you something true.

If you're learning Power BI, mastering how data flows from raw to actionable insight will save you months of confusion and make your dashboards far more valuable than any fancy visual ever could.

What's been your biggest challenge learning Power BI? Drop a comment below,I'd love to hear what trips people up or what clicked for you.

Power BI Data Modeling

Mariam Turnesh — Mon, 02 Feb 2026 11:40:29 +0000

A well-designed Power BI report is built long before the first visual is added. It starts with the data model. When modelling is done poorly, no amount of DAX or formatting can fully fix slow performance or inconsistent numbers.

In this article, I’ll cover the essentials of data modelling in Power BI fact tables, dimension tables, relationships and star schemas and explain how these design choices directly impact performance and accuracy. If you want reports that scale and behave predictably, this is where it all begins.

What Exactly Is Data Modeling in Power BI?

Data modeling is the process of structuring your data tables and establishing connections between them within Power BI's Model view. Think of it as creating the blueprint for how your data will be queried, filtered, and analyzed.

Here's what many beginners don't realize: Power BI was built for analytical processing, not transactional systems. This means the traditional database designs you might be familiar with often need to be restructured for optimal performance in Power BI. The way you organize and link your tables directly impacts:

Query speed – How fast your visuals load
Calculation accuracy – Whether your numbers are correct
User experience – How intuitive your reports feel
Scalability – How well your model grows with your business

A well-designed Power BI data model should:

Mirror real-world business logic and processes
Minimize complexity in DAX calculations
Enable fast query execution -Guarantee accurate and consistent reporting results

Understanding the Building Blocks: Fact and Dimension Tables

Every effective Power BI model is built on two fundamental table types: fact tables and dimension tables. Understanding the distinction between these is crucial for building models that perform well and make logical sense.

Fact Tables: The Measurable Data

Fact tables are where your business transactions and measurable events live. These tables contain the numeric values you'll aggregate in your reports—the "what happened" of your business.

Real-world examples of fact tables:

Sales transactions (daily orders, revenue, units sold)
Website analytics (page views, clicks, session duration)
Manufacturing data (units produced, defect rates, cycle times)
Financial records (expenses, payments, invoices)

Key characteristics of fact tables:

Numeric focus – Contain measures that can be summed, averaged, or counted

Foreign keys – Have columns that reference dimension tables

High row count – Often contain millions or billions of records

Transaction-based – Each row represents a business event or observation

Example fact table structure:

Dimension Tables: The Descriptive Context
Dimension tables provide the context and descriptive attributes that make your fact data meaningful. These tables answer the "who," "what," "where," "when," and "why" questions about your business events.

Real-world examples of dimension tables:

Product catalog (names, categories, brands, SKUs)
Customer directory (names, locations, demographics, segments)
Date calendar (dates, months, quarters, fiscal years)
Employee roster (names, departments, job titles, managers)

Key characteristics of dimension tables:

Descriptive focus – Contain text, categories, and attributes
Primary keys – Have unique identifiers that link to fact tables
Low row count – Typically contain hundreds to thousands of records
Reference data – Used for filtering, grouping, and slicing

Example dimension table structure:

Quick Mental Check: If you're summing it up in a report, it belongs in a fact table. If you're filtering or grouping by it, it belongs in a dimension table.

The Star Schema

The star schema is the most recommended data modeling approach in Power BI, and for good reason. It's called a "star" schema because when you visualize it in Model view, it literally looks like a star—with your fact table at the center and dimension tables radiating outward.

What Makes a Star Schema?

In a star schema design:

Central fact table – Contains all measurable transaction data
Surrounding dimensions – Connected directly to the fact table
Direct relationships – Each dimension links straight to facts, no intermediaries
No dimension-to-dimension links – Dimensions remain independent

Key Characteristics In a star schema

One central fact table
Dimension tables connect directly to the fact table
One-to-many relationships
No relationships between dimension tables

Why Star Schema Works Best in Power BI

Faster performance (fewer joins)
Simpler DAX calculations
Easier to understand and maintain
Optimized for Power BI’s VertiPaq engine

Visual representation:

The Snowflake Schema

The snowflake schema takes the star schema concept and normalizes the dimension tables by breaking them into multiple related sub-tables. While this might seem like good database design (and it is for transactional systems), it's typically problematic in Power BI's analytical environment.

Advantages

Reduces data redundancy
Can help manage very large or complex dimensions

Disadvantages in Power BI

More relationships to manage
Slower performance due to extra joins
More complex DAX expressions
Harder for report users to understand

Because of these drawbacks, snowflake schemas are generally discouraged in Power BI, unless there is a specific need.

Relationships in Power BI
Power BI supports several relationship types:

One-to-many (1:*) – most common and recommended
Many-to-many (:) – use cautiously
One-to-one (1:1) – less common

Relationship Best Practices

Dimension tables should be on the one side
Fact tables should be on the many side
Prefer single-direction filtering
Avoid unnecessary bi-directional relationships

Why Good Data Modelling Is Critical
1. Performance
Well-designed star schemas reduce joins and allow Power BI to process queries faster. Poor modelling often results in slow-loading visuals and sluggish reports.

2. Accurate Reporting
Incorrect relationships or poorly structured tables can cause duplicated values, missing data, or misleading totals. Good modelling ensures calculations reflect business reality.

3. Simpler DAX
Clean models lead to simpler, more readable DAX measures. This reduces development time and minimizes errors.

4. Scalability
As datasets grow, a solid data model can handle new dimensions, additional facts, and more complex analysis without requiring major redesigns.

5. Better User Experience
End users benefit from intuitive field lists, reliable slicers, and predictable report behavior—all outcomes of good data modelling.

Conclusion
Schemas and data modelling are at the heart of effective Power BI solutions. By organizing data into fact and dimension tables and adopting a star schema approach, analysts can build models that are fast, accurate, and easy to maintain. While snowflake schemas may reduce redundancy, they often introduce unnecessary complexity and performance issues in Power BI.

Ultimately, good data modelling is not just a technical best practice—it is essential for delivering trustworthy insights and high-quality business intelligence.

Power BI Data Modeling: Why Your Reports Are Slow (And How to Fix Them)

Mariam Turnesh — Mon, 02 Feb 2026 08:23:24 +0000

Power BI Data Modeling: Why Your Reports Are Slow (And How to Fix Them)

Look, I'm going to be honest with you. When I first started with Power BI, I thought data modeling was just some boring technical thing that database people worried about. I was wrong. Really wrong.

My first dashboard took 45 seconds to load a single visual. My manager wasn't impressed. Neither were the users who had to stare at loading spinners. The worst part? My numbers didn't even add up correctly half the time.

Turns out, if you get your data model wrong, nothing else matters. Your fancy visuals, your beautiful color schemes, your clever DAX formulas - all useless if they're sitting on a broken foundation.

So let's talk about data modeling, but without the academic jargon. Just the stuff you actually need to know.

What Even Is a Data Model?

Think of your data model like organizing your closet. You could throw everything in one giant pile - it all fits, technically. But when you need your favorite shirt, good luck finding it in under 10 minutes.

A data model is just how you organize your tables and connect them together. That's it. But here's the thing - organize them wrong, and Power BI has to work 10x harder to find what it needs. Organize them right, and everything just... flows.

The Two Types of Tables You Need to Know

Before we get into schemas (I promise we'll keep this simple), you need to understand that tables generally fall into two camps:

Fact Tables: Where the Numbers Live

This is where your actual business data lives - sales amounts, quantities sold, costs, hours worked. Basically, anything you'd put in a SUM() function.

Here's a simple sales fact table:

OrderID | CustomerID | ProductID | Date       | Quantity | Revenue
------------------------------------------------------------------
1001    | C101       | P501      | 2024-01-15 | 5        | 500
1002    | C102       | P502      | 2024-01-15 | 2        | 200
1003    | C101       | P503      | 2024-01-16 | 3        | 450

Notice how there's no customer names or product names here? That's on purpose. This table just holds the transaction facts and references to other tables.

Dimension Tables: Where the Context Lives

These tables describe your data. Customer names, product categories, dates, store locations - all the stuff that makes your numbers meaningful.

ProductID | ProductName    | Category     | Brand
--------------------------------------------------
P501      | Laptop Pro 15  | Electronics  | TechCo
P502      | Office Chair   | Furniture    | ComfortCo
P503      | Desk Lamp      | Furniture    | BrightCo

See the difference? This table tells us WHAT we sold, while the fact table tells us HOW MUCH.

![Fact vs Dimension visualization]

Star Schema: The Setup That Actually Works

Here's where things get interesting. There are different ways to organize your tables, but in Power BI, one approach wins almost every time: the star schema.

It's called a star schema because when you look at it in model view, it literally looks like a star. You've got:

One fact table in the middle (your sales, transactions, whatever)
Dimension tables around the edges (customers, products, dates, stores)
Direct connections from each dimension to the fact table

That's it. No complicated chains. No tables connecting to other tables that connect to other tables. Just a clean hub-and-spoke pattern.

![Star Schema visualization]

Why This Works So Well

Power BI's engine (called VertiPaq, if you care) is specifically optimized for this pattern. When someone filters by "Electronics" category:

Filter hits the Product dimension
Flows directly to the Sales fact table
Done

Fast. Simple. Predictable.

Compare that to having your categories in a separate table, connected to subcategories, connected to products, connected to sales. Now Power BI has to hop through three relationships instead of one. Multiply that by thousands of filter selections, and you see why your reports are slow.

Snowflake Schema: When You Normalize Too Much

Now, you might see some data models that look more like snowflakes - where dimension tables are split up and connected to each other. Like having:

Product table → connects to → SubCategory table → connects to → Category table

This is called a snowflake schema. It's "normalized" in database terms, which sounds fancy but usually just means "unnecessarily complicated for Power BI."

![Snowflake Schema visualization]

Why You Probably Don't Want This

Yeah, it saves some storage space. And yeah, it follows traditional database design principles. But:

It's slower - more relationships to traverse
It's confusing - users can't easily find what they need
It doesn't play well with DAX - time intelligence functions hate this pattern

The only time I use snowflake schema is when I'm importing from a data warehouse that's already set up this way AND I'm too lazy to flatten it in Power Query. (Don't be like me - flatten it.)

Relationships: The Glue That Holds Everything Together

Here's something that took me way too long to understand: relationships aren't just lines connecting tables. They control how filters flow through your model.

One-to-Many: Your New Best Friend

This is the standard, and you want almost all your relationships to look like this:

One customer in the customer table
Many orders in the sales table for that customer

The "one" side is your dimension, the "many" side is your fact table. Power BI automatically detects these and sets them up correctly... most of the time.

![Relationships visualization]

The Filter Direction Trap

By default, filters flow from the "one" side to the "many" side. Makes sense - when you select a customer, you want to see their sales, not the other way around.

But sometimes people get clever and set relationships to filter "both ways." Don't do this unless you absolutely have to. It causes weird stuff to happen, kills performance, and makes debugging a nightmare.

I spent three hours once trying to figure out why my totals were wrong. Turns out I had a bi-directional relationship I forgot about. Three. Hours.

Why This All Actually Matters

Remember how I said my first dashboard took 45 seconds to load? Here's what happened after I rebuilt it with a proper star schema:

Before:

Load time: 45 seconds
Refresh time: 30 minutes
File size: 2.1 GB
User complaints: Daily

After:

Load time: Instant
Refresh time: 3 minutes
File size: 650 MB
User complaints: None (they actually started asking for MORE reports)

Same data. Same visuals. Just organized properly.

Quick Wins You Can Implement Today

Here's the stuff that made the biggest difference for me:

1. Create a proper date table
Don't use the auto-generated one. Make your own with Year, Quarter, Month, Week, Day columns. Your time intelligence functions will thank you.

2. Use integer keys for relationships
CustomerID should be a number, not text. Same with ProductID, DateKey, everything. Numbers are way faster to match than text.

3. Remove columns you don't need
That 100-column table from your ERP system? You probably use 10 of those columns. Delete the rest in Power Query. Your model will be smaller and faster.

4. Hide your foreign keys
Users don't need to see CustomerID in the field list - they should see Customer Name. Right-click those ID columns and hide them from report view.

5. Star schema is the default, always
Unless you have a really good reason not to, go with star schema. It's what Microsoft recommends, what the engine optimizes for, and what actually works.

The One Thing You Need to Remember

If you take nothing else from this article, remember this: your data model is more important than your visuals.

You can have the most beautiful dashboard in the world, but if it's built on a messy, slow data model, nobody's going to use it. They'll just go back to Excel.

But nail the data model? Everything else becomes easy. Visuals load fast. Filters work correctly. DAX formulas make sense. Users are happy.

And honestly, that's all that matters.

What's Next?

Start with your worst-performing report. Open up the model view. Look at your relationships. I'm willing to bet you'll find:

Tables that shouldn't be connected
Bi-directional relationships you don't need
Snowflake patterns you can flatten
Missing relationships that should exist

Fix those, and watch your performance problems disappear.

Have questions about data modeling? Drop them in the comments. I've probably made every mistake in the book, so I can probably help you avoid them.

Quick Reference:

✅ Use star schema (fact table in center, dimensions around it)

✅ One-to-many relationships (dimension → fact)

✅ Single direction filtering (no bi-directional unless necessary)

✅ Create a date dimension (don't use auto-date)

✅ Hide foreign key columns (show names, not IDs)

❌ Avoid snowflake schema (flatten in Power Query instead)

❌ Avoid many-to-many (create bridge tables if needed)

❌ Don't import unnecessary columns (remove in Power Query)

❌ Don't use text for keys (use integers)

❌ Don't skip data modeling (it's not optional)

Found this helpful? Share it with someone struggling with slow Power BI reports. We've all been there.

Introduction to MS Excel for Data Analytics

Mariam Turnesh — Sun, 25 Jan 2026 20:33:09 +0000

Introduction

If you're stepping into data analytics, Excel is your best friend. It's the most accessible, powerful tool for working with data - no coding required. Whether you're tracking sales, analyzing trends, or building reports, Excel has you covered.

This guide uses real examples to make learning fun and relatable. By the end, you'll know how to clean data, write formulas, create pivot tables and extract insights like a pro.

Let's dive in!

Part 1: Understanding the Excel Interface

Before we jump into formulas and functions, let's get comfortable with Excel's layout.

Key components you need to know:

Ribbon: The toolbar at the top with tabs (Home, Insert, Formulas, Data, etc.) - this is where all Excel's features live
Formula Bar: Shows the contents of the active cell - especially useful when viewing formulas
Column Headers: The letters (A, B, C...) that identify columns
Row Headers: The numbers (1, 2, 3...) that identify rows
Active Cell: The currently selected cell with a green border (like A1 in the image)
Worksheet Tabs: Switch between different sheets in your workbook (Sheet1, Sheet2, etc.)

Pro tip: When someone says "cell D5", they mean the intersection of column D and row 5. This naming system is how we reference data in formulas.

PART 2: Data Cleaning & Sorting/Filtering

1. How to Find Duplicates

You might want to see duplicates before deleting them to be sure what you’re removing.

Highlight Duplicates Using Conditional Formatting

Select the cells you want to check.
Click Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
Pick a color style and click OK.
Now all duplicates will be colored so you can see them easily.

✅ This doesn’t delete anything, it just shows duplicates so you can inspect them.

2. How to Remove Duplicates

If you’re sure you want to delete the extra copies:

Use the Remove Duplicates Tool

Select your data range (just click anywhere inside your table).
Go to Data → Remove Duplicates.
A box will pop up. Excel will usually guess your column headers for you.
Make sure the columns you want to check for duplicates are ticked.
Click OK.

Excel will remove all rows that are exact duplicates and leave only the first occurrence. It will also tell you how many duplicates it found and removed.

3.Find & Replace

Find and Replace is a feature in Excel that helps you quickly search for specific text or numbers and change them across your worksheet. It’s especially useful when cleaning data or fixing repeated mistakes.

Use Ctrl + F to find data and Ctrl + H to replace it. This feature saves time, fixes repeated errors, and helps clean data, but you should be careful with Replace All since it changes everything at once.

4. Sorting and Filtering

Sorting and Filtering help you organize data and focus only on what matters, without changing the actual data.

Sorting

Sorting in Excel helps you arrange your data in a meaningful order so it’s easier to understand.

Types of Sorting

Text: A → Z or Z → A

Numbers: Smallest → Largest or Largest → Smallest

Dates: Oldest → Newest or Newest → Oldest

How to Sort Data

Click anywhere inside your table.
Go to Data → Sort A–Z or Sort Z–A.
Excel will reorder the rows based on the selected column.

Filtering

Filtering in Excel lets you show only the data you want to see while hiding the rest. It helps you focus on specific information without deleting anything.

How to Apply a Filter

Click anywhere inside your table.
Go to Data → Filter.
Click the dropdown arrow in a column header.
Choose the values you want to see and click OK.

4. Freeze Panes in Excel

Freeze Panes is an Excel feature that keeps certain rows or columns visible while you scroll through your worksheet. This is helpful when working with large tables, so you don’t lose sight of headers.

How to Use Freeze Panes

Click the cell below the row or to the right of the column you want to keep visible.
Go to View → Freeze Panes.

Choose:

Freeze Top Row – keeps column headers visible

Freeze First Column – keeps row labels visible

Freeze Panes – freezes both rows and columns

Part 3: Basic Functions in Excel

In Excel, functions are built-in formulas that help you perform calculations quickly and correctly. Instead of doing math by hand, you tell Excel what to calculate.

All functions start with an equals sign (=).

Functions are pre-built formulas that perform calculations for you. Let's look at the most important ones you'll use every day.

The SUM Function

Need to add up numbers quickly? SUM is your go-to function.

What's happening here:

Formula: =SUM(D2:D10)
This adds all values from cell D2 through D10
Result: 2489

Real-world use: Totaling sales, expenses, inventory quantities, hours worked, etc.

The AVERAGE Function

Want to find the mean of a group of numbers? AVERAGE does the math for you.

What's happening here:

Formula: =AVERAGE(B2:E2)
This calculates the average of values across columns B through E in row 2
Result: 4 (average number of pokeballs for trainer Iva)

Why it matters: Averages help you understand typical values in your data - average sales per month, average customer rating, average response time, etc.

The MAX and MIN Functions

These functions find the highest and lowest values in a range.

What's happening:

Formula: =MAX(D2:D21)
Finds the highest value in the Total stats column
Result: 525 (Cloyster has the highest stats)

What's happening:

Formula: =MIN(D2:D21)
Finds the lowest value in the Total stats column
Result: 200 (Magikarp has the lowest stats)

Real-world applications:

MAX: Find your best-selling product, highest revenue month, top performer
MIN: Identify lowest inventory levels, minimum order quantities, slowest sales day

The MEDIAN Function

While AVERAGE can be skewed by extreme values, MEDIAN finds the true middle value.

What's happening:

Formula: =MEDIAN(A2:G2)
Finds the middle value when all numbers are sorted
Result: 2

When to use MEDIAN vs AVERAGE: If you have outliers (extremely high or low values), MEDIAN gives you a better sense of the "typical" value. For example, median income is often more useful than average income because a few billionaires don't skew it.

Logical Functions

Logical functions help Excel make decisions based on conditions you set. They're like teaching Excel to think: "If this is true, do that. Otherwise, do something else."

The IF Function

The IF function is your decision-maker. It checks a condition and returns one value if true, another if false.

What's happening here:

Formula: =IF(C5>80,TRUE)
Logic: If the Score (in column C) is greater than 80, display TRUE, otherwise display FALSE
Result:
- Ava Thompson (85) → TRUE
- Lily Smith (79) → FALSE
- Ethan Johnson (82) → TRUE
- And so on...

Key Insight: This simple IF formula evaluates each student's score and returns TRUE for passing scores (>80) and FALSE for those who didn't meet the threshold.

The AND Function

AND checks if ALL conditions are true. Every single condition must pass.

What's happening here:

Formula: =AND(B2>=60, C2>=90)
Logic: Returns TRUE only if BOTH Score 1 is greater than or equal to 60 AND Score 2 is greater than or equal to 90
Result:
- Richard (53, 80) → FALSE (Score 1 fails)
- Jennifer (60, 91) → TRUE (both conditions met)
- James (58, 75) → FALSE (both conditions fail)
- Lisa (79, 51) → FALSE (Score 2 fails)
- Sharon (41, 33) → FALSE (both conditions fail)

Key Insight: ALL conditions must be satisfied. If even one condition is false, the entire AND function returns FALSE.

The OR Function

OR checks if AT LEAST ONE condition is true. Only one needs to pass.

What's happening here:

Formula: =OR(B8>30, C8="North")
Logic: Returns TRUE if EITHER Sales Volume is greater than 30 OR Team equals "North"
Result:
- Malkowitz, Sally (18, North) → TRUE (Team is North)
- Wilson, Timothy (31, East) → TRUE (Sales > 30)
- Birch, Julia (42, West) → TRUE (Sales > 30)
- Fairclough, John (57, North) → TRUE (both conditions met!)
- Johnson, Mark (30, East) → FALSE (Sales not > 30 AND Team not North)
- Williams, Eric (16, South) → FALSE (neither condition met)

Key Insight: Only ONE condition needs to be true. If any condition passes, the entire OR function returns TRUE.

The NOT Function

NOT reverses the logic. It turns TRUE into FALSE and FALSE into TRUE.

What's happening here:

Formula: =NOT(OR(B5="green", B5="red"))
Logic: First checks if Color is green OR red, then reverses that result
Result:
- Red → FALSE (NOT green or red = NOT TRUE = FALSE)
- Blue → TRUE (NOT green or red = NOT FALSE = TRUE)
- Green → FALSE (NOT green or red = NOT TRUE = FALSE)

Key Insight: NOT flips the logic. It's useful when you want to find everything EXCEPT certain values.

Text Functions

Text functions help you manipulate, clean, and standardize text data. Essential for messy real-world datasets!

The TRIM Function

TRIM removes extra spaces that mess up your data.

What's happening here:

Formula: =TRIM(A2)
Original data has inconsistent spacing (extra spaces before, after, or between names)
Result: Clean, properly formatted names
- " Ronnie Anderson" → "Ronnie Anderson"
- "Tom Boone" → "Tom Boone"
- "Sally Brooke " → "Sally Brooke"
- "Jeremy Hill" → "Jeremy Hill"

Why this matters: Excel treats " Ronnie Anderson" and "Ronnie Anderson" as different values. When counting or filtering, extra spaces create duplicate entries and wrong totals. TRIM fixes this instantly.

The UPPER, LOWER, and PROPER Functions

The Excel functions UPPER, LOWER and PROPER change text case: UPPER converts all letters to caps

LOWER converts all to lowercase
PROPER capitalizes only the first letter of each word (useful for names)

Upper Function

What's happening here:

Formula: =UPPER(A4)
Converts all text to UPPERCASE
Examples from the data:

"william" → "WILLIAM"
"jody" → "JODY"
"sophie" → "SOPHIE"
"gurpreet" → "GURPREET"
"maximus" → "MAXIMUS"

lower Function

Formula: =LOWER(A4)
Converts all text to lowercase
Examples:

"william" → "william"
"JODY" → "jody"
"SOPHIE" → "sophie"

Proper Function

Formula: =PROPER(A4)
Capitalizes the first letter of each word (Title Case)
Examples:

"william" → "William"
"jody" → "Jody"
"sophie" → "Sophie"
"gurpreet" → "Gurpreet"
"maximus" → "Maximus"

Key Insight: These three functions give you complete control over text capitalization. UPPER for acronyms and codes, LOWER for email addresses and PROPER for names and titles.

The CONCATENATE Function

Combine text from multiple cells into one.

What's happening here:

Formula: =CONCATENATE(B5,", ",C5,", ",D5) or using &: =B5&", "&C5&", "&D5
Combines multiple values with separators
Examples:

Values A, B, C → "A, B, C"
Numbers 1, 2, 3 → "1, 2, 3"
"apples", "oranges", "pears" → "apples, oranges, pears"
"red", "blue", "green" → "red, blue, green"
Title "Mr.", First "Bob", Last "Smith" → "Mr. Bob Smith"

Pro tip: The & symbol is faster to type and does the same thing!
Real-world use: Create full names from first/last names, combine address fields (street + city + state), build product descriptions, generate email addresses (first.last@company.com)

LEFT Function

The LEFT function is used to retrieve a chosen amount of characters, counting from the left side of an Excel cell.

What's happening here:

Formula: =LEFT(B5, 3)
Extracts the first 3 characters from the left
Examples:

"January" → "Jan"
"February" → "Feb"
"March" → "Mar"
"April" → "Apr"
"September" → "Sep"

RIGHT Function

The RIGHT function is used to retrieve a chosen amount of characters, counting from the right side of an Excel cell.

What's happening here:

Formula: =RIGHT(B5, 1) or =RIGHT(B5, 2) or =RIGHT(B5, 3)
Extracts characters from the right side
Examples:

"ABC" with RIGHT(B5,1) → "C"
"ABC" with RIGHT(B5,2) → "BC"
"Portland, OR" with RIGHT(B8,2) → "OR"
"google.com" with RIGHT(B10,3) → "com"
"+1 303-512-4271" with RIGHT(B12,12) → "303-512-4271"
"2000 x 3000" with RIGHT(B14,4) → "3000"

Date & Time Functions

Excel stores dates as numbers (January 1, 1900 = 1), which means you can do math with dates! These functions make working with dates much easier.

The TODAY Functions
Get the current date or date and time.

What's happening here:

Formula: =TODAY()
Returns current date only (no time component)
Note: These functions take NO arguments - just empty parentheses!
Examples of using TODAY():

=TODAY() → 31-May-21 (current date)
=TODAY()-7 → 24-May-21 (one week in past)
=TODAY()+7 → 7-Jun-21 (one week in future)
=TODAY()+90 → 29-Aug-21 (90 days from today)

The NOW function

Formula: =NOW()
Returns current date AND time
Examples of using NOW():

=NOW() → 31-May-21 17:39 (current date and time)
=NOW()-7 → 24-May-21 17:39 (last week same time)
=NOW()+7 → 7-Jun-21 17:39 (next week same time)

Important: These functions update automatically every time you open the file or recalculate.

The Year Function

What's happening here:

Formula: =YEAR(B5)
Extracts the year from any date
Examples:

15-Apr-12 → 1912
28-Jun-14 → 1914
29-Oct-29 → 1929

Month Function

Formula: =MONTH(B5)
Extracts the month as a number (1-12)
Examples:

15-Apr-1912 → 4 (April)
21-May-1927 → 5 (May)
29-Oct-1929 → 10 (October)

DAY Function

Create a date from separate year, month and day values.

What's happening here:

Formula: =DATE(B5, C5, D5)
Combines separate Year, Month, and Day columns into a complete date
Examples:

Year: 1995, Month: 1, Day: 1 → 1-Jan-1995
Year: 2010, Month: 1, Day: 1 → 1-Jan-2010
Year: 2021, Month: 1, Day: 1 → 1-Jan-2021
Year: 2021, Month: 2, Day: 1 → 1-Feb-2021

Key Insight: This is incredibly useful when your data has dates split across multiple columns (common in imported data or forms).

Part 4: Pivot Tables

Pivot tables are Excel's most powerful feature for analyzing large datasets. They let you summarize, analyze, and explore thousands of rows of data in seconds without writing a single formula.

Think of it this way: If you have a list of 1,000 sales transactions, a pivot table can instantly tell you:

Total sales by salesperson
Average order value by month
Sales by product category and region
Year-over-year comparisons

All without a single formula!

Creating Your First Pivot Table

Step 1: Prepare Your Data

Your source data should be organized in a table format with:

Headers in the first row: Employee Name, Department, Salary, Joining Year, Performance Rating
No blank rows or columns
Consistent data types in each column
One record per row

Step 2: Insert the Pivot Table

Click anywhere in your data
Go to Insert tab → PivotTable (top-left corner)
Excel will automatically detect your data range
In the Create PivotTable dialog:
- Table/Range: Verify the range (e.g., Sheet3!$A$1:$E$10)
- Choose where to place: Select "New Worksheet" (recommended for beginners)
- Click OK

Step 3: Build Your Report

You'll see a blank pivot table and the PivotTable Fields pane on the right with four areas:

The Four Key Areas:

FILTERS (top-left box)
- Add fields here to filter the entire report
- Example: Add "Joining Year" to filter data by year
COLUMNS (top-right box)
- Fields placed here become column headers
- Example: Add "Department" to see departments across columns
ROWS (bottom-left box)
- Fields placed here become row labels
- Example: Add "Employee Name" to list employees down the side
VALUES (bottom-right box)
- The actual numbers you want to calculate
- Example: Add "Salary" to calculate sum or average

Step 4: Drag Fields to Build Your Analysis

Example: Summarize Salaries by Department

Drag Department → ROWS area
Drag Employee Name → ROWS area (below Department for grouping)
Drag Salary → VALUES area
Drag Joining Year → VALUES area
Drag Performance Rating → VALUES area

Key Insights from this Pivot Table:

IT department has the highest total salary ($226,600)
Finance has the highest individual salary (Chris Taylor at $89,300)
You can see totals by department AND individual employees
Grand total shows company-wide statistics

Pivot Charts

Pivot charts are dynamic charts connected to your pivot table. When you update the pivot table, the chart updates automatically.

Creating a Pivot Chart

Method 1: From Existing Pivot Table

Click anywhere in your pivot table
Go to Insert tab → PivotChart (in the Charts group)
Choose your chart type

Method 2: Create Both at Once

Select your source data
Insert tab → PivotChart dropdown → PivotChart & PivotTable
Excel creates both simultaneously

Choosing the Right Chart Type

Slicers

Slicers are visual filter buttons that make filtering pivot tables intuitive and user-friendly. Instead of dropdown menus, you get clickable buttons.

What are Slicers?

In the example above:

Left slicer: Filter by Product (Apples, Bananas, Cherries, Oranges)
Right slicer: Filter by Reseller (John, Mike, Pete, Sally)

The pivot table shows sales data that updates instantly when you click slicer buttons.

Adding Slicers to Your Pivot Table

Steps:

Click anywhere in your pivot table
PivotTable Tools → Analyze tab → Insert Slicer (in the Filter group)
In the Insert Slicers dialog, check the fields you want as slicers:
- ☑ Product
- ☑ Reseller
- ☐ Month
- ☐ Sales
Click OK

Slicers appear as floating windows you can move and resize.

Conclusion

Excel is more than a spreadsheet,it’s a powerful tool for turning raw data into clear insights. With logical, text and date functions, you can clean data, automate decisions and analyze trends efficiently. Pivot tables, charts, and slicers take this further by helping you summarize, visualize, and explore data with ease.

You don’t need to know every Excel feature to be effective. What matters is understanding which tool to use and when. With these fundamentals, you’re already equipped to solve most real-world data problems confidently.

Keep practicing, experiment with your own datasets, and build from here. This foundation sets you up to work smarter, faster, and more confidently with data. 🚀

Git & GitHub: Your Quick Start Guide

Mariam Turnesh — Fri, 16 Jan 2026 17:05:09 +0000

What's Git Anyway?

Git = Time machine for your code. Every change gets saved. You can always go back.

GitHub = Cloud storage for your Git projects. Like Google Drive, but designed for code.

That's really all you need to know to get started.

Step 1: Install Git Bash

Windows

Go to git-scm.com
Download and run the installer
Keep the default settings and click "Next" through everything

Mac

brew install git

Linux

sudo apt-get install git  # Ubuntu/Debian
sudo dnf install git      # Fedora

Verify It Worked

git --version

See a version number? You're good! ✅

Step 2: Connect to GitHub

Create Your Account

Head to github.com and sign up. Verify your email.

Tell Git Who You Are

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

Important: Use the same email as your GitHub account!

Set Up SSH Keys (The Better Way)

Instead of typing passwords constantly, use SSH:

# Generate your key
ssh-keygen -t ed25519 -C "your@email.com"

# Press Enter 3 times (default location, no password)

# Display your key
cat ~/.ssh/id_ed25519.pub

Copy everything that appears (starts with ssh-ed25519).

Now on GitHub:

Settings → SSH and GPG keys
Click "New SSH key"
Paste your key and save

Test the Connection

ssh -T git@github.com

See "Hi username!"? Perfect! You're connected. 🎉

Step 3: Why Version Control Matters

Without Git, you end up with:

project.zip
project_final.zip
project_final_ACTUALLY_FINAL.zip

With Git:

One project folder
Complete history of all changes
Ability to jump back to any previous version
Safe experimentation without fear

That's version control. It keeps your project history organized and accessible.

Step 4: Track Your First Project

Start a New Project

mkdir my-project
cd my-project
git init

Git is now watching this folder!

Create a File

Make a file called app.py:

print("Hello, Git!")

See What Changed

git status

Git says: "I see app.py but I'm not tracking it yet."

Track the Changes

git add .

This "stages" your changes - telling Git you want to save them.

Save a Snapshot (Commit)

git commit -m "Create initial app"

You just made your first commit! It's a saved point you can always return to.

Good commit messages:

"Add login form"
"Fix mobile navigation bug"
"Update installation instructions"

Bad commit messages:

"stuff"
"changes"
"idk"

View Your History

git log --oneline

There's your commit with its unique ID!

Step 5: Push to GitHub

Create a Repo on GitHub

Click the + icon → New repository
Name it my-project
Keep it Public (or choose Private)
Don't initialize with README
Click "Create repository"

Link and Upload

git remote add origin git@github.com:YOUR-USERNAME/my-project.git
git branch -M main
git push -u origin main

Refresh GitHub. Your code is online! 🚀

The Regular Workflow

From now on, it's just:

# Make changes to your files
git add .
git commit -m "Describe what you changed"
git push

Three commands. That's your daily routine.

Step 6: Pull Changes

Someone else updated the project? You edited on GitHub? Working from another computer?

git pull

Done. You now have the latest version.

Pro tip: Always git pull before you start working!

When Conflicts Happen

Sometimes you and someone else edit the same line. Git shows:

<<<<<<< HEAD
print("Your version")
=======
print("Their version")
>>>>>>> origin/main

Fix it:

Choose which version to keep (or combine them)
Delete those marker lines (<<<<<<<, =======, >>>>>>>)
Save, then:

git add .
git commit -m "Resolve conflict"
git push

Conflicts are normal. Don't stress!

Command Cheat Sheet

The ones you'll actually use:

Command	What it does
`git status`	"What changed?"
`git add .`	"Track all changes"
`git commit -m "..."`	"Save snapshot"
`git push`	"Upload to GitHub"
`git pull`	"Download updates"
`git log --oneline`	"Show history"

Undo Mistakes

Command	What it does
`git restore <file>`	Undo changes to a file
`git restore --staged <file>`	Unstage a file
`git reset HEAD~1`	Undo last commit

Common Mistakes (And Fixes)

❌ Forgot to commit before pushing

git add .
git commit -m "Add missing commit"
git push

❌ Didn't pull first

git pull
# Fix any conflicts if needed
git push

❌ Committed passwords

Delete them, then:

echo "secrets.txt" >> .gitignore
git add .gitignore
git commit -m "Ignore sensitive files"

Why This Matters

Safety: Never lose work again
Confidence: Experiment without fear of breaking things
Collaboration: Work with teams smoothly
Portfolio: Show your projects on GitHub
Career: Every dev job uses Git

What's Next?

You know enough to start using Git daily. Just apply it to everything you build:

Personal projects
School assignments
Practice exercises

The best way to learn? Use it!

Wrapping Up

You now know how to install Git, connect it to GitHub, track your changes and collaborate with others. These are the fundamentals that every developer uses daily.

The key is to start small. Pick a project - any project - and start committing. Make mistakes. Break things. That's how you learn. Git has your back because you can always go back to a working version.

Remember: the best developers aren't the ones who never make mistakes. They're the ones who know how to recover from them. And with Git, recovery is always just one command away.

Now stop reading and start coding. Your first commit is waiting! 💻

What's the first project you're going to put on GitHub? Or if you've already started using Git, what was your biggest "aha!" moment? Share in the comments below - I'd love to hear your Git journey!