DEV Community: Samuel Mwaura Ndungu

Power BI Data Modelling, Relationships and Joins: A Practical Guide to Building Effective BI Models

Samuel Mwaura Ndungu — Sun, 27 Sep 2026 21:18:17 +0000

Introduction

Businesses and organisations collect large volumes of data in their day to day operations. Many a times, these data in itself does not answer management questions or rather help in decision making. It is therefore important for the data to be analyzed.
The data collected is analysed, cleaned, structured, connected and thereafter analysis made from the data.
Powerbi is a microsoft office software solutions that allows data analyst to transform data into interactive dashboard and reports and behind the interactive dashboard is a well thought out data model that determine how tables interact.
Consider an institution like an hospital that stores patient data, doctors data, sickness data, visits data, ward data, lab data, pharmacist data etc. Data from various departments will be stored in tables/spreadsheets. It therefore becomes imperative for one to align/model the tables in a way they can interact with each other so as to answer business questions in a seamless manner. For this to be possible, one would need to understand data modeling, relationships and joins.
Data Modeling is the Process of organising your table and defining relationship between your tables so that tables can work together effectively within Powerbi. A well thought out model helps Powerbi understand how the tables are related.
There are two distinct concept when building up a model. These are;

Relationships - connects tables in Powerbi
Joins - Is performed in power query during the data preparation stage where columns from two different tables can be combined to a query. This article will delve into principles behind Powerbi modeling, discuss concepts such as fact table, dimension table, star schemas, snowflake schemas, cardinality, Primary Key and Foreign Key among others. I will use a practical example of Hospital records in this article so that I can be able to demonstrate the concepts clearly.

Understanding Data Modelling in Power Bi

What is Data Modelling
Data Modelling is the process of organising your table and defining relationship between your tables so that you may use Power Bi to summarise/use your data correctly.
A Power Bi Model will contain several tables containing different aspects of business records. For a example, an institution like a hospital may have records of patients in one table, doctors records in another table, another table containing sickness records, another table containing Lab records, another with Pharmacy records, another with Finance records etc. Data modelling will thus eatblish a relation how this records/tables will interact to form meaningful data presentation.
A Power Bi model will likely to contain a fact table connected to several dimensional tables.
Its important therefore to clearly understand the differences between a fact table and a dimensional table.
Facts table - is the table that holds specific events/transactions that you are looking forward to making analysis. It is characterised with the following features.

It contains numerical records or events
Holds data metrics
Holds data in quantities
Helps to get a grain about the data. Grain refers to a description of what each record in a table consists of.
Contains primary keys and foreign keys

Dimension table - A table that gives a description of specific events held in the fact table. It is characterised with the following features;

They have descriptive contents
Mainly you will see texts that describe events
Every dimensional table has a primary key

The model will basically look as follows;

Above is an example of a star schema.

The model may also be presented in the following manner as a snowflake schema.

The main difference is that a star schema has one fact table surrounded by dimensional tables while snow flake schema is like a star schema, however, the dimensional tables have been broken down to smaller tables.

The fact table and dimensional table will be developed from the flat table. A flat table is one table containing all information and you will find a lot of duplication.

The advantages of a star schema are;

Easy to understand
Simple DAX calculations
Less data repetition
Easy reporting
Easy to maintain

The Disadvantages are;

It may necessitate one to have many tables
It requires relationship as wrong keys or table may result in misleading results
Some repetition may still remain

Star schema is frequently applicable in the following cases;

When the data contains clear business figures and descriptive values
Where users need to evaluate the same figures from different scenarios.

The advantage of Snow Flake Schema is as follows

It reduces redundancies
Shared information can be stores at a glance
Useful for complex structures

The disadvantages are;

Potentially more complex DAX calculations
There may be need to have many relationships to manage
May make the model complex rather than simplyfying it

Snow flake schema is applicable in the following scenarios;

Complex or highly structured dimensions
Business with high level of hierachy
When you have different entities that need to be handled independently
Where reducing duplication is very important.

NB - Grain referes to level of information represented by each row in a fact table. It will answer the question what does each row in the fact table represent.

