DEV Community: Naomi Kwamboka Onderi

Power BI Data Modelling, Relationships & Joins

Naomi Kwamboka Onderi — Sun, 13 Sep 2026 15:00:32 +0000

Introduction

This week we got started on learning Power BI its uses and how its applied in data analysis. When working with Power BI, the way data is organized and connected has a major impact on how easily reports can be created, how calculations work, and how well a Power BI solution performs.
Data modelling in Power BI involves organizing data into tables and defining relationships between those tables so that the data can be analyzed effectively. A well-designed data model makes reports easier to understand, DAX calculations easier to write, and Power BI solutions easier to maintain and scale.

Data Modelling in Power BI

Data modelling is the process of organizing data into structured tables and establishing relationships between those tables so that Power BI can understand how the data is connected.
Instead of keeping all this information in one huge table, the data can be separated into different tables and connected using common fields.
A well-designed data model is important because it can improve:

Model readability
Maintainability
Filter propagation
Overall reporting experience
DAX calculations
Data organization
Scalability
Report performance

There are several ways data can be structured. The three common modelling approaches are flat tables, star schemas, and snowflake schemas.

Flat Tables

A flat table stores all the required information in a single table. For simple datasets, a flat table may be sufficient, but it is generally less suitable for large and complex business intelligence solutions.

Advantages of a Flat Table

A flat table is relatively easy to understand because there are no relationships between multiple tables.
It can be appropriate when there are few repeated values, a quick analysis is required, the analysis is simple and dataset is small.

Disadvantages of a Flat Table

The major problem is data redundancy which can result in repeated data, larger datasets, more difficult maintenance, potential inconsistencies and less flexible modelling.

Star Schema

A star schema separates the data into a main fact table and several surrounding tables. The structure resembles a star, which is where the name comes from. The main fact table contains business events or transactions, while the dimension tables contain descriptive information about those events.
It is particularly useful for business intelligence and reporting solutions where users need to analyze business events across different dimensions.
For example, a company could use a star schema to analyze:

Sales by product
Sales by product category
Sales by customer
Sales by location
Sales by month

Advantages of a Star Schema:

It is generally well suited to Power BI's analytical model.
It is relatively simple to understand.
It reduces unnecessary data duplication.
It supports efficient reporting and analysis.
It makes DAX calculations easier to manage.
It provides a clear structure for filter propagation.
It is easier to maintain and extend. ## Disadvantages of the Star Schema Incorrect relationships or poorly designed keys can result in incorrect calculations or unexpected filtering it also requires a proper understanding of relationships between tables for everything to be correct.

Snowflake Schema

A snowflake schema is similar to a star schema, but dimension tables are further divided into additional related tables.
Instead of storing all product-related information in one dimension table, information can be separated into multiple related tables.
It may be appropriate when the underlying data contains complex hierarchical structures or when dimensions are large and naturally divided into related entities. However, unless it's necessary it's advised to keep the dimensions simpler to make the Power BI model easier to work with.

Advantages of a Snowflake Schema

Represent complex hierarchies.
Organize highly structured source data.
Useful when dimensions contain multiple levels.
Reduce repeated information within dimensions. ##Disadvantages of a Snowflake Schema It can be more difficult to navigate than a straightforward star schema because of the extra tables, extra relationships and more filter paths. This makes it more complex. It has a steeper learning curve. The report development can be complicated.

Comparing the Three Approaches

The key difference is how the data is organized. A flat table keeps everything together, a star schema separates facts from dimensions, while a snowflake schema takes normalization further by splitting dimensions into additional related tables.
The star schema provides a practical balance between performance, simplicity, and maintainability.
Power BI models consists of tables, keys, relationships, and rules that determine how information flows between those tables.

Fact Tables and Dimension Tables

A fact table stores measurable business events or transactions. It answers questions such as how many?, how much?and how many transactions.
Dimension tables contain descriptive information used to analyze the facts. It answers questions such as who?, when? and where?

Grain or Granularity

The grain describes exactly what one row in the fact table represents. Clearly defining the grain is important because it prevents confusion and incorrect calculations. For example For example, a sales fact table should not have one row representing a whole order in some cases and one row representing an individual product in other cases.

