DEV Community: Njuguna Gichia

Data Modelling, Relationships & Joins in Power BI

Njuguna Gichia — Sun, 13 Sep 2026 12:55:12 +0000

Data modelling is the process of analyzing and defining different data types, as well as the relationships between those bits of data. In Power BI, data modelling is the process of organizing multiple tables and defining the relationships between them so that power BI can analyze and visualize business data.

A good data model improves report performance, makes DAX calculations easier, supports accurate business insights, simplifies maintenance and provides better scalability as the amount of data and reporting requirements grow.

Data Modelling Approaches.

1.Flat Table.

A flat table / flat data model organizes data into a single table where each row represents a record and each column represents an attribute. Flat tables approach consolidates all the necessary data into a single denormalized table before it gets to the BI tool which sometimes means one table with all the columns (Facts and Attributes) already joined and ready for use.

Advantages
- It isn't tied to a specific tool since all transformations happen at a database level and so moving across tools becomes easier.
- It minimized the risk of discrepancies between reports and KPI's by ensuring a single source of data
- It makes it easier to track changes, roll back updates and maintain data integrity by allowing for version control
- It makes querying easier without needing to understand complex relationships between tables.

Disadvantages.
- Can become large and occasionally slower to query when dealing with massive datasets or frequent refresh cycles.
- May struggle in high volume environments, when powering multiple dashboards or when using databases that aren't optimized for analytical workloads.
- Risk of increasing storage costs and complicating updates due to data duplication.
- Very costly to optimize especially because they become inefficient as datasets grow.

Situations where appropriate.
-When your dataset has only a few thousand rows, making performance slowdowns unnoticeable.
- When individual users or small teams manage data in simple tools like Excel or CSV files.
- When you need to test an idea rapidly without spending time building complex table relationships.
- When denormalized data speeds up read operations in specific reporting layers or data pipelines by avoiding table joins.
- When you must easily share raw data across different apps, platforms, or serverless functions without a database engine.

Implications for Power BI performance and model complexity.
- Using a flat table data model in Power BI severely undermines performance and increases model complexity because it conflicts directly with the tool's underlying VertiPaq columnar storage engine. By forcing data into one massive, denormalized table, you suffer from inefficient column compression and a high memory footprint due to repeated text values, which triggers sluggish slicers and slow DAX scan times as Power BI is forced to query millions of rows rather than tiny dimension tables. Furthermore, while a single table eliminates relationships, it introduces development complexity through cluttered field lists, rigid models that cannot scale to new business processes, and high-risk DAX bugs like Auto-Exist, which can silently cause incorrect report totals when multiple columns are filtered together.

2. Star Schema

The star schema features a central fact table that contains measurable quantitative data surrounded by dimension tables that contain descriptive attributes related to the fact data. The structure looks like a star, with a single dense table sitting at the center, and connected to surrounding lookup tables using simple relationships.

This schema contains two distinct table types.
- Fact Tables.
These are at the center of the model. They record actual observations, metrics or transactional events (e.g., Fact_Sales) and consist of continuous numerical values and a collection of Foreign Keys which act as pointers linking back to the surrounding descriptive tables.
- Dimension Tables.
These are tables surrounding the fact table that contain descriptive context or attributes related to the events recorded in the fact table (e.g., Dim_Product, Dim_Date, Dim_Customer). Each Dimension table contains a unique Primary Key that anchors a one to many relationship flowing directly into the fact table. They also contain highly denormalized, flattened, repeated attributes to avoid deep branching relationships.

Advantages.

Yield rapid query responses because analytical databases scan short, wide tables to filter rows in long narrow tables.
The layout is easy for business analysts, report developers and end users to understand and navigate.
Aggregations run quickly because calculations are grouped by highly indexed dimensional attributes.

Disadvantages.

Because the dimensional tables are denormalized and repeat text fields, they increase the physical storage size of those dimensions.
This schema does not naturally handle complex hierarchy variations natively without forcing them into flat structures.
Because a single star schema focuses around one core process, combining multiple distinct processes requires creating independent fact tables that share dimensions.

 Situations where appropriate.

When stakeholders use tools like Tableau or PowerBI to build their own reports without needing to write complex SQL queries.
When tracking performance metrics across consistent structural or Temporal intervals is required such as daily sales or machinery sensor output.
When the data warehouses or data lakes process millions or billions of rows of historical data.

 Implications for Power BI performance and model complexity.

Adopting a star-schema architecture is the single most effective way to maximize Power BI performance and streamline model complexity because it aligns perfectly with the underlying VertiPaq in-memory columnar storage engine. By separating transactional metrics into a central fact table and descriptive attributes into dedicated, highly compressed dimension tables, Power BI achieves superior data compression, smaller file sizes, and accelerated DAX query scans. When users interact with report elements, visual rendering is nearly instant because slicers only need to query small, indexed dimension tables to dynamically pass filters down a clean one-to-many relationship path to the fact table. From a usability standpoint, this structural layout reduces model complexity by eliminating confusing relationship loops and replacing multi-table snowflake structures with an intuitive field list. Consequently, DAX formulas become remarkably simple, clean, and elegant, freeing report developers from writing defensive code to handle mismatched data granularities or fighting calculation defects like the Auto-Exist bug, resulting in highly accurate calculations and faster report development.

3. Snowflake Schema

This is a multi-dimensional data model where centralized, quantitative fact tables connect to dimension tables that are broken down into further lookup tables. The complex branching relationships extend outward from the center, resembling a snowflake.

Built using three distinct structural layers.

Central Fact Tables. Contains transaction-level keys, numbers and facts - (e.g., Fact_Sales containing metrics like Quantity and Revenue).
Primary Dimension Tables. These are tables that map directly to the fact table via a foreign key. They store a secondary foreign key pointing further down the line (e.g., Dim_Product contains a key for categories rather than the category text name).
Sub-Dimension Tables. These are normalized lookup tables connecting strictly to primary dimensions tables rather than the core fact table (e.g., a standalone Dim_Category table or a separate Dim_Geography table containing city/country data).

Advantages.
- because of data normalization rules, text labels are never repeated across rows which minimizes storage in relational databases.
- Changing a group name or an attribute hierarchy only requires updating a singe record in a sub-dimension table instead of bulk-row modifications.
- Eliminates the risk of typing or recording anomalies since attributes are isolated in single, authoritative lookup sources.

Disadvantages.
- Query engines must evaluate complex multi-table joins to trace a filter from a sub-dimension down to a transactional fact table.
- The visual layout is much more crowded and difficult to parse at a glance compared to a clean star structure.
- Keeping track of sequential, multi-layered keys increases development friction when writing ETL or data pipeline logic.

 Situations where appropriate.

Environments where reducing physical storage footprints and ensuring total database normalization are the absolute priorities.
When a single branch of dimensions (like a complex, multi-tiered geography master list) is shared globally across multiple entirely different business lines or separate fact tables.
Systems where data is strictly read directly via a traditional SQL engine optimized to parse multi-table relational joins natively.

 Implications for Power BI performance and model complexity.

In Power BI, a snowflake schema should generally be avoided or flattened into a Star Schema during the ingestion phase due to several architectural friction points:
A snowflake schema significantly degrades Power BI performance and adds immense development overhead because it introduces multi-hop relationship chains that force the VertiPaq columnar engine to calculate complex cross-table joins during runtime. While it saves minimal physical disk storage, this design undermines Power BI’s native column compression algorithms, resulting in sluggish slicer response times as filters must cascade through multiple intermediate lookup tables before arriving at the core fact table. From a usability standpoint, the model complexity spikes dramatically, loading the user interface with a cluttered, multi-table field list that confuses report creators. This intricate web of relationships forces developers to write highly complex, defensive DAX measures to accurately manage filter context across varying table granularities, drastically increasing the likelihood of development bottlenecks, unoptimized query paths, and frustrating calculation bugs.

Fact Tables & Dimension Tables.

Fact Tables.

Fact tables are quantifiable events of businesses that are recorded at a certain degree of detail. It exclusively records measurable, quantitative business events at the exact moment they occur. The information stored inside consists entirely of numeric data points known as measures and structural foreign keys (FK) used to identify when, where, and how the event happened.

Measures (Fact Tables) are numeric values meant to be aggregated using mathematical operations such as SUM, AVERAGE, MIN, or MAX. Examples include physical units sold, gross profit margins, account balances, or sensor temperature readings.

Dimension Tables.

Facts are given a descriptive context by dimension tables. They specify the way users slice, filter, group, and drill into measures. They contain the textual, categorical, or qualitative information that answers the "who, what, where, and when" of a transaction. Dimension rows are relatively stable and slow-moving compared to the rapidly expanding rows of a fact table.

Descriptive Attributes (Dimension Tables) are textual descriptors, IDs, or dates used to filter, group, slice, and slice-and-dice measures in a report. Examples include an employee’s job title, a product’s color, a customer's country, or a calendar month name.

Granularity.

Grain defines exactly what one row represents in a fact table. All foreign keys and measures inside the fact table have to match this chosen grain to prevent double counting or data corruption during reporting.

Fact Table Example.

Dimension Table Example.

Table plain Text.

Relationships in Power BI

A relationship in Power BI defines a logical link between two tables based on a shared column. In a relational data model, data is split into specialized tables to eliminate redundancy and improve performance (normalization). Relationships allow Power BI to filter and slice metrics in transaction tables using descriptive attributes from dimension tables without combining everything into a single massive, flat table.

Core Data Modeling Concepts

Primary Key (PK): A column (or set of columns) in a dimension table where every value is completely unique. It serves as the master identifier for a single entity (e.g., CustomerKey in Dim_Customer).
Foreign Key (FK): A column in a table that references a Primary Key in another table. It allows multiple records to map back to a single primary entity (e.g., CustomerKey in Fact_Sales).
Unique Values: Ensures that each key appears exactly once on the "One" side of a relationship. If duplicates exist where Power BI expects unique values, data integrity is lost, leading to calculation errors.
Cardinality: Refers to the numerical mapping between rows of two related tables (e.g., 1:*, 1:1, :).
Referential Integrity: The rule ensuring that every Foreign Key value in a fact table exists as a matching Primary Key in the corresponding dimension table. If a sale records CustomerKey = 999, but key 999 is missing from Dim_Customer, referential integrity is broken, resulting in blank/unmatched categories in reports.
Active vs. Inactive Relationships:
- Active Relationship: The default link between two tables used to propagate filter context automatically across visuals. Only one active relationship can exist between any two tables at a time.
- Inactive Relationship: A dormant connection created when multiple paths exist between two tables (e.g., Dim_Date linked to both OrderDate and ShipDate in Fact_Sales). It remains unused until explicitly activated in DAX measures using functions like USERELATIONSHIP().

