DEV Community: Victor Muyanzi

Data Modelling, Relationships and Joins

Victor Muyanzi — Tue, 29 Sep 2026 13:04:14 +0000

You come across a very neatly designed Power BI report and immediately get excited at how easy it is to understand the data presented. What you may not see, however, are the various relationships that exist in the background, making it possible to present data clearly. This underscores the importance of establishing accurate relationships, especially when working with multiple tables. It is therefore important to understand data modelling, relationships and joins in order to present data accurately.

Data Modelling in Power BI

Data modelling in Power BI refers to the process of organising raw data, which is in the form of tables, to ensure that it is well structured and can be easily used for reporting. A well-designed data model is key to presenting data well and, therefore, to supporting insight generation. It ensures that reports are generated faster and more accurately, and that new data can be added easily without affecting reports that have already been developed.

Data Modelling Approaches

In modelling, there are two main types of tables used to present data: the fact table and the dimension table. A fact table stores quantitative information about a business event, while a dimension table provides descriptive or qualitative information about the numerical data.

Fig 1.0. An illustration of a fact table containing the details of transactions from patient visits to a hospital

Fig 2.0. An illustration of dimension tables (circled in red) showing details of the transactions in the fact table (facts_visit).

Various data modelling approaches can be used when working with Power BI. These are the flat table, the star schema and the snowflake schema.
Flat Table Schema. The flat table schema is a single table that contains both the transactions and the descriptive information. It contains all the columns, so no relationships are required. The main advantage of a flat table schema is that there is no relationship management, since all the data is within the same table. Secondly, it can load faster because it is the only table. However, a flat table performs poorly as the number of rows grows, since data loads more slowly. Another disadvantage is that writing DAX expressions can become difficult and error-prone. Given these considerations, the flat table schema is appropriate when working with smaller datasets.

Fig 3.0. Flat table showing all the details related to hospital visits.

Star Schema. A star schema consists of one fact table surrounded by related dimension tables. The fact table has its own primary key and various foreign keys, which are the primary keys in the dimension tables; it therefore has a one-to-many relationship with each of them. The fact table contains the details of transactions, while the dimension tables describe these transactions in detail.

Fig 4.0. An illustration showing a star schema with a central fact table named Fact Visits and dimension tables having a one-to-many relationship with it.

In this case, the facts_visit table contains the details of transactions related to hospital visits, while dimension tables such as dim_diagnosis, dim_wards, dim_patients, dim_doctors and dim_procedures provide further descriptions of the details relating to the hospital visit transactions.

Snowflake Schema: A snowflake schema is a dimensional model made up of dimension tables and related sub-tables, which creates a hierarchical structure. One advantage of this design is that it reduces data redundancy and improves storage efficiency. It also provides a hierarchical representation that shows different levels of granularity. The primary disadvantage of a snowflake schema is its complexity, which can lead to slower query performance.

Fig 5.0. An illustration showing a snowflake schema, in which the fact table is described by dimension tables, which are further described by sub-dimension tables in a hierarchy.

Relationships in Power BI

Creating relationships in Power BI is critical because it ensures that data is analysed accurately and that proper insights are generated. A relationship in Power BI refers to the connection between two tables using a shared column. Creating relationships between tables links them, which makes it possible to pull combined data from various categories across the tables. Relationships also reduce data redundancy, as information in one table does not have to be repeated in the other, thus saving storage space.
Power BI supports the following relationship cardinalities:
i. One-to-many (1: )
ii. One-to-one (1:1)
iii. Many-to-many (: *)

One-to-Many (1: *) relationship exists between a dimension table and a fact table, where one row in the dimension table is matched to many rows in the fact table. A practical example is a customer ID in the customer table that matches many transactions in a sales table. This relationship should be used only where the column in the fact table is a foreign key that corresponds to the primary key in the dimension table.

Fig 6.0: This illustration shows the one-to-many relationship, where dim_diagnosis and dim_patients each have a primary key that matches several records in the facts_visit table, in which it appears as a foreign key.