Relationships in Power BI

A relationship connects two tables using a common column. Relationships are necessary because business data is often distributed across multiple tables. For example it shows how a customer relates to a product or different products.

Relationship Cardinality

Cardinality describes how many records in one table can be related to records in another table. Power BI supports several relationship types such as:

One-to-Many (1:*)- This is normally the preferred relationship between a dimension and a fact table. For example, A customer can make many sales, but each sales record belongs to one customer.
One-to-One (1:1)- A one-to-one relationship means that each record in one table corresponds to only one record in the other table. For example, Every employee has their own unique employee details such as their email or ID.
Many-to-Many (:)- A many-to-many relationship occurs when multiple records in one table can relate to multiple records in another table for example, students can enroll in many courses, and each course can have many students. Many-to-many relationships can be useful, but they should be handled carefully because they can create ambiguous filtering and unexpected results.

Primary Keys and Foreign Keys

A primary key uniquely identifies a record in a table, for example employee ID, Student ID.
A foreign key is a column used to reference a record in another table for example, a customer ID column can be found in different tables since it's possible for customers to make multiple purchases. The relationship works because the dimension contains unique values while the fact table can contain repeated values.

Referential Integrity

Referential integrity means that foreign-key values should correspond to valid records in the related dimension. If the column does not exist in the dimension, the model may produce unexpected results when filtering or calculating data.
Ensuring that keys are clean and valid is therefore an important part of data preparation.

Active and Inactive Relationships

An active relationship is the default relationship used when filters travel between tables. Only one relationship between the same two tables can normally be active at a time. An inactive relationship can be used in specific DAX calculations when required.

Filter Direction

Relationships can determine how filters move between tables.

Single-Direction Filtering- In a typical star schema, filtering usually flows from the dimension to the fact table. This is generally the preferred approach for a star schema because the filter path is clear and predictable.
Bidirectional Filtering- This is when filters flow in both directions. It is recommended to use the single-direction filtering unless there is a specific reason to use bidirectional filtering.

Joins in Power Query

Power Query provides a feature called Merge Queries, which allows tables to be joined during the data transformation stage therefore relationships in the Power BI model are not the same thing as joins in Power Query:

** Left Outer Join**- This keeps all records from the left table and matching records from the right table.
** Right Outer Join**- This keeps all records from the right table and matching records from the left table.
** Full Outer Join**- This keeps all records from both tables. ALL LEFT records + ALL RIGHT records
** Inner Join**- This keeps only records that have a match in both tables. Inner joins are useful when we only want records that exist in both datasets.
** Left Anti Join**- This returns records that exist in the left table but do not have a match in the right table. This can be useful for data quality checks and finding missing relationships.
** Right Anti Join**- This returns records that exist in the right table but do not have a match in the left table.

Power Query Joins vs Power BI Relationships

A Power Query merge combines information from two tables during the data preparation/transformation stage. A Power Query merge can physically bring columns from one table into another table.
A relationship in Power BI is created later in the data model. Instead of physically combining the tables, we keep them separate. This means creating a relationship in Power BI does not physically merge the tables together.
The relationship tells Power BI how filters and calculations should move between the tables.

When Should You Use a Merge Instead of a Relationship?

A merge can be appropriate when information genuinely belongs together or when data transformation requires combining columns. However, excessive merging can create very large and complicated flat tables. For analytical reporting, keeping fact and dimension tables separate is often preferable.

Why Keep Fact and Dimension Tables Separate?

Reduced Redundancy
Easier DAX-Measures can be written against a clear fact table while dimensions provide filtering context.
Better Model Readability- A developer can easily understand which tables contain facts and which contain dimensions.
Easier Reporting
Better Scalability

Conclusion

