How to Remove Duplicate Rows in Power BI Power Query?

Are you looking at how to remove duplicate rows in Power BI Power Query? If your requirement is yes, select the column in the Power Query Editor, go to the top ribbon, expand Remove Rows, then click on Remove Duplicates.

Recently, I worked on a Power BI report where my dataset contained numerous duplicate values. Utilizing Power BI Power Query, I successfully removed all duplicate values from my dataset.

This Power BI tutorial explains how to remove duplicate rows in Power BI and how to remove duplicate values from single and multiple columns. Also, we will see how to remove duplicates of case-insensitive in Power BI.

Power BI Remove Duplicate Rows Values

In Power BI, duplicate values result in multiple instances of the same data. Removing duplicate rows in Power BI helps overcome the issue of having multiple instances of the same data.

You can see in the screenshot below that the left-side table contains duplicate rows. After removing the duplicates, the resulting table is displayed on the right side.

Remove Duplicate Rows in Power BI Power Query

How to Remove Duplicate Values from a Single Column in Power BI

Now, we see how to remove duplicate values from a single column in Power BI.

I have a SharePoint list (Supermart) that contains below columns with various data types:

ColumnsData Types
Order IDSingle line of text
Customer NameSingle line of text
StateSingle line of text
Product NameSingle line of text
SalesCurrency
QuantityNumber
Remove Duplicates using Power Query in Power BI Desktop

Now, I will show you step-by-step how to remove duplicates from a single column in Power BI:

1. Open Power BI Desktop and load data. Under the Home tab, click Transform data.

How to Remove Duplicates in Power BI

2. Select the column in the Power Query Editor to remove duplicates.

Removing Duplicate Rows in Power BI

3. Under the Home Tab, click the “Remove Rows” drop-down and then click the “Remove Duplicates” option.

How to Remove Duplicate Rows in Power BI

4. The screenshot below shows that the Order ID has no duplicate values.

remove duplicate from single column in power BI Power query

Follow the above steps to remove duplicates in a single column.

How to Remove Duplicate Values from Multiple Columns in Power BI

In this example, we consider the three columns (i.e., Order ID, Customer Name, and State) to remove duplicates. Check the screenshot below.

Power BI Power Query Removing Duplicate

Now we see step-by-step how to remove duplicates from multiple columns:

1. Go to the Power Query Editor. Then select the three columns (Order ID, Customer Name, and State) by “ctrl + click.”

Remove duplicate from multiple column in Power BI power query

2. Under the Home tab, click the Remove Duplicate option.

remove duplicates from multiple columns in Power BI

3. Now our result table looks like the below screenshot.

A Step-by-Step Guide on How to Remove Duplicates in Power BI

This way, you can remove duplicates from multiple columns in Power BI.

Remove Duplicates Case-Insensitive in Power BI

In this example, I want to ignore the capitalization when removing duplicates in the Power Query. Check the screenshot below.

Power BI Remove Duplicate rows in Power query editor

The easiest way to remove case-insensitive duplicates is by adding a Compare function.

1. First, follow the above steps and remove the duplicate in the Customer Name column.

How to Remove Duplicates in Power BI Power Query M

Following the previous steps, you’ll notice that the duplicate customer names are not removed due to case sensitivity.

2. Then add Comparer.OrdinalIgnoreCase as equation criteria. Check the screenshot below.

= Table.Distinct(#"Removed Other Columns",{"Customer Name",Comparer.OrdinalIgnoreCase})
Removing Duplicate Rows in Power BI

In this way, you can remove case-insensitive duplicates in Power BI.

Conclusion

Select the Power Query Editor column, go to the top ribbon, expand Remove Rows, and then click Remove Duplicates. This way, you can remove duplicate rows in Power BI Power Query.

This tutorial taught us how to remove duplicates from single and multiple columns. Additionally, we learned how to remove duplicates case-insensitive in Power BI.

Moreover, you may like some more Power BI tutorials: