Resolving Errors, Nulls, and Data Quality Issues

Quick Answer: Common data quality issues include type mismatch errors, null values, duplicates, and inconsistent formatting. Use Remove Errors to discard error rows, Replace Errors to substitute default values, Fill Down/Up for nulls in hierarchical data, and Replace Values for standardizing inconsistent entries.

Understanding Errors in Power Query

Errors in Power Query appear as red [Error] cells and prevent data from loading correctly. Common causes:

Resolving Errors

Option 1: Remove Errors — Deletes entire rows containing errors

Right-click column header → Remove Errors

M code: Table.RemoveRowsWithErrors(previousStep, {"ColumnName"})

Option 2: Replace Errors — Substitutes a default value for errors

Right-click column header → Replace Errors → Enter replacement value

M code: Table.ReplaceErrorValues(previousStep, {{"ColumnName", 0}})

Option 3: Keep Errors — Isolates error rows for investigation

Right-click column header → Keep Errors

This is useful for debugging — create a reference query that keeps only errors, investigate the patterns, then handle them in the main query.

Option 4: Try-Otherwise in M — Custom error handling in formulas

try [Price] * [Quantity] otherwise 0

Handling Null Values

Null values represent missing or unknown data. The appropriate handling depends on context.

Fill Down / Fill Up

Fill Down copies the last non-null value downward — ideal for hierarchical or grouped data where headers appear only once:

Right-click column → Fill → Down

Example before Fill Down:

After Fill Down:

Fill Up works in the reverse direction.

Replace Values

Replace specific values (including nulls) with a standard value:

Right-click column → Replace Values → Value to Find: null → Replace With: "Unknown"

M code: Table.ReplaceValue(previousStep, null, "Unknown", Replacer.ReplaceValue, {"ColumnName"})

Conditional Column

Create logic-based replacements:

Add Column → Conditional Column →
  If [Status] is null Then "Pending" Else [Status]

Remove Null Rows

When entire rows are null (common in Excel imports):

Home → Remove Rows → Remove Blank Rows

Resolving Data Quality Issues

Inconsistent Values

Text data often has inconsistencies that must be standardized:

Example: Standardizing city names:

• "new york", "New York", "NEW YORK", " New York " → all should become "New York"

Steps:

1. Trim to remove leading/trailing spaces
2. Capitalize Each Word to standardize case
3. Replace Values for known variations

Duplicate Rows

Remove exact duplicates:

Home → Remove Rows → Remove Duplicates

Remove duplicates based on specific columns:

1. Select the columns that define uniqueness
2. Right-click → Remove Duplicates

Warning: Remove Duplicates keeps the first occurrence. Sort the data first if you need to keep the most recent or specific record.

Data Type Corrections

Setting correct data types is critical and should be done early:

Select column → Transform tab → Data Type → Select correct type

Common type corrections:

Exam Tip: Always set data types as an early step in the query. If you change types after other transformations, Power Query may insert an automatic "Changed Type" step that can conflict with your manual type assignments.

Resolving Import Errors

Import errors occur when data cannot be loaded into the model:

On the Exam

The PL-300 frequently tests:

• Choosing between Remove Errors and Replace Errors for different scenarios
• Using Fill Down to handle null values in hierarchical data
• Identifying common causes of data import errors
• Setting appropriate data types to prevent downstream errors
• Understanding the order of operations in data cleaning steps