Relatiosnhip in Power Bi

Relationship refers to relationship between two tables therefore making it possible for Power Bi to analyse two tables without having to combine them. It is very important especially where data are stored in fact table and several dimensional table.
The relatiosnhip is established by matching the foreign key in the facts table to the primary key in the dimensional table.
A primary key is a column that uniquely identifies each record in a table while a foreign key is a column in another table that refers to a primary key in another table usually in a dimensional table. Therefore we establish relationships by matching primary key on the dimensional table to the foreign key in the facts table.

Relationship cardinality

Refers to how data in one table relates to data in another based on the numerical count of matching rows.
The types of cardinality are;

Relationship cardinaility
Column cardinality

Relationship cardinality

These shows how tables are connected.

In Power Bi, the main relationship cardinality are;

One to Many cardinality - one row matching to many rows in another table. This is the most common type of cardinality and most applicable in a star schema. Mainly used when one record in a dimensional table is associated with many records in the fact table. For example one doctor in dim table can attend to many patients in the fact table.
Many to One cardinality - Many rows in a table matching one unique row in another table.
A good example in our case is when many visits can occur in one ward.
One to One - A row in a table that can only match/link to one row in another table.
For example Patient dim to Patient details dim. One patient has one patient records and one patient records belongs to one patient and therefore the relationship is one to one. It is applicable when each record in one table corresponds exactly to another table.
Many to many - Multiple rows that can link into multiple rows in another table. This is when many items in one table can be associated with other many items in another table. For example one patient can have various diagnosis and at the same time various one diagnosis can relate to many patients.

Column cardinality

Show the uniqueness of data inside a single column.

Filtering

This determines how filtering travels between two relatable tables.

Joins

Joins allows one to combine two tables using matching columns. In the current case of hospital records, joins allows us to join patients visits records with doctors dim, ward dim etc

The type of joins are;
Types of Joins

Inner Join - Used when you want to retain records that match (keeps only the records that match on both tables)
Left outer join - Used when used want to retain/keep all rows from the left cable (first table) and matching rows from the second table
Right outer join - Used when you want to keep all the rows from the second table and the matching rows from the first table.
Full outer join - used when you want to keep all the rows from all the tables whether they match or not
Left Anti join - used when you want to keep only the rows in the first table that have no match in the second table
Right Anti join - used when you want to keep only the rows from the second table that have no match in the first table.

Conclusion

Behind every dash board sits a well outlined data model with well structured fact and dimensional table whose relationship have been clearly set. The primary keys and foreign keys relate well across the table. At the centre we have patient visits facts table surrounded by dimensional tables such as Doctors_dim, ward_dim, department_dim, Procedure_dim, Diagnsosis_dim and thus forming a star schema. Having a clear understanding of fact table, dimensional table, primary keys, foreign keys, grain, relationship, cardinality, filter directions and joins helps one to develop a good model.

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

Samuel Mwaura Ndungu — Tue, 01 Sep 2026 13:06:44 +0000

What is Excel

Excel uses columns and rows to store data. The data is entered in cells by way of texts, numbers or formulas. Excel allows one to store data, correct/clean data and also transform the same data to useful information and present the same by way of charts and graphs.

Overview of Excel

Excel is arranged around a ribbon. The ribbon contains toolbar section which is found at the top of the window. The ribbon contains the following tabs where each tab has further array of commands;

Home
Insert
Page Layout
Formulas
Data
Review
View

Excel Navigation

Now lets understand how to move around excel.
Excel is structured as a workbook. It is the entire excel file. In the workbook, you will find worksheet. The worksheet is a single sheet within the workbook take it as a page within a book. The sheets are accessible at the bottom of excel. In each sheet, the data is arranged in columns and rows. The columns are named alphabetically from A,B, C, D, and so on. The rows are titled by numbers from 1,2,3 and so on. Where the column and row meet, they form a small box which we refer to as the cell, and therefore each cell will have an address of its column and the row position.
The cell address will be displayed just below the ribbon and next to the cell address you will find the formula bar.

Entering data and formatting data

To work on the excel work sheet, one simply needs to click on a particular cell and input data. Data can be numbers or texts. Excel is able to clearly understand the type of data being given. At a glance, you will notice that texts are usually aligned on the left while numbers are aligned on the right.
Formatting data refers to altering the way data appears to make it easier to understand and analyse. Formatting of data is usually done from the Home tab on the ribbon section.
As a general rule, its very important to enter data correctly and format the same appropriately because it will ease excel interprets information and analyse.

Data Cleaning

Once the data is entered and formatted, the next important step is to clean your data before you can process and analyse it. Just as the name suggests, data cleaning is correcting anomalies in your data so that your records are consistent and accurate. Some of the functions to clean data are;

Identifying duplicates and deleting them
Identifying blank cells and providing them with relevant values
Trim data to identify dashes
Replace (Ctrl+H)
Upper and Lower functions to standardise tests
Check length of texts using (LEN). Its useful for spotting inconsistencies.
Sort
Filter
Capitalise first letter of your data using (PROPER) function
Join texts from different cells using (CONCAT) function
Extract characters from the start of a cell using (LEFT) function
Extract characters from end of a cell using (RIGHT) function

Conclusion

Based on above knowledge, I am able to manoeuvre through excel, input data and check correctness of my data. This will come in handy when am working on my data to come up with accurate presentation of the data.
For those interested in learning more about excel, the following youtube video provides additional resource.
https://youtu.be/0tdlR1rBwkM?list=PLmHVyfmcRKyx1KSoobwukzf1Nf-Y97Rw0

My First GitHub Project: From a Local Folder To GitHub Using Git and SSH

Samuel Mwaura Ndungu — Sun, 23 Aug 2026 06:59:23 +0000

Introduction

Data is captured in almost every aspects of our lives and it is recorded somewhere. It is important to analyse this data and transform the same to useful information which will be used for decision making. For this reason, I have realized it is important to have the right knowledge and tools to transform data into useful information and also present the same in a manner decision makers can understand.
This article will explain step by step my first git hub project by creating a local folder, transferring the same to Github and sharing the same. The tools i will employ in my work include Git, Gitbash/template, Github among others.
Git helps me track changes made in projects while Github helps one to share the project online.

Understanding Git

Its very important to understand what is git and its importance in coming up with my project.
Git will be able to help me track changes i make in my process of coming up with the project. It will clearly show me what i did and at what particular stage of my processes. This will be able to help me correct or make better the various version i have in my project. It keeps a history of all the changes i will have made in the project.

Understanding Github

Just as the name suggest, Git and Github seems to be relates but they entirely do different functions in my project. Github is an online platform that allows one to store projects and share the same online. It allows one to have a public option and a private option when it comes to storing and sharing your project.
For example; when I am working on my project in my computer, I will be able to push it to my Github account and from there, I can be able to work on it and also share with others.

1. Creating a Local Folder

Here I will clearly explain the tools I will employ to create and access my local project folder.
Below are some important codes I will use to create and access folders;

mkdir This will make directory
cd This means change directory
cd .. This takes you back to previous directory
ls This lists down whats in the directory
rm This deletes document in the directory
rm -r This deletes the folder in the directory
touch This creates a document

In my computer, i will create a local folder using the command mkdir my-first-github-project
NB: We are using hyphen when naming our folder to avoid spaces in between names and make the item easier to read and also follows common project naming convention. Though even without the hyphens the folder will still work.

2. Creating Folder and a README Document in my Local Folder

The next step is to create a folder with the title date and also create a README document.
Why is it important to create a data file seperately from a README document?
It makes your project organized. Instead of puting everything in one folder, its important to seperate things according to their purpose.
The Data file is used to store the actual data files while README document is a well detailed explanation and manual instruction of your project.

You will use the command mkdir data to create a new folder called data.
Use command ls to list whats is inside my-first-github-project.
Since you have created a folder by the name data, you will only see the folder when you list.
Use the command touch README.md to create a REAMDE document.
Then use command ls to list whats inside my-first-github-project folder. Now you will be able to see that you have the folder by the name data and a README document.
NB. Its important to name your README.md document with capital letters. This is a widely acceptable and conventional way of naming a README file. It makes the file easily recognizable and Github recognizes it automatically.
The extension md in your REAMDE.md file means that it uses Markdown language.

