DEV Community: OSCAR MAINJE

Building and Querying a Database: A Practical SQL Guide

OSCAR MAINJE — Sat, 18 Jul 2026 14:35:20 +0000

As a data analyst, mastering database design and querying is essential. In this article, I will walk you through how I built and queried a database schema for Greenwood Academy using PostgreSQL.

The article covers everything from defining or changing the structure of database objects using the Data Definition Language (DDL), modifying data within tables using the Data Manipulation Language (DML), and fetching and retrieving data from the database using the Data Query Language (DQL). The article also explores filtering the results, utilizing specialized operators, and writing conditional logic with CASE WHEN.

Building the Database (DDL)

Data Definition Language (DDL) is used to define the database structure. For this task, I created a dedicated schema called greenwood_academy to keep everything organized. Inside the schema, I created three tables: students, subjects, and exam_results. Before creating the tables it is important to make sure that they are created in the intended schema. The SET search_path TO [schema_name] is necessary for this. A better and safer practice is to always initialize your table names with the schema name, for example to create a table students in the schema greenwood_academy you can use the command CREATE TABLE greenwood_academy.students. This will ensure that the table is created in the correct schema.

-- Creating a schema greenwood_academy and making sure SQL is using it.

CREATE SCHEMA greenwood_academy;
SET search_path TO greenwood_academy;

-- Creating the students table.

CREATE TABLE greenwood_academy.students (
    student_id    INT PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL,
    gender        VARCHAR(1),
    date_of_birth DATE, 
    class         VARCHAR(10),
    city          VARCHAR(50)
);

Table Modifications

In practice, database structural requirements change from time to time. In this case, I altered the existing table structures by adding a phone_number column to the students table, renamed a column in the subjects table for better clarity i.e. credits to credit_hours, and then dropped the phone_number column when it was no longer needed:

-- Adding a column 'phone_number'to the students table.

ALTER TABLE greenwood_academy.students
ADD COLUMN  phone_number VARCHAR(20);

-- Renaming column 'credits' to 'credit_hours'

ALTER TABLE   greenwood_academy.subjects 
RENAME COLUMN credits TO credit_hours;

-- Removing the phone_number column in the students table.

ALTER TABLE greenwood_academy.students 
DROP COLUMN phone_number;

Filling in the tables & Modifying the Database (DML)

With the structures ready, Data Manipulation Language (DML) was used to modify the data within the tables. Data for 10 students, 10 subjects, and their respective exam records was inserted into the three tables.

-- ===========================================================
-- Filling the Database(DML: INSERT, UPDATE, DELETE)
-- ===========================================================
-- Example of inserting student data
INSERT INTO greenwood_academy.students(student_id, first_name, last_name, gender, date_of_birth, class, city)
VALUES 
    (1, 'Amina', 'Wanjiku', 'F', '2008-03-12', 'Form 3', 'Nairobi'),
    (2, 'Brian', 'Mutua', 'M', '2007-07-25', 'Form 4', 'Mombasa'),
    (3, 'Cynthia', 'Mutua', 'F', '2008-11-05', 'Form 3', 'Kisumu'),
    (4, 'David', 'Kamau', 'M', '2007-02-18', 'Form 4', 'Nairobi'),
    (5, 'Esther', 'Akinyi', 'F', '2009-06-30', 'Form 2', 'Nakuru');

-- Note: Truncated for readability, full file includes 10 records per table

After inserting the data into the tables, it is important to confirm that it is entered correctly before proceeding.

-- SELECT query to confirm all 10 rows exist in each of the three tables

SELECT * FROM greenwood_academy.students;
SELECT * FROM greenwood_academy.subjects;
SELECT * FROM greenwood_academy.exam_results;

Data Maintenance

Databases are dynamic and get updates and deletions time and again. In this scenario, I updated a student's city, corrected a typing error in an exam mark, and deleted a specific exam result row entirely:

-- UPDATE statement to change Esther Akinyi's city to Nairobi

UPDATE greenwood_academy.students 
SET    city = 'Nairobi' 
WHERE  student_id = 5;

