Forum Discussion

AbbasG's avatar
AbbasG
Memorable Member
4 years ago
Solved

Removing duplicates from the data

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.

ParticularDatePriceMerged
ABC10/8/2021$40.00ABC - $40.00
ABC10/11/2021$39.00ABC - $39.00

 

Any help will be appreciated!

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

2 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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.