Prepare the data · Topic 4 of 8
Cleaning Data and Resolving Errors
PL-300 data cleaning: handle nulls, duplicates and inconsistent text, fix type conversion errors and resolve import errors such as missing files or columns.
- 5 min
- Fill Down copies a value into null cells below it. Empty text must become null first.
- Trim removes spaces, Clean removes non printing characters.
- Replace Errors keeps the row, Remove Errors deletes it, Keep Errors isolates it.
What it is
Cleaning turns messy source data into values you can trust: no stray blanks, consistent text, no duplicates and no errors. Power Query records each cleaning action as a step, so the same cleaning runs again at every refresh. Import errors are a related problem. They happen when a source changes, a file moves or a value cannot be converted, and you need to read the message to find the right fix.
How it works
Nulls, blanks and empty text
A null means no value. An empty string looks the same but is text. A cell with only spaces looks empty but is neither. Handle them in the right order.
| Goal | Command |
|---|---|
| Replace null with a value | Replace Values, typing null in the Value To Find box |
| Copy a value into blank cells below it | Fill Down (or Fill Up) |
| Remove rows that are empty | Remove Rows then Remove Blank Rows |
| Remove rows where one column is null | Filter the column to remove null |
Fill Down only fills null, so empty strings must first be replaced with null. This is typical for a report exported with the group name only on the first row of each group.
Inconsistent text
Under Transform then Format: Trim removes leading and trailing spaces, Clean removes non printing characters, and lowercase, UPPERCASE and Capitalize Each Word fix case. Replace Values fixes known variants such as "U.K." to "UK". Tick Match entire cell contents when the value to find must be the whole cell, so "UK" does not also change "UKRAINE". Without it, Replace Values matches part of a cell.
Duplicates and unwanted rows
Remove Duplicates keeps the first row for each unique combination of the selected columns. Select only the key column, not all columns, or near duplicates survive. Remove Rows offers Top Rows, Bottom Rows, Alternate Rows, Duplicates, Blank Rows and Errors. Keep Rows is the opposite, for example Keep Top Rows.
Errors in cells
When a step cannot compute a value, the cell shows Error. Common cause: changing a text column such as "N/A" to a number.
| Option | Effect |
|---|---|
| Replace Errors | Puts a chosen value in the cell, such as 0 or null |
| Remove Errors | Deletes the rows that contain errors |
| Keep Errors | Shows only the error rows, so you can study them |
Use Replace Errors when the row is valid but one value is bad, and Remove Errors when the row is useless. In a custom column, try ... otherwise catches an error and returns a default, for example try Number.From([Qty]) otherwise null.
Import errors
Some errors stop the whole query rather than one cell.
| Message | Cause | Fix |
|---|---|---|
| "DataSource.Error: Could not find file" | The file was moved or renamed | Change Source, or use a parameter for the path |
| "Expression.Error: The column 'X' of the table wasn't found" | The source column was renamed or removed | Edit the step that names the column |
| "Expression.Error: The key didn't match any rows in the table" | A sheet or table name no longer exists | Repoint the Navigation step |
| "DataFormat.Error: We couldn't convert to Number" | Text such as "N/A" in a numeric column | Replace the text first, or replace errors |
| Credential or privacy errors | Sign in expired or levels conflict | Data source settings |
Read the step name in the message, select it in Applied Steps and look at the preview just before it. The cause is usually in the step before the red one.
A worked scenario
A monthly finance workbook arrives with the region name only in the first row of each block, a "Qty" column that contains "n/a", and a trailing space after some account names. The analyst replaces empty text with null, applies Fill Down on Region, runs Trim on the account column, uses Replace Values to turn "n/a" into null, and then changes Qty to a whole number. The refresh later fails because someone renamed the sheet, so she edits the Navigation step.
What to remember for the exam
- Fill Down copies a value into null cells below it. Empty text must become null first.
- Trim removes spaces, Clean removes non printing characters.
- Replace Errors keeps the row, Remove Errors deletes it, Keep Errors isolates it.
- Remove Duplicates works on the selected columns only.
- A missing file or column is an import error. Fix it at the step that names it.
To fix a whole column at once for text with different casing, use Format, then Capitalize Each Word or lowercase, instead of many Replace Values steps.
How the exam asks about this
- "Only the first row of each group has the category name." Fill Down.
- "Values contain leading spaces and do not match." Trim.
- "A conversion error appears for a few rows, which you want to keep." Replace Errors.
- "The refresh fails after a column was renamed." Edit the step that references it.
Common mistakes
Removing duplicates on all columns. Rows that differ in one column stay.
Using Remove Errors to hide a real problem. Check why the conversion failed.
Forgetting Match entire cell contents. The replacement hits parts of other values.
Applying Fill Down before replacing empty text. Nothing happens, because the cells are not null.
Check yourself
Which command copies a value into null cells below it?
Fill Down.
What is the difference between Replace Errors and Remove Errors?
Replace Errors changes the cell value and keeps the row, Remove Errors deletes the rows.
How do you stop Replace Values changing part of a cell?
Tick Match entire cell contents.
Topic 4 of 8 in Prepare the data
Test yourself on Prepare the data
The full Microsoft PL-300 question bank has 657 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Microsoft. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.