DEV Community: Daniel Kemei

How Analysts Translate Messy Data, DAX, and Dashboards into Action Using Power BI

Daniel Kemei — Fri, 13 Feb 2026 07:16:31 +0000

The Reality of Messy Data

Power BI is one of the tools for analysts that allows you to create and share robust and interactive reports in a secure environment.However,data from different sources does not arrive clean, structured, or ready for decision-making.Analysts are mostly faced with dirty data which may contain duplicate records, missing values or inconsistent date formats.
Before any meaningful analysis can begin, analysts must first fix these issues.

Cleaning Data with Power Query

This is where Power Query,a Component in Power BI, becomes essential. Power Query is the Worlds’ greatest washing machine for dirty data.It contains essential transformation techniques such as:

Remove duplicates

This lets you identify and remove duplicate rows based on specific columns, ensuring each customer or transaction appears only once.

Change Data Types

Setting the correct data type prevents calculation errors and enables proper sorting and filtering. For instance,ensuring date column is set to date or revenue column to number

Handle Errors in Specific Columns

Errors can occur when data doesn't match expected formats. You can use the Replace Values to change "Error" to null or use conditional logic to handle them.

Remove blank or error rows

Power Query lets you filter out blank rows automatically.

Standardizing formats

Convert text to proper case, ensure dates follow a consistent format, and create uniform category names. For example, transforming "new york," "New York," and "NY" into a single standardized value.

Merging and appending data

Combine sales data from different regions, join customer information with transaction records, or append monthly files into a single dataset.

DAX: Turning Data into Insights

Once the data is clean, DAX (Data Analysis Expressions) is where the analytical process begins. The syntax of DAX is not dissimilar to Excel’s function language but is extremely more powerful.
DAX allows for the creation of the following:
Calculated columns are columns that are added to an existing table within the semantic model. These columns are defined by the DAX formula entered for the column. This formula is used to create a calculated value for each row in the table.
Measures are DAX formulas that are not tied to any particular table except as referenced within the DAX formula itself. These calculations are dynamic and can change values based upon the context within which the formula is calculated.
Calculated tables are entire tables within the semantic model whose columns, rows, and values are defined by a DAX formula.
There are several hundred DAX functions that can be used in formulas, and these functions can be infinitely nested to create extremely complex calculations. However, all DAX formulas have the same format: Name = Formula
Practical DAX Example
Consider a retail analyst needing to identify top-performing products. A simple measure might be:

Top 10 Product Sales = 
CALCULATE(
    SUM(Sales[Revenue]),
    TOPN(10, ALL(Products[ProductName]), [Total Sales], DESC)
)

Building Dashboards That Drive Decisions

Humans are visually oriented and thus it is advantageous to view the results of the analysis in the form of charts, reports, and dashboards. Charts may take the form of tables, matrices, pie charts, bar graphs, and other visual displays that help provide context and meaning to the analysis, and multiple charts are combined to make reports and dashboards
Power BI offers a rich array of visualization types, each suited for specific types of analysis. Knowing which visuals to use and when can greatly enhance the effectiveness of your reports.

Design Principles for Actionable Dashboards:

Clarity over complexity: Resist the urge to cram every metric onto one page. Focus on the 3-5 KPIs that actually drive decisions for your audience. A sales manager needs different insights than a CFO.
Choose visuals purposefully: Use bar charts for comparisons, line charts for trends over time, cards for single KPIs, and maps for geographical analysis. Don't use a pie chart with 12 slices when a sorted bar chart would be clearer.
Enable exploration with interactivity: Slicers let users filter by date ranges, regions, or product categories. Drill-through pages allow them to click a high-level metric and explore the underlying details. Tooltips can display additional context without cluttering the visual.
Visual hierarchy matters: Place the most critical metrics at the top left (where eyes naturally go first), use consistent color schemes, and ensure text is readable without zooming.

By mastering the three-step process—cleaning messy data with Power Query, creating intelligent calculations with DAX, and building focused dashboards—analysts enable their organizations to move from gut-feel decisions to data-driven action.
The skill isn't just in knowing the tools; it's in understanding which business questions matter, what data transformations are necessary, and how to present insights that compel action.

Getting started with Data Modeling in Power BI

Daniel Kemei — Mon, 02 Feb 2026 16:11:36 +0000

What is Data Modelling?

Data modeling is the process of organizing and structuring your data tables and defining the relationships between them.This creates a logical framework that allows Power BI to understand how your data connects and flows.
There are two types of table in Power BI:
-Fact Tables
-Dimension Tables

Fact Tables

Fact tables contain the measurable and quantitative data in your model.They contain numbers you want to analyze, such as sales amounts, quantities sold, revenue, costs, or transaction counts.Fact tables typically contain many rows and have foreign keys that link to dimension tables.

Dimension Tables

Dimension tables provide the descriptive context for your facts.A dimension table contains a key column that acts as a unique identifier, and other columns which support filtering and grouping your data.

Star schema

Star schema contains a central fact table connected to multiple dimension tables.

It is the most common and recommended data modeling approach in Power BI.
In a star schema, you have:

-One or more fact tables at the center containing your measurements

-Dimension tables radiating outward, each connecting directly to the fact table

-No connections between dimension tables themselves

Why Star Schema is Recommended

Power BI's engine is specifically optimized for star schemas. The way it compresses data and performs calculations works best with this structure, resulting in faster query performance and smaller file sizes.
Writing DAX formulas becomes more straightforward when your model follows a star schema, because the relationships are predictable and
consistent.

Snowflake dimensions

A snowflake dimension is a set of normalized tables for a single business entity.Products are assigned to subcategories, and subcategories are in turn assigned to categories.

While snowflake schemas can reduce data redundancy and save storage space in traditional databases, they're generally not recommended for Power BI for several reasons:

-Power BI loads more tables, which is less efficient from storage and performance perspectives. These tables must include columns to support model relationships, and it can result in a larger model size.

-Longer relationship filter propagation chains need to be traversed, which might be less efficient than filters applied to a single table.

-The Data pane presents more model tables to report authors, which can result in a less intuitive experience, especially when snowflake dimension tables contain only one or two columns.

-It's not possible to create a hierarchy that comprises columns from more than one table.

Relationships

Relationships are the connections between tables that allow Power BI to combine data from multiple sources.

Types of Relationships

One-to-one relationship

One-to-one relationships occur in two main scenarios: degenerate dimensions derived from fact tables, and row data that spans across tables where a single business entity is loaded as two or more model tables.
For Intra Source Group Relationships:
When a one-to-one intra source group relationship exists between tables,it is reccommended to consolidate the data into a single model table by merging Power Query queries using a left outer join.
For Cross Source Group Relationships:
When a one-to-one cross source group relationship exists between tables, there's no alternative model design unless you pre-consolidate the data in your data source, as Power BI evaluates these as limited relationships.

Many-to-Many Relationships

Many-to-many scenarios occur in three situations: relating two dimension tables, relating dimension tables to higher grain fact tables, and the many-to-many scenario involving entities like bank customers and bank accounts.

Active vs Inactive Relationships

Active Relationships

By default, only one active relationship can exist between two tables.
Active relationships propagate filters automatically in visuals.
Use active relationships where most filtering should occur

Inactive Relationships

Inactive relationships do not filter by default.
Use them when you have multiple possible joins (like a fact table with two date columns)

Bi-Directional relationship

A bi-directional relationship sends filters in both directions between tables, instead of the usual one-way flow.
When Bi-Directional Is Useful

Special cases such as many-to-many with a bridge table or one-to-one relationships.
Scenarios where slicers should reflect only values with data (like Excel pivot slicers).
Limited dimension-to-dimension analysis where filters need to propagate across multiple tables.

Why Good Data Modeling Matters

The quality of your data model directly impacts three critical areas: performance, accuracy, and usability.

Practical Tips for Building Your Model

-Start by identifying your fact tables—what are you measuring? Then identify the dimensions—what context do you need to analyze those measurements?

-Transform your data in Power Query to create star schemas. Merge snowflaked dimensions, remove unnecessary columns, and ensure your dimension tables have unique keys.
-Create a calendar table using DAX or Power Query, even if you have date columns in your fact tables. This gives you complete control over fiscal years, holidays, and custom date groupings.
-Name your tables and columns clearly and consistently. Use spaces in names to make them readable
-Hide technical columns like keys and internal IDs that users don't need to see in reports. Keep your field list clean and focused on business-relevant fields.

Excel:A Beginners Guide

Daniel Kemei — Sun, 25 Jan 2026 16:08:18 +0000

Excel is a powerful spreadsheet program created by Microsoft that helps you organize, calculate, and analyze data.In Data Analysis Excel structures data in a meaningful way by letting you:
-Collect, store, organize, and sort information.

-Identify patterns and piece the data together in a way that works for each specific data project.

-Create data visualizations, like graphs and charts.

Excel Overview

The Ribbon provides shortcuts to Excel commands.This can for example be to: insert a table, change the font size, or to change the color of a cell.
The Sheet is a set of rows and columns.
Rows are organized horizontally and are ordered by number while columns are ordered vertically and are ordered by letters.A cell is a single box at the intersection of a row and columns and is referenced by combining the column letter and the row number where it is located.For example,In the above image the word row is in cell D4.

