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.
You can use the below steps:
- First, select the data and then go to the Data Tab > Get & Transform Data > Get Data > From Other Sources > From Table/Range.
- After that, click OK to convert your Data into an Excel Table (if it’s not an Excel table already).
- Now, when you click OK, it opens the Power Query editor and loads the data into it.
- After that, from the Home Tab, click on the “Remove Rows” drop-down and then click on the “Remove Duplicate” option.
When you click on it, it removes the duplicate names from the list and leaves you with the unique names only.
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.
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.
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.
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.
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.
