How To Find Duplicates Using Microsoft Power Query

How To Find Duplicates Using Microsoft Power Query? In Excel, it’s easy to highlight or even delete duplicate data, but it’s more challenging to list them. Thankfully, Power Query makes generating a report that includes the same values or data simple. You, not Excel, should decide what criteria should be used to evaluate whether or not duplicate entries are desirable.

Many entries have identical information because they repeatedly utilize the same values in the same column. On the other hand, problems arise when reporting and evaluating the data set as a whole if there is a duplicate record in which all the values are duplicated. The existence of duplicates may be quickly identified by applying a filter to a data collection or by using a conditional format rule.

Unfortunately, Excel doesn’t make it simple to filter out and highlight duplicate data. The excellent news would be that Power Query makes it simple to present a list of duplicates, and that’s what we’ll be covering in this piece.

Why Should I Use Power Query?

Connecting to local and international data is accessible using Power Query, and the tool also helps you easily convert data for usage in Excel without touching the original. It’s user-friendly, yet the vast majority of people have never used it before. Although an essential Power Query capability is used, this is not a primer on the program. Let’s put Power Query to work and show off those redundant results.

Duplicates Using Microsoft Power Query: How To Get Started

Making a connection between Power Query and Excel data is the initial step; however, Power Query does demand that the data be formatted as a Table object. Therefore, when you begin the process, Excel will ask whether you want to transform a dataset into a Table.

Locate The Mirrored Information In Excel:

• To access the Data tab, choose a cell or range of cells within the Table and click your Data button. If asked, select OK to create a Table.
• To do so, go to the Get & Modify Data section and choose From Table/Range.
• Now that everything is in Power Query, we can look for duplicates. Sorting will reveal the two duplicate rows; however, that step is unnecessary using Power Query.

Power Query: Tagging Duplicate Records

• The duplicates shouldn’t be deleted. What we want to do is categorize them. A new column will be added to the Table to help us find and eliminate duplicates. You shouldn’t be intimidated by this procedure.
• We need to create an index column first; this will become clear in a moment. Do the following for now:
• To add a column, choose it from the tabs.
• Then, choose From Zero from the Reference Column drop-down menu in the Common section.

Importing Data Into Excel: A Guide For Duplicates Using Microsoft Power Query

The data may be easily imported into Excel. To close and load, choose the option from the Close group on the Home tab. Then, select Close & Load from the menu that appears.

Once the information has been restored to Excel, it may be used like any other spreadsheet. A conditional form that emphasizes duplication for Find Duplicates values 1 and 2 is an option to consider. Excel makes it simple to identify duplicates and provides you the option of showing or hiding them.