Understanding Data Modeling in Power BI: Joins, Relationships and Schemas Explained.

Sharon Chepkemoi — Mon, 30 Mar 2026 14:51:31 +0000

1.What is data modeling?
Data modeling is the process of structuring and organizing Data to make is easily accessible for reporting in power BI. It involves connecting tables, defining relationships, cleaning and shaping data.
Organizes data into a structured format.

2.SQL Joins in Power BI (power query)
Powe BI enables SQL like joins via merge queries to create a physical table. These are essential for data cleaning. Combine tables based on specific requirements.
INNER JOIN: Keeps only rows that have matching values in both tables.
Example: joining employee table with department table. Only employees assigned to a valid department are returned.
LEFT JOIN: Keeps all rows from the left table and matches from the right. No match it returns null.
Example; Joining sales table with product table. All sales products are kept and product details added and id a sale was made for a product not in the product table, the product name will be null.
RIGHT JOIN: Keeps all rows from the right table and matches from the left.
Example; Join customer table with sales. All sales are kept and if a sale exists with no corresponding customer, then it is replaced with null.
FULL OUTER JOIN: Keeps all rows from both tables matching where possible, filling gaps with nulls.
LEFT ANTI JOIN: Keeps rows from the left table that don’t exist in the right.
RIGHT ANTI JOIN: Keeps rows from the right that don’t exist in the left.

3.Power BI Relationships
Connects tables allowing them to communicate without physically merging them.
Cardinality: Describes the nature of relationships.
One to Many (1:M): One row relates to many rows.
Many to Many (M:M): Both sides have duplicates.
One to One (1:1): Matches unique rows. Used when splitting large tables.
Active vs Inactive relationships: Only one can be active (solid line). Use DAX (USERELATIONSHIP) for the inactive (dotted line).
Cross Filter Directions: controls filters.
Single: Dimension filters the fact table.
Both: Both tables filter each other.
4.Difference Between Joins and Relationships
Joins merge tables during data loading permanently while Relationships keep the tables separate but connected for dynamic filtering based on visuals.
Joins are used for data cleaning while Relationships are used for reporting and slicing.
Joins are created in power query while relationships are created in model view in power BI.
5.Facts vs Dimension tables
Fact tables: Contain quantitative, numeric data e.g. sales. Have many rows
Dimension table: Contains descriptive, categorical context e.g. “who”, “why”, “what”, product name. Have many columns.
6.Data Schemas
Star schema: Central fact table connected to dimension tables.
Example; A sales fact table linked to product table, customer table.

Snowflake schema: Dimension tables are split into multiple tables, normalizing the data.
Used when dealing with highly hierarchical data (complex product catalogs).

Flat table (DLAT): All data in one table. Simple but slow and inefficient. No joins needed.
7.Role Playing Dimensions
A single dimension serving multiple roles in the fact table.
Example; date dimension used for both harvest and planting dates.
8.Common Data Modeling Issues
Circular relationships between tables
Mismatched keys in merges that leads to missing data
Ignoring inactive relationships leads to incorrect totals
Doing too much transformation in DAX instead of power query
Poorly defined entities.
9.Step by Step Power BI Workflow
Get data: Open power BI desktop and use “get data” to connect to various sources.
Transform data to open and clean data in power query editor
Model data (data view): Switch to model view to establish relationships between tables.
Define: Create necessary DAX measures. Use DAX to build calculated columns and measures (e.g. SUM, AVERAGE, CALCULATE).
Visualize data (Report view): Build interactive reports by dragging fields onto the canvas, selecting chart types and formatting visuals.

How Excel is used in Real World Data Analysis

Sharon Chepkemoi — Mon, 30 Mar 2026 14:50:59 +0000

What is Excel?
Excel is a spreadsheet application that allows organizing, manipulating, calculating and visualizing data. Uses grid od rows and columns. Each grid is a cell that holds data.
How is Excel Used in the Real world
Excel is used across industries including finance, healthcare, marketing and retail. Common real-world applications;
Sales analysis: Tracking product performance, revenue and discounts.
Financial reporting: Budgeting, forecasting and expense tracking
Project management and planning: Create timelines, track tasks, and allocate resources.
Customer relationship management: Small businesses use excel to store, organize and analyze customer information and sales inquiries.

Data Cleaning
This involves removing inconsistencies, duplicates and errors to prepare data for analysis.Real world data features missing values, duplicates and inconsistent formatting. Here are the steps that I worked through:
Back up data: Created a copy of the original dataset
Removing duplicates: Using data tab and removed duplicates
Fixing data types: converting price columns from texts to numbers
Handling missing values: on the ribbon is the find and replace(Ctrl + H) where you replace blank cells with the appropriate data
Extracting numbers from texts (e.g. “4.5 out of 5” to 4.5)
Use TRIM to remove spaces

Formulas and Features learnt for Data Transformation
Excel formulas are essential for transforming raw data into usable formats.
Aggregate functions (SUM, AVERAGE, MIN, MAX, COUNT)
Categorizing data with IF function
=IF(b2>4, “excellent”, IF(b2>2, “average”, “POOR”))
LOOK UP functions. (VLOOK UP, XLOOK UP): They search for a specific value in a table and return corresponding data.
Pivot Tables for Data Analysis
Pivot tables are used to summarize and analyze data.
With pivot tables, you can compare categories such as discount category vs number of reviews, count products and calculate averages.
For Example; you can create a pivot table to find average rating by discount category

Excel allows users to create interactive dashboards using charts and slicers.
Common charts used: Bar charts, Column charts, Pie charts, Donut charts, scatter plots.
Example;
Analyze the relationship between discount percentage and number of reviews.

Adding a trendline helps identify whether it’s a positive, negative or no relationship between the variables.

Building Interactive Dashboards
A dashboard is a visualization tool for summarizing key performance indicators (KPIs) and data trends.
Key elements of an excel dashboard
KPIs: Total products, Average rating, Average discount percentage, Total reviews
Pivot tables
Pivot charts
Slicers; allows users to filter data dynamically, making the dashboard interactive

Conclusion
Learning excel has changed my approach to data, from overwhelming intimidating numbers to a clear, structured one. I now understand that data must be cleaned, organized and standardized before analyzing. Excel has trained my mind to think logically, like why did this happen. Tools like conditional formatting helps to spot outliers, while pivot tables help summarize large datasets. Understanding function has increased my confidence in data accuracy.

DEV Community: Sharon Chepkemoi

Understanding Data Modeling in Power BI: Joins, Relationships and Schemas Explained.

How Excel is used in Real World Data Analysis