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. 

Previously in Power QueryHow to Remove Columns Using Power Query?

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. 

Keep Duplicate Records Using Power Query

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.

Keep Duplicate Records Using Power Query

STEP2: Open Power Query

If you are using Excel 2016:

Go to Data > From Table (Under Get & Transform)

Remove Columns Using Power Query

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

Keep Duplicate Records Using Power Query

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.

Keep Duplicate Records Using Power Query
There is your new table with all those duplicate rows kept.