Removing Duplicate Records and Handling Missing Values in Power BI

Course: Microsoft Power BI Training

Topic: Removing Duplicate Records and Handling Missing Values

Course Link: Microsoft Power BI Training

Demo Link: Register for a Free Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Data Cleaning in Power BI

Data cleaning is an essential part of data preparation in Microsoft Power BI. Before creating reports, dashboards, charts, and analytical models, data must be checked for duplicate records, missing values, inconsistent entries, and other quality problems.

Power BI provides Power Query Editor, a data transformation tool that helps analysts clean and prepare information before loading it into the data model.

Two important data-cleaning activities are:

• Removing duplicate records: Identifying and removing repeated records according to the appropriate business key or combination of columns.
• Handling missing values: Identifying empty, null, or unavailable values and deciding whether to replace, retain, fill, or remove them.

These operations help improve reporting accuracy, reduce misleading calculations, and make datasets more reliable for business analysis.

Example

Consider a sales dataset containing the following records:

This dataset contains a repeated order and missing values in the Quantity and Sales Amount columns. If these issues are not investigated, reports may display incorrect totals or incomplete analysis.

2. Learning Objectives

After completing this topic, learners will be able to:

• Understand duplicate records and missing data.
• Identify duplicate rows and duplicate business keys.
• Remove duplicates using Power Query Editor.
• Understand the difference between null values, blank text, zero, and errors.
• Replace missing values with appropriate values when justified.
• Remove rows containing missing values when appropriate.
• Use Fill Down and Fill Up transformations.
• Apply conditional logic to handle missing data.
• Use Power Query M code for repeatable data-cleaning operations.
• Validate cleaned data before loading it into Power BI.

3. Understanding Duplicate Records

3.1 What Are Duplicate Records?

Duplicate records are rows that repeat information already represented in another row. Depending on the dataset and business rules, duplicate records may be completely identical or may share the same identifier while containing different values in other columns.

For example, an order might appear twice because the source system exported the same transaction more than once.

3.2 Types of Duplicates

3.3 Why Duplicate Records Occur

• Repeated imports from Excel or CSV files.
• Duplicate transactions in source systems.
• Combining multiple datasets with overlapping records.
• Appending monthly or quarterly files containing repeated rows.
• Incorrect joins between tables.
• Repeated form submissions or system exports.
• Inconsistent identifiers or text formatting.

3.4 Why Removing Duplicates Matters

Duplicate records can create inaccurate business results. For example, if a sales transaction worth ₹25,000 is recorded twice, a simple sum may count ₹50,000 instead of ₹25,000.

• Prevents inflated sales totals when duplicate transactions are invalid.
• Improves the reliability of customer and product counts.
• Supports cleaner data relationships and reporting.
• Reduces unnecessary records in analytical datasets.
• Improves confidence in business decisions.

Important: Never remove repeated rows automatically without understanding the business meaning. Two identical-looking transactions can sometimes represent two legitimate purchases.

4. Understanding Missing Values

4.1 What Are Missing Values?

Missing values occur when a field does not contain the information expected for a particular record. They may result from incomplete data entry, unavailable information, failed imports, optional fields, or limitations in the original source system.

4.2 Common Types of Missing or Invalid Data

4.3 Null Versus Zero

A null value is not the same as zero. Null generally means that a value is unavailable or unknown, whereas zero represents a known numeric value.

For example, a sales amount of zero may mean that a transaction generated no revenue. A null sales amount may mean that the transaction amount has not been provided.

Replacing every null value with zero without investigating its meaning can distort averages, totals, and business conclusions.

4.4 Common Causes of Missing Values

• Users leave optional fields empty.
• Data is not collected by the source system.
• Excel formulas return empty results.
• Data sources use different schemas.
• Joins or merges do not find matching records.
• Data conversions produce errors.
• Information is unavailable at the time of collection.

5. Introduction to Power Query Editor

Power Query Editor is used to connect to data sources, inspect records, transform columns, remove duplicates, handle missing values, and prepare tables for loading into Power BI.

5.1 Opening Power Query Editor

1. Open Power BI Desktop.
2. Import a dataset using Home > Get Data.
3. Select the required source, such as Excel, CSV, or SQL Server.
4. Choose Transform Data to open Power Query Editor.
5. Select the table or query that needs cleaning.
6. Inspect the columns and rows before applying transformations.

5.2 Important Areas of Power Query Editor

6. Removing Exact Duplicate Rows

6.1 When Should Exact Duplicates Be Removed?

Remove exact duplicates when the repeated rows are confirmed to be accidental copies rather than separate legitimate events.

For example, if a source file contains the same customer record twice and each column matches, the repeated row may be unnecessary.

6.2 Step-by-Step Procedure

1. Open Power Query Editor.
2. Select the relevant query.
3. Select all columns if the objective is to remove rows that match across the entire row.
4. Open the Home tab.
5. Select Remove Rows and then Remove Duplicates. Depending on the interface version, the command may appear under a related Remove Rows menu.
6. Inspect the remaining rows and compare the row count with the original dataset.
7. Rename the query step if necessary to make the transformation easier to understand.

6.3 Example Before and After

Before cleaning:

After removing exact duplicates:

The table now contains three unique rows instead of four.

7. Removing Duplicates Based on Selected Columns

Sometimes duplicate records must be identified using only a particular column or combination of columns rather than comparing every field.

7.1 Removing Duplicates Using One Column

Suppose a customer table contains Customer ID, Customer Name, City, and Email. If each Customer ID should identify one customer, duplicate detection can be based on Customer ID.

1. Select the Customer ID column.
2. Open the Home tab.
3. Choose Remove Rows > Remove Duplicates.
4. Check the resulting table and investigate any removed records with conflicting details.

7.2 Removing Duplicates Using Multiple Columns

A composite key combines multiple fields to identify a record. For example, a business might use Order ID and Product ID together to identify a unique order line.

1. Select Order ID.
2. Hold Ctrl and select Product ID.
3. Choose Remove Duplicates.
4. Verify that the combination is genuinely unique according to the business rules.

7.3 Important Consideration

Power Query does not guarantee which specific duplicate row will be retained. If matching keys have different values in other columns, do not assume that the first row will always remain. Define a reliable rule for selecting the preferred record, such as keeping the latest timestamp, before removing duplicates.

8. Identifying Duplicates Before Removing Them

It is often better to investigate duplicates before deleting them. This helps determine whether they represent data errors or valid business events.

8.1 Using Keep Duplicates

1. Select the column or columns that should be unique.
2. Open the Home tab.
3. Choose Keep Rows > Keep Duplicates.
4. Review the records that share the selected values.
5. Determine the appropriate corrective action.

8.2 Using Group By

The Group By operation can count how many times each identifier appears.

1. Select the identifier column, such as Customer ID.
2. Choose Group By from the Home tab.
3. Group by Customer ID.
4. Select Count Rows as the aggregation.
5. Filter the resulting count column to values greater than one.

This identifies customer IDs that appear more than once without immediately deleting any records.

8.3 Using Data Profiling

Power Query offers column-quality, column-distribution, and column-profile tools to help investigate the contents of a dataset. These features can highlight valid values, empty values, errors, and distinct-value patterns.

Enable these features from the View tab and inspect whether the profiling is based on the top 1,000 rows or the entire dataset. Use the entire dataset option when a complete quality assessment is required.

9. Handling Null and Blank Values

9.1 Identifying Null Values

1. Open the query in Power Query Editor.
2. Locate the column that may contain missing values.
3. Open the filter dropdown in the column header.
4. Inspect the available values and select null or blank entries as applicable.
5. Review the affected records before deciding how to handle them.

9.2 Removing Empty Rows

If an entire row contains no meaningful information, removing it may be appropriate.

1. Select the appropriate query.
2. Open the Home tab.
3. Choose Remove Rows and select the option for removing blank or empty rows, where available.
4. Confirm that only genuinely empty rows were removed.

Warning: Removing empty rows is not the same as removing every row that has a missing value in any column. A row with a valid Order ID and a missing optional comment may still be useful.

9.3 Filtering Null Values From a Specific Column

If a field is mandatory, such as a transaction identifier, you may need to exclude records where that field is null.

1. Open the column filter.
2. Deselect null or blank entries as appropriate.
3. Apply the filter.
4. Record how many rows were removed and why.

Use this method only when records without that field cannot be used reliably for the intended analysis.

10. Replacing Missing Values

Instead of deleting records, you may preserve them by replacing missing values. The replacement depends on the meaning of the field and the analytical purpose.

10.1 Replacing Null With Zero

Replacing null with zero is suitable only when zero has a valid business interpretation, such as an unrecorded optional count that is confirmed to mean no activity.

1. Select the numeric column.
2. Choose Replace Values from the Transform tab or the available column context menu.
3. Specify null as the value to replace where the interface supports it.
4. Enter 0 as the replacement.
5. Validate the results and confirm the replacement is justified.

Power Query may handle null values differently from text placeholders. For a reliable transformation, use the appropriate null-aware M expression when necessary.

10.2 Replacing Null With Text

For descriptive fields, missing values may be replaced with a label such as "Unknown" or "Not Provided".

Example:

Such labels should be used consistently and should not be mistaken for real locations.

10.3 Replacing Placeholder Values

Source systems sometimes represent missing information using values such as N/A, NA, Unknown, a hyphen, or an empty string. These values must be standardized before analysis.

For example, a text column can be cleaned by replacing "N/A" with null, provided that N/A does not have a different valid meaning in the dataset.

11. Using Fill Down and Fill Up

Fill Down and Fill Up are useful when missing values occur because a source table records a category or label only once for a group of rows.

11.1 Fill Down

Fill Down copies the most recent non-null value from above into subsequent null cells in the selected column.

Example before:

Example after Fill Down:

To apply Fill Down:

1. Select the relevant column.
2. Open the Transform tab.
3. Choose Fill > Down.
4. Inspect the output to confirm that each filled value belongs to the correct group.

11.2 Fill Up

Fill Up copies the next available non-null value from below into preceding null cells.

To apply it, select the column and choose Transform > Fill > Up.

Use Fill Up only when the following value is an appropriate replacement for the missing value.

11.3 Limitations of Fill Operations

• Fill Down can assign the wrong category if rows are not correctly ordered.
• Fill Up can incorrectly assign a value across unrelated groups.
• Neither operation should be used to invent unknown financial or measurement values.
• Sort and group records correctly before filling values.

12. Handling Missing Values With Conditional Logic

Conditional columns allow you to create rules for handling missing information according to business requirements.

12.1 Example: Handling Missing Quantity

Suppose a sales dataset contains a Quantity column. If the quantity is missing, you should investigate the source rather than automatically assume it is zero.

A conditional rule might classify records as follows:

12.2 Creating a Conditional Column

1. Open Power Query Editor.
2. Select the Add Column tab.
3. Choose Conditional Column.
4. Enter a descriptive column name, such as Quantity Status.
5. Define conditions for missing, zero, and valid values.
6. Set the appropriate output for each condition.
7. Review the results and adjust the rules as required.

13. Handling Errors Separately From Missing Values

Missing values and errors are different data-quality issues. A null value means that information is absent, while an error indicates that Power Query could not successfully evaluate or convert a value.

13.1 Example of a Conversion Error

A Sales column may contain the values 1200, 1500, and "Unknown". When Power Query attempts to convert the column to a whole number, the text value may cause a conversion error.

13.2 Removing Error Rows

1. Select the column containing errors.
2. Open the Home tab.
3. Choose Remove Rows > Remove Errors.
4. Review the removed records and confirm they can be excluded.

13.3 Replacing Errors

