How to discover duplicates utilizing Microsoft Power Inquiry

Microsoft Power Inquiry needs less customized knowledge when trying to find replicate records in a Microsoft Excel sheet.

Image: dennizn/Adobe Stock There are a number of methods to find duplicates in a Microsoft Excel sheet. You can utilize functions, conditional format, filtering and more. Microsoft Excel even provides a built-in feature that will discover and delete duplicates for you. If you’re working with a large amount of information or importing data into Power BI, you may want to consider utilizing Power Question to signify duplicates, without erasing them. In this tutorial, I’ll show you how to use Microsoft Power Query to discover duplicates in Excel information. The benefit is that the procedure requires no special

understanding of functions or conditional format. You can download the demo for this Power Inquiry tutorial. SEE: Feature comparison: Time tracking software application and systems(TechRepublic Premium )I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier variations through Excel 2010. How to connect to Power Inquiry The initial step to using Power Query with Excel information is to make a connection in between the 2, which is easy; however, Power Question needs that you format the information as a Table item. You don’t need to stress over it though because when you start the process, Excel will trigger you to transform a data variety into a Table if needed. Next, you identify the Excel information, which contains replicate records, as shown in Figure A, by doing the following: Click anywhere inside the Table or information variety and then click the Information tab. If triggered to create a Table, click OK. In the Get & Transform Data group

, click From Table/Range. Figure A Identify the Excel data. That’s it. As you can see in Figure B, the data is now in Power Inquiry. Figure B Load the data into Power Inquiry. With the information in Power Question, it’s time to discover the duplicates

1. . There are two replicate rows, which you can easily discover by sorting, but you don’t have to arrange when utilizing Power Inquiry.
2. How to label duplicates in Power Question We don’t want to eliminate the duplicates. Rather, we wish to identify them in some way. We’ll include a new column that recognizes duplicates in some way. This procedure is simpler than you may think. Initially, we need to add an index column, which will make more sense

   later. In the meantime, do the following: Click the Add Column tab.

   From the Index Column dropdown in the General group

   , select from Zero. Figure C Include an index column.

   As you can see in Figure C, Power Inquiry added an index column– a column of consecutive values that begin with 0. Must-read Windows protection Before we can recognize duplicates, we need to determine what constitutes the duplicate.

   We’ll use an advanced group based upon the Date, Value and Personnel columns. The other two columns are

   1. irrelevant.
   2. Statistically, it isn’t difficult that the exact same employee may make two sales on

   the exact same day of the exact same value, however it’s

   not likely. In our simple data set, this is the best we can do due to the fact that there isn’t a column that distinctively recognizes each record, such as

   an invoice number. First, we

   require to produce this group: Select the Date, Worth and Worker rows by holding down Shift while you click each header. Click the Transform tab and after that click Group By in the Table group. In the resulting dialog, Power Query occupies the very first 3 dropdowns with the chosen column names. Name the column Discover Duplicates and choose Count Rows from the Operation dropdown. Click Include Aggregation. Call the column Discover Duplicates 2 and pick All Rows from the dropdown(Figure D).

   Click OK. Figure D< img src ="https://d1rytvr7gmk1sx.cloudfront.net/wp-content/uploads/2022/09/PQDuplicates_D-770x583.jpg" alt =""width=" 770"height="

3. 583″/ > Set up the group based upon Date, Value and Personnel. Figure E Power Query shows a special set of records.
4. As you can see in Figure E
5. , the Discover Duplicates column returns the value 2 if the record has a duplicate.

The Find Duplicates 2 column returns the term Table, which we’ll look after later on. What this question does not do is show the duplicate records. Currently, the table returns only special records, which isn’t what we desire. We want to keep all records, which is an easy task: Click the Expand button for the Discover Duplicates 2 column. Uncheck the columns that comprise the group: Date, Worth and Worker. Uncheck the Use Original Column Call As Prefix choice

if required(Figure F). Click OK. Figure F Expand the Find Duplicates 2 column to see all the records.

Figure G Power Query shows all the records.