DEV Community: Clare Mwangi

Understanding Data Modeling in Power BI: Joins, Relationships and Schemas Explained

Clare Mwangi — Tue, 28 Apr 2026 16:26:56 +0000

Like I stated in my previous article How Excel is Used in Real-World Data Analysis, I'm on a learning journey. The next data analysis tool to be added to my toolkit is 'you guessed it!' Power BI. The last 2 weeks, have been eye-opening. I have learned that in addition to Excel (still a vital tool), there exists other tools that can do similar but better job. Below, I'll be explaining some concepts I have learned so far that could benefit your learning journey as well.

Data Modeling: What is it?

A Data Model is a visual representation of how data will be organized and stored in a database. It's like a map that shows what data is stored in each table and how they are connected or related to each other.

Data Modeling is the process of creating the 'map' and connecting the different data sources, defining how they relate to one another and organizing them into a structure that supports analysis.

How is a Data Model Created?

I will illustrate the process using a dataset that will hopefully help us better understand what a Data Model and Data Modeling is.

Below is a representation of what the dataset looks like in Excel. It contains multiple tables(customers, products, stores, sales) in different worksheets.

There are three methods used in Power BI:

Table View (highlighted in the picture): We start with loading our data into Power BI by opening a blank report and load our workbook using the Get Data command found in the ribbon. (we assume we have a clean dataset if not use the transform button to clean your dataset first then build your model)

Once your dataset is loaded, we can now begin building our Data Model using the Manage Relationships features seen on the ribbon

Model View (highlighted in the picture): We can also use the Manage Relationships feature under this set up to build our model.
Below is a snapshot what our model looks like before establishing the relationships between the tables.

This is how we 'Manage Relationships' between our tables. Once you click on the manage relationships feature, a pop out will appear with a large green button written '+New Relationship', click on it and below is what you should see.

From our example, we are establishing the relationship between the sales table and the customers table by selecting their shared column which is the ID columns. After doing the same with the rest of the tables this is our output:

The Data Model

Report View (the icon above the table view icon I've highlighted in the snapshot above): You can also Manage relationships from there and build your model.

Joins

These are methods used to combine rows in tables based on related columns. In Power BI, they are executed using the Merge Queries Command in Power Query.

Different types of Joins in Power BI explained:

Left Outer Join: This is the most common join type. It will keep all rows in your left table and match with the ones from your right table. In the Merge queries pop up window, look at the very bottom to see how many rows have been matched. If it's less than the total rows, it means there are extra rows in the left table that do not exist in the right table and will return as null.
Right Outer Join: This join merges tables by retaining all the rows in the right table and matching them to the related rows in the left table. In this case, whatever is in the left table that does not exist in the right table will return as null in the left table.
Full Outer Join: This join merges both the left and right table by keeping all the rows. Whatever row is missing in the right table will return as null, and if there is a match the rows are combined. The same happens in the left table.
Inner Join: It combines the left and right tables by matching exact rows therefore, reducing the number rows in the final table.
Left Anti Join: This join checks for matches and only returns the rows in the left table that don't match with the right table.
Right Anti Join: This join also checks for matches from the right table to the left table and will return the rows from the right table that don't match with the rows left table.

Relationships

In Power Bi, Relationships refer to the links or connections between tables. This is how Power BI is able to know which table is related to which via a common column.

Types of relationships:

1:M - This is a One to Many relationship, meaning one table links to many other tables. From our model view above you can see that the sales table is linked to the other 3 tables. This is the most common relationship and can also be termed as a Many to One. This also enables smooth aggregation of data when it comes to analysis.
M:M - This is a Many to Many relationship, meaning multiple fields in one table are related to multiple fields in another table. This could cause errors during calculations and so it's best to avoid it.
1:1 - This is a One to One relationship, meaning one table is connected to another table via a related column. This is rare to occur.
Active vs Inactive - You can have multiple relationships between tables. By default Power BI will use the active relationship every time you are conducting your analysis. For the inactive relationship to be used, you must manually instruct power bi to do so.
Cardinality - This defines the nature of relationships between tables [1:M, M:M or 1:1].
Cross-filter direction -This is a feature in Power BI that determines how filters will affect the data in your related tables. There are two types of cross-direction filters:
- Single direction: This filter only moves in one direction from the main table to the other tables connected to it but not the other way around, meaning that when you apply a filter in your secondary table, it will not affect the main table.
- Bidirectional: This filter moves both ways from the main table to the secondary tables and vice versa.

The difference between Joins and Relationships is that, Joins literally combines tables into one big table and Relationships show you how the tables are connected but it keeps them separate.

Schemas

Star Schema is a data model with a central table and several lookup tables, like in our data model. The sales table is our central table and the customers, products, stores are our lookup tables. The central table is also known as a Facts tables. This is because it contains facts that can be measured(quantitative data) like quantity sold, price, stock levels etc. The lookup tables are also known as Dimension tables. They give context to the facts tables meaning they contain descriptive information(qualitative data) like names, cities, categories, email addresses etc.
Snowflake Schema is a data model where the dimension tables also have dimension tables(sub-dimensions). One way to do it is by extending the dimensions tables in a star schema.
Flat Table (DLAT) - This is a table that contains all the information, what is supposed to be in the facts table and dimension tables is combined in a single table. For example, a single excel worksheet with all business information acting as its database.

Key points to note is to always use cleaned data before building your data model. This can easily be done using power query, you don't have to use excel. Use a star schema, it's easy to comprehend relationships between the tables and avoid many to many relationships as this could complicate computations and lead to errors.

You Want To Learn SQL? Here Is What I know So far...

Clare Mwangi — Tue, 28 Apr 2026 12:12:59 +0000

SQL: What is it?

First of all, it stands for Structured Query language. It is one of the programming languages used to communicate with relational databases. It's used to store, extract and manipulate data in a database.

There are different SQL command categorizaions used to facilate the different uses of SQL.

These are:

1. DDL (Data Definition Language)

2. DML (Data Manipulation Language)

3. DQL (Data Query Language)

4. DCL ( Data Control Language)

5. TCL ( Transactional Control Language)

In this article, we'll be covering the first three types. Why? We are only just beginning and I'd like to take you step by step.

Say you've been tasked with building a database to store data, manipulate it and query it(retrieve). Which type of commands will you use?

Data Definition Language (DDL)

These are commands for defining and managing database structures.

They are:

CREATE: When you want to build something new like schemas and tables, this is the command to use.

Example;

        CREATE SCHEMA nairobi_academy;

        CREATE TABLE students (
            student_id SERIAL PRIMARY KEY,
            first_name VARCHAR(50),
            last_name VARCHAR(50)
       );

ALTER: When you want to modify your existing table structure, say you forgot one column and would like to add it to your table without having to create the table afresh, this is the command to use.

Example;

       ALTER TABLE students
       ADD COLUMN phone_number VARCHAR(20);

You later realize you don't need the phone numbers after all and would like to do away with them, this is the command to use.

Example;

       ALTER TABLE students
       DROP COLUMN phone_number;

DROP: When you want to remove permanently.

Example;

      DROP TABLE students;

TRUNCATE: This is used when you want to remove all rows from your table but retain the table structure.

Example;

      TRUNCATE TABLE students;

Data Manipulation Language (DML)

These are commands for working with data. So you have your tables ready and you want to input your data.

INSERT: We use this commands to fill in our records.

Example;

     `INSERT TABLE students (student_id, first_name, last_name)
     VALUES ('Clare', 'Mwangi');

The reason as to why I have not included the student ID in my statement is because when 'CREATING' our table above, I used the function SERIAL which will auto-populate our ID's starting from the number 1. We'll talk about functions and constraints (primary key) later.

UPDATE: used when you want to modfify existing data e.g change a student's location because they relocated.

Example;

    ` UPDATE students
      SET city = 'Nairobi'
      WHERE student_id = 5;

DELETE: This used when you want to remove specific data from your table because you no longer need it.

Example;

    ` DELETE FROM students
      WHERE result_id = 9;

Data Query Language (DQL)

These are commands for retrieving data. You want to extract information from your database or rather ask questions so as to conduct your analysis and gain insights, this is the command to use:

SELECT:

Example;

    SELECT * 
    FROM students;

This command will enable you view the entire students'table with its data.

   `SELECT * 
    FROM students
    WHERE city = 'Nairobi';

This command will extract data only for students who come from Nairobi.

    SELECT result_id, student_id, grade
    From exam_results
    Where grade = 'A' AND grade = 'B';

This command will return data from the exam_reults table of students who scored an A or B.

By now, you've noted we've been using where quite a bit to extract specific information from our tables. That is exactly what the WHERE CLAUSE is used for to filter datasets.

You may also need to categorize you data without altering the tables you created and are wondering what command to use;

CASE WHEN:

Example;

    SELECT * 
    CASE 
        when marks >= 80 then 'Distinction'
        when marks >= 60 then 'Merit'
        when marks >= 40 then  'Pass'
        else 'Fail'
    END as Performance 
    FROM exam_results;

This is a big one, I know! It may look complicated but it's okay, I'll walk you through it.

What this command is doing is: First, it want to extract all columns' data from the exam_results table. Second, it wants to label the marks, this is where CASE WHEN comes into play. Anybody who scored an 80 and above will be labelled as having a 'Distinction' and so on and so forth. The END as performance gives the label column a name 'Performance'

This would be the output of our query:

So far so good! I would say. There is more coming.

How Excel is Used in Real-World Data Analysis

Clare Mwangi — Sat, 28 Mar 2026 20:55:25 +0000

Organizations in healthcare, finance, manufacturing, retail and every other you can think of, use Excel for various reasons. For example, financial institutions like banks use Excel to create budgets, track cashflow or forecast revenue. Retailers may use Excel to track their stock levels or store their suppliers' data. Healthcare organizations may use Excel to store patient data and many other uses.

So What Is Excel?

Excel is a spreadsheet program developed by Microsoft that is used to organize, analyze and even visualize data. It stores data in a tabular format and provides a wide range of formulas, functions and visualization features such as charts which are used in the data analysis process. Despite the increase in advanced analytical tools, Excel is still widely used because it is user friendly, flexible and powerful enough to handle large datasets. It is also a great tool for novice data analysts to start off with in their analytical journey. Being a beginner as well, the first tool in my data analytics toolkit is none other than Excel.

Useful Features in Excel for Data Analysts

Several functions and formulas in Excel are commonly used to transform and analyze data:

Data Cleaning

Data is often 'dirty' and therefore a bit of cleaning is recommended before analyzing it to ensure accuracy. Below is a couple of features I use in my data preparation:

Find and Replace: You can find this feature in the Home tab or through the shortcut [Ctrl+H]. This feature is used to remove unwanted texts or characters from your dataset.
TRIM Function: is used to remove extra spaces from text data type. [e.g. =TRIM(A2)] where A2 is the cell reference of the text you want to clean.
Text to Columns: Found in the Data tab, this feature is used to split data into separate columns. For example, if your dataset contains a field/column with full names and you would like to separate them into different columns, this feature will come in handy.
Conditional Formatting: Also found in the Home tab in the Excel ribbon. For example, you may have a dataset with duplicate entries say in the name column, use conditional formatting to highlight the cells with duplicates while assessing data in the entire row, if it's also duplicated you can do away with the duplicates and remain with one entry.
Data Validation: I recommend using this feature when gathering your data, to avoid e.g. 'typos' and any other inconsistencies. This feature found in the Data tab, will help to lighten the data cleaning process due to the preset rules at the point of data entry.

Data Transformation

You want to analyze data that is in a usable format and below as some functions that could be useful in transforming your data:

By Formatting

Once you open your dataset and you are finding it difficult to comprehend what you are looking at e.g. some columns might not be visible or the data cuts off at some point, some cells might be blank or you can't tell if your fields/columns have a title, this is why formatting is recommended to improve readability. In your Home tab, you'll find all formatting tools you need to improve your dataset. You can bolden the column titles to distinguish them from the rest of the dataset, you can autofit the column width to make all cells with data visible and also change the font type and size to your liking and many more.

Using Functions

ABS: [=ABS(cell reference)] This feature known as absolute, is useful when you want to convert negative values to positive values.
VALUE: [=VALUE(cell reference)] is used when converting numbers that are in text format to numbers.
UPPER: [UPPER(cell reference)] This feature is used to convert text that is in small letters to capital letters.
LOWER: [=Lower(cell reference)] is used to convert text that is in capital letters to small letters.
PROPER: [=PROPER(cell reference)] This is used to convert the first letter of the text to a capital letter.

There are more Excel features used by Data analysts to transform messy datasets into usable and readable formats. I only mentioned these few for a start that are, in my opinion; easy to grasp for a beginner.

Data Analysis

This is the part where the processed data is analyzed to generate insights that are used in decision making by stakeholders e.g. management, marketing team, healthcare professionals.

A few functions used in data analysis:

MEAN/AVERAGE: [=AVERAGE(range of cells)]
MEDIAN: [=MEDIAN(range of cells)]
MODE: [=MODE(range of cells)]
SUM: [=SUM(range of cells)]
COUNT: [=COUNT(range of cells)]
MAX: [=MAX(range of cells)]
MIN: [=MIN(range of cells)]

Pivot Tables

When you want fast results, think pivot tables. These are tables used in summarizing data according to the output you are looking of. This is a quick method and tool used in analyzing large datasets. Please note that it does not change the original dataset. It facilitates identifying patterns and trends and comparing metrics across groups or categories to list a few.

To create a pivot table, navigate to the Excel ribbon and click the insert tab and to the left most section of your ribbon click on pivot table and choose where to place it in your workbook.

Time may not always be on your side as an analyst, so pivot tables are a great tool to use when you want to save time from manual calculations listed above, explore your dataset quickly and are also helpful in creating dashboards and reports.

Data Visualization

Stakeholders; these are the people interested in what the data is 'communicating'. You'll be sharing the results with them and most will not be interested in seeing the process it took you to get to the results.
This is why dashboards are essential. While there are tools specifically developed for data visualization, Excel can still manage to handle visualization tasks.

You are able to create charts such as Bar Charts, Column Charts, Scatter Plots, Pie charts, Maps and many more depending on what you need to visualize.

Conclusion

The Data Analytics journey can feel overwhelming in the beginning, You are not alone!. Choosing which tool to start your learning journey can be confusing and the persistent need to learn everything at once will leave you stressed and feeling inept. I've been there!

Pick one tool to learn at a time. It's not about the quantity of knowledge and skill but the quality. Take your time in grasping the concepts, understanding how to question the data, understanding why inconsistencies exist and how to handle them and what different variables represent.

I chose to learn Excel first, and the experience strengthened both my technical and analytical skills. I still have a long way to go in terms of tools I intend to add to my toolkit, but I am now more confident in my ability to work with data.