Data modelling is one of the foundations of building effective Power BI solutions. Creating attractive visualizations is only one part of the reporting process. If the underlying model is poorly designed, calculations can become difficult, filters can behave unexpectedly, and maintaining the report can become increasingly complicated. For a typical business intelligence project, I would recommend a star schema. A well-designed star schema provides a relatively simple model with clear relationships between fact and dimension tables. This can support efficient filtering and analytical queries. And use one-to-many relationships between dimensions and the fact table and single-direction filtering, where filters flow from the dimension to the fact table. I would avoid unnecessary bidirectional relationships because they can introduce ambiguous filter paths and make the model harder to understand. Many-to-many relationships would also only be used when the business requirement genuinely requires them. Where possible, a bridge table can be used to create a clearer model.

Jumia Product Performance Analysis & Pricing Dashboard

Naomi Kwamboka Onderi — Mon, 07 Sep 2026 18:04:57 +0000

Introduction

As part of understanding how Microsoft Excel is used in E-commerce. I analyzed a dataset of products listed on Jumia to understand how product performance from pricing to discount affect sales and customer engagement. The first step was to clean and prepare the raw dataset. Then perform data analysis to create visualizations, and later developed an interactive dashboard that presented the findings.
These analysis aim to provide useful insights that can support better pricing strategies, promotional decisions, and customer engagement for Jumia.

Cleaning the Data

Before cleaning I copied the worksheet to a new sheet for reference purposes or incase something goes wrong or I accidentally delete data.

Missing Values- I checked every column for empty cells. Which were plenty in the ratings column. I wroteNot available in the blanks spaces for accurate output during analysis.
** Duplicates**- Searched and removed duplicates and fixed other inconsistencies.
Numeric transformation-Fixed the review column data as it was stored as negative numbers. Also removed the Ksh and commas in the Current price and old price columns to numeric values. Lastly was removing the out of 5in the ratings column. After cleaning this is what was displayed:

The new calculated columns

At this point I added the new columns which are:

Discount Amount-Discount Amount = Old Price − Current Price.=C2-B2
Rating Category- =IF(G2="","",IF(G2<3,"Poor",IF(G2<=4,"Average",IF(G2<=4.5,"Good","Excellent"))))
Discount Category-=IF(D2="","",IF(D2<20%,"Low Discount",IF(D2<=40%,"Medium Discount","High Discount")))
This means below 20%-Low, 20%-40%=Medium and above 40% is high
Price Category- =IF(B2<1000,"Low Price",IF(B2<=2000,"Medium Price","High Price"))
This means that Below Kes 1000=Low price, Kes 1000-2000= Medium Price and above Kes 3000.

Data Analysis

For the KPIs I used the following formulas

Total Products-=COUNTA('Excel_jumia_dataset'!A2:A113)
Average Current Price-=AVERAGE('Excel_jumia_dataset'!B2:B113)
Average Old Price-=AVERAGE('Excel_jumia_dataset'!C2:C113)
Average Discount-=AVERAGE('Excel_jumia_dataset'!E2:E113)
Average Rating-=AVERAGE('Excel_jumia_dataset'!I2:I113)
Total Reviews-=SUM('Excel_jumia_dataset'!H2:H113)
Most Expensive Product-=XLOOKUP(B9,'Excel_jumia_dataset'!B2:B113,'Excel_jumia_dataset'!A2:A113)
Least Expensive Product-=XLOOKUP(B11,'Excel_jumia_dataset'!B2:B113,'Excel_jumia_dataset'!A2:A113)
This results to:

Dashboard creation process

Putting everything together to create a dashboard I used
KPIs-The screenshot above to be specificthat shows total products, average price, average discount, average rating, and total reviews.
Pivot charts- I created multiple pivot charts to showcase the top 10 products by discount, rating and reviews.
Slicers- To connect all PivotTables/charts for Rating Category, Discount Category, and Price Category, so the whole dashboard can be filtered interactively.

Correlation Analysis

Used CORREL() to test three relationships:
Discount vs Reviews--0.1368, This means that higher discounts don't influence higher customer engagement.
Rating vs Reviews-0.0572, So a product having a high rating doesn't necessarily mean it has lots of reviews.
Current Price vs Rating-0.1080, Expensive products are not clearly rated higher than cheaper products.
Key Insights and Business Recommendations

