Power Query for Check If Any Duplicate Rows Contain Specific Value

[AlwaysLearn]

Hi, I am starting to use Power Query, which getting data from a Microsoft Excel in a SharePoint and containing 30k rows data.

It has following data:

Project Name | Gate | Reapproval Number | Value

A | 1 | 0 | 1

A | 3 | 0 | 1

B | 1 | 0 | 2

B | 3 | 0 | 2

B | 7 | 1 | 2.5

B | 7 | 2 | 3

C | 1 | 0 | 1

C | 3 | 0 | 1

In Power Query, I want to detect if there is any Gate 7 for specific Project Name, if yes then will pick up the Value, else will pick up the value of Gate 3. Also, if there is Gate 7, the value to be picked up will be the one with larger Reapproval Number (Project B, Gate 7, Reapproval Number 2, Value 3). Finally will combine all duplicated project names into one common row after above exercise.

[PhilipTreacy]

Hi @AlwaysLearn 

What do you want to do with Projects that don't have a Gate 7?  Keep them?  Discard them?

Regards

Phil

[AlwaysLearn]

@PhilipTreacy thanks for response. Would like to keep Max Gate number, Gate 3.

Thanks.