DEV Community: cheryl chebet

Understanding Data Modeling in Power BI: Joins, Relationships, and Schemas Explained Using Kenya Crops Data

cheryl chebet — Sun, 29 Mar 2026 19:51:00 +0000

Data modeling is the foundation of building accurate and high-performing reports in Power BI. It helps organize raw data into structured tables, define relationships, and ensure that calculations, filtering, and aggregations are correct. Using the Kenya_Crops dataset, we can explore joins, relationships, schemas, and common modeling practices in a practical, real-world scenario.

1. What is Data Modeling?

Definition: Data modeling is the process of designing how data is structured, connected, and used for analysis.
Purpose: Ensures efficient reporting, accurate calculations, and easy data maintenance.
Power BI components: Fact tables, dimension tables, relationships, calculated columns, measures, and hierarchies.

Example: In Kenya crops data:

Power Query showing fact table after transformation

2. Fact vs Dimension Tables

Fact Tables: Contain numeric, transactional data. In Kenya crops:
Kenya_Crops_Cleaned_Final with metrics: Yield (kg) and Price (KES).
Contains foreign keys: CountyID, CropID.
Dimension Tables: Contain descriptive attributes for slicing facts.
County → CountyID, CountyName
Crop → CropID, CropName, Category

Use Case: To analyze total crop yield by county or crop category.

3. SQL Joins in Power BI (Power Query)

Power BI allows SQL-like joins via Merge Queries:

INNER JOIN: Keeps only matching rows. Example: counties that have crops recorded.
LEFT JOIN: Keeps all counties, even those without crops.
RIGHT JOIN: Keeps all crops, even if not grown in a county.
FULL OUTER JOIN: Keeps all counties and crops, filling gaps with NULLs.
LEFT ANTI JOIN: Finds counties with no crops recorded.
RIGHT ANTI JOIN: Finds crops not grown in any county.

4. Power BI Relationships

One-to-Many (1:M): County → multiple rows in Kenya_Crops data.
Many-to-Many (M:M): Rare, but can occur if crops belong to multiple categories.
One-to-One (1:1): Each CropID in Crop table matches a unique row in fact table.
Active vs Inactive relationships: Only one relationship can be active per pair; use DAX (USERELATIONSHIP) for the inactive one.
Cross-filter direction:
Single: Filters flow from dimension to fact (default).
Both: Filters flow both ways; useful in M:M but may reduce performance.

5. Difference Between Joins and Relationships

Joins: Combine tables into a single table during data load in Power Query.
Relationships: Keep tables separate but connected for dynamic filtering in visuals and calculations.
Practical Advice: Use joins for data cleaning or enrichment; use relationships for reporting and slicing.

6. Data Schemas

Star Schema: Fact table (Kenya_Crops_Cleaned_Final) at center, dimensions (County, Crop, Region) surrounding it. Simple, fast, easy to understand.
Snowflake Schema: Dimension tables normalized. Example: County → Region → Zone. Less redundancy but more complex.
Flat Table / DLAT: All data in one table. Simple, but large datasets may slow refresh and reporting.

7. Role-Playing Dimensions

Scenario: Using a Date dimension for both PlantingDate and HarvestDate.

8. Common Data Modeling Issues

Circular relationships between tables.
Mismatched keys in merges → missing data.
Too many M:M relationships → slow performance.
Ignoring inactive relationships → incorrect totals.
Overly denormalized tables → heavy dataset, slow refresh.

9. Step-by-Step Power BI Workflow with Kenya Crops

Import Data: Get Data → load Kenya_Crops, County, Crop.
Transform Data in Power Query: Clean missing values, merge tables using LEFT JOIN.
Define Relationships: Model View → Manage Relationships → set cardinality, active/inactive, and, cross-filter direction.
Create Calculations: Measures like Total Yield = SUM(Kenya_Crops_Cleaned_Final[Yield])
Check Model: Look for orphan tables, circular references, and inactive relationships.
Visualize: Use slicers, maps, and, bar charts to analyze yields by county, crop, or region.