Example from Snowflake-Schema-Main

Dim_Customer Table: CustomerKey is the Primary Key. It contains strictly unique values because each customer is registered only once in master records.
Fact_Sales Table: CustomerKey acts as a Foreign Key. A single customer can make multiple purchases over time, so their CustomerKey appears multiple times (many rows) across transactional records.

Relationship Cardinalities

1. One-to-Many (1:*)

How It Works: One row in the dimension table connects to zero, one, or multiple rows in the fact table. Filters automatically flow from the "One" side (dimension) to refine the "Many" side (fact).
Practical Example: Dim_Product (1) connects to Fact_Sales (*) via ProductKey. Filtering for "Laptops" filters all transaction records for laptops.
When to Use: This is the standard best practice for dimensional data modeling (Star Schema / Snowflake Schema).
When NOT to Use: Never use if both tables contain non-unique, repeating key values.

[ Dim_Product ] (1)  <--------->  (*) [ Fact_Sales ]
(ProductKey: PK)                      (ProductKey: FK)

2. One-to-One (1:1)

How It Works: One row in Table A links directly to exactly one matching row in Table B.
Practical Example: Linking a Dim_Customer table to a separate Dim_CustomerSecurity table where sensitive user details are stored on a 1-to-1 basis.
When to Use: Useful when splitting large tables for security purposes, performance optimization, or separating frequently accessed columns from rarely used ones.
When NOT to Use: Avoid when unnecessary, as two 1:1 tables can almost always be merged into a single table, reducing model complexity.

[ Dim_Customer ] (1)  <--------->  (1) [ Dim_CustomerSecurity ]
(CustomerKey)                           (CustomerKey)

3. Many-to-Many (:)

How It Works: Neither table has unique values in the key column. Rows on both sides connect to multiple rows in the opposite table, requiring bi-directional filtering by default or bridge handling.
Practical Example: Linking a Fact_Sales table directly to a Fact_Targets table on DateKey when both tables contain multiple rows for each date.
When to Use: Use sparingly when modeling direct relationship scenarios where unique keys cannot be established.
When NOT to Use: Avoid using as a quick fix for duplicate key issues. : Relationships create complex DAX calculations, introduce ambiguity, and can produce unpredictable visual aggregations. Instead, build a intermediate bridge/dimension table with unique values to split it into two 1: relationships.

[ Fact_Sales ] (*)  <--- (Bridge / Intermediate) --->  (*) [ Fact_Targets ]
(DateKey)                                                   (DateKey)

Filter Direction.

When you select a value in a report visual or slicer (such as choosing a specific year or product category), Power BI creates a filter context. This filter context flows automatically across active relationships between tables to isolate the relevant transaction rows in your fact table.

How Filter Context Flows

Filters flow along the relationship line between tables, moving from the table where the selection was made to the target table. The direction in which this filter context is allowed to travel depends on the Filter Direction setting of the relationship.

[ Dimension Table ]  ─── (Filter Direction Arrow) ───>  [ Fact Table ]

1. Single-Direction Filtering (Default & Standard)

How It Works: Filters travel in one direction only—from the "One" side (Dimension) to the "Many" side (Fact).
Behavior: Selecting an entity in a dimension table filters the records in the fact table. However, filtering a fact table does not filter or restrict the items shown in the dimension table.
Model View Indicator: A single arrowhead pointing from the dimension table toward the fact table (1 ──> *).

2. Bidirectional / Both Filtering

How It Works: Filters travel in both directions across the relationship.
Behavior: Selecting a value in a dimension filters the fact table, and filtering the fact table simultaneously filters the connected dimension table (as well as any other downstream dimension tables).
Model View Indicator: A double arrowhead pointing toward both tables (1 <──> * or * <──> *).

Step-by-Step Example: Filtering `Fact_Sales` from `Dim_Product`

Using our dataset model (Dim_Category ──> Dim_Product ──> Fact_Sales):

User Action: A user clicks on the "Electronics" category in a report slicer.
First Propagation: The filter context targets Dim_Category where CategoryName = "Electronics". It passes down the 1 ──> * relationship on CategoryKey to Dim_Product, instantly filtering Dim_Product to only include products belonging to Electronics (e.g., ProductKey = 1 for "Laptop Pro" and ProductKey = 2 for "Smartphone X").
Second Propagation: The filter context continues down the next 1 ──> * relationship on ProductKey to Fact_Sales.
Final Result: Power BI filters Fact_Sales to keep only rows where ProductKey is 1 or 2, dynamically recalculating metrics like SUM(Revenue) or SUM(Quantity) strictly for Electronics transactions.

Why Bidirectional Filtering Should Be Used Carefully

While bidirectional filtering allows tables to filter each other, enabling it permanently in relationship properties introduces major data modeling risks:

Ambiguous Filter Paths: When multiple bidirectional relationships exist in a model, Power BI creates closed loops (circular paths). The engine cannot determine which path to take to apply a filter, leading to unpredictable measure results or DAX calculation errors.
Unnecessary Model Complexity & Slow Performance: Bidirectional paths force Power BI's VertiPaq engine to evaluate dynamic cross-filtering on large tables at report runtime, increasing memory usage and slowing down report visuals.
Unexpected Slicer Masking: Filtering a transaction table can unexpectedly mask or hide valid items in dimension drop-downs, confusing users who expect to see all available lookup options.

Best Practice: Keep physical relationship filter directions set to Single. If bidirectional filtering is required for a specific business metric, activate it dynamically inside individual DAX measures using CROSSFILTER() rather than changing the physical model properties.

Joins in Power Query.

A join is a relational operation that combines columns from two tables based on matching values in one or more common key columns.

In Power Query, tables are combined using Merge Queries (Home $\rightarrow$ Merge Queries). You select a primary table (Left Table), a secondary table (Right Table), and highlight the matching key columns in both preview panels. Power Query evaluates the matching key values and appends a nested Table column containing the matched rows from the right table, which can then be expanded.

Source Sample Tables from Our Model

To illustrate each join type clearly, we use two sample tables from our dataset:

Left Table: Dim_Customer
- Contains records for CustomerKey 1 through 5.
Right Table: Dim_CustomerSecurity
- Contains records for CustomerKey 1 through 4, plus an unassigned security record with CustomerKey 99.

1. Left Outer Join (All from First, Matching from Second)

How It Works: Evaluates every row in the left table and retrieves matching rows from the right table. If no match exists in the right table, null values are filled in.
Records Retained: All rows from the Left Table + matched rows from the Right Table.
Practical Example: Merging master customer details (Dim_Customer) with optional security audit records (Dim_CustomerSecurity).

2. Right Outer Join (All from Second, Matching from First)

How It Works: Evaluates every row in the right table and retrieves matching rows from the left table. Unmatched left attributes are filled with null.
Records Retained: All rows from the Right Table + matched rows from the Left Table.
Practical Example: Ensuring all compliance records in Dim_CustomerSecurity are evaluated against customer master records.

3. Full Outer Join (All Rows from Both Tables)

How It Works: Combines all records from both tables regardless of matches. Missing pairs on either side are filled with null.
Records Retained: All rows from both tables (complete union of matched and unmatched keys).
Practical Example: Reconciling two lists to see all active customers and all security profiles.

4. Inner Join (Only Matching Rows)

How It Works: Keeps only rows where the key value exists in both the left and right tables. Unmatched records from both sides are dropped.
Records Retained: Strictly matching pairs present in both tables.
Practical Example: Returning only customers who have completed security verification.

5. Left Anti Join (Rows Only in First Table)

How It Works: Filters the left table to isolate rows that have no matching key in the right table.
Records Retained: Left-only unmatched rows.
Practical Example: Identifying customers in Dim_Customer who are missing a security profile in Dim_CustomerSecurity.

6. Right Anti Join (Rows Only in Second Table)

How It Works: Filters the right table to isolate rows that have no matching key in the left table.
Records Retained: Right-only unmatched rows.
Practical Example: Finding orphan security profiles in Dim_CustomerSecurity that are not assigned to any customer in Dim_Customer.

Power Query Joins vs Power BI Relationships

Merging tables in Power Query and establishing model relationships in Power BI serve fundamentally different functions within your analytical data pipeline.

Key Structural Differences

Physical Data Combination in Power Query Merge: Yes, a Power Query Merge physically combines attributes into a single query structure during data transformation. When expanding columns from Dim_CustomerSecurity into Dim_Customer, Power Query reshapes the underlying M-code query and loads a single combined table into memory.
Separation via Model Relationships: No, creating a relationship between tables does not combine them physically. Both tables remain distinct physical storage objects in the VertiPaq engine. The relationship acts as a logical bridge allowing filter context to pass dynamically between them at visual rendering time.
Workflow Stage Execution:
- Power Query Merge: Occurs at the ETL (Extract, Transform, Load) stage before data is compressed and loaded into the tabular data model.
- Model Relationships: Defined in the Model View stage after data has been loaded into the VertiPaq engine, governing DAX calculation context and reporting visuals.

Core Comparison Matrix

Feature / Aspect	Power Query Merge Queries	Power BI Model Relationships
Primary Purpose	Denormalize, consolidate, or clean datasets	Build Star/Snowflake schemas for dynamic analytics
Storage Impact	Permanently increases table width (more columns/rows)	Keeps tables lean and distinct in VertiPaq memory
Execution Engine	Power Query Formula Engine (M)	VertiPaq Analytics Engine (DAX)
Dynamic Interactivity	Static (fixed during data refresh)	Dynamic (responds instantly to slicers and visual filters)

When to Choose a Merge vs. a Relationship

1. When to Use a Merge (Power Query)

Consolidating 1-to-1 Extensions: Merging extended attribute lookup tables (e.g., merging Dim_CustomerSecurity into Dim_Customer to avoid holding unnecessary table entities in your model).
Flattening Snowflake Dimensions: Collapsing multi-level dimension hierarchies (e.g., merging Dim_Category directly into Dim_Product to create a clean, single-level Product dimension table).
Pre-filtering Fact Rows: Joining reference keys to eliminate unwanted rows before loading data into the model.

2. When to Use a Relationship (Model View)

Connecting Fact and Dimension Tables: Linking Fact_Sales to Dim_Customer or Dim_Product.
Preserving Multi-Fact Models: Connecting shared dimensions (e.g., Dim_Category or Dim_Date) to multiple fact tables like Fact_Sales and Fact_Targets.

Impact of Excessive Merging on Data Model Performance

Severe Storage Bloat: Merging dimension attributes directly into large fact tables (e.g., merging Dim_Customer attributes into 1,000,000 rows of Fact_Sales) forces repetitive string text (such as customer names and email addresses) into every fact row. This ruins VertiPaq run-length encoding (RLE) dictionary compression and balloons your file size.
Granularity Errors and Measure Corruption: Merging a table with a higher row count (1-to-Many) into a fact table duplicates transaction metrics, causing measures like SUM(SalesAmount) or COUNTROWS() to return inaccurate, double-counted figures.

Why Keeping Fact and Dimension Tables Separate is Preferable

Keeping fact tables (containing numerical events/metrics) separated from dimension tables (containing descriptive attributes) inside a Star Schema provides critical modeling advantages:

Optimal Compression: High-cardinality text fields are stored once in lightweight dimension lookup tables. Fact tables retain only narrow integer keys (CustomerKey, ProductKey), allowing the VertiPaq engine to compress millions of rows efficiently.
Flexible Analytics Across Shared Dimensions: A single standalone dimension table (e.g., Dim_Category) can simultaneously filter multiple fact tables (Fact_Sales and Fact_Targets), enabling side-by-side performance tracking against target KPIs.
Clean, Readable DAX: Keeping entities separate simplifies DAX measures, avoiding complex filtering logic required when working with oversized single-flat-table models.

Recommended Power BI Model

Merging tables in Power Query and establishing model relationships in Power BI serve fundamentally different functions within your analytical data pipeline.

Architecture Overview: Merge vs. Relationship

Key Structural Differences

Physical Data Combination in Power Query Merge: Yes, a Power Query Merge physically combines attributes into a single query structure during data transformation. When expanding columns from Dim_CustomerSecurity into Dim_Customer, Power Query reshapes the underlying M-code query and loads a single combined table into memory.
Separation via Model Relationships: No, creating a relationship between tables does not combine them physically. Both tables remain distinct physical storage objects in the VertiPaq engine. The relationship acts as a logical bridge allowing filter context to pass dynamically between them at visual rendering time.
Workflow Stage Execution:
- Power Query Merge: Occurs at the ETL (Extract, Transform, Load) stage before data is compressed and loaded into the tabular data model.
- Model Relationships: Defined in the Model View stage after data has been loaded into the VertiPaq engine, governing DAX calculation context and reporting visuals.

Core Comparison Matrix

Feature / Aspect	Power Query Merge Queries	Power BI Model Relationships
Primary Purpose	Denormalize, consolidate, or clean datasets	Build Star/Snowflake schemas for dynamic analytics
Storage Impact	Permanently increases table width (more columns/rows)	Keeps tables lean and distinct in VertiPaq memory
Execution Engine	Power Query Formula Engine (M)	VertiPaq Analytics Engine (DAX)
Dynamic Interactivity	Static (fixed during data refresh)	Dynamic (responds instantly to slicers and visual filters)

When to Choose a Merge vs. a Relationship

1. When to Use a Merge (Power Query)

Consolidating 1-to-1 Extensions: Merging extended attribute lookup tables (e.g., merging Dim_CustomerSecurity into Dim_Customer to avoid holding unnecessary table entities in your model).
Flattening Snowflake Dimensions: Collapsing multi-level dimension hierarchies (e.g., merging Dim_Category directly into Dim_Product to create a clean, single-level Product dimension table).
Pre-filtering Fact Rows: Joining reference keys to eliminate unwanted rows before loading data into the model.

2. When to Use a Relationship (Model View)

Connecting Fact and Dimension Tables: Linking Fact_Sales to Dim_Customer or Dim_Product.
Preserving Multi-Fact Models: Connecting shared dimensions (e.g., Dim_Category or Dim_Date) to multiple fact tables like Fact_Sales and Fact_Targets.

Impact of Excessive Merging on Data Model Performance

Severe Storage Bloat: Merging dimension attributes directly into large fact tables (e.g., merging Dim_Customer attributes into 1,000,000 rows of Fact_Sales) forces repetitive string text (such as customer names and email addresses) into every fact row. This ruins VertiPaq run-length encoding (RLE) dictionary compression and balloons your file size.
Granularity Errors and Measure Corruption: Merging a table with a higher row count (1-to-Many) into a fact table duplicates transaction metrics, causing measures like SUM(SalesAmount) or COUNTROWS() to return inaccurate, double-counted figures.

Why Keeping Fact and Dimension Tables Separate is Preferable

Keeping fact tables (containing numerical events/metrics) separated from dimension tables (containing descriptive attributes) inside a Star Schema provides critical modeling advantages:

Optimal Compression: High-cardinality text fields are stored once in lightweight dimension lookup tables. Fact tables retain only narrow integer keys (CustomerKey, ProductKey), allowing the VertiPaq engine to compress millions of rows efficiently.
Flexible Analytics Across Shared Dimensions: A single standalone dimension table (e.g., Dim_Category) can simultaneously filter multiple fact tables (Fact_Sales and Fact_Targets), enabling side-by-side performance tracking against target KPIs.
Clean, Readable DAX: Keeping entities separate simplifies DAX measures, avoiding complex filtering logic required when working with oversized single-flat-table models.

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

Njuguna Gichia — Wed, 02 Sep 2026 20:26:38 +0000

Microsoft Excel is a spreadsheet software that allows users to organize, analyze, calculate and visualize data. Despite the rise of Python, R and SQL, Excel has remained a foundational data analysis tool across industries because of it's low learning curve, powerful features like pivot tables, formulas, functions and quick charts and because no coding experience is needed to navigate or see results.

In this article, we will explore navigating the grid, essential formulas, data filtering and cleaning messy data using Excel's built-in features.

Understanding The Excel Interface

The excel interface consists of these core elements.

The Ribbon & Tabs
- The toolbar across the top that contains the primary commands organized into tabs (Home, Insert, Draw, Page Layout etc.)
The Quick Access Toolbar
- The icons on the top left corner to Save, Undo and Redo.
Formula Bar
- The area above the grid where the content or the formula of the selected cell appears.
Name Box
- Displays the address of the selected Cell (e.g. B3)
The Grid
- This is the visual network of intersecting horizontal lines and vertical lines that creates the workspace. The Grid is made up of
  - Rows - Horizontal lines of cells labelled vertically with numbers (e.g. Row 1)
  - Columns - Vertical line of cells labelled horizontally with letters (e.g. Column A)
  - Cells - A single box where a row and a column intersect (e.g. B4)
  - Range - A group of selected cells (e.g., A1:C3).
Worksheet Tabs
- The tabs at the bottom that show the name of the spreadsheet and also allow you to navigate between the sheets in a workbook.

Essential Navigation & Keyboard Shortcuts

Keyboard Navigation:

Arrow keys – Move one cell at a time
Ctrl + Arrow key – Jump to the end of the data region
Tab – Move one cell right
Enter – Move one cell down
Ctrl + S – Save the file

Mouse Navigation:

Click to select a single cell
Click & Drag to select multiple cells
Scroll to move vertically or horizontally across the sheet

Efficiency Combinations (Keyboard & Mouse):

Ctrl + Right Arrow to jump to the end of row
Ctrl + Down Arrow to jump to the bottom
Ctrl + Shift + Arrow Keys to select ranges efficiently

The Data Environment

Data Types.

Excel supports 4 primary data types. i.e.,

Text - Any non-numeric or character based information e.g. names, addresses, descriptions).
Numbers - Any values that can participate in mathematical calculations.
Dates and Time - Values used to track time-based events and calculations
Boolean - Logical values, either (TRUE or FALSE) usually generated from logical formulas.

Entering Data

We can enter data anywhere in the grid by:

Clicking on a cell
Typing in our value (e.g. Hello)
Press Enter to move down or Tab to move right
Double click on the cell or selecting it and pressing F2 to edit existing content. ### Formatting Basics. Formatting changes how the data looks without changing the values stored in the cells, making the data visually clear and more professional.

Text Formatting.

Selecting the Cell
Navigate to Home > **Font Group
Clicking Bold (Ctrl+B), Italic (Ctrl + I), Underline (Ctrl + U)
Change Font, Font Size, and Font Colour.

Number Formatting

Select cells with numeric data.
Navigate to Home > Number section
Choose the appropriate format:
- Number (with decimals)
- Currency (KES, $, etc.)
- Percentage
- Date formats

Alternatively, we can access the Format Cells Dialogue by (Ctrl+1)

Data Cleaning and Sorting.

Data Cleaning is the process of formatting, or removing incorrect, duplicate or incomplete data within a dataset. Sometimes, especially when working with large datasets, data can get misplaced, mistyped or mislabeled. Cleaning ensures that the dataset is accurate and consistent.

Data cleaning prevents making decisions based on flawed data, catches and fixes errors early and provides clean inputs for formulas and charts.

For Practice, I used a spreadsheet containing HR data, applying a combination of built-in excel tools and formulas to clean it.

Best Practice: Always preserve your original raw data. Make all required cleaning changes on a duplicate worksheet or separate file.

How to Duplicate Your Data

Click anywhere in the spreadsheet and press Ctrl + A to select all cells.
Press Ctrl + C to copy.
Click the + button at the bottom of the window to create a new sheet.
Click cell A1 in the new sheet and press Ctrl + V to paste.
Double-click the sheet tab and rename it to Cleaned_HR_Data.

This gives us a canvas to work in.

The Data Cleaning Workflow

We did the cleaning in distinct stages.

Analyzing the Data:

Here we checked the data for visible errors and inconsistencies. In this dataset, we found mixed data types in numeric fields (text mixed with numbers), missing/blank values, duplicate entries, currency formatting errors, typos and misspellings etc.
Removing duplicates:

The second step was to delete identical rows or entries that were recorded more than once. I achieved this by:
Selecting the entire spreadsheet using Ctrl+A
navigating to the Data Tab and Clicking Remove Duplicates.
Confirming the target columns and clicking OK

Fixing structural errors:

