How to Validate Data in Power BI with Column Quality

How to Validate Data in Power BI with Column Quality

Validating data is one of the most important steps before building reports in Power BI. Without clean, reliable data, even the most polished dashboards can give misleading insights. One simple yet powerful way to validate data in Power BI is by using Column Quality in Power Query.

In an earlier blog, we explored how to validate data using Group By in Power BI. In this post, we’ll focus on another method: validating data with Column Quality.

This built-in profiling feature allows quick checks for errors, empty values, and unusual patterns, ensuring data quality before it ever reaches the model.

The Column Quality option is available in Power Query ribbon under the View tab. The View tab also contains other useful options.

Why Validate Data in Power BI?

When working with business data, common issues include:

• Missing or blank values
• Duplicate or inconsistent entries
• Outliers in numeric fields
• Incorrect data types

Catching these issues early prevents inaccurate totals, broken relationships, and incorrect KPIs later in the report.

What is Column Quality in Power Query?

Column Quality is a profiling feature that provides an at-a-glance summary of each column in Power Query. It shows:

• Valid values: data that is correctly formatted
• Errors: values that don’t match the expected type
• Empty: blank or null values

This helps to immediately identify whether a column is clean or needs transformation.

Sample Dataset Used in this Power BI Validation Demonstration

To demonstrate an hands-on exercise to explain data validation using Column quality. I will be using the following table:

Note about the data set:

This dataset contains around 3,000 rows, but only a portion is shown here for demonstration purposes. If you would like the full dataset for practice, feel free to mention it in the comments.

To demonstrate how to validate data, some errors have been intentionally included in this sample:

• Customer Name: certain names (e.g., Dareen Powers) contain leading spaces.
• Order Date: some cells contain text values such as “T”, “Q”, or “R” instead of valid dates.
• Customer ID: includes text entries such as “L”, “M”, and “T” instead of numeric IDs.

Hands-On Demonstration: Validate Data with Column Quality

1. Load the Sample dataset using Query Editor.
2. Navigate to Query Editor ribbon and then View tab. Check the options for Show whitespace.
   Checking “Show whitespace” show that Customer Name “Dareen Powers” contains some leading spaces and also Segment “Home Office” is displayed with the new line. These formatting issues in the data can be corrected if needed.

3. Validating Column quality. Under the View tab, select the Column quality option. This will display a small section below the column headers, displaying if the values in the column are Valid, Error or Empty as shown in the figure below:

In the current rows of data, all columns display 100% valid with no errors. However, remember that by default, Power Query profiles only the first 1,000 rows of data. To ensure accuracy, it’s best to profile the entire dataset to check for potential issues.

To include the entire dataset in profiling, navigate to the bottom of the Query Editor (below the data preview) and select Column profiling based on entire dataset, as shown in the following figure:

After selecting “Column profiling based on entire dataset”, the Column Quality bar will highlight errors in some columns, such as Order Date and Customer Ref#. When hovering over these columns, Power Query displays a summary of the issue along with a suggested fix, such as Remove errors as shown in the figure below:

The developer should decide to take Query Editor suggestions and remove the errors or explore more to understand the cause of the error.

Understanding the Errors Reported by Column Quality

It is crucial to understand the errors identified by Column Quality before attempting to remove them. This ensures that valid data is not accidentally discarded.

1. Select the Order Date column as Column quality reported error on it. Right-click on the Column quality bar and select “Keep errors”, as shown in the following figure:

Observe that the view changes to show the errors in the Order Date column. Notice a new step “Kept Errors” is added to the Applied Steps.

2. Click on the first error (in the data preview window) to see the details. It displays the error as shown in the following figure:

The error shows that a non-date data type value (T) is present in the Order Date column and query editor is unable to parse it.

Tip: To return to the entire error list, cross/delete the step “Order Date” under Applied steps.

When checking other errors, similar details appear, indicating that a few non-date values are present in the Order Date column. For the purposes of this example, these rows are not required, so we can accept Power Query’s suggestion to remove the errors.

3. Delete the step “Kept Errors” from the Applied Steps and return to the entire data preview.
4. Select the Order Date column, mouse hover on the Column quality bar and from the error summary, select “Remove Errors”. Notice how Applied Steps show “Removed Errors” step.
   This will remove errored rows and display Column quality on Order Date as Valid 100%.
5. Similarly, we can explore the errors in Customer ID column or accept the Query editor suggestion of removing the error.

Column Quality is just one way to ensure clean data. Another simple approach is to validate data with Group By in Power BI, which helps confirm totals and detect issues across different dimensions.

Have questions or want the full sample dataset to practice with? Drop a comment below — I’d be happy to share it with you.

New to Power BI? Get hands-on, step-by-step guidance in our Power BI for Beginners course. Start learning now.

---