DEV Community: Brian Kariuki

Data Relationships, Modelling Schemas, and Joins in Power BI

Brian Kariuki — Sun, 13 Sep 2026 12:29:04 +0000

Data modelling in Power BI is the process of organizing tables, columns, keys and relationships so that data can be analyzed effectively. A well-designed model improves reporting accuracy, simplifies DAX calculations, enhances performance, supports scalability, and makes solutions easier to maintain.

The majority of data is kept in a single table using the flat-table method. It is simple and suitable for small datasets, but repeated customer, product, and location information creates redundancy, increases memory usage, and can make the table difficult to manage.

The star schema divides descriptive data into dimension tables. Because it offers strong performance, clear DAX, and clear relationships, it is typically chosen in Power BI.

star schema:

The snowflake schema further normalizes dimensions into additional tables. It can reduce duplication but introduces more relationships and complexity.

snowflake schema:

Fact and Dimension Tables

A fact table stores business events and numeric measures, such as quantity, sales, cost, and profit. Examples include FactSales, FactOrders, and FactTransactions. A dimension table stores descriptive attributes, such as product names, customer details, dates, and locations.

Fact and Dimension Tables

Relationships

Relationships connect tables using keys. In a typical model, CustomerID is unique in DimCustomer but can occur many times in FactSales.

One-to-many (1:*) is the most common relationship and is ideal for dimension-to-fact connections. One-to-one (1:1) means each record matches exactly one record and should only be used when justified. Many-to-many (:) allows multiple matches on both sides but should be used carefully because it can create ambiguity.

Primary keys uniquely identify dimension records, while foreign keys connect fact records to dimensions. Relationships may also be active or inactive depending on how they are required for analysis.

Filter Direction

Filters normally flow from dimensions to fact tables. Selecting a product in DimProduct therefore filters the relevant records in FactSales. Single-direction filtering is generally preferred because it provides predictable results. Bidirectional filtering can be useful in specific situations but may create ambiguous paths and unnecessary complexity.

Joins in Power Query

Power Query uses Merge Queries to join tables during data preparation. The main join types are:

Left Outer: keeps all left records and matching right records.
Right Outer: keeps all right records and matching left records.
Full Outer: keeps all records from both tables.
Inner: keeps only matching records.
Left Anti: keeps unmatched left records.
Right Anti: keeps unmatched right records.

For example, Customers and Orders can be joined using CustomerID to identify matching or unmatched customers.

Power Query Joins vs Relationships

A Power Query merge physically combines columns into a query before loading the model. A Power BI relationship keeps tables separate while allowing filters and calculations to work across them. Therefore, relationships are generally preferable for maintaining separate fact and dimension tables, while merges are useful when data genuinely needs to be combined during preparation.

Recommended Model

For most business projects, the star schema is the preferred design. It reduces redundancy, supports efficient DAX, improves readability and performance, scales effectively, and simplifies maintenance. A typical model should use one-to-many relationships from dimensions to facts and primarily single-direction filtering. This creates a reliable and efficient foundation for Power BI reporting and analysis.

Building an Interactive Excel Dashboard for E-commerce Product Analysis: A Case Study of Jumia Products

Brian Kariuki — Sat, 05 Sep 2026 13:59:03 +0000

Objective

The goal of this project is to create an interactive Excel dashboard that provides insights into the performance of products listed on Jumia.
Emphasis is on product pricing, discounts, customer reviews, and ratings in order to understand product performance and identify trends. Its meant to help Jumia and sellers make better decisions around pricing strategies, promotions, and customer engagement.

Project introduction and objective

There were 115 source rows and six fields at the beginning of the project: Product, Current price, Old price, Discount, Review, and Ratings. The goal was to build a professional, interactive Excel dashboard that turns the supplied Jumia product data into useful pricing, promotion, and customer-engagement insights.

Dataset and business questions

The dataset consisted information about products listed on Jumia with the following columns:
• Product: Name of the product.
• Current Price: The current selling price of the product (in KSh).
• Old Price: The original price before discount (in KSh).
• Discount: The percentage discount offered on the product.
• Review: The number of customer reviews received by the product.
• Rating: The average customer rating of the product (out of 5).

While the business questions were: Which products have the highest ratings? Which products have the most reviews? Which products carry the largest discounts? How much of the catalogue has missing customer feedback? Are discount levels associated with review counts or ratings? Can these patterns be presented in a dashboard that decision-makers can filter and interpret quickly?

Initial data-quality audit

Six columns and 115 rows were recorded in the audit. The worksheet found 11 product duplicate rows, and each of the 58 review and rating rows was blank. Additionally, there were negative numbers in the reviews that needed to be looked at rather than deleted automatically. Currency was in form of KSh like "KSh 950" used in place of prices, and rating as text like "4.5 out of 5".

Cleaning and preparation decisions

Product names were cleaned/trimmed and duplicates handled. Currency was converted to numeric values for current and old price. Negative reviews were treated as an artifact rather than genuine negative reviews and converted to whole-number. Ratings had 'out of 5' text, they were removed, converted to decimals, and missing ratings were left blank.

Excel formulas and enrichment fields

The cleaned table was added columns such as; rating check, Discount check and Price check. Additional columns calculate the discount amount, calculated discount rate, rating category and discount category. Rating categories are Missing, Poor, Average and Excellent; discount categories are Low Discount (<20%), Medium Discount (20%–40%), and High Discount (>40%).

PivotTable and analysis workflow

The tblProducts table is the main table. Pivots tables rank products by rating, review count and discount, while separate summaries show the mix of rating and discount. The dashboard combines these outputs with scatterplots.

Dashboard design and slicer connections

The dashboard combines slicers, charts and tblProduct table into one display. Slicer filters are included in the spreadsheet.

Key findings

The cleaned table contains 110 products. The dashboard gives an average rating of 3.9 and 723 total reviews. 56 of 110 products have no rating, or about 51%. The rating mix is 17 Excellent, 26 Average, 11 Poor and 56 Missing. Discounting is concentrated in the high-discount group: 60 products above 40%, 32 medium-discount and 18 low-discount products.

Business recommendations

Products with missing customer feedback should be given priority for data-quality follow-up. Instead of thinking that a bigger discount equates to higher demand, evaluate high-discount products in conjunction with reviews. Look into high-rated products.

Limitations and lessons learned

Links to the GitHub repository and dashboard file

GitHub repository: Github repository

Raw-data screenshot requiring cleaning.

Cleaned-data screenshot: numeric prices, discounts, reviews and ratings prepared for analysis

Formula/calculation screenshot: validation checks and enrichment fields used in tblProducts.

Pivot/analysis screenshot: ranking and category summaries feeding the dashboard.

Pivot charts

Dashboard

Insight summary

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

Brian Kariuki — Sun, 30 Aug 2026 06:37:19 +0000

Microsoft Excel

Microsoft Excel is a spreadsheet software developed by Microsoft that allows you to collect,organize, analyze, calculate, and visualize data efficiently using tables, rows, and columns. Excel's features, which include charts, functions, formulas, and formatting tools, facilitate information organization and decision-making. It is a vital resource for professionals in a variety of industries and companies

The Excel Interface

The Microsoft Excel interface consists of a collection of customizable tools, menus, and grid-based workspaces designed for data management and analysis.

When you open Excel, you are presented with a user interface made up of many tools ie

Ribbon-Toolbar across the top that contains all commands organized into tabs
Quick Access Toolbar- Icons for Save, Undo, and Redo (top left corner)
Formula Bar- Area above the grid where the content or formula of the selected cell appears
Name Box- Displays the address of the selected cell (e.g., A1)
Columns- Labeled A, B, C... across the top vertically
Rows- Labeled 1, 2, 3... down the left side horizontally
Cell- A single box where a row and column intersect (e.g., B2)
Worksheet Tabs- Tabs at the bottom (Sheet1, Sheet2) that switch between spreadsheets within a workbook

The Excel’s Structure: Rows, Columns, Cells

Row- A horizontal line of cells (e.g., Row 1)
Column- A vertical line of cells (e.g., Column A)
Cell- Intersection of a row and column (e.g., A1)
Range- A group of cells (e.g., A1:C3)

Supported data types in include; Text (words): e.g., Brian, Numbers: 100, 60.1, Dates: 01/06/2025, Formulas: COUNT. =COUNT(A2:A10)

Data Formatting in Excel

Data formatting in Excel is the process of changing how data looks and is categorized in a spreadsheet without changing the actual value inside the cell.

Main Types of Formatting include;

Number Formatting Controls how numbers appear, such as changing raw digits into Currency ($1,250.00), Percentages (75%), Dates (MM/DD/YYYY), or decimals

Common Number Formats:

Currency (e.g., $1,000.00, KES 1000)
Percentage (e.g., 75%)
Date and Time (e.g., 03-Jun-2025, 2:30 PM)

Real life application in Number Formatting can be showing Salary of employees as currency

Currency formatting steps

Select the cells with numbers (Salary Column)
Go to Home tab > Number group
Click the dropdown arrow on the number format box
Select Currency
To change currency symbol, click the small dialog launcher → Choose symbol
Text Formatting Changes text appearance using font styles, sizes, bold, italics, underlining, and text colors via the Home tab.

Real life application in text formatting can be showing Employee records ie Clearly displaying employee names, departments, salary, and other information.

Cell Alignment To align cells in Excel, select your cells, go to the Home tab, and use the buttons in the Alignment group. You can change horizontal alignment (left, center, right), vertical alignment (top, middle, bottom), or use Wrap Text to fit long content

Real life application in Cell Alignment can be aligning employee IDs to the right and aligning names to the left appropriately.

Cell Styling & Borders Adds visual boundaries, background fill colors, and structure to tables. In Home > Font, click: Borders → Choose “All Borders” Fill Color → Choose a light background for headings

Real life application in cell styling & Borders can be Styling cells to distinguish Employee's First name, Last name, deparment etc.

Conditional Formatting highlights cells automatically based on rules or criteria, helping spot trends or outliers

Real life application in conditional formatting can be highlighting salary of employees below certain figure. ie our case <ksh 75,000

Steps:

Go to Home > Conditional Formatting
Choose a rule type:
- Highlight Cell Rules (Less than, Greater than)
Select Highlight Cell Rules > Less Than
Enter a value (e.g., ksh75,000)
Choose a formatting style (e.g., red fill)
Click OK

Data Sorting

Sorting means arranging data in a specific order — either ascending (A to Z or smallest to largest) or descending (Z to A or largest to smallest).

Types of Sorting:

Text Sorting (A to Z or Z to A)
Number Sorting (Smallest to Largest or Largest to Smallest)
Date Sorting (Oldest to Newest or vice versa)

Real life application of data sorting can be Sorting employee names alphabetically

Steps to Sort Data:

Select the column or data range to sort
Go to Home > Sort & Filter (or Data > Sort)
Choose Sort A to Z or Sort Z to A (our case A to Z)
For custom sorts, click Sort..., then choose:
Column
Sort order
Value/Cell Color/Font Color
Click OK

Filtering

Filtering allows you to display only the rows that meet certain criteria and hide the rest temporarily.

Filter Types:

Text Filter: Contains, Begins With, Ends With, etc.
Number Filter: Greater Than, Less Than, Equals, etc.
Date Filter: Before, After, Between, etc.

Real life application of data filtering can be showing hire date of employees between certain periods ie our case between 01-01-2021 and 01-01-2022

Steps to Apply a Filter:

Click anywhere in your dataset
Go to Home > Sort & Filter > Filter (or Data > Filter)
Little dropdown arrows appear in header row
Click the dropdown arrow for the column you want to filter
Choose the values to show or use text/number/date filters

Data Validation

Data validation restricts the type of data users can input into a cell.

Steps

Select the cells where you want a dropdown (e.g., A2:A10)
Go to Data > Data Validation
In the dialog:
Allow: List
Source: Department, Age, Gender
Click OK

Removing Duplicates

Duplicate data causes errors in analysis and inflates numbers.

Functions in Excel

Text Functions are built-in formulas used to clean, extract, change, and combine text strings in your spreadsheets.

Key Functions include;

UPPER() Converts text to uppercase
LOWER() Converts text to lowercase
PROPER() Capitalizes first letter of each word
TRIM() Removes extra spaces from text
LEFT() Extracts leftmost characters
RIGHT() Extracts rightmost characters
MID() Extracts characters from the middle
LEN() Returns length of text
FIND() Finds position of a substring (case-sensitive)
SUBSTITUTE() Replaces text within a string

An example can be Correcting names to lower case, Upper case and proper case

Number Function help you perform math, find totals, count items, and change how numbers look in your spreadsheet.

Arithmetic operators are symbols used to perform basic mathematical calculations like addition, subtraction, multiplication, and division

Addition + =A1+B1
Subtraction - =A1-B1
Multiplication * =A1*B1
Division / =A1/B1
Exponents ^ =A1^2

An example can be performing a Simple Addition Calculation

Functions A function is a predefined formula in Excel that performs a specific task.

