This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections: 
Prepare the data (25–30%) 
  --> Profile and clean the data 
    --> Resolve inconsistencies, unexpected or null values, and data quality issues

Note that there are 10 practice questions (with answers and explanations) at the end of each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub's main page.

---

High-quality data is essential for accurate analysis and trustworthy reports. In the PL-300 exam, Microsoft expects candidates to understand how to identify and resolve common data quality problems using Power Query before data is loaded into the model.

This section focuses on handling inconsistencies, unexpected values, nulls, and errors—all of which can negatively impact calculations, relationships, and visuals if left unresolved.

---

Why This Topic Matters for the Exam

From an exam perspective, this topic tests your ability to:

• Diagnose data quality problems using profiling tools
• Choose the correct transformation to fix an issue
• Understand when to remove, replace, or transform data
• Prevent downstream modeling and reporting issues

Most questions are scenario-based, asking what action you should take to fix a specific data issue.

---

Common Data Quality Issues You Must Recognize

1. Null (Blank) Values

Nulls represent missing or unknown data and can cause:

• Incorrect aggregations
• Broken relationships
• Visuals that behave unexpectedly

Common causes:

• Incomplete source data
• Left joins with no matching rows
• Data entry gaps

---

2. Unexpected or Invalid Values

These include:

• Negative values where only positives make sense
• Text values in numeric columns
• Dates outside expected ranges
• Misspelled or inconsistent category names

---

3. Inconsistent Data

Inconsistencies often appear as:

• Mixed casing (USA vs usa)
• Trailing or leading spaces
• Multiple spellings for the same value
• Different date or number formats

---

4. Error Values

Errors usually occur when:

• Converting data types
• Performing calculations
• Parsing malformed data

Examples include:

• Conversion failed
• Divide by zero
• Invalid date format

---

Identifying Data Quality Issues in Power Query

Power Query provides built-in data profiling tools to quickly detect problems:

Column Quality

• Shows percentages of Valid, Error, and Empty values
• Ideal for spotting nulls and errors

Column Distribution

• Displays value frequency and distinct counts
• Helps identify unexpected or inconsistent values

Column Profile

• Provides min, max, average, and other statistics
• Useful for detecting outliers and invalid ranges

Exam Tip: Profiling tools only analyze a sample by default. You may need to enable “Column profiling based on entire dataset” for accuracy.

---

Techniques to Resolve Null Values

Remove Rows

• Used when nulls make a record unusable
• Common for missing primary keys or required fields

Replace Values

• Replace nulls with:
  • 0 (for numeric measures)
  • “Unknown” or “Not Provided” (for text)
  • A default date

Fill Down / Fill Up

• Used for hierarchical or grouped data
• Common in spreadsheets with merged cells

Exam Insight: Replacing nulls should be a business-justified decision, not automatic.

---

Resolving Inconsistencies

Standardizing Text

• Use Transform → Format:
  • Uppercase
  • Lowercase
  • Capitalize Each Word

Trimming and Cleaning

• Trim removes leading/trailing spaces
• Clean removes non-printable characters

Replacing Values

• Normalize spelling differences (e.g., “US”, “USA”, “United States”)

---

Handling Unexpected or Invalid Values

Filtering

• Remove values outside acceptable ranges
• Exclude invalid categories

Conditional Columns

• Create logic to flag or correct invalid data
• Example: Replace negative sales with null or zero

Data Type Corrections

• Ensure columns use appropriate data types
• Prevents aggregation and calculation errors later

---

Fixing Error Values

Replace Errors

• Replace with null or a default value

Remove Errors

• Used when rows are unreliable

Fix the Root Cause

• Change transformation order
• Adjust data type conversion
• Clean data before applying calculations

Exam Tip: Microsoft often tests whether you know why an error occurs, not just how to remove it.

---

Best Practices for PL-300 Candidates

• Always profile before transforming
• Fix issues in Power Query, not DAX, when possible
• Understand the impact of removing vs replacing data
• Keep transformations repeatable and documented
• Prefer clean data models over complex report logic

---

Key Takeaways for the Exam

You should be able to:

• Identify different types of data quality issues
• Choose the correct Power Query tool to resolve them
• Understand the downstream impact on models and visuals
• Interpret profiling results correctly

Mastering this topic ensures cleaner datasets, better models, and fewer surprises during analysis—exactly what the PL-300 exam is designed to validate.

---

Practice Questions

Go to the Practice Exam Questions for this topic.