Where appropriate, select the affected column and use the Replace Errors command. Choose a replacement that is compatible with the column's data type and business meaning.

Do not replace every error with zero automatically. Investigate whether the error is caused by invalid source data, an incorrect data type, or an invalid transformation.

14. Using Power Query M Code

Power Query M is the formula language used by Power Query to define data transformations. Although the interface creates most transformation steps automatically, understanding M code helps users build repeatable and more advanced cleaning workflows.

14.1 Removing Exact Duplicate Rows

The following example removes rows that match across all columns:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    RemovedDuplicates = Table.Distinct(Source)
in
    RemovedDuplicates

This example assumes that the workbook contains an Excel table named SalesData.

14.2 Removing Duplicates Based on a Specific Column

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    RemovedDuplicates = Table.Distinct(Source, {"OrderID"})
in
    RemovedDuplicates

This example compares rows using OrderID. It should only be used when OrderID is expected to identify one record. If multiple legitimate order lines share an order ID, this rule may remove valid information.

Power Query does not guarantee which duplicate row is retained. When records with the same key contain different values, explicitly define how to select the preferred record.

14.3 Replacing Null Values With Zero

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    ReplacedNulls = Table.ReplaceValue(
        Source,
        null,
        0,
        Replacer.ReplaceValue,
        {"Quantity"}
    )
in
    ReplacedNulls

This example replaces null values in Quantity with zero. Use it only if a missing quantity can legitimately be interpreted as zero.

14.4 Replacing Null Values With a Text Label

let
    Source = Excel.CurrentWorkbook(){[Name="CustomerData"]}[Content],
    ReplacedNulls = Table.ReplaceValue(
        Source,
        null,
        "Unknown",
        Replacer.ReplaceValue,
        {"City"}
    )
in
    ReplacedNulls

This example replaces null values in the City column with "Unknown". The replacement is appropriate only when the column is intended to contain text.

14.5 Removing Rows With Null in a Required Column

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    FilteredRows = Table.SelectRows(
        Source,
        each [OrderID] <> null
    )
in
    FilteredRows

This example removes records whose OrderID is null. It assumes that an OrderID is mandatory and that records without one cannot be reliably processed.

14.6 Removing Null Values From Multiple Required Columns

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    FilteredRows = Table.SelectRows(
        Source,
        each [OrderID] <> null
            and [Product] <> null
    )
in
    FilteredRows

This example retains only rows with non-null OrderID and Product values. It does not automatically remove empty strings or whitespace-only text values, which may require separate cleaning.

14.7 Filling Down Missing Values

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    FilledDown = Table.FillDown(
        Source,
        {"Region"}
    )
in
    FilledDown

This fills null values in Region using the previous non-null value. Sort the source data appropriately before applying this transformation.

14.8 Combining Duplicate Removal and Null Handling

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    FilteredRows = Table.SelectRows(
        Source,
        each [OrderID] <> null
    ),
    RemovedDuplicates = Table.Distinct(
        FilteredRows,
        {"OrderID"}
    )
in
    RemovedDuplicates

This workflow first removes records with missing OrderID values and then removes duplicate OrderID values. Use it only when OrderID is a valid unique key. Otherwise, choose a suitable composite key or a business-specific record-selection rule.

15. Complete Practical Example: Cleaning a Sales Dataset

Consider a retail company that imports monthly sales records from several branches. The original dataset contains repeated order IDs, missing quantities, blank product names, and unavailable sales amounts.

15.1 Original Dataset

15.2 Step 1: Inspect the Dataset

Open Power Query Editor and inspect the columns, data types, row count, and missing values. Use column-quality indicators and filters to understand the extent of the problems.

15.3 Step 2: Investigate Duplicate Orders

Compare the Order ID, Product, Quantity, and Sales Amount columns. Confirm whether the repeated order is an accidental duplicate or a legitimate separate transaction.

If Order ID is a unique transaction identifier and the repeated record is confirmed to be invalid, remove the duplicate using the appropriate key or row comparison.

