Removing duplicates in Power BI - Step-by-Step Guide

Introduction

Sometimes your data has repeated rows that can confuse your reports. Removing duplicates helps you keep only unique records so your analysis is clear and accurate.

When your sales data has multiple entries for the same order and you want to count each order once

When customer lists include repeated contacts and you want to send one email per person

When product inventory data has duplicate rows and you want to see the correct stock count

When importing data from multiple sources that overlap and you want to clean it before analysis

When preparing data for charts that should show unique categories without repetition

Steps

Step 1: Click

- Home tab in Power Query Editor

The Home tab options appear at the top of the Power Query Editor window

Step 2: Select the column or columns

- Data preview grid in Power Query Editor

The selected columns are highlighted to indicate they will be used for duplicate detection

💡 Select all columns that define a unique row to remove exact duplicates

Step 3: Click

- Remove Rows dropdown in the Home tab

A menu with row removal options appears

Step 4: Click

- Remove Duplicates option in the Remove Rows menu

All duplicate rows based on the selected columns are removed from the data preview

Step 5: Click

- Close & Load button in the Home tab

The cleaned data without duplicates loads back into Power BI for reporting

Before vs After

Before

Data preview shows 100 rows including 20 rows where Order ID and Customer ID repeat exactly

After

Data preview shows 80 rows with all exact duplicate rows removed based on selected columns

Settings Reference

Remove Duplicates

📍 Home tab > Remove Rows dropdown in Power Query Editor

Deletes duplicate rows based on selected columns to keep only unique records

Default: No duplicates removed until action is taken

Common Mistakes

Selecting only one column when duplicates depend on multiple columns

This removes duplicates only based on that one column, leaving other duplicates in the data

Select all columns that together define a unique row before removing duplicates

Removing duplicates on the report view instead of in Power Query

Duplicates remain in the data model and can cause incorrect calculations

Always remove duplicates in Power Query Editor before loading data into Power BI

Summary

Removing duplicates cleans your data by keeping only unique rows based on selected columns

Use Power Query Editor's Remove Duplicates feature on the Home tab after selecting columns

Make sure to select all columns that define uniqueness to avoid partial duplicate removal

Practice

1. What is the main purpose of removing duplicates in Power BI data?

easy

A. To add more rows to the dataset

B. To keep data clean and reliable by eliminating repeated rows

C. To change the data types of columns

D. To create new calculated columns

Solution

1. Step 1: Understand the concept of duplicates

   Duplicates are repeated rows that can cause errors or confusion in analysis.

2. Step 2: Purpose of removing duplicates

   Removing duplicates ensures the data is clean and reliable for accurate reporting.

3. Final Answer:

   To keep data clean and reliable by eliminating repeated rows -> Option B

4. Quick Check:

   Removing duplicates = Clean data [OK]

Hint: Duplicates cause errors; removing them cleans data fast [OK]

Common Mistakes:

• Thinking duplicates add useful data
• Confusing duplicates with data transformation
• Believing duplicates change data types

2. Which of the following is the correct syntax to remove duplicate rows from a table named Sales in Power Query?

easy

A. Table.RemoveDuplicates(Sales)

B. Remove.Duplicates(Sales)

C. Table.Distinct(Sales)

D. DistinctRows(Sales)

Solution

1. Step 1: Recall Power Query function for duplicates

   The correct function to remove duplicates is Table.Distinct.

2. Step 2: Apply function to the table

   Using Table.Distinct(Sales) removes duplicate rows from the Sales table.

3. Final Answer:

   Table.Distinct(Sales) -> Option C

4. Quick Check:

   Table.Distinct = Remove duplicates [OK]

Hint: Use Table.Distinct to remove duplicates in Power Query [OK]

Common Mistakes:

• Using non-existent functions like Remove.Duplicates
• Confusing syntax with other languages
• Adding extra parameters unnecessarily

3. Given the table Customers with columns CustomerID and City, what will be the result of Table.Distinct(Customers, {"CustomerID"})?

medium

A. Removes duplicate rows based on all columns

B. Removes duplicate rows based only on City column

C. Returns the original table without changes

D. Removes duplicate rows based only on CustomerID column

Solution

1. Step 1: Understand Table.Distinct with column list

   When columns are specified, duplicates are removed based on those columns only.

2. Step 2: Apply to CustomerID column

   Here, duplicates are removed based on CustomerID, ignoring other columns.

3. Final Answer:

   Removes duplicate rows based only on CustomerID column -> Option D

4. Quick Check:

   Distinct by columns = Remove duplicates by those columns [OK]

Hint: Specify columns in Table.Distinct to target duplicates [OK]

Common Mistakes:

• Assuming all columns are checked when columns are specified
• Confusing column names in the list
• Thinking it returns unchanged table

4. You wrote Table.Distinct(Sales, "ProductID") to remove duplicates by ProductID but get an error. What is the likely problem?

medium

A. The second argument should be a list of column names, not a single string

B. Table.Distinct does not accept a second argument

C. ProductID column does not exist in Sales table

D. You must use Table.RemoveDuplicates instead

Solution

1. Step 1: Check Table.Distinct syntax for columns

   The second argument must be a list of column names, e.g., {"ProductID"}, not a string.

2. Step 2: Identify error cause

   Passing "ProductID" as a string causes a syntax error because it expects a list.

3. Final Answer:

   The second argument should be a list of column names, not a single string -> Option A

4. Quick Check:

   Columns argument = List, not string [OK]

Hint: Use curly braces {} for column list in Table.Distinct [OK]

Common Mistakes:

• Passing column name as string instead of list
• Using wrong function name
• Assuming single argument works for columns

5. You have a table Orders with columns OrderID, CustomerID, and OrderDate. You want to keep only the first order per customer by date, removing duplicates of CustomerID but keeping the earliest OrderDate. Which approach is best in Power Query?

hard

A. Sort Orders by OrderDate ascending, then use Table.Distinct(Orders, {"CustomerID"})

B. Use Table.Distinct(Orders, {"CustomerID", "OrderDate"}) without sorting

C. Remove duplicates by OrderID column

D. Filter Orders to keep only rows where OrderDate is max per CustomerID

Solution

1. Step 1: Sort Orders by OrderDate ascending

   Sorting ensures earliest dates come first for each customer.

2. Step 2: Remove duplicates by CustomerID

   Using Table.Distinct(Orders, {"CustomerID"}) keeps the first row per customer, which is earliest due to sorting.

3. Final Answer:

   Sort Orders by OrderDate ascending, then use Table.Distinct(Orders, {"CustomerID"}) -> Option A

4. Quick Check:

   Sort then distinct = keep earliest per group [OK]

Hint: Sort by date first, then remove duplicates by customer [OK]

Common Mistakes:

• Removing duplicates without sorting first
• Including OrderDate in distinct columns
• Filtering by max date instead of min