DEV Community: Leonard Mwilu

Data Modelling, Relationships & Joins in Power BI

Leonard Mwilu — Mon, 14 Sep 2026 18:02:15 +0000

Data Modelling in Power BI

Data modelling in Power BI is the process of connecting separate data sources, defining logical relationships between entities, establishing directional filter propagation paths and configuring metadata to transform raw transactional records into an analytical engine.
Data Modeling helps in:

Reporting & Analytics: Enables seamless aggregation across diverse business dimensions without requiring complex manual query rewrites.
DAX Calculations: Simplifies DAX expression logic, reducing the reliance on filter-overriding functions like CALCULATE, FILTER, or CROSSFILTER.
Scalability & Maintainability: Allows effortless additions

Comparison of Architectural Schemas

Introduction

Schema refers to the logical structure of a database or data model that defines how tables are organized and related. In Power BI, schemas are used to optimize data storage, retrieval, and reporting.
There are three primary types of schemas commonly used in Power BI:

Flat Table Schema

This is a single denormalized data table containing all transactional attributes, metrics, keys, and lookup descriptors within one wide structure. It organizes all information, such as transactions, customer names, and product details, into a single, wide table with no external relationships.

Structure: All transactional measures and descriptive text fields are repeated for every row.
Advantages: Very simple to set up, requires zero data modeling effort and works well for small datasets.
Disadvantages: Massive data redundancy, bloated file sizes and high risk of update anomalies.
Appropriate For: Increases memory consumption and slows down performance on large datasets because repeated text bloats file size.
Performance & Complexity: Very low structural complexity but severely degrades performance and memory efficiency as row counts grow.
A Flat Table Schema is as below:

Star Schema

A star schema is a more advanced approach to structuring and organizing quantitative, or measurable, data in Power BI. It allows for multiple tables to be connected through one central table, known as the fact table. The fact table is linked to dimension tables that contain records such as customers, employees, dates, and marketing campaigns etc.

Advantages of using Star Schema in Power BI

Reduces Data Redundancy: By separating facts and dimensions into distinct tables.
Boosts Query Performance: More efficient querying and aggregation of data, as dimension tables are typically smaller and can be indexed to improve performance.
Easy to Understand: The fact table serves as the focal point, and all other tables are linked to it through clear relationships

Disadvantages of using Star Schema in Power BI

Lacks Flexibility: Actions like adding new dimensions or modifying existing ones may require extensive changes to the schema.
Struggles with Complexity: May not be suitable for handling complex relationships between dimensions.

Star schema is structured as shown below:

Snowflake Schema

A snowflake schema is an extension of the star schema. It breaks down the dimension tables into multiple related tables, forming a hierarchical structure that resembles a snowflake. This process, known as normalization, reduces data redundancy and improves data integrity.

Advantages of using Snowflake Schema in Power BI

Efficient Storage: Reduced data redundancy through normalization.
Improved Data Integrity: Better data integrity due to more focused tables.
Offers Scalability: Greater flexibility in handling complex relationships between dimensions, as new tables can be easily added to accommodate new relationships.

Disadvantages of using Snowflake Schema in Power BI

Complex Data Analysis: More difficult to perform data analysis because of the extra relationships.
Challenging to Understand and Manage: The schema is harder to understand and maintain due to its complexity.
Slower Queries: Multiple join operations may be required to retrieve data from the related tables, resulting in slower query performance.

Snowflake schema structure is as in the sample image below.

Fact Tables and Dimension Tables

Separating tables into Facts and Dimensions is the foundation of dimensional modeling. Fact tables capture business measurements, events, and transactions, while Dimension tables store the context, descriptive attributes, and filtering hierarchies.

Fact tables consists of the measurements, metrics, or facts of a business process. In other words, they hold quantifiable measurable data. They represent numerical business actions that can be summed, averaged, or counted. Example: order id, product id, product price, quantity.
Dimension tables are typically textual fields and provide descriptive attributes related to fact data. They contain textual attributes, categorical groupings, hierarchies and primary surrogate keys. Dimension tables are linked to the fact table and include things such as date, employee, sales, and product data.

The below image shows a practical example of how a single Fact Table can be linked to several Dimension Tables.