One-to-One (1:1) relationship occurs when two tables have matching unique values, such as a customer ID, which can be assigned to only one customer and appears only once in each table. For example, two tables, customer location and customer preference, could share the same unique values (the customer ID), which serve as the key when linking the two tables.

Fig 7.0: One to many relationship showing one record that appears once in the fact table and many times in the dimension table.

_*Many-to-Many (: ) *_relationship occurs when many records in one table match several records in another table. A many-to-many relationship does not require a primary key, as records can appear more than once in both tables. For example, a customer’s region may appear several times in both the customer table and the sales transaction table.

Fig 8.0. An illustration showing a many-to-many relationship where a record appears more than once in both tables.

Filter Direction

Filtering is another important practice when working with data models in Power BI. It is critical to ensure that the filter direction is well defined when developing a data model. Filter direction defines how filters flow between related tables. There are two forms of filter direction that can be used in a data model:
i. Single-direction filtering
ii. Both-direction filtering

Single-direction filtering means that the filter flows in only one direction: from the dimension table to the fact table. For example, if a product category such as beverages is filtered in the dimension table, the matching records are retrieved from the fact table using the product key.

_Both-direction filterin_g means that filters flow both ways, so filtering either table affects the other. This is most commonly needed in many-to-many relationships between tables. Because of this, both-direction filtering should be used carefully, as it can add complexity and affect the performance of the report being developed.

Joins in Power Query

Joins in Power Query combine two tables using matching values. In Power BI, joins are usually done using the Merge function in Power Query, where one can select Merge Queries or Merge Queries as New. The following types of joins can be performed in Power Query:
Left Outer Join keeps all the rows from the left (first) table and includes the matching rows from the right (second) table. For example, a fact table such as facts_visit, which contains the details of hospital visits and transactions, can be matched with a dimension table such as the doctor table. The left outer join retains all the rows of the fact table and only adds the matching details from the doctor table to show which doctor treated a particular patient.
Right Outer Join keeps all the rows from the second (right) table, together with the matching rows from the first (left) table.
Full Outer Join joins two tables and returns all the rows from both, whether they match or not.
Inner Join returns only the rows that match in both tables.
Left anti join returns all the records from the first (left) table that have no matching record in the second (right) table.
Right anti join returns only the records from the second (right) table that have no match in the first (left) table.

Power Query Joins vs Power BI Relationships

Joins in Power Query can easily be confused with relationships in Power BI, which can make data analysis difficult. A Power Query join physically combines two tables into one, whereas a relationship only creates a connection between two tables using a shared column that serves as the primary key in the dimension table and the foreign key in the fact table. These two distinct processes happen during the data modelling stage, when data has been loaded and cleaned and is now being prepared for analysis to generate insights that support business decisions. Merge is used to join tables physically into one table. However, merging should be done carefully to avoid excessive merging, which can increase the complexity of the data and lead to data redundancy, poor performance and reduced granularity. When working with a Power BI model, it is important to keep the fact and dimension tables separate, because the fact table provides the details of a transaction while the dimension table provides descriptive information about it, thereby improving understanding of the data.

Conclusion

Power BI plays an essential role in data analysis and reporting, and at the centre of it all are the relationships between tables. It is advisable to use the star schema when building relationships in Power BI, as it creates clear and efficient relationships between tables. The star schema ensures that there is a central fact table containing the details of the transactions. Surrounding it are dimension tables, which provide qualitative information about the transactions. Relationships should then be created between the dimension tables and the fact table, whereby the primary keys of the dimension tables are linked to the foreign keys in the fact table, as this improves model readability and makes it easier to create reports. These should be one-to-many relationships guided by a single-direction filter approach.

Getting Started with Excel for Data Analytics: From Basics to Data Cleaning

Victor Muyanzi — Wed, 02 Sep 2026 16:41:31 +0000

Microsoft Excel has undeniably played a major career in the life of every data analyst or a data scientist. It is without a doubt one of the foundational or fundamental tools that every beginner in data analytics has to at least master before advancing to more tools. I have worked with excel before in my career solving various tasks, hence I have some familiarity with the tool. Starting a data science course demanded a repeat of the foundational concepts in excel, which proved to be critical. I have been able to develop a different perspective and skills on how excel is important to data analysis. In this module, Getting Started with Excel for Data Analytics: From Basics to Data Cleaning, I have been able to understand excel use cases, master basics in excel, and the steps it takes to have a clean data.

