Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

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:

LaZZaNoVa61_1-1666185627069.png

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:

LaZZaNoVa61_2-1666185782262.png


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 4
Luis98
Resolver II
Resolver II

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
Not applicable

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

LaZZaNoVa61_0-1666188675106.png



And 30 other columns as well...




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
Not applicable

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors