Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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



And 30 other columns as well...




  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

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