Excel Definition and Use Cases

In simpler terms, Excel is basically a statistical software package that allows users to organize, format as well as calculate data with the use of grids and columns. In most institutions, Excel is one of the most commonly used tools to handle data hence it has a number of use cases. Some of these cases are as follows:
i. Recording data: Recording data is the most common function that most people depend on excel, otherwise called data entry.
ii. Data Analysis: Excel helps to ensure that data can be transformed into meaningful insights.

iii. Reporting Insights from analyzed data: Meaningful insights from the data can be reported for ease of understanding for the stakeholders.
iv. Business Intelligence: Insights generated from excel data analysis can be used to informed the business or organization on the correct course of action.
v. Project management: Excel plays a vital role in handling projects through supporting the users to keep track of project milestones as well as timelines.

Excel Basics

Starting up with excel, exposes the users to a blank page with so many tools that could easily be confusing. To start with, excel has an interface called the ribbon located at the top, with commands which help to navigate between different functionalities in excel including File, Home, Insert, Draw, Page Layout, Formulas, Data, Review, View, Help, Power Pivot etc.

Quick Access Tool Bar

The next item is the Quick Access Tool Bar which is located at the top left corner of the excel worksheet. It contains commands such as Save, Undo, and Redo as shown in the image below:

Formula Bar

In excel, there are times when a user will be required to use formula. In this case, the function or the formula used will appear at the formula bar for the selected cells.

Name Box

While working with excel, the name box is used to show the selected cell. In this diagram shown below, the selected active cell is indicated by the name box as A1.

Columns

The columns represent a vertical arrangement of cells in an excel sheet. In the diagram below the columns is denoted by the alphabet letters, such as Column A, Column B, Column C etc.

Rows

The rows refer to the horizontal arrangement of cells in an excel sheet. Rows in excel are normally identified using the numbers, 1, 2, 3, etc. as shown in the diagram below.

Cells

The cells refer to the intersection between a row and a column within an excel sheet. In the sheet below, the selected cell is C3, which is an intersection of Colum C and Row 3.

Worksheet Tabs

Worksheet tabs are the tabs located at the bottom of the sheet, that allows users to navigate from one sheet to another within the workbook. The addition butting, allows the users to add new sheets into the workbook. One can as well rename, delete the sheet, move or copy by right clicking on it.

Entering and Editing Data in Excel

One of the roles that excel supports in organizations and institutions is data entry. Data can be entered in different formats in excel and once it has been entered, it can also be edited. The main formats are as follows:
i. Texts
ii. Numbers
iii. Dates
iv. Formulas

Formatting Data in Excel

Once the data has been entered, the next important step is to ensure that the date makes sense. One of the best approaches to do so is to format the data in the correct format. In this case, data can be formatted in various ways using excel. Some of the ways are as follows:

Text formatting

In text formatting, data can be customized into various formats using functions such as bold, italics, underline, increasing font size, selecting right font, and it can be accessed in the image shown below:

Cell Alignment

This approach refers to how cells are positions in a cell and it could be left, center, or right aligned. Accessing the alignment option in excel is as follows:

Number Formatting

Numbers can also be formatted in various ways to represent their true value whether it could be currency, percentage, data etc. as follows:

Data Cleaning with Excel

After data has been entered into excel, it is important to clean it so that the final output can have a proper meaning which can be used to generate insights for decision making. Cleaning data in excel goes through a number of processes for it to be ready for use.

Data Sorting

One of the steps involved in data cleaning is data sorting which involves arranging data in a specific order, either ascending (A to Z) or descending (Z to A) once the required row has been selected or the enter sheet.

Once the column has been selected, right click then select sort to pick the appropriate option.

Opening custom sort also opens up a new set of options that could allow the user to further sort their data in a precise manner and this depends on the information that the sheet contains.

For dates, the information can be sorted in the form of oldest to newest or vice versa.

Filtering

This step involved selecting only data that meets a specific criterion.

Data Validation

Data Validation plays an essential role when it comes to ensuring data on the workbook is clean and this is mainly achieved through restricting only what users can be able input in the cell by providing specific options.

Text Functions used for Cleaning Data

Various text functions can be used to clean data by the helping to standardize the text formats and this is as follows:
i. UPPER () which is used to convert texts into upper case.
ii. LOWER () allows for texts to be converted into lower function.
iii. PROPER () ensures that the first letter of each word is capitalized.
iv. TRIM () removes any extra space that could be within a cell.
v. LEFT () removes the characters that appears towards the extreme left.
vi. RIGHT () ensures that the right most characters are removed
vii. MID () returns the middle character
viii. LEN () provides the length of the text string provided.
ix. FIND () helps to determine the position of substring and in most cases, it is case sensitive.
x. SUBSTITUTE () Replaces text within a string

Conclusion

It is without a doubt that excel has a wide range of functions for any person that is dealing with data. Understanding excel basics is important foundation knowledge as it helps to ensure that one is able to integrate various functionalities to achieve a desired end goal. Moreover, getting to know how to clean data using the functionalities stated above is essential. These functionalities will ensure that you are working with clean data therefore, the output will be desirable and that it will be used to make important business decisions.

Understanding the Git Workflow: Working Directory, Staging, Commit and Push

Victor Muyanzi — Fri, 28 Aug 2026 09:34:57 +0000

Git or GitHub was just another buzzword that for so long I used to associate it with the so called "tech bros and siz". As a result, I never got quite interested in understanding these concepts. However, my passion towards learning data science and analytics, has brought it right before me, and I have had to throw myself in it. In the last two weeks, I began my classes, on Data Science, Analytics and AI, at Lux Dev HQ. Here, I have been exposed to a number of tools to be used in the program, with Git, and GitHub being one of the most important ones. Over the two weeks, the onboarding sessions have helped me understand git workflow including creating a working directory, staging, commit and push. Here is a step by step process through which I managed to set up my git account.

Understanding Git and Git Hub

First things first, and this case, we had to understand what git and git hub really is. From the lesson, Git is basically a version control systems installed on the computer that tracks changes made within a project. On the other hand, git hub refers to an online platform allowing users to store their git repositories and this could be easily accessed through [https://github.com/]. Apart from just storing repositories, GitHub allows for ease of project sharing, collaboration, and code review.

Creating a GitHub Account

First things first, I had to create a GitHub account online, using my email address, password and then created a user name for the GitHub profile. Once the online GitHub account was ready, the next step was to install git on the personal computer. However, we had to first confirm if git was already installed on the computer. In this case, the first step was to open the PowerShell, since I was using Windows OS (Operating System) then run git --version. In the event Git hub is installed, the following command pops up.

git version 2.55.0.windows.5
PS
C:\\WINDOWS\\System32>

Working Directory

One of the critical aspects to understand in git is a working directory. The working directory represents a folder where the terminal is currently operating inside. Basically, the working directory is considered as the workspace where, a user can be able to create, and edit files. Ideally, we can say that this is where most of the real work happens such as opening a file and making changes using the code editors.

Staging

Moving on from the working directory is the staging area. We could also call it the preparation zone as well as the midpoint between the workspace and the permanent storage. In this area, what happens is preparing and organizing changes before committing them eventually. In other words, we could call it a draft of the next commit. Moreover, files in the staging area are ready to be committed however, they haven't been saved permanently.

Commit

Now that we have already mentioned commit, lets get into what it actually means in git. A basic definition of the term is that, commit is a way of Git to save the current state of a project. This could be interpreted as the saving point of any project to capture the progress at that specific point. Once a commit has been created, git permanently stores it in the repository. Therefore, it is easy to determine how a project looked at a particular point. Moreover, what has been prepared during the staging area, is now permanently saved.

Push

Once a commit has been done, what follows is transfer it to the remote repository. This action is only possible, through the push command on GitHub. This happens with the following command

git add.
git commit -m "Your commit message"
git push origin main