Power BI Guide: Data Cleaning, DAX Formulas, and Dashboard Design

“Data is rarely clean. It’s duplicated, inconsistent, poorly structured, and sometimes misleading. Yet decisions worth millions depend on it.”

In this article we are going to learn to turn messy data into actionable insights i.e: Data Cleaning, Data Analysis Expressions(DAX) formulas and Dashboard Design. We are going to achieve this by using Power BI.

What is Power BI?

Power BI is Microsoft's business analytics platform that helps you turn data into actionable insights. Whether you're a business user, report creator, or developer, Power BI offers integrated tools and services to connect, visualize, and share data across your organization.
For a deeper dive into Power BI tool, I will point you to Microsoft official article: https://learn.microsoft.com/en-us/power-bi/fundamentals/power-bi-overview

Data Cleaning

Also called data cleansing or data scrubbing, is the process of identifying and correcting errors and inconsistencies in raw datasets to improve data quality, this ensures a dataset meets criteria for accuracy, completeness, validity, consistency, uniqueness, timeliness and fitness for purpose, and it is critical to all data governance initiatives within an organization.

Data Cleaning & Transformation (Power Query)

• Removing duplicates: When starting with data cleaning, one of the first steps is removing duplicates. However, this must be done carefully. You need to clearly identify which column defines uniqueness in your dataset. For example: In my dataset below is a Sample Hospital dataset with a Visits table and I've identified visit_ID column as my primary key. In power BI go to Transform data using the right-click on the column the select Remove Duplicates then power BI will automatically remove duplicates:

• Handling nulls: Handling null values also requires careful consideration. Not every null should be treated the same way. In many cases, instead of deleting rows that contain null values, it’s better to replace them with a sensible placeholder such as: "Not Provided", "N/A", "Unknown", 0 (only when logically appropriate). Below is an image illustrating how to deal with "nulls":

The principle is that Null handling is not just data cleaning - it’s preserving analytical integrity.

• Fixing inconsistent formats: Another common issue in messy datasets is inconsistent formatting — especially with dates and numeric columns. For example, in a pharmacy transactions table, you might find the different date formats in the same column, and the only way to deal with it is to ensure consistent date format as shown below:

• Splitting/merging columns: Another common transformation in Power Query is splitting or merging columns. However, this should only be done when it improves clarity, usability, or analytical accuracy. Splitting columns applies in cases where data is shared in a combined format which might limit analysis. For example having, "Kenya - Nairobi" we can have it split into "Country" and "City". The key principle is to transform data only when it improves modeling, filtering, or reporting — not just because it’s possible.

• Creating calculated columns: After cleaning and structuring your dataset, the next step may involve creating calculated columns. This is where you derive new information from existing fields at the row level.

A calculated column is computed during data refresh and stored in the model. It evaluates one row at a time.

Note that the above is just an illustration, it is not logical to add IDs in any manner.

DAX Formulas

DAX(Data Analysis Expressions) is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. DAX helps you create new information from data already in your model.

We will start understanding DAX around three fundamental concepts: Syntax, Functions, and Context. There are other important concepts in DAX, but understanding these three concepts will provide the best foundation on which to build your DAX skills.

In the above case we are looking at:

Total Profit = SUM('Kenya_Crops_Power BI DATASET'[Profit (KES)])

syntax:

Total Profit - is the New measure
= - this is an equal sign operator
SUM - is a DAX function that adds up all the Profit in the dataset
Parenthesis - these surround the expressions within the arguments.

This could also be broken down to a language that one can easily understand: For the measure named Total Profit, calculate (=) the SUM of values in the [Profit] column in the Kenya_Crops_Power BI DATASET table.

There are other DAX functions which could be explored in context with different datasets. For example: SUMX(), COUNT(), AVERAGE(), AVERAGEX(), IF(), GEOMEANX() and use of AND & OR operators

Dashboard Design

Designing an effective Power BI dashboard is more than just dropping charts onto a canvas. it’s about clarity, readability, and insight delivery. Here are the key principles and techniques you should follow:

1. Layout and Structure

• Keep it simple and organized: Group related visuals together. Use whitespace effectively.
• Use a grid system: Align charts and tiles for balance.
• Highlight key metrics: Place the most important KPIs at the top or in prominent positions.

2. Consistent Visual Themes

• Stick to consistent fonts, sizes, and styles across your dashboard.
• Avoid clutter—only display essential information.

3. Color Usage with HCL Model

Colors are crucial in dashboards—they guide attention and improve comprehension. Power BI allows you to customize colors, but using the HCL (Hue-Chroma-Luminance) color model ensures your choices are perceptually uniform.

Understanding HCL:

• Hue: The type of color (e.g., red, blue, green).
• Chroma: The intensity or purity of the color (saturation).
• Luminance: The lightness or darkness of the color.

Why HCL matters:

• Colors should differentiate data clearly without misleading perception.
• HCL ensures that differences in color intensity are perceived equally by the human eye.
• Avoid using colors that are too bright or too dull, which can make charts harder to read.

Practical Tips in Power BI:

• Use contrasting colors for categorical data to make groups distinguishable.
• For sequential data, adjust luminance gradually to show progression.
• Test colors for accessibility, especially for color-blind users.

Example:

• A bar chart showing sales performance could use a sequential green palette with increasing luminance for higher sales.
• A categorical chart for product categories could use distinct hues with similar luminance to make categories easy to compare.

4. Interactivity

• Use slicers and filters to allow users to drill down into the data.
• Include tooltips and hover effects to provide additional context without cluttering visuals.

5. Storytelling

• Arrange visuals in a logical order to tell a story with your data.
• Use titles, labels, and annotations effectively to guide the user.

---

Conclusion
Messy data is the norm: full of duplicates, nulls, inconsistencies, and hidden pitfalls yet critical decisions depend on getting it right. Through this Power BI guide, we transformed raw chaos into reliable insights: cleaning and shaping data in Power Query, unlocking deeper analysis with essential DAX formulas, and delivering clear, actionable stories via thoughtful dashboard design powered by perceptually accurate HCL colors. The payoff? Trustworthy visuals that stakeholders actually use to drive better decisions. Grab a messy dataset, follow the steps, and watch garbage-in become golden-out. You've got the full workflow — now go make your data work for you.