DEV Community: Kipyegon Patrick

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

Kipyegon Patrick — Tue, 10 Feb 2026 15:02:39 +0000

Power BI is a powerful tool, but it rarely starts with clean data. Analysts must turn complex, messy data into reliable insights for decision-making. Here's how they do it:

Turning Complexity into Clarity
Imagine trying to analyze sales data, but the numbers don't match across different reports. That's where analysts come in. They use:

Power Query: to clean and transform data, like removing duplicates or fixing formatting issues
Star schema modeling: to organize data into facts (e.g., sales) and dimensions (e.g., time, location)
Surrogate keys: to create unique identifiers for data points

For example, an analyst might use Power Query to exclude test transactions or normalize product categories, ensuring consistency across reports.

DAX: Controlling the Numbers
DAX (Data Analysis Expressions) is like a language that helps analysts control how numbers behave in Power BI. They use it to:

Redefine calculations based on user filters
Create custom time intelligence, like rolling periods or fiscal calendars
Build reusable measures for complex logic

Think of it like this; without DAX, numbers might not add up correctly when you slice data by region or product.

Dashboard Design

A well-designed dashboard is like a clear window into your data. Analysts ensure that:

Visuals are supported by a solid data model
Measures are used instead of implicit aggregations
Drill paths align with table relationships

For instance, a dashboard might show sales trends over time, with drill-down capabilities to specific regions or products.

From Insights to Decisions

The ultimate goal is to support business decisions. Analysts create a system where:

Forecasts are compared to historical data
Variance analyses point to specific drivers
Scenario models reuse trusted measures

When done right, Power BI becomes a decision support system, helping leaders make informed choices.

Why Technical Discipline Matters
Power BI is accessible, but impact comes from discipline through thoughtful data modeling, intentional DAX patterns, and performance-aware report design. Analysts who apply these skills create analytical infrastructure that leadership can rely on.

Shemas and Data Modelling in Power BI

Kipyegon Patrick — Sun, 08 Feb 2026 19:52:35 +0000

Definition of Terms

What is data modelling?

Data modelling is defined as the process of creating a visual representation of information to communicate connections between data points. Data modelling can explained simply as the process of designing how data is structured, stored, and related to each other so it can be used effectively for analysis to derive meaningful insights.

The importance of good data modelling

Data modeling is critical for creating a structured, accurate, and scalable blueprint for organizational data, ensuring high-quality, consistent, and efficient data management. It reduces development errors, eliminates data redundancy, speeds up application performance, and aligns data structures with business requirements, ultimately enabling better-informed decisions.

What is a Schema?

A schema in data science is the structural blueprint or design of a database, defining how data is organized, stored, and related. It specifies tables, column names, data types, and constraints or rules. It serves as a, "map" for data, ensuring integrity and enabling efficient querying and analysis.

Types of Schemas

Star Schema

Star schema is better for storing and analyzing large amounts of data. It has a fact table at its center and multiple dimension tables connected to it just like a star, where the fact table contains the numerical data that run business processes and the dimension table contains data related to dimensions such as product, time, people, etc. or we can say, this table contains the description of the fact table.

Snowflake Schema

Just like star schema, the snowflake schema also has a fact table at its center and multiple dimension tables connected to it, but the main difference in both models is that in snowflake schema – dimension tables are further normalized into multiple related tables. The snowflake schema is used for analyzing large amounts of data.

Wrap Up

A well-structured database schema maintains data integrity and supports scalability by providing a solid framework for dealing with growing data volumes and complexities.

Introduction to MS Excel for Data Analytics

Kipyegon Patrick — Sun, 25 Jan 2026 10:52:32 +0000

Microsoft Excel is a spreadsheet application that is mostly used for data analytics. It helps one to collect, clean, analyze, visualize and report data for decision making purposes. The beauty of Microsoft Excel is the fact it is easy to learn and is user friendly.
The application allows one to do the following:

Collect and store data
Clean and organize data
Perform calculations such as sum(+), averages(/), differences(-) and products(*)
Analyze the data and visualize the trends

This article will detail a beginner data analytics learning guide step by step.
Data in excel is arranged in rows (horizontal line of cells) and columns (vertical line of cells). A cell is the box formed where a row and column intersect. Data is entered and stored in the cells as shown bellow:

What is data analytics?

Data analytics is the science of examining raw data to determine:
a) Patterns
b) Trends
c) Relationships and
d) Insights that are used for decision making

Why use Excel for Data Analytics

Excel remains a popular tool because of the following reasons:

It is easy to learn and use and is widely available
It handles datasets efficiently
It comes with built-in-formulas and functions
It provides quick options for creation of charts and dashboards
It works well with other tools such as Power BI, SQL and Python

Organizing data for analysis

Excel as an analytic tool only works well with a well structured dataset. The data should have one clear header, every row and column should each represent one unique record. Also remove blanks and duplicates.

Data Cleaning

Removing duplicates:

Select the dataset -> click home -> conditional formatting -> highlight cell rules -> duplicate values -> choose a color -> Ok.

Fixing data types:

Format dates as dates, numeric values as numbers, currencies as currency and texts as Text. Correct data types are critical for accurate analysis.

Handling missing values:

Find and impute the missing values.
Highlight blanks by; selecting data range -> go to home -> conditional formatting -> new rule -> choose format only cells that contain blanks -> pick a colour click ok

Sorting and Filtering data

Sort data (A-Z, largest to smallest and vice versa) you can also filter to view specific values only.

Performing Calculations using operators

Formulas in Excel start with equal to sign (=) and with the operators below:
.Addition (+)
.Subtraction (-)
.Division (/)
.Multiplication (*)
.Exponent (^)

Addition operator

Subtraction operator

Division operator

Multiplication operator

Performing Calculations using Formulas

Sum

=Sum(E2:E10)

Average

AVERAGE(E2:E10)

Maximum

MAX(E2:E10)

Minimum

MIN(E2:E10)

Count

Count(E2:E10)

COUNTIF

COUNTIF(E2:E10,">90000")

Lookup Formulas

VLOOKUP

Finds data vertically
VLOOKUP(10007,A2:E10,4,FALSE)

HLOOKUP

Finds data horizontally
HLOOKUP(10007,A1:J5,4,FALSE)

XLOOKUP

Is a more advanced lookup functions can find data both vertically and horizontally and is less error-prone.

Pivot Tables

Allows one to summarize large datasets, group and aggregate data and to compare values easily.

Steps to create a Pivot Table

. Select your data
. Go to insert -> Pivot Table
. Choose where to place, as a best practice choose new worksheet
. Drag fields into rows (departments), columns and values(salaries)

Charts in excel

Common types includes:
. Column Chart- used when comparing categories
. Line Chart- used to show trends over time
. Pie Chart - used to show proportions

Creating a dashboard

A dashboard is a visual representation of of key data and metrics on one screen. It summarizes data in one place.

Summary

Excel is a foundational tool for data analytics. Excellent knowledge of excel gives you the requisite skills To:

Analyze data confidently
Make data-driven decisions and
Transition easily to more advanced analytic tools