Cleaning Data by Removing Duplicates, Handling Missing Values, and Correcting Errors

Raw datasets frequently contain incomplete or inconsistent records. Power Query provides several tools to identify and handle these problems.

1. Removing Duplicate Records

Duplicate records occur when the same record appears more than once in a dataset.

For example, a sales file might contain the following rows:

The first and third rows are identical. If both represent the same transaction entered twice, they may cause the total sales value to be overstated.

To remove duplicates:

1. Open Power Query Editor.
2. Select the column or combination of columns that identifies the records.
3. Open the Home or relevant column menu.
4. Choose Remove Rows → Remove Duplicates, where available.
5. Review the resulting data.

Important: Do not remove duplicates blindly. Two legitimate transactions can contain identical values. Select the correct identifying columns and confirm what constitutes a duplicate in your business context.

2. Handling Missing Values

Missing values occur when a field is blank or contains a null value.

For example:

Missing information can affect calculations, grouping, and reporting.

Possible ways to handle missing values include:

• Removing rows when the records are invalid or irrelevant.
• Replacing missing values with an appropriate value when justified.
• Keeping the missing values when they represent genuine unknown information.
• Investigating the source system to determine why the values are missing.

To replace a value:

1. Select the relevant column.
2. Choose Replace Values from the appropriate menu.
3. Specify the value to find and its replacement.
4. Apply the transformation and review the results.

For example, replacing a missing region with "Unknown" may be appropriate when the transaction is valid but the region has not been identified.

Do not replace missing sales amounts with zero unless the business rules support that decision. A missing amount does not necessarily mean that no sale occurred.

3. Correcting Data Types

Each column should use a data type appropriate to the information it contains.

Common Power Query data types include:

• Text
• Whole Number
• Decimal Number
• Fixed Decimal Number
• Date
• Date/Time
• True/False

For example, an OrderDate column stored as text may not behave as expected in time-based reports. Similarly, a SalesAmount column stored as text may not support the intended numeric calculations.

To change a data type:

1. Open Power Query Editor.
2. Select the column.
3. Open the data type selector in the ribbon or column header.
4. Choose the appropriate type.
5. Review any conversion errors.

If dates are interpreted incorrectly, check the regional format and use the appropriate locale when converting the column.

4. Identifying and Handling Errors

Power Query may produce errors when values cannot be converted into the required data type or when a transformation cannot be applied successfully.

For example, converting the text value "Unknown" into a whole number may produce an error.

To investigate errors:

1. Select the affected column.
2. Use the available error-filtering or error-removal options.
3. Inspect the original values that caused the errors.
4. Correct the source values, replace them where justified, or remove invalid records when appropriate.
5. Confirm that the resulting column contains the expected data.

Removing errors is not always the best solution. Investigating their cause helps prevent the same issue from returning during future refreshes.

5. Standardizing Text Values

Inconsistent text values can split a single category into multiple groups.

For example, a Region column might contain "North", "north", and "NORTH". These values may represent the same region but appear as separate categories in a report.

Power Query provides text transformations such as:

• Trim - removes unwanted spaces at the beginning and end of text.
• Clean - removes certain non-printable characters.
• Uppercase - converts text to uppercase.
• Lowercase - converts text to lowercase.
• Capitalize Each Word - changes text capitalization according to the selected transformation.

To standardize text:

1. Select the required text column.
2. Open the Transform tab.
3. Choose the appropriate formatting operation.
4. Inspect the results and confirm that the transformation preserves the intended meaning.

These operations are particularly useful for customer names, product categories, regions, and other descriptive fields.