Excel Functionalities Explained

1. Working with Formulas

A Formula is a set of instructions used to perform a calculation using the data in a spreadsheet.Formulas always start with the equal sign = typed in the cell, followed by your calculation.

Creating formulas, step by step

1.Select a cell
2.Type the equal sign (=)
3.Select a cell or type value
4.Enter an arithmetic operator
5.Select another cell or type value
6.Press enter
You can make formulas with arithmetic operations such as:
Addition(+)

Subtraction(-)

Multiplication(*)

Division(/)

2.Using Functions

A function is a preset command that automatically performs a specific process or task using the data in a spreadsheet.
SUM - Add Numbers Together
Syntax: =SUM(number1, number2, ...)

AVERAGE - Calculate Mean
Syntax:=AVERAGE(number1, number2, ...)

MIN and MAX - Find smallest and largest values
Syntax: =MIN(range)

Syntax: =MAX(range)

COUNT - Counts cells containing numbers
Syntax: =COUNT(range)

Text Functions for Data Cleaning

LOWER function is used to lowercase text in a cell
Syntax: =LOWER(cell)

UPPER function is used to uppercase text in a cell
Syntax: =UPPER(cell)

PROPER function capitalizes the first letter of every word in a text string and converts all other letters to lowercase
Syntax: =PROPER(cell)
TRIM function is used to remove irregular text spacing and keep single spaces between words.
Syntax:=TRIM(cell)
The CONCAT function is used to link multiple cells without adding any delimiters between the combined cell values.
Syntax:=CONCAT(cell1, delimiter, cell2)

Conditional Math Functions

These calculate only for rows meeting specific criteria.
SUMIF - Sum based on one condition
Syntax: =SUMIF(range, criteria, [sum_range])
SUMIFS - Sum based on multiple conditions
Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
COUNTIF counts cells as specified.
Syntax=COUNTIF(range,criteria)
COUNT IFS is used to count cells in a range based on one or more true or false condition.
Syntax=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Logical Functions

IF Function returns values based on a true or false condition.
Syntax=IF(logical_test, [value_if_true], [value_if_false])
AND - Returns TRUE only if ALL conditions are TRUE
Syntax=AND([logical1], [logical2], ...)
OR - Returns TRUE if ANY condition is TRUE
Syntax=OR([logical1], [logical2], ...)

Lookup Functions

VLOOKUP - Search vertically in a table
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
HLOOKUP - Search Horizontally in a table
Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, FALSE)
INDEX + MATCH-retrieves data from a table based on vertical or horizontal criteria
Syntax=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

3.Data Organization and Management

Excel is useful in organizing data in structured ways.

Sorting data

Arranging data into a meaningful order to make it easier to understand,analyze and visualize.
Ranges can be sorted using the Sort Ascending and Sort Descending commands.
Sort Ascending: from smallest to largest.
Sort Descending: from largest to smallest.
1.Select Data: Select the entire range of data, including headers, or just a single cell in the range.
2.Open Sort Dialog: Go to the Data tab and click the Sort button (the icon with A/Z and an arrow).
3.Configure Sort: In the dialog box:

My data has headers: Check this if your selection includes header rows. -Sort by: Choose the column you want to sort by first.

Filtering data

Showing only the data that meets a specific criteria

-Select the header row of your dataset.

-Go to the Data tab and click Filter.

-Click the arrow in the header cell of the column you want to filter.

-Uncheck "Select All" and select the desired criteria, or use "Text Filters" / "Number Filters" for specific criteria.

-Click OK

4.Conditional Formatting

Spreadsheet tool that changes how cells appear when values meet specific conditions.

5.Pivot Table

Data summarization tool used in data processing.They are used to summarize,sort,reorganize,group,count,total or average data.

How to create a pivot table

1.Insert: Click any cell in your data, go to the Insert tab, and select PivotTable.

2.Configure: Choose to place it on a new or existing sheet, then click OK.

3.Field List: Drag fields from the list into the four areas: Filters, Columns, Rows, or Values.

Key Components & Functions

Rows/Columns: Define the categories for organizing data.
Values: The numerical data to be summarized (e.g., Sum of Sales).
Filters: Restricts the data displayed.
Calculated Fields: Create new data points
Slicers: Visual filters for creating interactive dashboards.

6. Creating Charts and Graphs

Visual representations make data patterns immediately obvious.

Common Chart Types:

Column Charts - Vertical bars comparing categories

Best for: Comparing sales across different products

