How to use Power Query to display a list of duplicate values or records in Excel

2 years ago 350

Highlighting oregon adjacent deleting duplicate records successful Excel is elemental but listing them isn't arsenic easy. Fortunately, you tin usage Power Query to easy show a database of duplicate values oregon records.

Whether duplicate records are bully oregon atrocious depends connected circumstantial conventions that you determine, not Excel. For the astir part, duplicate information is communal due to the fact that galore records repetition the aforesaid values wrong the aforesaid column.

SEE: 83 Excel tips each idiosyncratic should master (TechRepublic)

On the different hand, a duplicate record—where each of the values are repeated—can spell occupation erstwhile reporting and analyzing the information acceptable astatine large. It's casual to find duplicates; you tin filter them retired of a information acceptable oregon format them utilizing a conditional format rule. What you can't easy bash successful Excel is show lone the duplicate records. The bully quality is that doing truthful is casual utilizing Power Query, truthful successful this article, I'll amusement you however to usage Power Query to show a database of duplicates.

I'm utilizing Microsoft 365, but Power Query is disposable done 2010, arsenic an add-in. Excel Online doesn't afloat enactment Power Query, but you tin tally queries. You tin download the objection .xlsx file oregon enactment with your ain data. This nonfiction assumes you person basal Excel skills, but adjacent a beginner should beryllium capable to travel the instructions to success.

What is Power Query?

Power Query lets you link to overseas and section information and past lets you alteration that information that truthful you tin usage it successful Excel without changing the root data. It's casual to usage but unfortunately, astir users are unfamiliar with it. Although we're utilizing a elemental diagnostic wrong Power Query, this nonfiction isn't a basal instauration to Power Query. Now, let's really usage Power Query to show duplicates.

Define duplicate

To enactment efficiently with duplicates, you request to specify what a duplicate is wrong the discourse of your information and however you usage it. Any worth that occurs much than erstwhile wrong the aforesaid file is simply a duplicate. For instance, galore records successful a tracking information acceptable mightiness person the aforesaid transportation day oregon customer. These are duplicate values, and they are common.

SEE: Windows 10: Lists of vocal commands for code designation and dictation (free PDF) (TechRepublic)

The word tin besides specify a grounds wherever each worth successful the grounds is repeated successful different record. In different words, the full grounds is simply a duplicate. For instance, 2 records that incorporate the aforesaid transportation date, lawsuit and invoice fig mightiness origin a problem; you wouldn't privation to invoice a lawsuit doubly for the aforesaid order. These are duplicate records, and you'll usually privation to delete 1 of them.

For our purposes successful this article, we'll usage Power Query to show a database of some duplicate types: values repeated successful the aforesaid file and values repeated crossed each columns.

As you tin spot successful Figure A, the objection expanse contains duplicate values. In specified a tiny information set, duplicates aren't hard to spot. Working with them is simply a antithetic matter, particularly if the information acceptable is large. We besides person 1 duplicate record.

Figure A

How to database duplicate values with Power Query

Let's usage Power Query to spot values repeated successful the columns. To bash so, click anyplace wrong the information set, click the Data tab, and past bash the following:

In the Get & Transform Data group, click From Sheet. The resulting model shows the information successful Power Query (Figure B).
Select the file that you privation to cheque for duplicates. In this case, the day file is already selected, truthful let's usage it.
On the Home tab (in Power Query, not Excel), click the Keep Rows dropdown successful the Reduce Rows group.
In the resulting dropdown list, take Keep Duplicates.

Figure B

As you tin spot successful Figure C, the information acceptable repeats 2 dates astatine slightest once. To spot duplicates successful the different columns, prime a file and repetition measurement 3. For example, Figure D shows the duplicate worth successful the unit column.

Figure C

Figure D

Now you cognize that astatine slightest 2 columns repetition astatine slightest 1 value. If you like, cheque for duplicates successful each column; you'll find that each file repeats a worth astatine slightest once.

What if you privation to spot if there's a duplicate record? Let's tackle that next.

How to database duplicate records with Power Query successful Excel

To rapidly recap, a duplicate grounds repeats values crossed each columns. To cheque the information acceptable for duplicate records, prime each of the columns successful Power Query. To bash so, clasp down the Shift cardinal portion you click each column. Then, take Keep Duplicates from the Keep Rows dropdown. Figure E shows the result. As you mightiness person guessed already, the effect is the aforesaid arsenic the unit query successful the past section.

Figure E

Granted, this is simply a elemental example, and the results were casual to predict. That won't ever beryllium true, particularly successful a ample information set.

How to usage the results of your Power Query search

Seeing the records mightiness not beryllium enough. Fortunately, you tin instrumentality the results to Excel arsenic a sheet. Simply click Close & Load successful the Close group. Doing truthful volition make a caller expanse and prevention the resulting information set, arsenic shown successful Figure F. Once the information is successful Excel, you tin usage it arsenic you would immoderate different information set.

Figure F

This is simply a elemental usage for Power Query. Take immoderate clip to go acquainted with the antithetic options truthful you tin use it to much analyzable tasks.