-- UPDATE to fix the marks for result_id 5 to 59

UPDATE greenwood_academy.exam_results
SET    marks = 59
WHERE  result_id = 5;

-- DELETE statement to remove result_id 9 from the exam_results table

DELETE 
FROM  greenwood_academy.exam_results 
WHERE result_id = 9;

Quick Reminder: Forgetting to specify the condition for the update or deletion in the WHERE clause in each of the queries above is catastrophic, as the command will affect the entire table.

Querying Data (Filtering with WHERE)

Filtering data effectively reduces server load and isolates relevant insights. Below are a couple of specific queries using basic WHERE, AND, and OR logical operators to look through the records:

Find all students in Form 3 AND from Nairobi:

SELECT student_id, first_name, last_name
FROM   greenwood_academy.students
WHERE  class = 'Form 3' AND city = 'Nairobi'
ORDER BY student_id;

Find all students who are in Form 2 OR Form 4:

SELECT student_id, first_name, last_name
FROM   greenwood_academy.students
WHERE  class = 'Form 2' OR class = 'Form 4'
ORDER BY student_id;

Range, Membership & Search Operators

To handle more versatile matching constraints, built-in SQL operators like BETWEEN, IN, and LIKE are usually used.

Range Filtering (`BETWEEN`)

Useful for narrowing down continuous datasets like dates or numeric boundaries:

-- Finding all exams that took place between 15th March 2024 and 18th March 2024.

SELECT result_id, student_id, subject_id, exam_date  
FROM   greenwood_academy.exam_results
WHERE  exam_date BETWEEN '2024-03-15' AND '2024-03-18'
ORDER BY exam_date;

Set Membership (`IN` / `NOT IN`)

Replaces repetitive multi-line OR statements:

-- Finding all students who live in Nairobi, Mombasa, or Kisumu using IN

SELECT first_name, last_name
FROM   greenwood_academy.students
WHERE  city IN ('Nairobi', 'Mombasa', 'Kisumu')
ORDER BY city;

-- Finding all students who are NOT in Form 2 or Form 3 using NOT IN.

SELECT student_id, first_name, last_name
FROM   greenwood_academy.students
WHERE  class NOT IN ('Form 2', 'Form 3')
ORDER BY student_id;

Pattern Matching (`LIKE` with Wildcards)

The % wildcard allows flexible string evaluation to search for prefixes or embedded substrings:

-- Finding all students whose first name starts with the letter 'A' or 'E' using LIKE.

SELECT student_id, first_name
FROM   greenwood_academy.students
WHERE  first_name LIKE 'A%' OR first_name LIKE 'E%'
ORDER BY student_id;

-- Finding all subjects whose subject name contains the word 'Studies'.

SELECT subject_name
FROM   greenwood_academy.subjects
WHERE  subject_name LIKE '%Studies%'
ORDER BY subject_name;

Aggregating Data with COUNT

Aggregation helps make sense of the larger scale of the data. Using the COUNT() function, we can calculate quick statistical summaries, such as performance thresholds:

-- Students currently in Form 3

SELECT count(*) AS form_3_students
FROM   greenwood_academy.students
WHERE  class = 'Form 3'; 

-- Exam results having a mark of 70 or above

SELECT count(*) AS scores_70_and_above
FROM   greenwood_academy.exam_results
WHERE  marks >= 70;

Conditional Logic with CASE WHEN

The CASE WHEN expression acts as an if-else statement directly inside the SQL query. This is useful for dynamically creating computed category labels on without changing the underlying stored data.

Generating Performance Descriptions

-- Exam results with a grade description

SELECT result_id, marks,
    CASE
        WHEN marks >= 80 THEN 'Distinction'
        WHEN marks >= 60 THEN 'Merit'
        WHEN marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS perfomance
FROM greenwood_academy.exam_results
ORDER BY marks;

Grouping Students by Seniority Level

-- Using CASE WHEN to label each student's level

