Launch Deal · 50% off 1st month ·

1.4 — Profile and Clean the Data

PL-300 Domain 1 · Skill 1.4

What You Need to Know

Before shaping data, you check what's actually in it. Power Query's data profiling tools are on the View tab of the Power Query Editor.

The profiling tools

Tool Shows Use it to find
Column quality % Valid, Error and Empty under each header Errors and nulls
Column distribution A small histogram with distinct and unique counts Duplicates and candidate keys (distinct = unique = row count)
Column profile Full statistics for the selected column (count, error, empty, distinct, unique, min, max, average, standard deviation, NaN, zero) and a value distribution chart Outliers and unexpected values
  • Distinct = how many different values. Unique = how many values appear exactly once.
  • By default, profiling uses only the top 1,000 rows. To profile the whole table, click the status bar text and choose Column profiling based on entire data set. Missing problems in row 50,000 is a classic exam scenario.

Resolving inconsistencies

Problem Fix in Power Query
"NY", "N.Y." and "New York" Replace Values, or merge with a mapping table
Leading or trailing spaces, non-printing characters Format > Trim and Clean
Mixed case that breaks matching Format > lowercase / UPPERCASE / Capitalize Each Word
Blank cells under a group heading (as in Excel exports) Fill Down (or Fill Up)
Header rows or totals inside the data Remove Top Rows / Remove Bottom Rows, Use First Row as Headers, filter out the total rows
Duplicate rows or keys Remove Duplicates on the key columns
Unexpected nulls Replace with a default, filter them out, or fix them in the source

Power Query is case-sensitive, so "Seattle" and "seattle" are two distinct values, and joins on them don't match. The model engine (VertiPaq) is case-insensitive, which can cause errors when two values that only differ by case land in a column that must be unique.

Errors

  • A cell-level error (shown as Error in a cell) happens when a value can't be converted, such as text "N/A" in a column changed to Whole number. The row still loads, but the cell is empty in the model and the refresh reports errors.
  • A step-level error stops the whole query, for example Expression.Error: The column 'Region' of the table wasn't found after a source column is renamed, or DataSource.Error when a file moved.

Fixes for cell-level errors:

  • Replace Errors with a value such as null or 0
  • Remove Errors to drop the rows
  • Keep Errors to see only the bad rows while investigating
  • Change the type Using Locale when the values are dates or decimals in another regional format (for example 03/04/2026 in a day-first format)

Fixes for step-level errors: edit or remove the failing step (the gear icon or formula bar), or update the source path in Data source settings.

Key Concept: Profile the entire data set, find errors and inconsistencies, and fix them in Power Query as early as possible. Never rely on the first 1,000 rows to prove a column is clean.

Exam Traps

  • Trusting column statistics that were based on only the top 1,000 rows
  • Confusing distinct (different values) with unique (values that appear once)
  • Changing a column type before fixing text placeholders like "N/A", which turns them into errors
  • Removing errors when the requirement says the rows must be kept (replace errors instead)
  • Forgetting that Power Query comparisons are case-sensitive

Check your understanding

Practice Scenario

In Power Query, column quality shows 0% errors for the OrderDate column, yet refresh fails with a type conversion error in that column. The table has 2 million rows. What should you do first to find the bad values?

Report issue

Log in to save your progress.

Sources