Prioritize highly reviewed products when evaluating customer engagement because review volume varies substantially across products.
Monitor products with low ratings and identify opportunities to improve product quality, descriptions, or customer experience.
Focus promotional efforts on products where discounts can attract attention without compromising product quality.
Use the dashboard's slicers to compare product performance across different price, discount, and rating categories.
Investigate products with high discounts but poor ratings, as large discounts may not compensate for poor customer satisfaction.

Conclusion

The analysis showed that discounts, ratings, and customer reviews did not have strong relationships in this dataset. In particular, the weak correlation between discount percentage and reviews suggests that offering larger discounts does not necessarily result in higher customer engagement. The dashboard also made it easier to identify highly reviewed products, highly discounted products, and products that may require attention due to low ratings. The interactive dashboard brings these findings together through KPI cards, charts, PivotTables, and slicers, allowing users to explore product performance across different rating, discount, and price categories.
Overall, this project strengthened my understanding of Excel for data cleaning, analysis, visualization, and dashboard development, while also demonstrating how data-driven insights can support better e-commerce business decisions.
You can find the README.md and workbook that contains the clean dataset, pivot charts, pivot tables, Excel formulas, slicers and the dashboard on my GitHub link below:

https://github.com/naomionderi-codes/Jumia-Product-Performance-Dashboard

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

Naomi Kwamboka Onderi — Sat, 29 Aug 2026 19:56:21 +0000

Introduction

Excel is a powerful tool used in data analytics, it is one of the most used tools for working with data. You can use Excel to collect, organize, clean, analyze, visualize, and present data without needing to write code. It is also the easiest way you can learn important analytical concepts while working with an actual spreadsheet.

Definition of Excel and Data Analytics

Excel is a spreadsheet that allows you to store information in rows and columns.
Data analytics refers to the process of examining data to discover useful information, patterns, and insights that can help with decision-making.
For example a company like LC Waikiki has thousands of sales records. The raw data might contain:

Customer names
Sales representatives
Store Locations
Product Names
Sales etc.

Understanding the Excel Interface

Workbook- This is basically an excel file.
Worksheet- This is an individual spreadsheet inside a workbook. The worksheet tabs are found at the bottom.
For example:Sheet1 | Sheet2 | Sheet3
Rows- They run horizontally across the spreadsheet. They are identified by numbers.
Columns- They run vertically. They are identified by letters.
Cell- This is the intersection of a row and a column.
Cell Range- This is a group of cells. A1:C10

Example of a Worksheet

Formatting data

Formatting makes data easier to read. Correct formatting becomes especially important when analyzing dates and numerical values.
You can use the following formatting options:

Date
Currency
Time
Percentage
Number
Fraction
Text

Basic Excel Formulas

SUM adds numbers together. =SUM(F2:F6)
AVERAGE calculates the mean. =AVERAGE(F2:F6)
COUNT counts cells containing numbers. =COUNT(F2:F6)
COUNTA counts cells that are not empty. =COUNTA(B2:B6)
MAX returns the largest value. =MAX(F2:F6)
MIN returns the smallest value. =MIN(F2:F6)
IF function allows Excel to make decisions. =IF(F2>500,"High","Low") This type of logic becomes very useful when analyzing large datasets.

Sorting Data

Sorting allows you to arrange your data according to a particular order. Like Largest → Smallest ,or alphabetically from A-Z,Z-A etc

Filtering Data

Filtering allows you to display only the records that meet specific conditions.

What is Data Cleaning?

Data cleaning is the process of identifying and correcting problems in a dataset so that the data is accurate, consistent, complete, and suitable for analysis and decision-making.
Some of the issues one can encounter in a large data set may include:

Invalid values
Inconsistent capitalization- These can be standardized by using functions like =UPPER(A2) to change characters to uppercase, =LOWER(A2) to change characters to lowercase, =PROPER(A2) to change characters to proper capitalization.
Missing values
Duplicate records- This is when the same record appears more than once.
Spelling mistakes- Find & Replace (ctrl+H) can be used to fix spelling mistakes
Incorrect dates- You should make sure dates are stored consistently and recognized as actual dates rather than text.
Extra spaces- Excel can treat them as different text values. The **TRIM **function helps remove the unnecessary spaces. =TRIM(A2)
Wrong currencies
Numbers stored as text- =VALUE(A2) can be used to fix this.

