Keep Unique Rows Only based on unique values in one column

Support
1

Is there an action where I can keep only rows with a unique value? Or to put it another way remove ALL duplicate values?

I can see:

  • “Deduplicate rows” which keeps one duplicate
  • “Keep duplicates” which will get rid of all unique rows

I think I could use the “Keep duplicates” followed by the “Deduplicate rows” to produce a list of the values I want removed, and then use Iterate against the original source to pull out all the unique values?

so for eg, if we had this data:

Name Type Moral
Adam man good
Eve woman good
Adder snake bad
Eve woman good
Adam man good
Adam man good
Eve woman good

I’d want to end up with only the Adder row!

2

This should do what you want.
Keep unique row only.morph (3.8 KB)

1 Like
3

Keep Unique Row Only Example.morph (3.8 KB)

2 Likes
4

Thanks Jochen!

That works perfectly for unique rows.

I’ve now come across a situation for some other data where I need to do the same thing but for unique values in a column.

I have data in the following format

Column 1 Column 2 Column 3 Column 4
202305 sah12 jjf98 1000
202305 sah12 jjf99 1221
202306 sah11 jjf98 1234
202306 sah12 jjf99 2243
202306 sah13 jjg01 1234
202306 sah14 jjg02 3424

I then merge columns 2 and 3 to generate a combined id, e.g. sah12_jjf99 (there’s probably a better way to do this!)

so the table becomes:

Column 1 Column 2 Column 3 Column 4 Column 5
202305 sah12 jjf98 1000 sah12_jjf98
202305 sah12 jjf99 1221 sah12_jjf99
202306 sah11 jjf98 1234 sah11_jjf98
202306 sah12 jjf99 2243 sah12_jjf99
202306 sah13 jjg01 1234 sah13_jjg01
202306 sah14 jjg02 3424 sah14_jjg02

so you can see there are now 2 sah12_jjf99, and I want to remove both of these rows while keeping all of the data to give:

Column 1 Column 2 Column 3 Column 4 Column 5
202305 sah12 jjf98 1000 sah12_jjf98
202306 sah11 jjf98 1234 sah11_jjf98
202306 sah13 jjg01 1234 sah13_jjg01
202306 sah14 jjg02 3424 sah14_jjg02

And then I will remove Column 5 to give me my source columns to work from:

|Column 1 | Column 2 | Column 3 | Column 4|
|— | — | — | —| —|
|202305 | sah12 | jjf98 | 1000 |
|202306 | sah11 | jjf98 | 1234 |
|202306 | sah13 | jjg01 | 1234 |
|202306 | sah14 | jjg02 | 3424 |

I’ve been trying to get this to work with your method and the one provided by nicktagz, but I’m getting tied up in knots because I can work out where the duplicates are but I can’t keep all the other data in tact!

5

Just a quick additional note, I’ve been able to get around this by exporting to Excel and processing there and re-importing, but there must be a way of doing this in EasyMorph - I’ve well and truly hit my data manipulation talent wall with this one!

6

I guess you could do this more generic, too, but at least for this example, it works.

Keep unique row only - v2.morph (4.6 KB)

1 Like
8

Jochen, you star!

managed to get this to work for my data after a few hiccups!!!

It would be good to have this as an Action though!!!

1 Like
9

Here is my version. Not generic as it requires specifying columns explicitly, but it probably can be made generic with unpivoting/pivoting.

Keep unique row only - DG.morph (2.5 KB)

image
image1352×516 24.3 KB

1 Like