SELECT student_id, first_name, last_name,
    CASE
        WHEN class IN ('Form 3', 'Form 4') THEN 'Senior'
        ELSE 'Junior'
    END AS student_level
FROM greenwood_academy.students
ORDER BY student_level;

Conclusion

This exercise simulates a realistic scenario for setting up and querying databases. It covers schema design, table modifications, and relevant classification using CASE WHEN. The task gives foundational knowledge to manipulating enterprise-level data.

The entire source code is structured cleanly into structured query files and tracked on my GitHub repository.

Thanks for reading! If you have any questions or alternate methods for querying this data, feel free to drop a comment below! Happy querying!

The Ultimate Step-by-Step Guide: Connecting Power BI to Cloud-Based & Local PostgreSQL

OSCAR MAINJE — Sun, 05 Jul 2026 21:38:57 +0000

Introduction

Whether your data is securely stored in a file on your desktop or is being streamed from a cloud-based platform on Aiven, establishing a connection to Power BI is a fundamental skill for the modern data analyst. Connecting Power BI to a database shouldn't feel like guesswork. This article provides a comprehensive, step-by-step guide for linking Power BI Desktop to two distinct environments: a local PostgreSQL instance localhost:5432 and a cloud-hosted Aiven PostgreSQL deployment secured by an SSL ca.pem Certificate.

Connecting Power BI Desktop to a Locally Hosted PostgreSQL Database

Before opening Power BI, it's important to ensure that the local database is running and note the credentials.
The credentials used should be as follows;

Host: localhost or 127.0.0.1
Port: 5432
Database Name: The specific name of your database for example postgres or your custom project database.
Username: Usually postgres
Password: The password you created when installing PostgreSQL on your laptop

Establishing the Connection in Power BI Desktop.

Launch Power BI Desktop.
On the Home ribbon tab, click Get Data > select More
Select Database from the left categories > click on PostgreSQL database > click Connect. As shown in the image below.

In the connection window, fill out the database credentials as follows:
Server: Type Host:Port, e.g., 127.0.0.1:5432
Database: Type the name of your local database e.g., postgres
Data Connectivity Mode: Select Import
Click OK as shown below.

Input your local database credentials

A credential window will pop up asking how you want to access the database.

Select the Database tab on the left margin.

Enter your local credentials:

User name: Type postgres (or your specific local username)
Password: Type your local database password.
Click Connect Because local connections do not typically use an SSL certificate, Power BI might show a warning message saying: The data source doesn't support an encrypted connection.
Simply click OK or Run Unencrypted to bypass this.

Since the data is residing on your own laptop, it does not travel across the internet, making an unencrypted local stream safe.

A Navigator window will open showing all the schemas and tables inside your local database. You can now pick the dataset you wish and proceed to transform in Power Query.

Connecting Power BI Desktop to a Cloud Hosted PostgreSQL Database on Aiven.

Power BI Desktop utilizes the underlying Windows Certificate Store to validate encrypted database links. Because Aiven uses its own to secure cloud data, Windows will block the connection as "Untrusted" unless we add it to the laptop's root directory.

To do this, we must first download the CA certificate from the Aiven console.
Open your web browser, navigate to your Aiven Console, and click on your running PostgreSQL service.
Stay on the Overview page and scroll down to the Connection information section.
Locate the row labeled CA Certificate and click the Download button next to it.

This will save a file named ca.pem directly to the laptop's Downloads folder.

Install it into the Windows Trusted Root Authorities.

To make this certificate trusted by Power BI for all connections, you must install it at the local machine.

In the search box, search for "Manage user certificates.
In the left-hand folders console, expand Certificates.
Right-click on the Trusted Root Certification Authorities folder > hover over All Tasks > click Import

The Certificate Import Wizard will open.

Click Next → Browse to find your file.
Crucial Step: In the file browser pop-up, change the file extension filter dropdown in the bottom right to All Files. Otherwise, your ca.pem file will stay hidden.
Navigate to your Downloads folder, select ca.pem, and click Open.
Click Next.
Ensure the option Place all certificates in the following store is selected and if it explicitly targets the Trusted Root Certification Authorities store.
Click Next.

Review the summary page and click Finish.

A pop-up confirming "The import was successful" will appear.
Close the console.

Establishing the Connection in Power BI Desktop

Now that your laptop recognizes Aiven as a trusted authority, you can complete the link securely.
Launch Power BI Desktop.

On the top Home ribbon, click Get Data → select More → choose Database → select PostgreSQL database.
Click Connect.

Input your parameters copied from your Aiven Console:

Server: Enter your Host name followed by a colon and the port number (e.g.,pg-123a4b5c-my-project.aivencloud.com:12345)
Database:Type defaultdb

Data Connectivity Mode

Select Import.
Click OK.

When the database authentication window pops up, click the Database tab on the left-side margin.
Input your database user details.

User name: avnadmin
Password: Paste your specific Aiven service password.
Click Connect

A Navigator canvas window will appear displaying your database contents.

Simply check the box next to your loaded table (jcars in my case) and click Transform Data to start transforming it within Power Query

In Conclusion ...

At the end of the day, a data analyst's value isn't just in making pretty charts but in building robust, dependable data infrastructure. Moving your pipelines onto PostgreSQL and securing them via trusted CA root certificates proves you understand high-level data security and database operations.
And that’s a wrap! You now have a fully functional, end-to-end data pipeline running seamlessly from a local environment or an encrypted cloud database straight into Power BI. No more connection timeouts, no more hidden SSL certificate rejections, and no more headaches.

Power BI Data Modelling Demystified: Models, Schemas, Relationships, and Joins

OSCAR MAINJE — Wed, 01 Jul 2026 12:31:04 +0000

Data Modelling in Power BI

Assume you are running a business that requires you to order and supply different products from different suppliers and sell them to different customers every day. To keep records of your business, you are likely to use tools such as Excel to store data for your suppliers, products, customers, and sales made on a daily basis in a table.

Having so much data in a table soon becomes problematic as the transactions grow into tens and hundreds of thousands. If this data is summarized in different tables, each storing data relating to one entity, then there's a need to establish the relationships between the different tables. This is where data modelling comes in.

The purpose of data modeling in Power BI is to create a structured, optimized, and meaningful representation of data to support effective analysis, reporting, and visualization. Data modeling is a critical step in the Power BI workflow that transforms raw data into actionable insights for decision-making and business intelligence.

A data model in Power BI is a logical representation of how data is structured and connected within the tool. It consists of:

Tables: Represent the data from one or more sources like Excel files, CSVs, or cloud services.
Relationships: Define how these tables are connected to each other.
DAX (Data Analysis Expressions): The formula language used to create custom calculations, i.e., measures and calculated columns

A well-designed data model is the foundation for creating reports and visualizations fast and should have the following features.

Scalability: Well-designed data models should handle more complex analysis as business needs grow without major changes.
Reduced Maintenance: Once the model is set up, it should be easier to maintain if it’s well organized and streamlined.
Proper Documentation: Its design should ensure that anyone working with it can easily understand how it works.

Why is Data Modeling Important in Power BI?

Efficient Data Exploration

Data Modeling helps create hierarchies and drill-down paths, allowing users to easily explore data, uncover insights and identify trends.

For example, hierarchy looks like this;
In a Sales Data set:
Country →County →City →Store →Product
or
In a Time Data set:
Year →Quarter →Month →Day.

Improved Performance

A well-designed data model makes data retrieval fast and efficient, whereas a poorly structured data model with redundant data or complex relationships can slow down queries and delay reports.

Accurate Reporting

Having a good data model ensures accuracy, consistency, and reliability. This helps in generating reliable reports, provides better insights, and leads to better decision-making. Proper data modeling helps eliminate errors and ensures that the data used for analysis is correct and dependable.

Types of Data Modeling

Relational Model: It prioritizes writing data efficiently without duplication. For example, a customer's address is only stored in one table, and linked to orders via IDs. This makes it great for applications but complex for analysts to run a simple report.

Dimensional Model: Uses Star or Snowflake schemas. It trades storage efficiency for query speed by duplicating and centralizing context (denormalization). For example, instead of querying multiple interconnected tables, an analyst looks at one central Sales Fact table linked to a Customer Dimension table to get sales by customer in seconds

The table below shows comparison between the relational and dimensional data models.

Fact Tables and Dimension Tables

What is a Fact Table?

A Fact table stores quantitative and numerical data which can be aggregated and analyzed. Fact table is a centerpiece of a data model and has information and has information about Transactions, Numerical measures like Sales amount, Quantity sold, Product cost and Foreign keys that links to dimension tables.

In a Fact table, each row represents a single transaction or event, and each column represents a measurable attribute. For example, a sales fact table may have columns for the product sold, quantity and the total sales amount.

Key Characteristics:

Measures or Metrics: Fact tables contain facts (quantitative data) such as sales amount, quantity sold, etc.
Foreign Keys: They have foreign keys that link to dimension tables, providing context for the facts.
Granularity: The level of detail or granularity of the fact table is defined during the design process (e.g., sales per day, per transaction).

Example:
Consider a retail business analyzing its sales data. The fact table might look like this:

ProductID, and StoreID are foreign keys linking to dimension tables.

What is a Dimension Table?

A dimension table in Power BI contains descriptive data. It provides context and additional details to the numerical data in a fact table. For Example: A product dimension table may have columns such as product name, category, description and supplier name. Dimension tables usually contain fewer rows, whereas Fact tables can hold large amounts of data that grows over time.

In a data model, a dimension table is connected to a fact table through a relationship. This allows Power BI users to analyze the fact table data by filtering, grouping and aggregating it based on the attributes in the dimension table.

Key Characteristics:

Descriptive Data: Contains descriptive data such as product names, customer names, dates, etc.
Primary Keys: Each dimension table has a primary key that corresponds to a foreign key in the fact table.
Attributes: Dimension tables include various attributes that describe the dimension.

Example:
Continuing with the retail business, dimension tables might look like this:
Product Dimension Table

Store Dimension Table

In these examples:
The Product Dimension Table provides details about each product.
The Store Dimension Table provides details about each store.

By linking these dimension tables to the fact table, you can generate insightful reports and analyses that provide a complete view of your business data.

Schemas

What is a Schema?

A schema is a collection of database objects, including tables, views, and indexes. It defines the structure of the data and the relationships between the different objects. In Power BI, a schema represents a logical grouping of tables that are related to each other.

Types of Schemas

There are two types of schemas: Star schema and Snowflake schema. Let's explore each of these in detail.

Star schema

Star Schema is a database design that separates data into fact tables(for summarizations) and dimension tables(for filtering and grouping).Its highly effective for Power BI as it optimizes the performance and usability by structuring data for easy reporting and visualization.

Explicit Measures are custom calculations created with DAX Formulas, whereas Implicit Measures are automatically generated by Power BI from columns that can be summarized such as sum or average without needing custom formulas.

A star schema may be preferable in some instances because it offers the following;

Simplified Querying: Star Schemas are easy to understand and implement. Their denormalized structure reduces the number of joins and required to retrieve data. This simplifies and leads to faster data aggregation and reporting.
Denormalization: It is the process of combining tables to reduce complexity and improve performance often by adding redundant data.
Flexibility: Star schemas are adaptable and easy to update when new data sources are added to a Power BI report.
Intuitive Analysis: Star schemas provide more intuitive and user-friendly experience for end-users of a Power BI report. The data is organized in a logical and easy to understand manner, making it easier for end-users to identify trends and patterns in the data.
Easier Maintenance: Having fewer tables and simple relationships between them, star schemas are easier to maintain and update over time.

An example of a model with a star schema design is shown below.

Snowflake schema

The Snowflake Schema is like the Star schema in terms of having a central fact table that everything passes through, but the main difference is that the Snowflake schema has normalized dimension tables as opposed to denormalized dimension tables for the Star schema.

The Star schema typically goes down two levels of data hierarchy while the Snowflake schema goes to a third level. The purpose of a Snowflake schema is to normalize the denormalized data in a Star schema.

In a snowflake schema, the relationships between tables form a pattern that resembles a snowflake, with the central fact table connected to multiple dimension tables and each dimension table potentially connected to additional sub-dimension tables.

An example of a model with a snowflake schema design is shown below.

Relationships in Power BI

In Power BI, relationships between tables are created to enable data to be combined across them. This is done using the following:

Primary and Foreign Keys: Ensures unique identifiers in fact tables match corresponding keys in dimension tables.
Cardinality: Defines the type of relationship between two tables, defining how data and filters flow between them.

Relationships Concepts

Primary keys
Primary keys are unique columns in a table that helps creates a relationship between two tables by sharing common values. The Primary key uniquely identifies each record.

Foreign keys
Foreign key is a column in one table whose values match the values of the primary key in another table.

In the above image, In the product dimension table, primary key is the product Id column, which is a unique identifier of a product or row in the products table. The product dimension table is connected to the fact Orders Fact table based on Product ID, so here the product Id column within the orders table acts as a foreign key.

Cardinality

Each relationship in a model is defined by a cardinality type. It refers to the number of unique values in one table related to the number of unique values in another table.

One to One (1:1)

This relationship occurs when one record in the first table is related to one and only one record in the second table. This is relatively uncommon in Power BI.

One to Many(1:*)

This occurs when a single record in one table relates to multiple records in another table, commonly used to link dimension tables(one side)to fact tables(many side).

Many to One(*:1)

This occurs when multiple records in a fact table relate to a single record in a dimensional table, which is commonly used in Power BI because it simplifies data aggregation such as sum or average and enhances filtering.

Many to Many (* : *)

This occurs when many records in the first table can be related to many records in the second table. This type of relationship is not directly supported in Power BI because it creates ambiguity in data aggregation and filtering, leading to performance issues and invalid results. Instead, such relationships require a bridge table to resolve the Many to Many connections.

Ambiguity: Unclear data aggregation due to multiple relationship paths.
Bridge Table: A table that links Many-to-Many relationships.

Cross Filter Direction

In Power BI, the cross-filter direction controls how filters propagate between visuals. Each relationship in a model defined with a cross-filter direction. It can be a single (Single directional) or both (Bi-directional).

Single

A single arrowhead represents a single-direction filter in the direction of the arrowhead. Here, only one of the tables in the relationship can filter data across to the other table. This means filtering data in one table affects the data shown in the other table, but filtering in the other table does not affect the data shown in the first table.

Bi-Directional

Bi-Directional cross-filtering enables them to apply filters on both sides of a table relationship(data can be filtered in either direction).This means filtering data in one table affects the data shown in the other table and vice versa. This type of filtering is less common and can degrade performance while potentially creating ambiguous filter paths. Cross-Filter options are dependent on the cardinality.

Active and Inactive Relationships

Active Relationships

Active Relationships are created in Power Bi Desktop’s Model View. To create one, drag and drop a common filed from one table to another table. Active relationships are visible in solid lines. These are default relationships used for filtering and calculations between tables ensuring data is linked for analysis.

Inactive Relationships

In Power Bi, inactive relationships are used when multiple relationships exist between tables, letting us specify which one should be used for certain calculations. Marking a relationship as inactive prevents it from being used by default, resolving issues like ambiguous paths and conflicting filters.

Joins

What are Joins?

Table joins can be used to compare or combine rows between tables. They compare tables based on one or more related columns. When the compared row values between two tables are identical, they are considered a match. The behavior that follows depends on the join type. Mastering joins is a valuable skill for the data cleaning process.

How to Merge Queries

So, how do you perform a join in Power Query? The easiest way to join tables in Power Query is by using merge queries. To start, go to the Home tab in the ribbon and navigate to the Combine section. There you will find the Merge Queries button. You have two options there:

