DEV Community: Timothy M Kariuki

Power BI Technical Article: Data Modelling, Relationships & Joins

Timothy M Kariuki — Sun, 20 Sep 2026 07:04:33 +0000

Introduction

Power BI transforms raw data into actionable dashboards and reports, but the quality of these outputs depends heavily on initial data preparation. Data modeling arranges tables, keys, and relationships so Power BI can efficiently process connections between dataset components. Building a proper model boosts report performance, streamlines DAX calculations, reduces redundancy, improves scalability, and makes the system easier to navigate. This article examines essential modeling schemas, fact and dimension tables, filter directions, and Power Query joins, while highlighting the key differences between merging tables in Power Query and establishing model relationships in Power BI.

Data Modelling

Data modeling in Power BI is the process of structuring tables and establishing relationships between them. Rather than consolidating everything into a single, massive flat table, data is divided into distinct logical entities linked by unique keys.
For instance, a travel agency's dataset might be split into dedicated tables such as:

Customers (demographics, contact details)
Products (tour packages, flight options)
Dates (booking dates, travel dates)
Locations (destinations, departure points)
Sales Transactions (revenue, payment status, bookings)

A high-quality data model ensures:

Accurate Reporting: Uniform results across all pages and visuals.

2.Cleaner DAX: No need for complicated formula workarounds.

3.Peak Efficiency: Faster query execution and reduced memory usage.

4.Growth Potential: Easy scaling for larger datasets and new requirements.

5.Simple Maintenance: Easier troubleshooting, updates, and future enhancements.

Power BI implements these data modelling designs using three core schema patterns: flat, star, and snowflake.

Flat table

A Flat Table schema combines all attributes, entities, and transactional measures into a single, massive table without establishing relationships across multiple entities. It is fully denormalized, meaning data repetition is intentionally accepted instead of splitting entities into separate lookup tables.

Table Structure

Structure: A single table containing every column needed for analysis.
Granularity: Each row represents a single atomic transaction or event alongside all contextual descriptive metadata.
Redundancy: High textual redundancy across rows (e.g., customer names, addresses, product descriptions, and categories are repeated on every sales row).

Advantages:

Simplicity: No relationships to manage, configure, or troubleshoot.
Ease of Export: Ideal for simple reporting tools or CSV exports.
Quick Setup: Fast to build for ad-hoc, single-purpose analysis.

Disadvantages:

High Memory Footprint: Duplicate text values inflate file size and memory usage significantly.
Data Anomalies: Risks update, insert, and deletion anomalies (e.g., changing a customer's region requires updating thousands of rows).
Slower Performance on Large Datasets: Large text fields reduce columnar compressional efficiency in analytical engines.

Appropriate Use Cases

Simple, single-topic reporting (e.g., exporting a basic Excel sheet into Power BI).
Small datasets (< 100,000 rows) where model architecture optimization is unnecessary.
Ad-hoc data exploration and prototyping before formal modeling.

Star Schema

A Star Schema separates data into a central Fact Table (containing numerical, quantitative metrics) surrounded by Dimension Tables (containing descriptive context). The schema resembles a star, where the central fact connects directly to each surrounding dimension via single-key relationships.

Fact Table Structure:

Central table containing foreign keys and quantitative measurements (numerical facts like quantity, revenue, cost).
Dimension Tables: Surrounding denormalized tables containing attributes used for filtering, slicing, and grouping (e.g., Customer, Product, Date, Location).
Relationships: Direct 1-to-Many ($1:*$) single-directional relationships from Dimensions to Fact.

Advantages:

Optimal Performance: Designed specifically for Power BI’s VertiPaq engine.
Simplified DAX: Filter context flows naturally from dimensions to fact tables without complex code.
High Scalability: Scales effortlessly to tens or hundreds of millions of rows.
Intuitive Design: Easy for business analysts and report developers to understand.

Disadvantages:

Minor Data Redundancy: Dimension tables contain denormalized hierarchies (e.g., Category stored in Product table).
Initial Prep Required: Requires data transformation in Power Query or the data warehouse prior to reporting.

Appropriate Use Cases

Standard enterprise analytics, business intelligence dashboards, and Power BI semantic models.
Datasets of any medium-to-large size requiring fast visual cross-filtering and time intelligence.
Standard practice for almost all Power BI reporting solutions.

Snowflake Schema

A Snowflake Schema is a variation of the star schema where dimension tables are normalized into sub-dimension tables. Hierarchies (such as Category, Subcategory, Product) are split into separate physical tables to remove data redundancy across dimensions.

Table StructureFact Table:

Central table with foreign keys and quantitative values.
Normalized Dimensions: Main dimension tables connect to secondary lookup tables rather than containing all attributes directly.
Relationships: Chains of 1-to-Many relationships extending outward from the fact table.

Advantages:

No Data Redundancy: Strictly normalized dimensions conserve storage space in traditional relational databases (SQL).
Data Integrity: Updates to hierarchy attributes occur in a single place.

Disadvantages:

Increased Complexity: Multiple tables create a web of relationships that complicate the model diagram.
Slower Queries: Requires evaluating multiple relationship joins across tables to filter a single report visual.
Harder DAX: Complex relationship chains can complicate filter propagation and time intelligence metrics.

Appropriate Use Cases

Enterprise data warehouses optimized strictly for relational database storage efficiency.
Scenarios with extremely complex, multi-level hierarchies shared across distinct business domain entities.
When source systems enforce normalization and merging them into a Star Schema is unfeasible due to ETL constraints.

Fact Tables and Dimension Table

The fact table sits at the center of a data model. Every time a business process occurs—such as a sale, a website click, or a bank deposit—a new row is added.
Types of Data Stored:

Additive Metrics: Numeric values you can sum across any dimension (e.g., total sales revenue, units sold).
Foreign Keys: Numerical IDs that point directly to corresponding dimension tables.Example: In a tour company database, the FACT_SALES table records each transaction with columns like TravelDateKey, CustomerKey, PackageKey, AgentKey, and HotelKey.

Dimension Table
Dimension tables surround the fact table. They contain descriptive, textual attributes that allow users to ask "who, what, where, when, and why" about the numerical data.
Types of Data Stored:

Attributes & Hierarchies: Detailed text descriptions (e.g., Product Name, Subcategory, Category).
Primary Key: A unique identifier per row used to link to the fact table.Example: A DIM_CUSTOMER table holds distinct rows for every customer with details like CustomerName, Email, Gender, City, and Country.

Relationships in PowerBI

In Power BI, a relationship is a logical link established between two distinct tables based on a shared column . Instead of combining all attributes into one large, flat table, relationships define how rows in one table correspond to rows in another.

Why Relationships Are Necessary Across Multiple Tables

When data is normalized and split across multiple tables, relationships are required for the following reasons:

Enables Cross-Filtering: Relationships allow filters applied in one table (e.g., selecting a country in Dim_Customer) to automatically flow and filter data in another table (e.g., updating the total revenue in Fact_Sales).
Eliminates Data Redundancy: Storing descriptive data (like customer addresses ) in separate lookup tables saves massive amounts of memory compared to repeating that text across millions of transaction rows.
Simplifies DAX Calculations: Power BI's DAX engine relies on automatic filter context propagation through active relationships. Without them, every measure would require complex, manual lookup functions like LOOKUPVALUE() or TREATAS().
Maintains Data Integrity: Separating core entities prevents update and deletion anomalies, ensuring a product name change only needs to happen in one place rather than thousands.

Relationship Cardinalities in Power BI

Cardinality defines the numerical relationship between the unique values in the key columns of two tables.

One-to-Many (1:) / Many-to-One (:1)

How it Works
One Side : Contains unique, non-duplicate values in the key column (Primary Key).Many Side ($*$): Contains repeating values in the corresponding key column (Foreign Key).Filter context naturally flows from the One side down to the Many side.

One-to-One (1:1)

How it Works
Both connected columns contain only unique values, with no duplicates on either side.
A row in Table A matches at most one corresponding row in Table B.

Many-to-Many (:)

How it Works
Neither column contains unique values; duplicate keys exist on both sides of the relationship.

Power BI evaluates this relationship using a non-directional or bi-directional filter context where both tables can filter each other.

Primary keys act as the unique, immutable identifiers for every row in a lookup table, establishing the target "One" side of a relationship so other tables can link directly to specific records without ambiguity. Foreign keys complement this structure by residing in fact or child tables, referencing those primary keys to allow filter context to flow seamlessly without repeating descriptive metadata across transaction rows. For these connections to work reliably, unique values are essential on the primary side, guaranteeing distinct dimension entities that eliminate relationship ambiguity and maximize Power BI’s columnar compression engine. Together, these elements determine the model's cardinality the specific ratio of unique keys between tables—which dictates how filters propagate across report visuals and directly impacts DAX logic, query speed, and RAM consumption. Maintaining strict referential integrity ensures every foreign key points to a valid primary key, preventing orphaned rows in fact tables and avoiding the generation of unwanted "Blank" categories in reports. Finally, relationships operate as either active or inactive: active relationships serve as the default pathway for automatic filter propagation across standard measures, while inactive relationships remain dormant to prevent circular dependencies, offering secondary analytical pathways that can be activated dynamically on demand using specific DAX measures.

PowerQuery Joins

In Power Query, a join is an operation that combines two tables horizontally based on matching values in one or more key columns, producing a single merged table. This process is executed using the Merge Queries feature, where you select a primary (first) table and a secondary (second) table, specify the key columns to match on, and choose the appropriate join type to control which rows are retained.

Join Types in Power Query

Left Outer Join How it Works: Keeps all rows from the left (first) table and matches them with corresponding rows from the right (second) table. If a row in the left table has no match in the right table, the expanded columns from the right table will display null values.

Use Case: Appending lookup information (e.g., bringing product names into a sales transaction table) while ensuring no sales records are lost, even if a product ID is missing from the product list.

Right Outer Join How it Works: Keeps all rows from the right (second) table and matches them with corresponding rows from the left (first) table. Unmatched rows from the left table are omitted, and unmatched right table rows display null values for left-table columns.

Use Case: Analyzing master data against transactions (e.g., keeping all registered products and checking which ones have sales data from a secondary table).

Full Outer Join How it Works: Retains all rows from both tables, regardless of whether a match exists. Where rows match, data is combined; where no match exists, null values fill the missing side.

Use Case: Reconciling two comprehensive lists (e.g., comparing last month's customer database with this month's database to see new, remaining, and deleted records in a single view).

Inner Join How it Works: Retains only the rows that have a matching key in both tables. Any row from either table that lacks a corresponding match in the other table is completely filtered out.

Use Case: Filtering a dataset to analyze strictly active interactions (e.g., keeping only sales transactions for customers who are active subscribers listed in a VIP membership table).

Left Anti Join How it Works: Retains only the rows from the left table that DO NOT have a match in the right table.

Use Case: Identifying missing or orphaned records (e.g., finding sales transactions that reference invalid or deleted product IDs that don't exist in the main product catalog).

Right Anti Join How it Works: Retains only the rows from the right table that DO NOT have a match in the left table.

Use Case: Spotting unused entities or inactive master data (e.g., finding products in the inventory catalog that have zero sales recorded in the transaction table).

Conclusion

For a typical business intelligence project, the optimal architectural choice is a Star Schema backed by strict One-to-Many (1:∗) single-directional relationships.
While alternative structures like Flat Tables offer quick setup and Snowflake Schemas eliminate data duplication in source databases, the Star Schema strikes the ideal balance across every critical model dimension:

Query & Report Performance:

Power BI’s VertiPaq engine is explicitly optimized for star schemas. Storing descriptive metadata in lookup dimensions and high-cardinality numeric metrics in central fact tables maximizes columnar compression and enables high-speed query execution with minimal memory usage.

DAX Simplicity & Filter Propagation:

A Star Schema provides a clean, single-directional filter context that flows predictably from dimension tables down to fact tables. This eliminates the need for complex, performance-heavy DAX workarounds like CALCULATE().

Model Readability & Ease of Use:

The straightforward "center-and-spokes" layout creates an intuitive visual diagram in Power BI's Model View. Report builders can easily identify where to source slicers and visual headers (Dimension tables) versus aggregated metrics (Fact tables).

Controlled Redundancy vs. Complexity:

Unlike a Snowflake Schema, which introduces a complex web of normalized tables, a Star Schema accepts minor, controlled redundancy within dimension tables (e.g., keeping Category and Subcategory in a single Product table) to eliminate relationship complexity and streamline join processing.

Scalability & Long-Term Maintainability:

As datasets grow into tens of millions of rows, a Star Schema scales efficiently. New business domains can be integrated as standalone dimension tables without disrupting existing relationship logic, making the entire solution easier to audit, update, and maintain over time.

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

Timothy M Kariuki — Sat, 12 Sep 2026 14:13:33 +0000

E-commerce sites like Jumia gather enormous volumes of data every day, including product ratings, prices, discounts, and customer reviews. However, this data only takes on significance when it is sufficiently processed and scrutinized to reveal trends that decision-makers may take action upon, such as improving pricing strategy, honing promotions, or enhancing user interaction with the platform.

In order to investigate the relationship between pricing, discounts, and customer ratings and reviews, I looked at a dataset that included 115 raw Jumia product listings. My goal was to turn a raw dataset into a polished, interactive Excel display that leadership and management could use to inform actual business choices.

This article walks through the process used to turn the dataset into a clean, analysis-ready Excel Table, using structured references, data-driven thresholds, and error-aware formulas throughout.

1. Starting Point: Cleaning and Converting a Range into a Table

The first part was data cleaning by deleting all the duplicate values in the excel sheet then converted the cleaned raw data range into a proper Excel Table called tblProducts rather than working with plain cell ranges. I could reference columns by name instead of by letter e.g tblProducts [Current Price] or tblProducts![B2:B113].

Categorization Using IF Formulae

I wrote three IF-based formulas to flag rows worth a second look, each evaluated per row using the [@ColumnName] syntax:

Discount check: I used this to rank the discounts: =IF([@Discount]="","Missing",IF([@Discount]<20%,"Low Discount",IF([@Discount]<=40%,"Medium Discount","High Discount")))
Price check: I added this because I wanted to catch cases where the current price was higher than the old price, which usually meant a data-entry reversal: =IF(B2>D2,"Check prices","OK")
Missing rating check, combined with a rating-quality tier: =IF([@Rating]="","Missing",IF([@Rating]<3,"Poor",IF([@Rating]<=4.5,"Average","Excellent")))

Top Rated and Least Rated Products

In order to get the least rated or the top rated products I used the conditional formatting for top or bottom 5 products:

Summarizing the Key KPIs
In order to get a summary of my data I used the formulas below to get: MAX, AVERAGE and COUNTS of different metrics.

=ROWS(tblProducts[Product]) ' total product count =AVERAGE(tblProducts[Current Price]) ' average price =AVERAGE(tblProducts[Discount]) ' average discount =AVERAGE(tblProducts[Rating]) ' average rating =SUM(tblProducts[Review]) ' total review volume =MAX(tblProducts[Current Price]) ' most expensive item =MIN(tblProducts[Current Price]) ' least expensive item
the results were as follows:

Graphs and Correlation

I used the graphs to show the relationship between different table parameters and their relationships e.g:

For the relationship questions, I calculated Pearson correlations directly against the Table and the results were as shown below:
=CORREL(tblproducts!G2:G113,tblproducts!H2:H113) =CORREL(tblproducts!I2:I113,tblproducts!H2:H113) =CORREL(tblproducts!B2:B113,tblproducts!I2:I113)

Pivot Tables

PivotTables did the heavier lifting for the rating mix, discount mix, price-vs-rating, and top-10 lists by rating, discount, and reviews each connected to slicers on Rating Category, Discount Category, and Price Category so the dashboard stays interactive rather than static.
Example:

Dashboard

I kept the dashboard to a single screen: title and slicers at top, a KPI strip (total products, average price, average discount, average rating, total reviews), three ranked top-10 panels, three scatter plots (discount-vs-reviews, rating-vs-reviews, price-vs-rating), and a mix/insights row at the bottom.

Conclusion

The discount-vs-review correlation of roughly -0.15 is close enough to zero that I can't call it a real pattern hence sellers shouldn't expect a bigger markdown to automatically generate more reviews. Moreover, Highly rated products receive slightly more customer reviews hence slight positive correlation. Finally, expensive products are rated higher than cheaper products hence slight positive correlation.

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

Timothy M Kariuki — Fri, 28 Aug 2026 12:01:00 +0000

Introduction

There is a joke that says "Never publicly boast you are good at excel, there are levels". (You will be humiliated by what others can do with it ) which I think is a show of how many capabilities excel has for one to master all; there is always something you haven't learnt. As society becomes increasingly data-driven, exploring a variety of data analysis tools is essential to effectively manage and interpret information. Microsoft Excel remains a fundamental and widely accessible tool for data collection, management and analysis. Despite the rise of Artificial Intelligence (AI) transforming large-scale data handling, Excel excels(pun in 10....) at organizing and processing data into clear, readable formats. This capability is essential for extracting insights that support research, business strategy, and various data-driven projects.
Excel is often the first tool beginners learn in data analytics because it is visual, easy to understand, and widely used in workplaces. Whether you’re managing numerical data, such as budgets, or qualitative data, such as customer survey responses, Excel workbooks let you import and organize data effectively.

Components And Tools of Excel

Excel uses a grid of numbered rows (↔) and lettered columns (↕). Every piece of data sits inside a cell, which is the basic building block of a spreadsheet. Each cell gets its address from its column letter followed by its row number—meaning A1 refers to the cell in Column A, Row 1.

Excel has a ribbon at the top that give options for data manipulation and editing within the workbooks; the clickable ribbon labels across the top (Home, Insert, Page Layout, Formulas, Data, Review, View).

Excel offers a variety of tools found within the ribbon for data analysis that help identify trends in large datasets, saving time compared to manual analysis. Key tools include:

Sort and Filter: Create separate views with drop-down menus to filter data.
Conditional Formatting: Highlights data for easy reference, such as numbers exceeding a threshold or cells containing specific words.

Moreover, At the bottom of the excel workbook we find the sheets tab where you can replicate or open a new tab to to inser new data.

Finding and Replacing Data

Finding Data: Use Ctrl + F or head to Home > Editing > Find & Select > Find. Type what you are looking for into the Find what box and click Find Next or Find All.
Replacing Data: Use Ctrl + H or navigate to Home > Editing > Find & Select > Replace. Enter the existing text in the Find what field, type the updated content in Replace with, and choose Replace or Replace All.

Excel Formulae

Excel has formulas that perform calculations, manipulate text and test conditions. Every Excel formula begins with an equal sign (=). The formulas include: Math formulas, Text formulas, Logical formulas such as below:
Math formula: Perform arithimetic calculations on data.
=SUM(A1:A10) — Adds all numbers in cells A1 through A10.
Logical formula: Evaluate conditions and return different values based on whether the condition is true or false.
=IF(C2>=50, "Pass", "Fail") -If student score in C2 is 50 or higher, displays "Pass"; otherwise, displays "Fail".
Text Formula: Combines, cleans, or alters text strings Example: UPPER / LOWER: =UPPER(A2) — Converts text in A2 to uppercase.

Process of Cleaning Data

Cleaning data in excel involves a number if steps that help eliminate inconsistencies and provide data reliability as well as readability:

Elimination of Duplicates: After opening the excel, sheet choose your data range, select Data > Remove Duplicates. Select the columns to look for duplicate values in, then allow Excel to remove the rows that are repeated.
Fix Texts: Use UPPER()/LOWER()/PROPER() to standardize capitalization across text columns. This elps to make the data in the excel readable.
Investigate Missing Data in Cells: find empty cells, then decide whether to fill them with a default value, a formula, or delete the row entirely.
Standardize Texts Formats: Ensure that dates, numbers, and currency are stored as actual values (not text) so that sorting and calculations function properly.
Fix Inconsistent Data: Use Filters to correct inconsistent data labels (e.g Luxdev, LxDev, L.D) and use Find & Replace to standardize them.
Identify, Investigate and Correct/Eliminate Outliers: Use formulas (MIN, MAX, AVERAGE, IFERROR) to flag values that fall outside expected ranges or contain calculation errors.
Adding Additional Fields: If a new field is needed e.g Full name, use CONCATENATE() to merge(first and last name) fields together.
Proper Font Formats: Use the official and agreed upon fonts to format your data and make it professional.

Conclusion

Excel remains a vital tool for managing data, but the quality of the data determines how powerful and accurate it can be. Effective data cleaning eliminates errors, standardizes formats, and ensures accurate analysis. In the end, clean data turns Excel from a being just a spreadsheet into a trustworthy tool for confident data analysis.

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

Timothy M Kariuki — Fri, 21 Aug 2026 13:30:09 +0000

Introduction

Walking into a new space of learning or career means you will hear vocabularies you haven't heard before. Well, when I joined the data Analytics class I didn't know Git and GitHub and perhaps one might think that Git is the short form for GitHub. Git is a powerful distributed version control system used by developers and coders to track and manage code changes locally with features like branching and merging while web-based platform that hosts Git repositories and enables collaboration through pull requests, issues and team management. Git operates mainly via command-line interface (CLI) with optional GUI tools while GitHub provides a web-based UI plus integrations along with Git command support.

Installations, Configurations and Signup to the Environments

I started by downloading and setting up the Gitbash terminal on my computer. After successful set up I typed cd Desktop command to go to my desktop folder. then used ls command to list the items on my desktop folder. Next, I created a folder and named it Kenya-Hospital-Records-Analysis using the mkdir Kenya-Hospital-Records-Analysis command then used the cd My-First-Github-Project to move into the folder. I created another folder called data using mkdir Data and a README file using touch README.md command.

Creating a GitHub Repository

Since I had already created a GitHub account so I tapped the add button and created a new repository named Kenya-Hospital-Records-Analysis. I create an SSH authentication key used when pushing project from the computer to the Github Repository. I achieve this by running the ssh-keygen-t ed25519 -c command and copied the key to GitHub, authenticated and tested the connection.

Uploading the Project to Github

The Git initialization process involved the following steps:

To test whether my computer could successfully authenticate with GitHub using SSH I run the $ ssh -T git@github.com command. and used the cd Desktop command
I initialized the git repository inside the folder using the: git init command.
After initialization I run the git add . to prepare the files in the Kenya-Hospital-Records-Analysis directory for upload and then run the git commit -m command.

Then comes the steps to link the local folder to the repository

The next step involved logging in to the github repository created a new repository to load the project.
I then copied the SSH key from github and used it to link the local folder to github as shown: git remote add origin git@github.com:Timothy*****/Kenya-Hospital-Records-Analysis
And finally to push the project to the repository I used the command: git push -u origin main.

With those steps above the project was uploaded to the github repository and could be viewed by the public.

Conclusion

In this exercise, I went through the complete process of taking a project from a local folder on my computer and publishing it to GitHub using Git and SSH. I started by creating a local project, initializing it as a Git repository, tracking our files, and creating our first commit. Understanding this workflow provides a strong foundation for working with Git and GitHub and I believe that continued practice will give me the confidence and skills to partake more complex tasks