Like the Remove Duplicates feature in Excel, Power Query also can be used to remove duplicates from a data set.
But, why should we use Power Query to get rid of the Duplicates?
Here is why . . .
Following is a sample sales data containing duplicate records (The records with arrows against them).
Now, I want to get rid of the duplicate records from the above data set. How should I do that?
In the Data tab of the Excel ribbon > Remove Duplicates > OK > Done!
Great! But, I am expecting more data in the coming days. I should repeat this procedure every time when I receive new data, right?
In that case, Remove Duplicates is certainly not the Tool I should use to get rid of the duplicate records. If the data is supposed to change frequently, we should go for a more dynamic method like Power Query to remove duplicates.
To remove the Duplicate Records from the above the above data using Power Query,
Select a cell in the data set > go to the Data tab in Excel ribbon > From Table/Range > Click OK in the Create Table dialog
Selected data is loaded into the Power Query Editor of Excel
Right-click on the column header of the first column > Select Remove Duplicates
Note that, in this particular case the record name is sufficient to spot the duplicates. If your data doesn’t have a column like this, you can select multiple columns and apply this option.
Duplicate records are gone and the Unique records remain.
To load this data into the Excel sheet, Click on the Split button for Close & Load in the Home tab > Close & Load To…
In the Import Data dialog > Select Existing worksheet > Select the cell where you want to place the data (Here, I have selected the cell I2) and Click OK
See the new table doesn’t have any Duplicate record in it.
And here is the Best part!
Whenever you add new data to the Source Table or make some changes to this table, you don’t need to repeat these steps again.
Just right-click on the output table and select Refresh.
Watch my Video on 12 Methods to Clean Data using Power Query