Forum Discussion
Need help with removing duplicates
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
4 Replies
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!
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