Need help with removing duplicates

[Anonymous]

Hello everyone,

I've got this table with duplicates, but when I add the "remove duplicates" step in Power Query, it removes the wrong rows.

Here's an example of the table:

Pay attention to xyz10005 and xyz10016.
I need Power BI to select the lowest status. 
But the remove duplicate selects the rows that have a higher status.


After removal:

as you can see above, the wrong rows have been selected to keep.

Can anybody help me out on this one?

With kind regards,

Lazzanova

[Anonymous]

Hi @Anonymous,

If you want you will always have the lowest value, you should group maia.Persoon.cd_relatie by minimum Count value. You can do it with this formula:

= Table.Group(#"Changed Type", {"maia.Persoon.cd_relatie"}, {{"Count", each List.Max([Status]), type nullable number}})

And you would have always the minimum value.

I hope this solve your question!

[Anonymous]

The problem is that the table has other columns as well:

And 30 other columns as well...

[Anonymous]

Ok, so you have two options:

First one, you sort the status column in ascending order, so if you remove duplicate in maia.Persoon.cd_rate you would keep the minimum values of status,

Second option, you can create a key column, then duplicate the table to keep the key column and the two columns we had (maia.Persoon.cd_relatie and status), and then group by minimum value and combine this table with the other table by the key column.

If you have problems tell me @LaZZaNoVa-61 

[Anonymous]

The first option is what I've done, but it is not working.
The second option is something I have not tried out yet.