Relationships in Power BI

Relationships establish logical connections between physical tables in the data model based on matching key columns. They govern how filter context propagates across tables when report visuals evaluate.

Relationship Cardinalities

One-to-Many (1:) / Many-to-One (:1) A single distinct key value in the dimension table maps to zero, one, or many identical key instances in the fact table. Practical Example: DimCustomerCustomerKey connected to FactSalesCustomerKey. This is the recommended primary relationship pattern for analytical modeling.

One-to-One (1:1) A single row in Table A maps to exactly one corresponding row in Table B. Practical Example: DimUser[UserID] connected to DimUserProfile[UserID]. Usage: Rarely needed. One should prefer merging both tables into a single table in Power Query, unless separating highly sensitive security attributes.

Many-to-Many (:) In this kind of relationship, neither table contains unique key values; keys repeat across both sides of the relationship. Practical Example: Linking sales targets set at a high product category level directly to transactional sales containing product line items. Usage guidance: It is advisable to avoid this relationship where possible. It introduces non-deterministic visual calculations. Use a table with two 1:* relationships instead.

Key Relational Concepts

Primary Key (PK): A column in a dimension table containing strictly unique values that definitively identify each row (e.g., DimCustomer[CustomerKey]).
Foreign Key (FK): A column in a fact table that references a Primary Key column in a dimension table (e.g., FactSales[CustomerKey]).
Unique Values: A strict requirement for the '1' side of any 1:* relationship; duplicate values on the 1 side, trigger modeling errors.
Referential Integrity: Ensures that every foreign key value in a fact table correctly matches an existing primary key in the corresponding dimension table.
Active vs. Inactive Relationships: Only ONE active relationship path can exist between two tables to prevent ambiguous paths. Additional relationships (e.g., FactSales[OrderDate] vs FactSales[ShipDate] linking to DimDate[Date]) must remain inactive and activated dynamically using USERELATIONSHIP() in DAX.

Filter Direction & Propagation

When a user selects an item in a slicer or visual, Power BI establishes a Filter Context. This filter automatically propagates along active relationships between related tables.

Single-Direction vs. Both/Bidirectional Filtering

Single-Direction Filtering (1 -> *): Filters propagate exclusively from the '1' side (Dimension) to the '*' side (Fact). Selecting DimProduct[Category] = 'Bikes' automatically filters FactSales down to rows containing bike sales. Selecting values in FactSales does NOT filter DimProduct.
Both / Bidirectional Filtering (<->): Filters propagate in both directions across the relationship. Filtering FactSales automatically filters DimProduct, and vice versa.

Risks of Bidirectional Filtering: Bidirectional relationships can create circular, ambiguous filter paths in non-trivial models. They cause unexpected measure calculation results, prevent efficient query plan caching, and significantly degrade execution performance. The best practice is to keep relationships single-direction and activating cross-filtering explicitly via DAX (CROSSFILTER) only when required.

Joins in Power Query

Power Query utilizes Merge Queries to physically combine two tables side-by-side during the ETL extraction and transformation phase. Rows are matched based on specified key columns from both tables.

There are several types of Joins and to explain them better it would be good to use a practical example for ease of understanding and reference.
Practical Scenario Datasets
Consider two sample tables, Customers (Left Table) and Orders (Right Table) as show in the image below:

Join Type Breakdown:

Left Outer Join: Retains ALL rows from the Left table (Customers) and matching rows from the Right table (Orders). Unmatched right attributes appear as null.
Right Outer Join: Retains ALL rows from the Right table (Orders) and matching rows from the Left table (Customers). Unmatched left attributes appear as null.
Full Outer Join: Retains ALL rows from BOTH tables. Unmatched rows on either side are filled with null values.
Inner Join: Retains ONLY rows where matching keys exist in BOTH tables (e.g., CustomerID 1 and 2).
Left Anti Join: Retains ONLY rows from the Left table that have NO match in the Right table (e.g., CustomerID 3 - Charlie).
Right Anti Join: Retains ONLY rows from the Right table that have NO match in the Left table (e.g., OrderID 103 - CustID 4).

Power Query Joins vs Power BI Relationships

