Removing duplicates from the data

[AbbasG]

Hello People,

I have the data on the Power BI desktop. Where there are duplicate rows as below.

I need to remove the duplicates but want to keep the one with the minimum price.

I tried to remove the duplicates in the query editor, but it removes the one with minimum price.

Particular	Date	Price	Merged
ABC	10/8/2021	$40.00	ABC - $40.00
ABC	10/11/2021	$39.00	ABC - $39.00

Any help will be appreciated!

[v-chenwuz-msft · Accepted solution]

Hi @AbbasG 

You can try this method.

First , sort Particular , then sort price by ascending. This is make sure the min price to be the first under the same Particular

And then use the remove duplicate row to remove. Because this remove duplicates rows will all ways keep the first one.

Or you can try dax to reach that. Try column like the following :

Column =
VAR _MIN =
    MINX(
        FILTER( 'Table', [Particular] = EARLIER( Table_DAX[Particular] ) ),
        [Price]
    )
RETURN
    IF( _MIN = [Price], 1, BLANK() )

And select the 1

I put the pbix file in the end you can reference.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@AbbasG , refer if this topic can help

https://exceleratorbi.com.au/remove-duplicates-keep-last-record-power-query/