A twist to turn Keep Duplicates into Keep Unique in #PowerQuery

In Power Query Editor, there is a button of “Keep Duplicates” in the ribbon. It’s located under Keep Rows in the section of Reduce Rows in the Home tab of the ribbon.

 

 

With this, it is super easy to keep duplicates in our dataset. By the way, duplicates mean value appears more than once. Let’s take a look at the screenshot below, “A”, “B”, and “F” are duplicates,

IMPORTANT: Power Query is case sensitive!

while “b”, “C”, “D”, “E” are uniques (i.e. values appear only once). 

Unique values appear only once

When we apply “Keep Duplicates” to Column1, we obtain the following result.

Keep Duplicates is a result of a quite complicated M code…

The good thing about Power Query is, we do not have to worry about the M code auto-generated. Power Query handles the M codes, we handle the User Interface, which is mainly “select and click”. Easy for users, isn’t it?

Why is there NO “Keep Unique”?

However there are moments we could not find an appropriate action from the User Interface. For example, why “Keep Unique” is not there? I have no answer for that. Luckily, we can get what we need by modifying the M code, some (if not most) of the time.

Below is the M code auto-generated for “Keep Duplicates”.

= let columnNames = {"Column1"}, addCount = Table.Group(#"Changed Type", columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(#"Changed Type", columnNames, removeCount, columnNames, JoinKind.Inner)

It is alien language to most Excel users, including myself. 😅 Nevertheless, we can borrow the step of “Keep Duplicates” and then turn it into “Keep Unique” with a little twist to the M code, as demonsatred below:

Here we go!

Can’t believe it is as simple as that? Yes it is, when you know where to modify.

Hey, don’t forget to rename the step accordingly.

I hope you like this trick. 😎

Want to learn more about different ways to keep the rows you need in Power Query? Here’s a short video for you. (Cantonese version is also avaliable)

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Power Query and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.