15.4 Step 3: Handle Missing Product Names

Investigate the source to determine whether the product can be identified from a product code or another reliable field. If the product cannot be identified, consider using "Unknown Product" for reporting or separating the record for investigation.

15.5 Step 4: Handle Missing Quantity

Do not automatically replace the missing quantity with zero. Check the original sales system or transaction details. If the correct quantity cannot be recovered, retain null or flag the record for review according to the reporting requirements.

15.6 Step 5: Handle Missing Sales Amount

Check whether the sales amount can be calculated from a reliable quantity and unit price. If the information is insufficient, retain the missing value or flag the transaction rather than creating an unsupported amount.

15.7 Step 6: Handle Missing Region

Check whether the branch, customer address, or another trusted field can identify the region. Use Fill Down only if the original structure indicates that missing region values inherit the preceding region. Otherwise, use an appropriate lookup or an "Unknown" category.

15.8 Step 7: Validate the Results

• Compare the original and cleaned row counts.
• Confirm that accidental duplicates have been removed.
• Verify that valid transactions have not been deleted.
• Review remaining null values and errors.
• Check column data types and totals.
• Document the cleaning rules for future refreshes.

16. Data Quality Validation After Cleaning

Cleaning data is not complete until the results have been checked. Validation ensures that the transformation steps have improved the dataset without removing important information.

16.1 Row Count Validation

Compare the number of rows before and after cleaning. A large reduction in row count should be investigated to confirm that only unwanted records were removed.

16.2 Duplicate Validation

Group by the selected business key and count the records. If the key must be unique, confirm that no identifier appears more than once after cleaning.

16.3 Missing Value Validation

Use column filters and profiling tools to inspect remaining nulls, blank strings, and errors. Decide whether each remaining missing value is acceptable or requires further investigation.

16.4 Numeric Validation

Compare sales totals, quantities, and other important measures before and after cleaning. Differences should be explainable by the specific records removed or values corrected.

16.5 Relationship Validation

If the cleaned data will be related to other Power BI tables, check that the intended key columns have the required uniqueness and matching values. Duplicate keys in a dimension table can prevent a one-to-many relationship from working as intended.

17. Common Mistakes to Avoid

18. Best Practices for Professional Power BI Projects

• Keep an unchanged copy of the source data whenever possible.
• Document why each duplicate-removal rule is needed.
• Use primary keys or appropriate composite keys rather than arbitrary columns.
• Investigate conflicting records before choosing which record to retain.
• Understand the business meaning of null, zero, and blank text.
• Preserve missing values when replacing them would introduce misleading assumptions.
• Use data profiling to identify quality issues early.
• Apply descriptive names to important Applied Steps.
• Keep transformations repeatable so that new data can be cleaned during refresh.
• Check query performance when working with large datasets.
• Consider query folding where supported by the source and transformation.
• Validate the cleaned data before building relationships and report visuals.

19. Practical Exercises

Exercise 1: Remove Duplicate Products

Create a product dataset containing Product ID, Product Name, Category, and Price. Add a few exact duplicate rows.

• Import the dataset into Power BI.
• Remove exact duplicates.
• Remove duplicates based on Product ID only where it is a valid unique key.
• Compare the row counts before and after cleaning.

Exercise 2: Handle Missing Customer Information

Create a customer dataset containing Customer ID, Name, Email, and City.

• Identify null and blank values.
• Replace missing city values with "Unknown" where appropriate.
• Investigate missing Customer IDs separately.
• Use a filter to display records requiring review.

Exercise 3: Clean a Sales Dataset

Create a sales table containing Order ID, Product, Quantity, Unit Price, and Sales Amount.

• Identify repeated transactions.
• Investigate missing quantities and sales amounts.
• Correct data types.
• Remove confirmed duplicate records.
• Validate sales totals and document the cleaning decisions.

Exercise 4: Use Power Query M

