Find And Remove Duplicate Values With Power Query Pivot tables only list unique values for items in the Rows area, so this pivot table will automatically remove any duplicates in your data. You now have a pivot table that mimics a tabular set of data! Go to the Design tab ➜ select Subtotals ➜ select Do Not Show Subtotals. You will also need to remove any subtotals from the pivot table.
![excel query table remove column excel query table remove column](https://exceltown.com/wp-content/uploads/2019/03/selectcolumns.png)
The Criteria range can be left blank and the Copy to field will need to be filled if the Copy to another location option was chosen.
![excel query table remove column excel query table remove column](https://i1.wp.com/radacad.com/wp-content/uploads/2018/09/2018-09-25_11h57_50.png)
This command will alter your data so it’s best to perform the command on a copy of your data to retain the original data intact. When you press OK, Excel will then remove all the duplicate values it finds and give you a summary count of how many values were removed and how many values remain. There are also handy Select All and Unselect All buttons above you can use if you’ve got a long list of columns in your data.
![excel query table remove column excel query table remove column](https://i0.wp.com/www.fourmoo.com/wp-content/uploads/2018/11/Replace-to-delet.png)
The first image highlights all the duplicates based only on the Make of the car.In the above example, there is a simple set of data with 3 columns for the Make, Model and Year for a list of cars.
![excel query table remove column excel query table remove column](https://www.excel-easy.com/examples/images/microsoft-query/select-database.png)
Video Tutorialĭuplicate values happen when the same value or set of values appear in your data.įor a given set of data you can define duplicates in many different ways. In this tutorial, we are going to look at 7 different methods to locate and remove duplicate values from your data.