Merge Queries performs a join on your current table and adds the result of this merge in the same query. This means you can easily see the impact of the merge in a single place.
Merge Queries as New performs the same join on your table but puts the result of the merge in a separate query. The benefit of this approach is that your starting query remains the same. This means that you can still reference that starting query for other purposes.

After choosing your desired way to merge queries, the below pop-up appears. This menu allows you to choose the Merge type you want to perform.

Types of Joins

Left Outer Join

One of the most common joins is the Left Outer Join or simply the Left Join. The left outer join returns all rows from the Left Table (table1). Then, by matching one or more column values with rows from table1 with similar column(s) from rows in table2, the join returns the matching values of the Right Table (table2).

Right Outer Join

The Right Outer Join returns all values from the Right Table (Table2) while only returning the matching values from the Left Table (Table1).
Its behavior is very similar to the Left Outer join, except that its base table is the Right table instead of the Left.

Full Outer Join

There are cases where you want to return all rows from both Table1 and Table2. In those cases, the Full Outer Join is your friend.

The Full Outer Join tries to match column values from Table1 with Table2 and if successful, puts them next to each other. The values of Table1 that don’t exist in Table2 get their own row. Any rows without a match in Table 2 return null values for these rows.

Similarly, the values of Table2 that don’t exist in Table1 also get their own row. For these rows, the missing columns from Table1 get null values.

Left Anti Join

The Left Anti Join only returns rows from the Left Table (Table1). Based on the key columns, the join searches for which values in Table1 do not exist in Table2. It then only returns the unique values from Table1.

Right Anti Join

The Right Anti-Join is the twin sister of the Left Anti-Join. It returns the rows from the Right Table (Table2) which are not present in the Left Table (Table1).

Inner Join

The Inner Join is a useful join that returns only those rows that have matching values in both the Left Table and the Right Table. This can be useful when creating tables for your data model. Imagine filtering down a transaction table to contain only those transactions you want to analyze, but your product table still contains all products from your database.

Self Join

A self-join is a join like all the others, but the table is joined with itself. In the Merge Queries interface, you can select the table to merge. When you want to do a self-join, you can simply fill in the same table twice.

The following images show the common types of joins in Power BI.

Conclusion
This article has provided an overview of data modelling in Power BI by exploring the types of modelling, different types of schemas, the relationships that exist within a schema and how to join tables in Power Query, covering standard joins like Inner, Left, Right, and Full Outer. The article also explores special joins such as the Self Join, along with ways to union tables.

It’s important to remember that each join type has its use case and that you should choose the one that best fits your needs. With these tools, you can now efficiently model data in Power BI.

Enjoy Power BI!

How Excel is used in Real-World Data Analysis

OSCAR MAINJE — Sat, 06 Jun 2026 12:03:10 +0000

What is Excel and who needs to know it?

Excel is a popular software that allows users to organize, format, and perform various calculations on data in a spreadsheet. Data in Excel is usually stored in rows or records that run horizontally from left to right and columns or fields that run vertically from top to bottom. The intersection of a row and column forms a cell wherein data is entered.

Excel is widely used in almost all industries to store, process, analyze, and visualize data. In finance, sales, and project management, for instance, it’s used to track invoices and forecast revenue. It’s even more important for those in careers that work daily with data, such as data analysts and scientists, marketing managers and business owners, in their day-to-day work.

What makes Excel even more popular are its collaboration tools like shared workbooks, which are valuable in business settings as they transform a single spreadsheet into dynamic and shared workspaces and improve operational efficiency.

Excel for Practical Data Analysis.

Having known what Excel is, we can now explore how it is used in real-world data analysis in 3 different industries and the most common features in Excel that facilitate that process.

Supply Chain and Logistics Management.

Managers in this industry can rely on Excel for inventory control to prevent product stockouts and track the shipping of products. In this case, conditional formatting can be used to automatically highlight a product in a certain color if the current stock falls below the preset threshold. Functions such as NETWORKDAYS() can be used to calculate the arrival dates for products on order.

Project Management.

