Data Profiling and Handling Errors
The Qty column now has a type, but somewhere in 20,000 rows a few cells say Error, and nobody knows how many or why. Scrolling to find them would take all afternoon. Power Query can summarise every column's health in a glance, and then give you three clean ways to deal with what it finds.
The rule
Profile the whole column, then fix errors at their cause, not just the cell.
Three profiling views live on the View tab:
- Column quality puts a bar under each header: the percentage of values that are Valid, Error and Empty.
- Column distribution shows how many distinct and unique values a column has, with a small histogram. Distinct counts the different values; unique counts the values that appear exactly once. For Pune, Pune, Mumbai, Delhi there are 3 distinct values and 2 unique ones.
- Column profile opens a panel for the selected column: count, errors, empties, minimum, maximum, average and a chart of how often each value appears.
By default these views look at only the top 1,000 rows, as the status bar at the bottom says. Click that message and choose profiling based on the entire data set when trouble might be further down.
Errors come in two sizes:
- A cell-level error hits one value, such as
N/Athat cannot become a number. The rest of the query runs. - A step-level error stops the whole query: the source file has moved, or a step asks for a column that no longer exists.
For cell-level errors, right-click the column header:
| Action | Effect | Use it when |
|---|---|---|
| Keep Errors | Shows only the rows with errors | You are investigating |
| Remove Errors | Deletes those rows | The whole row is junk |
| Replace Errors | Puts a value in place of each error | A sensible stand-in exists, usually null |
Often the best fix is upstream. If the till writes N/A for "not recorded", add a step before Changed Type that replaces N/A with null, and the error never happens.
Prefer null to 0 as a stand-in. Averages skip nulls but count zeros: prices of 30, 40 and a replaced error average 35 with null, but about 23.33 with 0.
The shortcut
Look, locate, decide. Look at column quality over the entire data set. Locate the problem with Keep Errors and click a cell to read its message. Decide: remove the row, replace the value, or fix the step that caused it.
Look
Column quality says 100% valid, but only for the top 1,000 rows. Switched to the entire data set, it shows 3 errors.
Drills
Answer fast — trust the rule.
Which view shows the percentage of valid, error and empty values in each column?
A City column holds Pune, Pune, Mumbai, Delhi, Delhi, Chennai. How many distinct and unique values does it have?
Column quality shows 0% errors, yet loading the query reports errors in 3 rows. What is the likely reason?
Prices are 30, 40 and one error. Which replacement for the error gives an average price of 35?
A refresh stops with "The column 'Shop' of the table wasn't found." What kind of error is this?
Remember
- Profile the whole column, then fix errors at their cause, not just the cell.
- Look, locate, decide: remove the row, replace the value, or fix the step upstream.
- Traps: profiling only the top 1,000 rows, and replacing errors with 0 when null is honest.