How to Check Data Quality in Power BI

Your Power BI reports are only as reliable as the data behind them. An impressive-looking dashboard can be dangerously misleading if it's built on a foundation of duplicate entries, missing values, and inconsistent formatting. This guide provides a straightforward walkthrough on how to find and fix common data quality problems directly inside Power BI, ensuring your insights are accurate and your decisions are sound.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Why Poor Data Quality Sinks Your Dashboards

There's a classic saying in data analysis: "garbage in, garbage out." It means that no matter how sophisticated your charts or compelling your visuals are, their value is zero if the underlying data is flawed. Making decisions based on bad data is often worse than making decisions with no data at all.

Imagine a scenario where your sales dashboard suddenly reports a huge dip in revenue. Panic sets in, and you start questioning your team's performance. But after hours of digging, you discover the issue: a batch of records was imported with the wrong date format, causing an entire month's sales to be excluded from the report. Bad data didn't just cause a headache, it created unwarranted stress and could have led to poor strategic choices.

Common data quality issues include:

• Missing Values (Nulls): Empty cells that can break calculations and skew averages.
• Duplicate Records: The same transaction or customer entered multiple times, artificially inflating your numbers.
• Incorrect Data Types: Dates stored as text, or numbers stored as text, which prevents them from being used in calculations or slicers.
• Inconsistent Formatting: Different spellings for the same thing (e.g., "CA," "Calif.," and "California") that prevent proper grouping.
• Extraneous Characters: Hidden spaces or non-printable characters that mess up filtering and relationships.

Tackling these issues isn't just a technical task - it's a fundamental step in building trust in your reports and making data-driven decisions with confidence.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Your Data Quality Command Center: The Power Query Editor

While fixing data at the source is always the best approach, it's not always possible. Fortunately, Power BI has a powerful built-in tool that acts as your data quality command center: the Power Query Editor. This is where you can profile, clean, and transform your data before it gets loaded into your report model.

To get started, open your Power BI file, go to the Home tab, and click Transform data. This will launch the Power Query Editor, opening the door to a suite of data cleaning tools.

Step 1: Get a High-Level Overview with Data Profiling

Before you start fixing things, you need to understand the scope of the problem. Power Query's data profiling features give you an instant snapshot of your data's health without you having to inspect every row manually.

In the Power Query Editor, go to the View tab in the ribbon. Here, you'll find three incredibly useful options in the "Data Preview" section:

• Column quality: Check this box, and a small bar will appear at the top of each column showing you the percentage of data that is Valid, has an Error, or is Empty. This is the quickest way to spot columns with a significant number of nulls or data type conversion issues.
• Column distribution: This provides another visual bar under the column quality bar, displaying the distribution of values. It shows you the number of distinct (different) and unique (appearing only once) values. A high number of unique values in a category column (like "Country") might indicate inconsistent entries.
• Column profile: Clicking on a column and checking this box gives you a more detailed view below your data preview. It provides a visual distribution of the values and a list of each specific value along with its frequency. This is perfect for spotting typos, misspellings, or other outliers at a glance.

Spend a few minutes with these views enabled. You'll quickly identify which columns need your attention most.

Step 2: Hunt Down and Handle Nulls, Blanks, & Errors