Use the Advanced Editor to create a query that filters out records with missing Order IDs, removes confirmed duplicate records, and replaces justified missing text values.

Verify the results in the Power Query preview before loading the table into the model.

20. Interview Questions and Answers

Q1. What is data cleaning in Power BI?

Answer: Data cleaning is the process of identifying and correcting data-quality problems, including duplicate records, missing values, inconsistent formats, and invalid data types, before analysis.

Q2. What is Power Query Editor?

Answer: Power Query Editor is the data preparation environment used to connect to data sources and transform data before loading it into Power BI.

Q3. How do you remove duplicate records in Power BI?

Answer: Select the relevant column or columns in Power Query Editor, then use the Remove Duplicates command under the Home tab's row-removal options.

Q4. What is the difference between exact duplicates and key duplicates?

Answer: Exact duplicates match across all compared columns. Key duplicates share a specified identifier even when other column values differ.

Q5. What is a composite key?

Answer: A composite key combines two or more columns to identify a record uniquely, such as Order ID and Product ID.

Q6. What are missing values?

Answer: Missing values represent information that is unavailable or not provided in a field. They may appear as nulls, blank text, or source-specific placeholders.

Q7. What is the difference between null and zero?

Answer: Null generally represents an absent or unknown value, while zero is an actual numeric value.

Q8. When should missing values be replaced with zero?

Answer: Only when zero is a valid interpretation of the missing information according to the source data and business rules.

Q9. What is Fill Down in Power Query?

Answer: Fill Down propagates the previous non-null value into subsequent null cells in the selected column.

Q10. What is Fill Up?

Answer: Fill Up propagates the next available non-null value from below into preceding null cells.

Q11. How can you identify duplicate records before removing them?

Answer: Use Keep Duplicates, Group By with Count Rows, or suitable data-profiling tools to investigate repeated values.

Q12. What is the purpose of Table.Distinct?

Answer: Table.Distinct is a Power Query M function that removes duplicate rows based on all columns or a specified set of columns.

Q13. Does Power Query always retain the first duplicate row?

Answer: No. Power Query does not guarantee which duplicate will be retained. A reliable selection rule is necessary when matching keys contain conflicting details.

Q14. What is the difference between a null value and an error?

Answer: Null represents missing information, while an error indicates that an operation or transformation could not be evaluated successfully.

Q15. Why should data be validated after cleaning?

Answer: Validation helps confirm that duplicate removal and missing-value handling did not delete valid records, introduce misleading values, or distort important measures.

21. Quick Revision Checklist

• Understand the difference between exact duplicates and key duplicates.
• Identify the correct unique identifier before removing duplicates.
• Inspect conflicting records before deleting them.
• Distinguish null, blank text, zero, placeholders, and errors.
• Choose between replacing, filling, retaining, or removing missing values based on business rules.
• Use Fill Down and Fill Up only when the data structure supports them.
• Understand basic M functions such as Table.Distinct, Table.ReplaceValue, Table.SelectRows, and Table.FillDown.
• Validate row counts, duplicate keys, missing values, and numeric totals.
• Document transformations and keep the process repeatable.

22. Conclusion

Removing duplicate records and handling missing values are essential data-preparation skills for every Power BI developer and data analyst. Power Query Editor provides practical tools for detecting duplicate rows, identifying incomplete information, replacing values, filling missing entries, filtering invalid records, and applying repeatable transformations with M code.

The most important principle is to clean data according to its business meaning rather than applying rules blindly. A well-designed cleaning process removes confirmed errors, preserves valid records, documents decisions, and validates the results before reporting.

To learn more, explore the Microsoft Power BI Training course, register through the course demo registration page, or visit the JustAcademy website.

23. Additional Learning Resources

• Microsoft Learn: Working With Duplicate Values
• Microsoft Learn: Filtering and Removing Empty Values
• Microsoft Learn: Table.Distinct Function
• Microsoft Learn: Dealing With Errors in Power Query
• Microsoft Learn: Clean, Transform, and Load Data in Power BI