PowerPivot – Removing Duplicate Rows in Excel

You have a list of data and you want to remove duplicates in Excel before importing into PowerPivot.   There are two approaches that will give you the same visual result, but only one that will suit PowerPivot.

1. Advanced Filter – Filter Unique Rows, this is only a visual filter so the full list including duplicates still be imported when added to the PowerPivot model.   Optionally select the Copy to another location and use that list to import into PowerPivot.

 

 

 

 

 

 

 

 

2. Remove Duplicates – This option is preferred and will be discussed below.  You will notice the Remove Duplicates feature in the Data menu of the ribbon.

 

 

 

 

 

But first, we select our list of duplicate values.

 

 

 

 

 

 

 

 

 

 

In the Data menu select Remove Duplicates, then select the column list that applies.

 

 

 

 

 

 

 

You will get a message to advise the number of     duplicate rows found and the number of unique rows that remain.

 

 

 

The data list is now ready for PowerPivot.