Hello Excellers. Today I will share with you a great Excel tip. How to remove really quickly duplicate data records using Power Query.
Power Query (previously called Get & Transform) in Excel 2016 is a really easy way to manage your data, and removing duplicate records is one of those easy tasks.
See a sample set of Excel data in the screen grab below.
Let’s take a look at how easy it is to use Power Query to resolve the duplicate rows of data by following the steps below.
Step 1. Turn Your Data Into An Excel Table.
Select your data and use the shortcut Ctrl+T or Insert Tab | Tables Group | Table to turn your data into an Excel Table. (my data above is already in table format).
Step 2. Import Your Data Into Power Query.
Next, in the Data Tab | Get and Transform Group select From Table/Range.
Step 3. Working In Power Query.
The Power Query editor will automatically open. On the Home Tab | Reduce Rows Group select the Remove Duplicates option.
Step 4. Loading The Cleansed Data Set.
The final step is to load the cleansed data into Excel. Once the duplicates have been removed, you can hit the Close & Load option. This loads your new data set into a new Excel sheet. Job Done!.
You now have a new cleansed data set. Easy as that. The duplicates have been removed.