Bar Charts - Horizontal bars comparing categories

Best for: Showing rankings or when category names are long

Line Charts - Show trends over time

Best for: Tracking monthly revenue growth

Pie Charts - Show parts of a whole

Best for: Displaying market share percentages

Scatter Plots - Show relationships between two variables

Best for: Analyzing correlations
Creating Charts in Excel

1.Select Data: Highlight the cells containing the data you want to visualize.

2.Insert Chart: Go to the "Insert" tab on the ribbon and select a chart type (e.g., column, line, or pie).

3.Customize: Use the "Chart Design" and "Format" tabs to add titles, axis labels, legends, and adjust styles.

Getting Started with Git and GitHub

Daniel Kemei — Sun, 18 Jan 2026 10:52:23 +0000

What is Version Control?

Version Control is a system that tracks and manages changes to your code over time. This lets you:

Track who made what changes and when
Revert to previous versions of your project
Collaborate with others
Git is the most popular version control system.
GitHub on the other hand is a platform where you can store and share your Git repositories online.A repository Is a place where all versions of your files and their complete change history are stored.
Think of Git like your personal diaries where you write entries and Github like the library where you share your diaries with others

Step 1: Introduce Yourself to Git

Before you start using Git, you need to configure it with your identity which will be attached to every change you make.

Set Your Username

git config --global user.name "yourname"

Set Your Email

git config --global user.email "youremail@gmail.com"

Important: Use the same email address that you'll use for your GitHub account.

Verify Your Configuration

To confirm everything is set up correctly, run:

git config --global --list

This command will display your configured username and email.

Step 2: Connect Git to GitHub Using SSH

SSH (Secure Shell) provides a secure way to connect your computer to GitHub without typing your password every time.

Generate Your SSH Key

Run this command to create a new SSH key pair:

ssh-keygen -t ed25519 -C "youremail@gmail.com"

What this does: Generates a public/private key pair using the ed25519 encryption algorithm.
When prompted:
Path to save the key: Press Enter to accept the default location
Passphrase: You can press Enter to skip, or add a password for extra security

Start the SSH Agent

The SSH agent is a program that holds your private keys in memory. Start it with:

eval "$(ssh-agent -s)"

You should see output like Agent pid 12345, confirming the agent is running.

Add Your Key to the SSH Agent

ssh-add ~/.ssh/id_ed25519

This adds your newly generated private key to the agent so it can be used for authentication.

Copy Your Public Key

To view and copy your public key, run:

cat ~/.ssh/id_ed25519.pub

This will display your public key. Copy the entire output.

Step 3: Add Your SSH Key to GitHub

Now you need to tell GitHub about your SSH key:

Go to GitHub.com and sign in
Click your profile picture (top right) → Settings
In the left sidebar, click SSH and GPG keys
Click the New SSH key button
Give your key a descriptive title (e.g., "My Laptop")
Paste your public key into the "Key" field
Click Add SSH key

Test Your Connection

Verify everything is working:

ssh -T git@github.com

If successful, you'll see a message like: Hi username! You've successfully authenticated...

Step 4: Create Your First Repository

You can create a repository locally on your computer or on GitHub.

Option A: Create a New Repository on GitHub

Go to GitHub.com and sign in
Click the + icon in the top right → New repository
Give your repository a name
Add a description
Choose Public or Private
Check "Add a README file"
Click Create repository

Option B: Create a Local Repository

Navigate to your project folder and initialize Git:

bashcd /path/to/your/project
git init

This creates a hidden .git folder that tracks all your changes.

Step 5: Track Changes with Git

Git tracks changes in three stages: Working Directory → Staging Area → Repository.

Check the Status of Your Files

See which files have been modified:

git status

This shows you:

Files that have been changed
Files that are staged (ready to commit)
Files that are untracked (new files Git doesn't know about)

Add Files to the Staging Area

Before Git can save your changes, you need to stage them:

# Add a specific file
git add filename.txt

### Add all changed files
git add .

# Add multiple specific files
git add file1.txt file2.txt file3.txt

Commit Your Changes

A commit is a snapshot of your project at a specific point in time. Each commit has a message describing what changed.

git commit -m "Add initial project files"

View Your Commit History

See all the commits you've made:

git log

Step 6: Push Your Code to GitHub

Pushing means sending your local commits to GitHub so others can see them.

Push for the First Time

git push -u origin main

Push Subsequent Changes

After the first push, you can simply use:

git push

Step 7: Pull Changes from GitHub

Pulling means downloading changes from GitHub to your local computer.

Pull the Latest Changes

git pull

This downloads and merges changes from GitHub into your local repository.