Before analyzing data, these issues should be addressed.

How to use Find & Replace for Data Cleaning

Find & Replace is one of the simplest and most useful Excel cleaning tools. You can open it using Ctrl + H. It is useful in correcting abbreviations, unwanted characters, spelling errors, inconsistent labels and category names.

Data Validation

Data validation helps control what users can enter into a spreadsheet. This reduces the likelihood of users entering inconsistent values. This is particularly useful when creating spreadsheets that other people will use to enter data.

Conclusion

When you receive a new dataset, carefully go through the entire worksheet before creating charts and calculating averages. Understand and inspect the dataset for irregularities and any inconsistencies. And after fixing the errors review the clean dataset again just incase. Learning Excel for data analytics is a gradual process and you don't need to learn every Excel function before you can start analyzing data. The skills you develop in Excel can be transferred to different areas of data analytics.

How To Upload Your First Project From a Local Folder to GitHub Using Git and SSH.

Naomi Kwamboka Onderi — Sat, 22 Aug 2026 21:51:01 +0000

Introduction

If you want to upload a project from your computer to your GitHub account, it can be done by simply using Git and SSH. This is one of the most important technical skills you can learn as a beginner because you will use this every time you want to create and or update an existing project. For a person using Windows OS, follow these simple steps as illustrated below.

1. Download Git and create a GitHub account.

The GitHub account can be generated easily on the browser by using your email/username and a password. Git can be downloaded from Microsoft Store and installed in your computer.

2. Set up your Identity.

Since it's your first time you are required to configure your name and email after installation.
Run the following commands on Gitbash
git config --global user.name "Your Name" Insert your name
Then run
git config --global user.email "your-email@example.com"Insert your email, the one used to create your GitHub account

3. Check whether you already have an SSH key

Run:
ls -al ~/.ssh
You'll see
id_ed25519 id_ed25519.pub
id_ed25519refers to the private key and id_ed25519.pub refers to the public key.

4. Generate a new SSH key

Run:
ssh-keygen -t ed25519 -C "your-email@example.com"Replace your email with your GitHub email
You'll then see Generating public/private ed25519 key pair.
Press Enter since it's your first key.
Next you'll see
Enter passphrase (empty for no passphrase):Enter passphrase (password) for security
You'll be asked to confirm your passphrase just enter it again.
You'll see messages indicating the private and public keys are being generated.
On Windows open PowerShell as an Administrator and run:
Get-Service -Name ssh-agent | Set-Service -StartupType Manual
Then:
Start-Service ssh-agent
Return to Gitbash and add your private SSH key:
ssh-add ~/.ssh/id_ed25519
It should indicate that the Identity was added.

5. Add your public SSH key to Github

Run:
cat ~/.ssh/id_ed25519.puband press Enter. Copy the entire line that pops up upto the .pub
On GitHub open Settings → SSH and GPG keys then select New SSH Key give it a title and paste the public key then press add key.

6. Locate your project folder and adding it to GitHub

Locate your project it may be in the documents folder on your desktop. Open Gitbash and navigate into the project folder
cd ~/Documents/Loan Report
You can use ls confirm
Run:
git init -b main initializing an untracked local project.
Then run:
git status this shows the untracked files.
Then run:
git add . this adds the file in the current directory. Rerun git status again.
Then run:
git commit -m "Initial commit"

7. Create a new Repository on GitHub

Create a new repository with a new name preferably the same name as the file Loan Report and choose whether it is public or private.
After creating the repository, you will see the setup information and select and copy the SSH URL. Paste the link in Gitbash then press enter.
Whatever comes up replace the name section with your GitHub username and press enter.
Run:
git remote -v this confirms that Git knows where the GitHub repository is
Then run:
git push -u origin main This pushes a new repository to GitHub.
Afterwards go to GitHub and confirm if the project is there.

Conclusion

This is the basic Git and GitHub steps that you'll have to use repeatedly while working on different kind of projects and with time all of these will become easier.