3. Adding content in your README.md file

Its important to clearly explain or give instruction for your project. This will be clearly documented in your README.md file.
This can be done using VS Code or Terminal/Gitbash.

Using bash or terminal, access the folder cd My-first-github-project To write content in your README.md file, we shall use the command echo "My-first-Github-Project" >README.md To check what you wrote, use the command cat README.md

We can also use Nano to simplify our writing task. We shall run the command nano README.md. A text editor will appear wherein we shall type our content.
To Save our content, we shall press Control + O to save, Press Enter Key to confirm the file, then press Control + X to exit.
NB: To check what you have written, use the command cat README.md

4. Create a Git Repository

We need to convert the ordinary file we are working in to a Git Project. We shall use the coomand git init. This creates a hidden .Git directory which contains information that our Git uses to track the developments.
After running git init command, our folder becomes a Git Repository.

5. Copying the Document With the Data to our Data folder

Since we need to work on some data, we shall transfer the data to our Data Folder.
Assume we have downloaded an excel from internet and thus is lying in our local machine download folder, I shall copy the document from the Downloads file and copy to my data file.
Alternatively, I can use the following command to copy the document to my data folder.
cp ~/Downloads/Kenyan-Health-Records-Analysis-File.xlx data/. Assume that the file was an excel file having the name Kenyan-Health-Records-Analysis.

6. Check the Status of the File

It is important to check the status of the file. You will run Git status to check the status of the file.
You will be able to see that Git has found the project file but is not tracking them.

7. Allow Git to Track the File

The next action is to let Git to track the file. This is possible by running the command Git add .
This means that Git will start tracking everything in the folder.

8. Commit Your Work

This creates a saved version of your project. Its a very important step of the project as it saves the version of the project and can allow one to track changes made in the project.
The command used to commit your work is git commit -m "Version 1 Add Kenyan Health records"
NB The wordings Version 1 Add Kenyan Health Records is tailored to your own understanding to help you know what exactly u saved at that particular stage. You can message anything provided it will help you understand what you did at that particular stage.

9. Connecting to Github

Now that we have saved our project, we now connect to Github.
Its important to run comman Git status to confirm everything is clean. You will get a message nothing to commit, working tree clean
After confirming everything is okay

Log in to Your Github Account
Create a repository
Give the repository a name

10. Creating an SSH key

An SSH key is important as it gives your computer as safer way to connect to your Github account without the need to inputting password everytime when you need to work on your project.
I can check in my computer whether there it has an existing SSH Key. I will use the following command to check ls -al ~/.ssh. If you see in your computer file such as

id_ed25519
id_ed25519.pub It means your computer already has a key. But in cases where you dont have a key, You will be required to create one. I will describe in details what is required when generating an SSH Key.

Generating an SSH Key Open your Git bash/terminal and key in the following command ssh-keygen -t ed25519 -C "your-email@example.com" Put your email and replace with the wordings your-email@example.com.
Enter the File in which you want to save the key
Copy your public Key
Add the public key in Github
Under Key, paste the Public key you just created
Add the SSH Key
Test the connection

11. Connecting Your Local Project to Github

After successfully creating my Github repository and setting up SSH key, I will now tey connect my local Project to Github.
I will run the following command git remote add origin git@github.com:YOUR-USERNAME/kenya-hospital-records-analysis.git
NB: Replace your **YOUR-USERNAME with your user name appearing in Github.
Use the command 'git remote -v' to check the connection.

Once you confirm the connection was successful, I will now push my project to Github.
Use the command git push -u origin main.
Afterwards, refresh the page in github and you will be able to see your project.

Conclusion

The entire process has helped me understand the tools used and commands to adopt in order to push my project from a local folder to Github using SSH. The process might be lengthy at first but when you clearly understand the reason and importance of the codes being used, the process becomes clear well organized. There are sections your Gitbash/Terminal will give you errors and when you successfully correct the errors, you learn why the system behaved in such a manner and it becomes an additional learning point.