Project managers can use Excel to make sure that complex projects are staffed adequately, executed within the budget, and delivered on time. Excel can be used to create a visual project timeline chart with fixed start and end dates. The function SUMIF() can be used, for instance, to calculate the total time an employee has been assigned across multiple projects and prevent overburdening one individual.

Human Resources Management.

HR managers can use Excel to manage employee data, analyze organizational costs, track overtime work, and use features such as PivotTables to ensure employee salaries across various departments are correct.

Formulas and Functions in Excel.

Formulas and functions are used in Excel to analyze data and manipulate text. Although technically different, the two terms are often used interchangeably.
Function: A built-in tool that is pre-programmed, e.g., SUM() or COUNT().
Formula: An equation written by the user to calculate a value in Excel. It always begins with an equal sign (=), e.g., =C4+B2

The following are the most common functions in Excel:

Math and Trigonometry functions.

Some functions in this category include the following;

SUM(): Adds its arguments.
SQRT(): Returns a positive square root.
ROUND(): Rounds a number to a specified number of digits.

The following image illustrates the use of the ROUND() function.

Text functions

These are the functions used to extract, clean, format, and manipulate text data.
Some functions in this category include the following:

TRIM(): Removes spaces from text.
PROPER(): Capitalizes the first letter in each word of a text value
UPPER(): Converts text to uppercase

The following image shows the use of the UPPER() function

Statistical functions

These functions are used to summarize, analyze and interpret data.
Some functions in this category include the following:

MIN()Returns the minimum value in a list of arguments
COUNT() Counts how many numbers are in the list of arguments
AVERAGEIF() Returns the average of all the cells in a range that meet a given criteria
AVERAGE() Returns the average of its arguments

The following image shows the use of the AVERAGE() function to calculate the average current price of 10 products

Conclusion

Having embarked on a journey of learning Excel as a tool for data analysis, I no longer see data just as a collection of numbers and texts with little meaning in real life. If analyzed well using tools such as Excel, data proves to have power to answer important questions and solve many operational challenges that businesses face from day to day. As I continue to develop my skills in Data Science, Analytics and AI, I now appreciate that even though there are more advanced tools like Python and Power BI, Excel will remain to be a tool that is widely used in the data field.

DEV Community: OSCAR MAINJE

Building and Querying a Database: A Practical SQL Guide

Building the Database (DDL)

Table Modifications

Filling in the tables & Modifying the Database (DML)

Data Maintenance

Querying Data (Filtering with WHERE)

Range, Membership & Search Operators

Range Filtering (BETWEEN)

Set Membership (IN / NOT IN)

Pattern Matching (LIKE with Wildcards)

Aggregating Data with COUNT

Conditional Logic with CASE WHEN

Generating Performance Descriptions

Grouping Students by Seniority Level

Conclusion

The Ultimate Step-by-Step Guide: Connecting Power BI to Cloud-Based & Local PostgreSQL

Introduction

Connecting Power BI Desktop to a Locally Hosted PostgreSQL Database

Establishing the Connection in Power BI Desktop.

Input your local database credentials

Connecting Power BI Desktop to a Cloud Hosted PostgreSQL Database on Aiven.

Install it into the Windows Trusted Root Authorities.

Establishing the Connection in Power BI Desktop

In Conclusion ...

Power BI Data Modelling Demystified: Models, Schemas, Relationships, and Joins

Data Modelling in Power BI

Why is Data Modeling Important in Power BI?

Types of Data Modeling

Fact Tables and Dimension Tables

What is a Fact Table?

What is a Dimension Table?

Schemas

What is a Schema?

Types of Schemas

Star schema

Snowflake schema

Relationships in Power BI

Relationships Concepts

Active and Inactive Relationships

Joins

What are Joins?

How to Merge Queries

Types of Joins

How Excel is used in Real-World Data Analysis

What is Excel and who needs to know it?

Excel for Practical Data Analysis.

Formulas and Functions in Excel.

Conclusion

Range Filtering (`BETWEEN`)

Set Membership (`IN` / `NOT IN`)

Pattern Matching (`LIKE` with Wildcards)