This step involved correcting typos, standardizing inconsistent capitalization (e.g., changing "Nairobi", "nairobi", and "NAIROBI" into one uniform format), merging mislabeled categories and trimming extra spaces in the text columns. Certain categories like employer ID also had inconsistent formatting.

Standardizing Codes:
Employer ID inconsistent formatting was remedied by selecting the column, then applying an auto filter using Shift+Ctrl+L and checking the Drop down to see the range of values I had. Then I selected the Column, and Ctrl+H to find and replace and replaced all EMP- with a Blank to fix the formatting.

Trimming Spaces and fixing Capitalization: TRIM() removes extra spaces between words. Name Entries were fixed by using =PROPER(TRIM(TEXT)) on the first and last name columns for both text length and proper case and then replacing the names in both columns using Paste (Values)

Handling missing values:

Deciding whether to filter out empty cells, fill them with zeroes, or input an average value.
Empty/missing values in the remaining text and numerical Columns were replaced by unknown converting them into unknown values using the Ctrl+H find and replace feature.

Standardizing formats:

Ensuring all dates followed a consistent format (e.g., YYYY-MM-DD) and numbers are converted from plain text into actual numeric formats.

Salary & Bonuses: Highlighted the numeric columns and pressed Ctrl + 1 to set the format to Currency (Ksh).

Dates: Selected the date column and set the formatting to DD/MM/YYYY.

To verify that we had successfully cleaned and validated our data, we applied auto filters Ctrl + Shift + L to the entire Spreadsheet and sorted each column to confirm that our categories were uniform, and our numbers were formatted correctly and whether we had any lingering errors. We then had a 'clean' dataset that was ready for analysis.

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

Njuguna Gichia — Sat, 22 Aug 2026 20:20:07 +0000

Two weeks ago, I joined a Data Science bootcamp and part of the learning outcomes for this week was independently navigating the Git Workflow to successfully upload a test project as part of the data portfolio to a GitHub repository.

Git is a version control system that runs locally on a personal computer and allows us to keep track of changes made to our projects. Git creates saved checkpoints knowns as commits that act as snapshots of the state of the projects.

GitHub is an online service where we can store Git repositories. While Git is installed and works locally on our personal computers, GitHub stores our repositories online and allows us to share and collaborate on projects

My project was a custom energy consumption segmentation analysis using k means. Other than local python, this project required additional installation of pandas, numpy for data processing and manipulation, scikit-learn for preprocessing, and matplotlib and seaborn for the data visualization.

The Git workflow happened in 4 distinct stages.

The Working Directory.

The working directory is the folder containing my project files where the terminal or Git is currently working from. These include:
- README.md file which is a description of the entire project written in raw markdown. It contains information about the project such as:
- What the project is about
- What dataset was used
- The tools used
- The analysis was performed
- Contents of each folder
- Important findings
- How another person can understand the project
- Data Folder which contains an excel spreadsheet named energy_consumption_data.csv that contained the consumer data.
- Scripts Folder which contains a K means python script named segmentation.py. Python script files typically end with the .py extension.

To create a working directory using Git Bash, I opened Gitbash on Desktop by clicking on the windows icon on the taskbar, typing in bash and clicking on Gitbash to open a new window. Once the window was open, I ran cd Desktop, to navigate to my Desktop where I created a new Folder Titled energy_consumption_analysis by running the command

mkdir energy_customer_data_analysis

The command cd means change directory and it allows you to navigate in and out of directories. cd allows you to enter a folder while cd .. allows you to move backward by exiting a directory or a folder into the parent directory. Inside energy_customer_data_analysis, I created two folders Data and Scripts simultaneously by running the mkdir command which allows you to create 2 folder simultaneously by listing two folder names separated by a space:

mkdir Data Scripts

command created two folders named Data and Scripts.

I also created the README.md file by running touch README.md in bash.

to make sure that I had created all the files and folders, I ran the ls command in bash which allowed me to see all the files and folders created in the working directory.

I copied the CSV file from my downloads into the Data folder then used bash to navigate into the Scripts folder by running cd Scripts since I was still inside my project folder. once inside the Scripts folder, I ran touch segmentation.py to create the python script file and then ran code . in bash to then open segmentation.py in VS Code.

After writing the python code in VS Code, I went back to bash and ran cd .. to exit the scripts folder and ran nano README.md to open the README file in Bash's text editor nano where i wrote the description for the project in Markdown. Once this was complete, Ctrl + O, then Enter to save then Ctrl + X to go back to bash. To see what's inside the README file, use cat README.md in bash to display text inside the README.md file and ls to inspect the project folder. Once satisfied with everything, we then move on to the next step.

The Staging Area.

The staging area is the preparation area for the next commit. Here, we Initialize Git, check changes and stage files. Before running any git commands, we check our location by running pwd . Pwd means print working directory and allows us to see the exact location we are working from.

Initialize git

Once we ascertain that we are in the correct project folder, we can initialize git by running the command

git init

which instructs Git to begin managing the project. When run successfully, we should see : `Initialized empty Git repository in (the working directory path)

We also run ls -la which allows us to see a .git folder which acts as Git's internal memory for the project.

Check Project Status

We check project status by running

git status

This allows git to show us exactly what is happening in our project and because this is a completely new project and repository that git has not started tracking, our project status is untracked.

Project Staging

We can add individual files in our directory by running git add <filename> to select the individual file or we can run git add . to add everything in the current directory. This moves the selected files from the working directory into the staging area.

We can check the status of our project by running git status and if the staging is successful, our selected files and folders should be listed in green.

The Commit.

We create the first commit by running

git commit -m "Add Energy Consumption Segmentation Analysis Project"

git commit creates a saved Git checkpoint while -m means the message we want to attach to the commit which in this case is "Add Energy Consumption Segmentation Analysis Project"

The message explains what was saved. A good commit message creates an understandable history of the project.

Commit History

We can view the commit history by running

git log

which shows us the commit history or by running:

git log --oneline

for a much shorter commit history.

Creating the Github Repository.

We can create a new repository in GitHub through our browser.
Go to Github.com
Sign in.
Once Signed in Click the + button.
Select: New repository
Enter Repository Name(e.g., energy_consumer_data_segmentation_analysis)
You can also Add an optional description such as:(energy_consumer_data_analysis_using_Python)
Choose between a Public or Private Repository (keep in mind that a public repository can generally be viewed by anyone and should not be used for sensitive or confidential data. In our case, the data is synthetic and non-specific so a public repository is fine.)
Leave options such as: Add README, Add .gitignore, and Add license unchecked.
Select Create New Repository.

After creating the repository, navigate to the connection options, Select SSH if you have it already configured.

The Push.

Pushing means sending committed changes on our projects to GitHub.

After copying the SSH string, return to Git Bash. To check whether we are still in the project, run

pwd

If still in the folder, run:

git remote add origin git@github.com:USERNAME/project-name.git

USERNAME: is your GitHub Username
Project-name.git: is the name of the repository you've just created.

type git remote add origin and then press Shift + Insert on your keyboard to paste the SSH string you've just copied from GitHub to complete the command and then run it.

Checking the Remote.

A remote is a connection between our local repository and another repository which is in our case, the one on GitHub.

We check the remote by running:

git remote -v

origin git@github.com:USERNAME/project-name.git (fetch)

means that Git can retrieve information from the remote repository

origin git@github.com:USERNAME/project-name.git (push)

means that git can send commits to the remote Repository.

Pushing the Code

Pushing means sending the committed changes in my project to the Remote Git Repository.

we do this by running:

git push -u origin main
Bash will prompt you to enter the configured passphrase. if you have one, type it in and then Enter

Verifying the Push

Return to the repository page on your browser and refresh it.
GitHub should render the contents in your README.md file visually.
You should also be in a position to see your files and the contents in those files (i.e the CSV file in the Data folder and the .py file in the Scripts folder).

If we can see this, then our project has successfully been pushed to GitHub.

DEV Community: Njuguna Gichia

Data Modelling, Relationships & Joins in Power BI

Data Modelling Approaches.

1.Flat Table.

2. Star Schema

3. Snowflake Schema

Fact Tables & Dimension Tables.

Fact Tables.

Dimension Tables.

Granularity.

Relationships in Power BI

Core Data Modeling Concepts

Example from Snowflake-Schema-Main

Relationship Cardinalities

1. One-to-Many (1:*)

2. One-to-One (1:1)

3. Many-to-Many (:)

Filter Direction.

How Filter Context Flows

1. Single-Direction Filtering (Default & Standard)

2. Bidirectional / Both Filtering

Step-by-Step Example: Filtering Fact_Sales from Dim_Product

Why Bidirectional Filtering Should Be Used Carefully

Joins in Power Query.

Source Sample Tables from Our Model

1. Left Outer Join (All from First, Matching from Second)

2. Right Outer Join (All from Second, Matching from First)

3. Full Outer Join (All Rows from Both Tables)

4. Inner Join (Only Matching Rows)

5. Left Anti Join (Rows Only in First Table)

6. Right Anti Join (Rows Only in Second Table)

Power Query Joins vs Power BI Relationships

Key Structural Differences

Core Comparison Matrix

When to Choose a Merge vs. a Relationship

1. When to Use a Merge (Power Query)

2. When to Use a Relationship (Model View)

Impact of Excessive Merging on Data Model Performance

Why Keeping Fact and Dimension Tables Separate is Preferable

Recommended Power BI Model

Architecture Overview: Merge vs. Relationship

Key Structural Differences

Core Comparison Matrix

When to Choose a Merge vs. a Relationship

1. When to Use a Merge (Power Query)

2. When to Use a Relationship (Model View)

Impact of Excessive Merging on Data Model Performance

Why Keeping Fact and Dimension Tables Separate is Preferable

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

Understanding The Excel Interface

Essential Navigation & Keyboard Shortcuts

Keyboard Navigation:

Mouse Navigation:

Efficiency Combinations (Keyboard & Mouse):

The Data Environment

Data Types.

Entering Data

Text Formatting.

Number Formatting

Data Cleaning and Sorting.

How to Duplicate Your Data

The Data Cleaning Workflow

Analyzing the Data:

Removing duplicates:

Fixing structural errors:

Handling missing values:

Standardizing formats:

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

The Working Directory.

The Staging Area.

Initialize git

Check Project Status

Project Staging

The Commit.

Commit History

Creating the Github Repository.

The Push.

Checking the Remote.

Pushing the Code

Verifying the Push

Step-by-Step Example: Filtering `Fact_Sales` from `Dim_Product`