Conclusion
In short, building a clean and well-structured data model in Power BI turns raw data into meaningful insights. With the right fact and dimension tables, joins, relationships, and schema design, you can create dashboards that are accurate, fast, and easy to understand, helping you make smarter decisions with your data.

How Excel is Used in Real-World Data Analysis

cheryl chebet — Wed, 25 Mar 2026 08:24:28 +0000

How Excel is Used in Real World Data Analysis

Introduction

Excel is used across multiple industries including finance, marketing, healthcare, and retail. Some common real-world applications include:

Sales Analysis: Tracking product performance, revenue, and discounts
Customer Insights: Analyzing ratings, reviews, and behavior patterns
Financial Reporting: Budgeting, forecasting, and expense tracking
Operational Monitoring: Measuring KPIs and performance metrics

For example, in an e-commerce setting, Excel can be used to analyze product pricing, discount strategies, customer reviews, and ratings to guide business decisions.

Data Cleaning in Excel

Before analysis, data must be cleaned to ensure accuracy and consistency. In real-world datasets, data is often messy and unstructured.

Some common data cleaning tasks include:

Converting text values into numeric formats (e.g., "34%" to 34%)
Extracting numbers from text (e.g., "4.5 out of 5" to 4.5)
Handling missing values using functions like IF
Removing duplicates and correcting inconsistencies For example:

=VALUE(LEFT(A2, LEN(A2)-1))

This formula converts percentage values stored as text into numeric format.

Using Formulas for Data Transformation

Excel formulas are essential for transforming raw data into usable formats.

Example 1: Extracting Ratings

=VALUE(LEFT(A2, FIND(" ", A2)-1))

This extracts the numeric rating from text like "4.5 out of 5".

Example 2: Categorizing Data with IF

=IF(A2<3,"Poor",IF(A2<=4.4,"Average","Excellent"))

This categorizes ratings into meaningful groups.

Example 3: Handling Missing Data

=IF(A2="","Missing",A2)

This helps identify or replace missing values.

Pivot Tables for Data Analysis

Pivot tables are one of Excel’s most powerful features. They allow users to summarize and analyze large datasets quickly.

With pivot tables, you can:

Calculate averages (e.g., average rating or discount)
Count products or transactions
Compare categories such as discount levels or rating groups

For example, you can create a pivot table to find:

Average rating by discount category
Total number of reviews per product
Data Visualization and Dashboards

Excel allows users to create interactive dashboards using charts and slicers.

Common Charts Used:
Bar Charts: Compare product performance
Pie/Donut Charts: Show distribution of categories
Scatter Plots:Analyze relationships between variables

Example: Trend Analysis

Scatter plots can be used to analyze relationships such as:

Discount vs Number of Reviews
Rating vs Customer Engagement

Adding a trendline helps identify whether there is a positive, negative, or no relationship between variables.

Building Interactive Dashboards

A well-designed dashboard typically includes:

Key Metrics (KPIs): Total products, average rating, average discount, total reviews
Performance Charts: Top products by rating, reviews, or discount
Trend Analysis: Visual relationships between variables
Category Breakdown: Distribution by rating and discount categories

Slicers can be added to allow users to filter data dynamically, making the dashboard interactive and user-friendly.

Personal Reflection

Learning Excel has significantly changed the way I understand and interpret data. Previously, I viewed data as just numbers, but now I see it as a source of insights and decision-making.

Through working with real datasets, I have learned how to clean data, apply formulas, build pivot tables, and create dashboards that tell a story. I now approach problems more analytically and pay closer attention to patterns and relationships within data.

Excel has not only improved my technical skills but also strengthened my ability to think critically and make data-driven decisions.

Conclusion

Excel remains a powerful and relevant tool in real-world data analysis. From cleaning and transforming data to building dashboards and uncovering insights, it provides everything needed to turn raw data into meaningful information.

As I continue learning, I look forward to applying these skills in real-world scenarios and expanding into more advanced tools in data analytics.