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.
