Remove Duplicates and Errors
Learn how to identify duplicate records, remove unwanted duplicates, detect data errors, and improve data reliability using Power Query in Power BI.
1. Introduction to Duplicates and Errors
Duplicate records occur when the same record or key value appears more than once. Data errors occur when a value cannot be interpreted or processed as expected, such as text in a numeric column or an invalid date.
Why Data Cleaning Matters
- Duplicates can inflate counts and totals.
- Incorrect data types can cause calculation errors.
- Invalid values can reduce report accuracy.
- Consistent records improve analysis and reporting.
- Data validation makes problems easier to identify.
Visual 1: Examples of duplicate records and incompatible values.
2. Understand Duplicate Records
A duplicate depends on the rule you use to identify it. Two rows may be identical across every column, or they may share the same key while containing different values in other columns.
| OrderID | Customer | Amount | Observation |
|---|---|---|---|
| 101 | Samiksha | 2500 | First record |
| 101 | Samiksha | 2500 | Exact duplicate of the first record |
| 102 | Omkar | 1800 | Different order |
| 103 | Pradnya | 3200 | Different order |
3. Remove Duplicates in Power Query
- Open Power BI Desktop.
- Select Home > Transform data.
- Select the required query.
- Select the column or columns used to identify duplicates.
- Open the Home or Transform tab.
- Choose Remove Rows > Remove Duplicates, or use the available column context menu.
- Review the result and verify the remaining row count.
Visual 2: A practical workflow for removing duplicates.
4. Understand Data Errors
Power Query errors can occur when a transformation cannot process a value as expected. For example, converting the text "abc" into a whole number can produce an error.
| Error Type | Possible Cause | Suggested Action |
|---|---|---|
| Number conversion | Text appears in a numeric column. | Inspect the value and correct the source or conversion rule. |
| Date conversion | Invalid date text or incorrect locale settings. | Check the date format and locale. |
| Missing column | A source column was renamed or removed. | Review the affected transformation step. |
| Expression error | A formula uses invalid values or references. | Inspect the formula and its input fields. |
| Source error | A source path or connection is unavailable. | Check connection settings and permissions. |
5. Find and Handle Errors
Before removing errors, determine why they occurred. An error may reveal a genuine source-data problem or an incorrect transformation that should be fixed instead.
- Open the query in Power Query Editor.
- Inspect the column quality indicators when available.
- Locate cells displaying errors in the data preview.
- Select an error cell to inspect its details.
- Correct the source value, transformation, or data type where possible.
- If removal is justified, select the relevant column and use Remove Errors.
- Review the remaining rows and confirm that important records were not lost.
Visual 3: A responsible approach to handling data errors.
6. Example: Correct an Invalid Quantity
Consider a table where Quantity is expected to contain whole numbers, but one record contains the text "abc".
| OrderID | Product | Quantity | Observation |
|---|---|---|---|
| 201 | Laptop | 2 | Valid numeric value |
| 202 | Monitor | abc | Invalid for a whole-number field |
| 203 | Keyboard | 5 | Valid numeric value |
Recommended Steps
- Inspect the original source record for OrderID 202.
- Determine the correct quantity from a trusted source.
- Correct the value if reliable information is available.
- If the value cannot be recovered, apply the approved data-quality rule.
- Validate the resulting column and row count.
7. Practical Exercise
Exercise: Sales Data Quality
Import a sales table containing OrderID, Product, Quantity, UnitPrice, and OrderDate.
- Find exact duplicate rows.
- Determine whether OrderID should be unique.
- Check Quantity for non-numeric values.
- Check UnitPrice for conversion errors.
- Check OrderDate for invalid dates.
- Correct errors when reliable source information exists.
- Remove duplicates only according to documented rules.
- Compare row counts before and after cleaning.
Expected outcome: A cleaned sales query with duplicate handling and error corrections documented.
8. Knowledge Check
Question 1: What is a duplicate record?
A. A record with a missing date only
B. A record repeated according to the selected duplicate criteria
C. A report page
D. A data source connection
Question 2: What should you do before removing duplicates?
A. Choose the columns that define a duplicate
B. Delete every repeated customer
C. Change all values to text
D. Remove all filters
Question 3: What may cause a number conversion error?
A. A valid integer
B. A report title
C. Text such as "abc" in a numeric field
D. A table border
Question 4: What is a good first response to a data error?
A. Always delete the entire query
B. Investigate the cause and inspect the affected value
C. Ignore every error
D. Publish without checking
View Answers
- B — A record repeated according to the selected criteria.
- A — Choose the columns that define a duplicate.
- C — Text in a numeric field can cause conversion errors.
- B — Investigate the cause and inspect the value.
Summary
In this lesson, you learned how to identify duplicate records, select appropriate duplicate criteria, and remove unwanted duplicates using Power Query.
You also learned how data errors occur, how to investigate invalid values, and why correcting an error is often preferable to removing an entire record. Always validate the final dataset before loading it into Power BI.