SUM() Adds a range of numbers
AVERAGE() Calculates the mean of values
MIN() Finds the smallest number
MAX() Finds the largest number
COUNT() Counts how many numbers are in a range
POWER() to raise a base number to a specified exponent or power
SQRT() to calculate the square root of a positive number
PRODUCT() to multiply all the numbers given as arguments and return the final result
MODE() to find the most frequently occurring number in a datase
MEDIAN() to find the middle number in a set of given values

An example can be performing Addition for cells ranging from A12 to D13

Click on an empty cell (e.g., D15)
Type =SUM(A12:D13)
Press Enter
Excel returns the total sum of A12 to D13

My First Github Project: From A Local Folder To Github Using Git And SSH.

Brian Kariuki — Sat, 22 Aug 2026 11:29:21 +0000

Git and Github

Github is a website and cloud-based platform where users may store, exchange, and collaborate on computer code. It makes use of Git, a program that keeps track of file changes over time.

Creating Project Folder

I first created a new project using Gitbash called Kenya-Health-Records-Analysis using the mkdir make directory command.

Steps I took:

Navigated where I want project stored (cd ~/Desktop)
Created the project folder (mkdir Kenya-Health-Records-Analysis)
Enter the created folder (cd Kenya-Health-Records-Analysis)

Inside the Kenya-Health-Records-Analysis folder, I created an additional folder called data (mkdir data) and a file README.md (touch README.md)

Inside the data folder I added a Kenya_Hospital_Health_Records xlsx file.

User Guide To Our First Project

Our README.md file serves as introduction or user guide to our project. I opened and edited it in gitbash by running nano README.md

Steps I took

Navigated to the Project Folder cd Kenya-Health-Records-Analysis
Typed open command nano README.md
Edited text insided the terminal window explaining our project.
Saved changes ctrl+o then Enter
Exit nano ctrl+x

Creating our Github Account

As stated earlier, Github is a cloud-based platform where users may store, exchange, and collaborate on computer code. It makes use of Git, a program that keeps track of file modifications over time to prevent you from losing previous work or becoming confused by different stored versions. I used my email to create Github, set a good password, chose the Github free tier and customized my profile.

Set Up SSH for GitHub

SSH enables secure communication between my computer and Github without requiring me to input my Github username and password each time I push changes.

Steps I took

Checked whether I already have an SSH key by running ls -al ~/.ssh, In my case I didn't have one.
I created a new SSH key by running ssh-keygen -t ed25519 -C "your-email@example.com" , replaced email with the email address connected to my GitHub account.
Created an SSH passphrase.

Add SSH Key to the SSH Agent

Started the SSH agent by running eval "$(ssh-agent -s)"
Then added my private SSH key ssh-add ~/.ssh/id_ed25519

Add Public SSH Key to GitHub

Show my public SSH key by running cat ~/.ssh/id_ed25519.pub
Copy the Public SSH Key.
Open GitHub and go to Settings, then select SSH and GPG keys.
Click New SSH key, enter a descriptive title such as My laptop, and paste the public SSH key into the key field.

Test SSH Connection

Tested whether GitHub recognizes my SSH key by running, ssh -T git@github.com. My case I was asked to confirm GitHub's host by typing yes. GitHub displayed a successful authentication message.

Open Project Folder

I navigated to the folder containing my project, cd Kenya-Health-Records-Analysis
I confirmed that I'm in the correct folder by listing its contents, ls

Initialize Git in Local Project

Inside my project folder, I initialized Git by running, git init, this command creates a new local Git repository and allows Git to begin tracking changes to your project files.
Checked the repository status by running, git status

Add Project Files to Git

Added all files in the current project folder to Git by running, git add .
Checked whether the files are ready to be committed by running git status

Create First Commit

Commit main goal is to permanently store your project in your local repository history, I run git commit -m "Initial commit".

Create a New Repository on GitHub

Inside Github, I selected New repository,entered a name for my project, chose whether the repository should be Public or Private and clicked Create repository.

Connect Local Project to GitHub

In GitHub repository created, I clicked SSH and copied the URL.
I connected local repository to gitHub by running, git remote add origin git@github.com:YOUR-USERNAME/Kenya-Health-Records-Analysis, replaced USERNAME with my actual github username.
I verified that the remote repository was added successfully by running, git remote -v.

Push Project to GitHub

Pushed my project to GitHub by running, git push -u origin main
Project files were visible in my GitHub repository.