Null or blank values are the most common data quality culprits. They can throw off your calculations (like averages) and make your reports incomplete. Errors are equally problematic, often popping up when you try to convert a column from one data type to another (e.g., having the text "N/A" in a column you're trying to set as a number).

Finding and Removing Empty Rows

If you notice a high percentage of empty values via the Column Quality indicator, you can choose how to handle them. A common clean-up step is to remove rows that are entirely blank or rows that are blank in critical columns.

In the Home tab, click the Remove Rows dropdown. Here you have a few options:

• Remove Blank Rows: This gets rid of any row where every single cell is empty.
• You can also filter a specific column by clicking the dropdown arrow on its header. Uncheck "(null)" or "(blank)" to exclude those rows from your dataset.

Replacing Nulls and Errors

Sometimes, removing a row isn’t the right move. If an order is just missing a "Discount" value, you probably want to assume the discount was zero instead of deleting the entire sale. This is where "Replace Values" comes in.

1. Right-click on the header of the column you want to clean.
2. Select Replace Values.
3. In the dialog box, enter "null" in the "Value To Find" box.
4. Enter "0" (or another appropriate default) in the "Replace With" box.

You can follow the exact same process for errors by selecting Replace Errors instead.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 3: Eliminate Duplicates

Duplicate records can completely invalidate your metrics. If you have two entries for the same $5,000 sale, your revenue will be overstated by $5,000. Power Query makes finding and removing them simple.

• To remove duplicates based on a single column (e.g., Order ID): Right-click the header of the unique identifier column (like "OrderID") and select Remove Duplicates. Power Query will keep the first instance it finds and discard all others.
• To remove duplicates based on the entire row: If you need to find rows that are identical across all columns, go to the Home tab, click the Remove Rows dropdown, and select Remove Duplicates.

Generally, you'll want to remove duplicates based on a primary key column to ensure each unique transaction or entity is counted only once.

Step 4: Standardize Your Formatting

Inconsistent text entries are a silent killer of good reporting. If your data has "Google," "google," and "Google LLC" all in the same "Source" column, Power BI will treat them as three distinct sources. Here’s how to clean that up.

• Trim & Clean: Hidden spaces are a frequent problem. An entry like "USA" won't group with "USA". Select the column, go to the Transform tab, click Format, and choose Trim. This removes leading and trailing whitespace. The Clean option removes non-printable characters. It’s good practice to apply both to all your text columns.
• Adjust Case: To fix case sensitivity issues (like "google" vs. "Google"), use the same Format menu and select Capitalize Each Word or UPPERCASE. This forces all text into a standard format.
• Replace Values: For standardizing different spellings of the same thing, the "Replace Values" feature is your best friend. Right-click the column, select "Replace Values," and systematically replace variations ("U.S.A.", "United States") with a single standard ("USA").

Step 5: Verify Data Types

The final sanity check in Power Query is making sure every column has the correct data type. An incorrect data type prevents you from performing the right kind of analysis.

Look for the small icon next to each column header:

• ABC: Text
• 123: Whole number
• 1.2: Decimal number
• Calendar icon: Date
• Clock icon: Time

If a column that should contain numbers is set to "Text," you won't be able to sum it. If a date column is set to "Text," you can't use a date slicer. To change a data type, simply click the icon and select the correct type from the list. If you see errors after the change, it means there are values in that column that couldn't be converted, and you'll need to go back and handle them.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Building a Data Quality Monitoring Page

As a final step, consider creating a dedicated data quality page within your Power BI report. You can hide this page from the final published version, but it can serve as a valuable diagnostic tool for you and your team.

On this page, you can build visuals that monitor the health of your data, such as:

• Card visuals showing the count of null values in critical columns.
• Table visuals that are filtered to only show rows containing errors.
• A bar chart showing value counts for a categorical field, which can help you quickly spot new misspellings or anomalies.

This "health check" dashboard allows you to keep an eye on data quality over time as new data is refreshed, without having to dive back into the Power Query Editor every time.

Final Thoughts

Building reliable reports starts with clean, trustworthy data. Putting in the effort to profile, scrub, and standardize your information in Power Query is one of the most valuable activities you can perform in Power BI. By creating a repeatable data cleaning process, you ensure that the insights you deliver are accurate, leading to smarter, more confident business decisions.

For many teams, the process of wrestling with data cleaning in tools like Power BI can become a full-time job, pulling them away from actual analysis. We built Graphed to automate and simplify this entire workflow. By connecting your Google Analytics, Shopify, Salesforce, and other accounts, our AI handles the complex data modeling and cleaning for you. You can build dashboards and get instant answers just by asking questions, skipping the manual clean-up process and going straight to insights.