How to compare 2 rows and retain a row that is lower

[PeraZo]

Hello community,

In the dataset below, there are 2 rows with different rate.

I want to retain row that is lower (cheaper) comparing tariff.

The destination is same but the origin is different.

So in this case, I want to retain the red boxed row since 720 is lower than 780.

Is there way to do this by using Power Query Editor? 

[v-yilong-msft · Accepted solution]

Hi @PeraZo ,

@lbendlin , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:  

I create a table as you mentioned.

Then I go to the Power Query, add index and remove duplicates.

Finally you will get what you want.

Best Regards

Yilong Zhou

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

[PeraZo]

Thank you for quick response.

I tried but it did not delete the row that is on top of the other one.

It retained all the column A the same value.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[lbendlin]

1. sort your table by Destination and Tariff

2. select the Destination column and Remove Duplicates

That will keep the first row for each Destination value.