Data Quality Validation
Learn how to validate data quality in Power BI, identify incomplete or inconsistent records, verify data types, check business rules, and prepare trustworthy datasets for reporting and analysis.
1. Introduction to Data Quality Validation
Data quality validation is the process of checking whether data is complete, accurate, consistent, valid, and suitable for its intended purpose. It helps identify problems before data is used in reports, dashboards, or business decisions.
Power Query provides data profiling, filtering, transformation, and error-handling features that can help validate data during the preparation stage.
Key Data Quality Dimensions
- Completeness: Required fields contain values.
- Accuracy: Values correctly represent the source information.
- Consistency: Similar values follow the same rules and formats.
- Validity: Values follow defined data types and business rules.
- Uniqueness: Records that must be unique do not contain unwanted duplicates.
- Timeliness: Data is sufficiently current for its intended use.
Visual 1: Important dimensions of data quality.
2. Validate Data Using Column Quality
Power Query's column quality feature helps identify valid values, errors, and empty values. It is a useful first step when investigating a newly imported dataset.
Steps to Inspect Column Quality
- Open Power BI Desktop.
- Select Home > Transform data.
- Select the query you want to inspect.
- Open the View tab in Power Query Editor.
- Enable Column quality.
- Review the indicators displayed above the data preview.
- Investigate columns with errors or empty values.
Visual 2: Column quality can help identify columns requiring attention.
3. Validate Data Types and Required Fields
Each column should use a data type suitable for its contents. Numeric values should not contain unexplained text, dates should represent valid dates, and required identifiers should be checked for missing values.
| Column | Validation Rule | Example of a Problem |
|---|---|---|
| OrderID | Must not be null; uniqueness depends on the business key. | A missing identifier. |
| Quantity | Must be numeric and follow the quantity rules. | Text such as "abc". |
| UnitPrice | Must be numeric and within an acceptable range. | An unexplained negative price. |
| OrderDate | Must be a valid date when required. | An invalid date string. |
| Must follow the required format if provided. | A value without an expected email structure. |
Validation Checklist
- Confirm each column's data type.
- Identify null values in required fields.
- Check for conversion errors.
- Check identifiers for unwanted duplicates.
- Review numeric values against expected ranges.
- Verify that dates follow the correct source format.
4. Validate Business Rules
A value can have a valid data type and still violate a business rule. For example, a negative quantity may be invalid for an ordinary sales order but valid for a return transaction. Validation must therefore consider the context of the data.
Visual 3: Apply business context before deciding whether a value is invalid.
Examples of Business Rules
- OrderID must be present for every sales record.
- Quantity must follow the rules for sales and returns.
- UnitPrice must fall within an approved range.
- DeliveryDate must not precede OrderDate unless the source rules explain the case.
- A customer identifier must match a valid customer record.
5. Create Validation Columns in Power Query
A conditional column or custom column can classify records according to a defined rule. For example, a sales dataset can flag rows with missing order identifiers or invalid quantities.
Example: Quantity Validation
Assume Quantity is numeric and the rule requires a non-negative value. The following M expression illustrates how to classify records when nulls are also possible.
In Power Query, you can create a custom column from the Add Column tab and adapt the expression to your actual column names and business requirements.
Visual 4: Validation categories can help separate valid records from exceptions.
6. Validate Relationships and Reference Data
Data quality also depends on how tables relate to each other. For example, every non-null ProductID in a sales table may be expected to match a ProductID in the Products table.
A value that does not match a reference table may indicate a typo, an outdated identifier, a missing lookup record, or an expected business exception.
Reference Validation Checklist
- Check that key columns use compatible data types.
- Look for null values in required key fields.
- Identify keys in the fact table that do not exist in the lookup table.
- Check whether the lookup table's key is unique when required.
- Investigate unmatched records before removing them.
Visual 5: Compare identifiers with a reference table to find unmatched records.
7. Practical Exercise: Sales Data Validation
Dataset Columns
Use a sales dataset containing OrderID, ProductID, Quantity, UnitPrice, OrderDate, and CustomerID.
Practice Tasks
- Check required fields for missing values.
- Confirm that Quantity and UnitPrice have appropriate data types.
- Inspect the dataset for duplicate order records.
- Check Quantity against the rules for sales and returns.
- Inspect OrderDate for invalid dates.
- Compare ProductID values with the Products table.
- Create a validation status column for records requiring review.
- Record the number of valid, invalid, and missing-value records.
- Correct confirmed errors and document unresolved exceptions.
Expected outcome: A validated dataset with clearly identified exceptions and documented data-quality rules.
8. Knowledge Check
Question 1: What is data quality validation?
A. Changing report colors
B. Checking whether data meets defined requirements
C. Creating a new dashboard page
D. Publishing a report without reviewing it
Question 2: What can Column quality help identify?
A. Valid values, empty values, and errors
B. Only report titles
C. Only visual formatting
D. Only dashboard permissions
Question 3: Why are business rules important?
A. Every negative value is always invalid
B. They determine whether values are acceptable in context
C. They automatically remove all errors
D. They replace the need for data types
Question 4: What should you do with an unmatched ProductID?
A. Delete every sales record
B. Replace it with a random identifier
C. Investigate the mismatch and follow the data-quality rules
D. Ignore all reference tables
View Answers
- B — Checking whether data meets defined requirements.
- A — Valid values, empty values, and errors.
- B — Business rules determine whether values are acceptable in context.
- C — Investigate the mismatch and follow the rules.
Summary
In this lesson, you learned how to validate data completeness, accuracy, consistency, validity, uniqueness, and timeliness. You explored column quality indicators, data type checks, required-field validation, and business-rule checks.
You also learned how to create validation status columns and compare identifiers with reference tables. Apply documented rules, investigate exceptions, and verify the final dataset before using it in Power BI reports.