Remove Duplicates using Power Query

puneet-gogia-excel-champs

- Written by Puneet

Power Query allows you to remove duplicates from the data, just like in Excel. And in this tutorial, we will learn to remove duplicate values from the data.

Steps to Remove Duplicates with Power Query

Below you have a list of names where we have some names in duplicates, and now you need to use power query to delete these duplicate values.

remove-duplicates-using-power-query

You can use the below steps:

  1. First, select the data and then go to the Data Tab > Get & Transform Data > Get Data > From Other Sources > From Table/Range.
    get-data-from-table-range
  2. After that, click OK to convert your Data into an Excel Table (if it’s not an Excel table already).
    convert-the-data-into-excel-table
  3. Now, when you click OK, it opens the Power Query editor and loads the data into it.
    load-the-data-into-power-query-editor
  4. After that, from the Home Tab, click on the “Remove Rows” drop-down and then click on the “Remove Duplicate” option.
    choose-remove-duplicates-option

 

When you click on it, it removes the duplicate names from the list and leaves you with the unique names only.

leaves-only-the-unique-values

Above, the status bar shows the count of rows with duplicates and without duplicate values. Apart from the “Home Tab”, you can right-click the column header and click the “Remove Duplicates” option.

status-bar-shows-count-of-rows-with-and-without-duplicate-values

It is the same option you used in the above steps and does the exact.

Removing Duplicates When You Have Multiple Columns

If you have more than one column of data, then the power query gives you two different ways to remove the duplicate values.

In the below example, you have two columns, names, and scores.

remove-duplicates-when-have-multiple-columns

Now if you want to remove duplicates by considering duplicates from both columns, then you need to select both columns and use the “Remove Duplicate” option.

select-both-columns-and-click-remove-duplicate-columns

And you can see only one duplicate row when we consider it by selecting both columns.

Information on Duplicate Values

In Power Query, you can also change the view of the data to get the count of duplicate values. Go to the View tab > Column Distribution.

information-on-duplicate-values

Above, you can see how many unique and distinct values you have in each column.

Keeping Duplicate Values

There is an option allowing you to keep duplicates and remove unique values. Home Tab > Keep Rows > Keep Duplicates.

keep-duplicate-values
Last Updated: December 25, 2023