How to display duplicates or unique Values using Power Query. Watch the video below:
Most of the time users wish to display unique values from their worksheet data. To display unique values we can use a variety of methods like advanced filter, autofilter or VBA. All these methods cannot display all the duplicates at the same time easily. Power Query in Excel can help us display all the duplicates quickly and easily.
First we click inside our worksheet data.
We then click on the DATA tab and from the ‘Get & Transform Data’ command group, we select ‘From Sheet’.
The ‘Create Table’ window opens. The data in our worksheet is automatically selected. Also the checkbox next to the item ‘My Table has headers’ is checked. We click on OK.
The Power Query editor fires up. Our data is displayed. We click inside our data. From the ‘Reduce Rows’ Group we select the item ‘Keep Rows’ and from the drop down options we select ‘Keep Duplicates’. Our data now displays a sample of thousand rows of the duplicates.
From the ‘Close Group’ we click on ‘Close & Load To…’ A new window with the title ‘Import Data’ opens. By default ‘Table’ and ‘New Worksheet’ options are selected. We just need to click on OK. A new worksheet with the analyzed duplicate data is displayed.
Now we can add more data to our original worksheet as required. We then select the worksheet with the analyzed data like the worksheet ‘ShowDuplicates’ and select ‘Refresh All’ from the ‘Queries & Connections’ group to view the new analyzed data.
In this manner we can display all the duplicates or the unique data in our worksheet using Power Query. For any doubts we can watch the training video again.
You can download the sample workbook below: