Keep Duplicate Records Using Power Query
From Excel 2016 has a new set of features known as Get & Transform which was known as Power Query in Excel version 2010. It provides you with a platform to perform a series of steps to help you to transform your Excel data. There is one step which will help you to take is to keep duplicate records. Everyone removes duplicate lines in Excel sheet but in some cases, we need to keep them so in that Excel will be helpful for you.
For this tutorial, I have the following set of data that is given below. Now you will be seeing that the marked ones are the duplicates value that I want to keep.
Here are the steps are given below to follow in your excel:
STEP1: Create Table
first you need to convert your data into Excel Table so for that select your data and press the shortcut Ctrl + T or go to Insert > Table.
It will easily turn your data into an Excel Table.
STEP2: Open Power Query
If you are using Excel 2016:
Go to Data > From Table (Under Get & Transform)
Or if you are using Excel 2013 & 2010:
Power Query > Excel Data > From Table
STEP3: Working In Power Query
After following them you will see a Power Query Editor on your screen.
Now follow this:
Go to Home > Keep Rows > Keep Duplicates
At last to open a new worksheet in your Excel workbook,
Click Close & Load from the Home tab and this will open with the updated table.