Understanding the architectural distinction between merging tables in Power Query and creating relationships in the Power BI Data Model is essential for efficient Power BI design.

Recommended Power BI Architectural Standard

After doing deep research, I strongly recommend a Star Schema utilizing Single-Direction One-to-Many (1:) relationships.
**Justification*:

Query & Report Performance: Maximizes VertiPaq columnar dictionary encoding and segment compression, ensuring visuals render in sub-seconds.
DAX Simplicity: Measures evaluate over clean, single-direction filter propagation paths, eliminating the need for complex filter-overriding functions.
Model Readability: Provides an intuitive visual structure where fact tables sit in the center surrounded by clean dimension lookup tables.
Scalability: New transactional fact tables (e.g., FactBudget or FactForecast) can immediately integrate with existing dimensions without refactoring.
Maintainability: Prevents circular relationship paths, ambiguous filter routes, and unnecessary memory consumption.

Learning is a continuous thing and I am learning, leave a comment on sites I can use to learn in this journey of becoming a Data Scientist

Jumia Product Performance & Pricing Dashboard

Leonard Mwilu — Sat, 05 Sep 2026 17:47:07 +0000

This is an end-to-end Jumia e-commerce data analysis and interactive Microsoft Excel dashboard project; analyzing product pricing, promotional discounts, customer ratings, and engagement metrics for Jumia listings.

Project Overview

Jumia and its marketplace sellers require data-driven insights into how pricing strategies, promotional discounts, and customer ratings impact overall engagement. Because direct sales volume and revenue figures are not present in the dataset, in the provided dataset I assumed that review counts indicated customer engagement.

In this project I processed raw Jumia product data, performed data quality audits, standardized messy records, enriched the dataset with logical categories and performance flags, evaluated linear correlations, and build an interactive, single-screen Microsoft Excel dashboard.

Core Questions I used to drive my Analysis

Are larger promotional discounts associated with higher customer engagement (reviews)?
Do highly rated products attract stronger customer interaction?
Do current price points move together with average customer ratings?
Which products represent top catalog performers versus underperforming promotional risks?
What specific pricing or marketing adjustments should sellers execute?

PivotTable and analysis workflow

Data Audit Summary

Total Raw Records: 115 rows
Duplicate Records: 3 full duplicate rows identified and removed (reducing clean catalog count to 112 unique products).
Price Ranges: Evaluated and converted to midpoints on row 39 for consistent numeric analysis while preserving auditable source text.
Missing Values: 58 missing values in ratings and reviews were treated as "uncaptured data" rather than converted to zeros. Below is a snippet of the audit summary.

Data Cleaning & Transformation Rules

All data transformations were executed cleanly in Cleaned_Data (tblProducts) and documented in the workbook Data_Dictionary:

Product Name: Standardized using =TRIM().
Prices: Cleaned of text characters (KSh, commas) and converted to numeric currency (KSh #,##0.00). Range values were resolved using midpoints: (Min + Max) / 2.
Discounts: Stripped of % signs, converted to decimals, and formatted as Percentage (0%).
Reviews: Converted negative values to absolute whole numbers via =ABS().
Ratings: Extracted numeric values by removing " out of 5" and converted to decimals.

Calculated & Enriched Fields

Discount Amount: Old Price - Current Price
Price Category:

Low Price: KSh 493.00 (First Quartile)

Medium Price: KSh 493.01 – KSh 1,669.50 (First Quartile to Third Quartile)

High Price: > KSh 1,669.50 (Third Quartile)
Discount Category: Low (<20%), Medium (20% - 40%), High (>40%)
Rating Category: Poor (<3.0), Average (3.0 - 4.5), Excellent (>4.5)

Excel formulas I used

I used several Excel formulas in this project and below is a list of most of the Formulas.

=VALUE(SUBSTITUTE(SUBSTITUTE(B2,"KSh",""),",",""))
=VALUE(SUBSTITUTE(D2,"%",""))/100
=IF(E2="","",ABS(VALUE(E2)))
=IF(F2="","",VALUE(SUBSTITUTE(F2," out of 5","")))

Total products  =ROWS(tblProducts[Product])     
Average current price   =AVERAGE(tblProducts[Current Price])        
Average old price   =AVERAGE(tblProducts[Old Price])        
Average discount    =AVERAGE(tblProducts[Discount])     
Average rating  =AVERAGE(tblProducts[Rating])       
Total reviews   =SUM(tblProducts[Review])       
Most expensive price    =MAX(tblProducts[Current Price])        
Least expensive price   =MIN(tblProducts[Current Price])        

Discount Amount: =[@[Old Price]]-[@[Current Price]]     
Rating Category: =IF([@Rating]="","Missing",IF([@Rating]<3,"Poor",IF([@Rating]<=4.5,"Average","Excellent")))

Discount Category: =IF([@Discount]="","Missing",IF([@Discount]<0.2,"Low Discount",IF([@Discount]<=0.4,"Medium Discount","High Discount")))

Price Category =IF([@[Current Price]]="","Missing",IF([@[Current Price]]<=Price_Q1,"Low Price",IF([@[Current Price]]<=Price_Q3,"Medium Price","High Price")))

Validation Check : =IF(OR([@Rating]<0,[@Rating]>5),"Check rating",IF(OR([@Discount]<0,[@Discount]>1),"Check discount",IF([@[Current Price]]>[@[Old Price]],"Check prices","OK")))       

=CORREL(tblProducts[Discount],tblProducts[Review])
=CORREL(tblProducts[Rating],tblProducts[Review])
=CORREL(tblProducts[Current Price],tblProducts[Rating])

=QUARTILE.INC(tblProducts[Current Price],1)
=QUARTILE.INC(tblProducts[Current Price],3)

=IF([@[Current Price]]="","Missing",IF([@[Current Price]]<=Price_Q1,"Low Price",IF([@[Current Price]]<=Price_Q3,"Medium Price","High Price")))

Below is a snippet image of the cleaned data table (tblproducts) achieved after data cleaning and addition of relevant columns(Enriched Fields).

After completing clean-up and analysis these are the total sheets I had in my Excel workbook.

I finally generated PivotTables and charts which I later used to build my dashboard. Below is an image of the PivotTables in one sheet.

Using this PivotTables I was able to generate Pie Chart and Column Charts for ease of building the Dashboard.

Dashboard design and slicer connections

To build a seamless, audit-ready presentation, I designed the Dashboard tab as a single-screen analytical hub that isolates reporting from underlying data logic.
The visual layout features high-level KPI cards, dynamic charts, and assumptions & recommendations sections, all driven by interactive Slicers connected across multiple PivotTables.
Using Excel's Report Connections, selecting a filter—such as Rating Category, Discount Category, or Price Category—instantly updates all visuals and summary metrics simultaneously, allowing stakeholders to cross-analyze product performance without altering the core dataset.

Below are the slicers I used.

The steps involved in connecting the Slicers with all the PivotTables are; right click on the Slicer, click report connections,on the pop-up min-window that comes up, tick all the boxes in the list and then click OK. This steps are as shown in the below image.

Below is the dashboard I came up with at the end of the project.

Key Findings & Correlation Analysis

Linear relationships were evaluated across complete paired observations using Pearson correlation (=CORREL()):

Business Recommendations

Optimize Discount Bands: Products in the Medium Discount group (20%–40%) generate higher average engagement (15.26 reviews) than items in the High Discount group (>40%, averaging 11.13 reviews).
Sellers should avoid slashing prices beyond 40% as it destroys margin without boosting sales volume.
Audit Low-Rated, High-Discount Listings: 10 products pair high discounts (>40%) with poor ratings (<3.0). Deep price cuts on low-quality products harm brand reputation; Jumia should inspect these items for quality defects or inaccurate descriptions.
Promote Catalog Star Performers: 6 listings achieve both Strong Engagement (greater or equal to 14 reviews) and Excellent Ratings (>4.5). Sellers should feature these items in sponsored ad campaigns to capture high conversion rates.
Fix Average-Rated High-Volume Listings: 6 items generate high engagement (greater or equal to 14 reviews) but possess mediocre satisfaction ratings (3.0–4.5). Sellers should inspect buyer feedback to fix minor packaging or product flaws.

Data Limitations & Analytical Challenges

Engagement Proxy: Review count is used as a proxy for customer engagement. The dataset omits direct sales units, page views, and financial revenue.
Listing Age & Visibility: Review totals may reflect how long a product has been active on Jumia or its placement algorithm rather than price responsiveness.
Correlation vs. Direct link: Observed statistical correlations do not establish direct cause-and-effect relationships.

Some of the Key Lessons I Learned while doing the project

Audit-Trail Data Cleaning: Cleaning raw e-commerce data requires preserving source inputs alongside normalized values. Converting price ranges to midpoints while keeping original text ensuring data integrity and traceability.

Data Types and Regional Formatting: Unstructured text strings (e.g., "KSh", ",", " out of 5", "%") break Excel numerical functions. Stripping formatting artifacts early prevents downstream #VALUE! errors in PivotTables and Formulas.

Percentile-Based Performance Thresholds: Applying objective mathematical cutoffs (such as using Price_Q1/Price_Q3 for 1st and 3rd Quartiles) eliminates subjective bias when categorizing catalog items.

Separation of Architecture & Presentation: Structuring the Excel workbook into dedicated functional layers (Raw_Data -> Cleaned_Data ->tblproducts ->Data Dictionary -> Analysis -> Pivot_Tables -> Dashboard) maintains readability, prevents circular references, and simplifies troubleshooting.

The Repository Structure I used

jumia-product-performance-dashboard/

├── README.md

├── data/

│   └── Excel_jumia_dataset.csv

├── dashboard/

│   └── jumia_product_dashboard.xlsx

└── images/

         ├── raw-data.png

         ├── cleaned-data.png

         ├── pivot-tables.png

         └── dashboard.png

Github repository link: https://github.com/Immeleo/jumia-product-performance-dashboard

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

Leonard Mwilu — Sat, 29 Aug 2026 18:33:11 +0000

Introduction

3rd week into Data Science and Analytics with Lux Dev Academy. This week we started a two-week learning module on the use of Microsoft Excel to clean and analyze data.

In the modern data-driven landscape, organizational decision-making hinges on the ability to transform raw, unstructured inputs into clear and actionable insights.

"Garbage in, garbage out" (GIGO) remains the defining axiom of data engineering. Statistical models, pivot tables, and visualization dashboards are only as reliable as the underlying data clean-up routines. Data cleaning is not a preliminary administrative chore; it is the core analytical step where data validity and structural integrity are established.

Excel Use Cases

Some of the common area/scopes where Excel is used are:

Data Entry
Data Analysis
Data Cleaning
Reporting (using dashboards & Pivots).

Navigating the Excel Interface & Understanding Data Types

Navigating the Excel Interface

We started by learning most if not all the components and features of Microsoft Excel. Below images show most important sections of the Microsoft excel, indicated on an open blank sheet.

The core interface components are:

Ribbon: this is the top navigation toolbar organize into tabs which include Home, Insert, Data, Formulas, Reviews and Help.
Formula bar: Displays and also can be used to edit the cell value or the underlying formula active in a selected cell.
Name Box: Displays the name or the defined name of the currently selected cell or range.
Worksheet Grid: This is the primary worksheet made up of rows(numbered 1 - 10000...) and columns(lettered A-X...)
** Cells**: this is the intersection of a row and a column where data, text, or formulas are entered.
Sheet Tabs: this a located at the bottom and enables one to switch between different worksheets within a single excel workbook file.
Status Bar: this is bar at the bottom of the worksheet that shows the real-time information or calculations like count of values in selected cells, sum of values in a selected range. Highlighted in yellow in the below image.

Other important sections of excel can be found once you click the file tab at the top left corner. This sections include Home(button to return to worksheet), New(for creating new workbook), Open (to open an existing excel file from the laptop/host machine),info, print, export ( can be used to convert the file into other formats like pdf), close, recent, Favorites and more. This appears as shown in the image below.

Data Types

Excel categorizes data entering a cell into distinct underlying visuals and mathematical data types. Understanding how Excel stores and processes these data types is important since mismatched data types result in execution errors during analysis.
Some the Excel data types include the following:

Numeric(Values); these are right-aligned and are floating-point numbers.
Text(Strings); these are left-aligned and are alphanumeric sequences(cannot be evaluated mathematically)
Date & Times; also Right-aligned and are sequential serial numbers where Day 1 is 1/1/1900. Time is represented by fractional values.
Boolean(Logical); they are centered on a cell and restricted to uppercase binary values: TRUE or FALSE.
Formulas & Errors; expressions starting with =. They yield dynamic data or error states.

Conditional Formatting

Learned about conditional formatting which is a feature of Microsoft Excel that automatically changes how a cell looks based on the data inside it. Instead of manually applying colors or styles to individual cells, you set rules (conditions) and the software formats the cells automatically when those conditions are met.
Conditional formatting can be applied on both text and numbers. When working with conditional format there as many options and functions provided in Microsoft Excel. Some of this options are highlighted in the below image.

Sample of data with conditional formatting applied is as shown below.

How it works: You select the cells you want to monitor, define the rule, and the forma updates automatically.

One important function during conditional formatting of data in Excel is the Find and Replace tool. You can access it instantly using keyboard shortcuts:
- Ctrl + F opens the Find tab directly.
- Ctrl + H opens the Replace tab directly.

Below is how it appears once open in an Excel sheet.

Formulas, Functions, and References: The Calculation Engine.

Formulas represent the foundational syntax of Excel analytics. Every formula begins with an equals sign =, signaling to the calculation engine to evaluate the subsequent expression rather than treating it as a literal string.

Mathematical Operators and Order of Operations.

Excel adheres to the standard algebraic order of operations PEMDAS: Parentheses, Exponents, Multiplication/Division, Addition/Subtraction. While using Excel we face situations where we use one or all of these operations and there its very crucial to understand how to apply and use them properly. When building analytical sheets, one is required to structure complex algebraic expressions logically. The symbols used for the different operations are as shown below.

Addition & Subtraction: + and -
Multiplication & Division: * and /
Exponentiation:^ (e.g., =10^2 yields 100)
Comparison Operators: =, <> (Not equal to), >, <, >=, <=

Mastering Cell References: Relative vs. Absolute vs. Mixed

Understanding cell referencing is very important for one to become proficient data analyst. When formulas are copied from one row or columns to another, cell references adapt based on the presence of dollar sign $ anchors.

Relative References: Cell references are relative by default. For a formula containing =A1+10 in cell B1 is dragged down to B2, it automatically shifts to =A2+10. The formula stores the relative offset rather than a fixed address.
- Absolute References: Adding a dollar sign $ before both the column letter and row numbers locks the reference completely. For example if =$A$4*.001 is copied from cell L1 down to L10, every single cell will evaluate against cell A4. This is very essential for central parameters like discount factors, tax rates or KPI targets.
- Mixed References: This lock either the row or the column, leaving the other free to adjust dynamically. eg $A10 and B$10.

Core Statistic Functions

The foundation of initial exploratory data analysis relies on fundamental aggregation functions:

=SUM(range): used to calculate the total arithmetic sum of a numeric range.
=AVERAGE(range): used to compute the arithmetic mean.
=COUNT(range): used to count cells containing numeric values only.
=COUNTA(range): used to count non-empty cells (includes text, numbers, logical values TRUE/FALSE and errors).
=COUNTBLANK(range): Counts empty cells within a specified range.
=MIN(range) or =MAX(range): used get the minimum or maximum value in a range of values. Other statistical functions we covered included: =COUNTIF()& =COUNTIFS(), =SUMIF() & =SUMIFS(), =AVERAGEIF() and =AVERAGEIFS().

Structured Data: Working with Excel Tables.

The single most impactful practice for modern analytics in Excel is converting raw data blocks into official Excel Tables(Keyboard Shortcut: Ctrl + T).
Some of the advantages of using Structured data are:

Dynamic Auto-Expansion: When new rows or columns are added directly adjacent to a Table, the table automatically expands to encompass the new data, automatically extending formulas, formatting, and data validation rules.
Structured references.
Calculated Columns: Entering a formula in a single cell of a table column automatically fills down the entire column instantly.
Integrated Filter Controls: Automatic addition of sorting and filtering drop-downs on the header row

Data Preparation & Data Cleaning Techniques.

Data cleaning is the process of detecting, correcting or removing corrupt, inaccurate, incomplete, improperly formatted or duplicate records from a dataset. Below are some of the methodologies we covered for execution with Excel.

Text Cleaning & Manipulation Functions

Uncleaned text files often contain leading or trailing whitespaces, non-breaking spaces, merged text strings or inconsistent casing.
Some of the functions for cleaning text we covered are:

=TRIM(text): removes all leading & trailing spaces and reduces multiple internal spaces to a single space.
=CLEAN(text): removes all non-printable ASCII characters.
=UPPER(text) and =LOWER(text): standardizes text strings into uniform uppercase or lowercase for case-sensitive comparisons.
=PROPER(text): Capitalizes the first letter of each word in a text string. It is useful for names and addresses.
=LEN(text): Used to count the total characters in a text string. One of the use cases would be when validating Fixed-length IDs. These functions can be used together when need arises. e.g =LOWER(CONCAT(text1,text2,text3,..))

Substring Extraction: LEFT, RIGHT, MID, and FIND.

Data fields frequently combine multiple pieces of data into single alphanumeric codes, for example Transaction ID is TXN-88492-2026. To extract individual variables, you combine string positional functions:

=LEFT(text, [number of characters]): used to extract characters from the start of a string.
=RIGHT(text, [number of characters]): used to extract characters from the end of a string.
=MID(text, start number, number of characters): used to extracts characters from any starting position within the string.
=FIND(find_text, within_text): Returns the 1-based index position of a specific character sequence and it is case-sensitive.

Conclusion

Mastering Microsoft Excel for data analytics is not about memorizing isolated functions but it is about building robust, repeatable data processing pipelines. We established the baseline mechanism required to operate within the spreadsheet efficiently. understanding cell layout, controlling relative and absolute reference framing, using official Excel Tables, and applying string extraction and text-cleaning functions to sanitize messy real-world inputs.

_ Never replace blank cells with guesses but you can replace them with a keyword like "Unknown"._

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

Leonard Mwilu — Thu, 20 Aug 2026 18:32:22 +0000

Introduction

In my 2nd Week in Data Science & AI Analytics at LUX Academy we dive more into Git Bash and GitHub interactions and operations by doing our first project and publishing it on GitHub using push , commit and remote origin commands.

Steps from start to publishing on GitHub

Gathering Information

The data for this project was provided as Excel forms both .xlsx and .csv files, by the instructor.
Creation of local folders

I created my local folder in my laptop's Desktop folder using git bash commands. In Git Bash, using the cd command I navigated to my Desktop which was located inside OneDrive folder. cd ~/OneDrive/Desktop/
Inside the Desktop folder, I created a new folder and named it after my project Kenya-Hospital-Health-Records-Analysis using the mkdir command.
I then used cd Kenya-Hospital-Health-Records-Analysis to move into the new folder(directory). Inside this folder, I created a new subfolder called data using cd data, to hold my files. I then created the README file which holds information about my project and is a crucial part of any GitHub repository. Here I used the touch command i.e touch README.md.
Moving files from Downloads to my project data folder

Since the data I was provided with was shared via WhatsApp, I downloaded the files into my laptop's Downloads folder and I had to move them to my project folder(data).
Using git bash commands I easily moved my file from Downloads to data folder. Command for moving files in git is mv /sourceFolder/filename.file_extension /destinationFolder. In my case all i did to do was

mv ~/Downloads/Kenyan_Hospital_Health_Records.xlsx ~/OneDrive/Desktop/Kenya-Hospital-Health-Records-Analysis/data

Now that my folder and data were set, I was ready to push and publish on GitHub.

Creation of GitHub Repository

In order to push from my local folder to GitHub, I needed to create a repository on my GitHub account. I logged into my GitHub account, navigated to Repositories(appears once you click the profile icon at top right corner) and then clicked NEW. I then type my repository name Kenya Hospital Health Records Analysis, entered a short description about it, and then clicked Create repository. Once my repository was ready I copied the SSH address.
Connecting Local folder and GitHub repository
- First I used ssh -T git@github.com to test my connection which was successful.
- Using git init I created the hidden .git folder that lets git to track the files in my working directory(folder).
- Using git branch -M main I ensured that my local branch was named main to align with GitHub defaults.
- using git add . and git commit -m "adding my kenya health records analysis project" I added my files to the staging area and created my baseline snapshot.
- I then linked my local repository with GitHub repository via SSH using git remote add <SSH_address copied from GitHub earlier>
Pushing my project to GitHub

Using git push -u origin main I uploaded my commits and set the default upstream tracking branch.
Verifying Successful Push

I then returned to my GitHub account, and after refreshing the page, my project had successfully been uploaded to my GitHub repository and the content of my README.md was on display.

_ I am so happy that after a few challenges with git bash commands I successfully managed to complete my first project. I am ready for the next challenge as we dive deep into Data Science_

Starting My Journey with Data Science and AI Analytics: What I've Learned So Far

Leonard Mwilu — Sun, 16 Aug 2026 08:47:04 +0000

Hello DEV Community! 👋

I'm starting a new chapter in my career journey and wanted to document my progress here. I recently enrolled in LUXDEV (Data Science and AI Analytics Cohort 9 2026), and I'm excited to share my first updates!

🎯 Why I Enrolled

I wanted to build a rock-solid foundation in Data Science and get certified instead of learning randomly without structure. My goal is to understand data analysis, structuring and how to deduce information from cleaned data, using this to make predictions and decisions.

📚 What We've Covered So Far

Here is a quick summary of what I've tackled in the first couple of modules:
**1st Week: Software Installation and Initial Setups

Git and GitHub: Installed and set up Git on my laptop and linked it with my existing GitHub account.
DBeaver and Postgress: Installed Postgress and DBeaver. Used DBeaver to create DB on Postgress.
Aiven: Created an account on Aiven Site. Created two services MySQl and Postgress. Linked both database with DBeaver for ease management.

💡 Key Takeaway

DBeaver is a Open Source Database management tool
Aiven is a online(cloud) platform for creating database and more and gives free usage for 1 DB at a time for learning purposes.
Learned about this site DEV Its the first time to hear about it

🚀 What's Next

This 2nd week we dive into the real deal now that we are set.

I'd love to connect with others currently taking Data Science & AI Analytics or learning Data Engineering. Leave a comment below or drop your favorite learning tips!

DEV Community: Leonard Mwilu

Data Modelling, Relationships & Joins in Power BI

Data Modelling in Power BI

Comparison of Architectural Schemas

Introduction

Flat Table Schema

Star Schema

Snowflake Schema

Fact Tables and Dimension Tables

Relationships in Power BI

Relationship Cardinalities

Filter Direction & Propagation

Single-Direction vs. Both/Bidirectional Filtering

Joins in Power Query

Power Query Joins vs Power BI Relationships

Recommended Power BI Architectural Standard

Jumia Product Performance & Pricing Dashboard

Project Overview

Core Questions I used to drive my Analysis

PivotTable and analysis workflow

Data Audit Summary

Data Cleaning & Transformation Rules

Calculated & Enriched Fields

Excel formulas I used

Dashboard design and slicer connections

Key Findings & Correlation Analysis

Business Recommendations

Data Limitations & Analytical Challenges

Some of the Key Lessons I Learned while doing the project

The Repository Structure I used

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

Introduction

Excel Use Cases

Navigating the Excel Interface & Understanding Data Types

Navigating the Excel Interface

Data Types

Conditional Formatting

Formulas, Functions, and References: The Calculation Engine.

Mathematical Operators and Order of Operations.

Mastering Cell References: Relative vs. Absolute vs. Mixed

Core Statistic Functions

Structured Data: Working with Excel Tables.

Data Preparation & Data Cleaning Techniques.

Text Cleaning & Manipulation Functions

Substring Extraction: LEFT, RIGHT, MID, and FIND.

Conclusion

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

Introduction

Steps from start to publishing on GitHub

Gathering Information

Creation of local folders

Moving files from Downloads to my project data folder

Creation of GitHub Repository

Connecting Local folder and GitHub repository

Pushing my project to GitHub

Verifying Successful Push

Starting My Journey with Data Science and AI Analytics: What I've Learned So Far

🎯 Why I Enrolled

📚 What We've Covered So Far

💡 Key Takeaway

🚀 What's Next