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.

Advanced Filter

 

 

 

 

 

 

 

 

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.

RemoveDuplicates

 

 

 

 

 

But first, we select our list of duplicate values.

Unsorted

 

 

 

 

 

 

 

 

 

 

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

DistinctListBox

 

 

 

 

 

 

 

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

RemoveDuplicatesResults

 

 

 

The data list is now ready for PowerPivot.

DistinctListRemoveDuplicates

 

 

 

 

 

 

 

 

 

 

 

 

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s