Is there a way to add a cloumn to a table in Power Query that contains the aggregate of a partition of another column?
For example I have:
| Group | Value | Color |
| A | 1 | Red |
| A | 2 | Orange |
| A | 3 | Yellow |
| A | 4 | Green |
| A | 5 | Blue |
| B | 6 | Purple |
| B | 7 | Red |
| B | 8 | Orange |
| B | 9 | Yellow |
| B | 10 | Green |
I need the max Value per Group (without losing the granularity of Color):
| Group | Value | Color | MaxGroupValue |
| A | 1 | Red | 5 |
| A | 2 | Orange | 5 |
| A | 3 | Yellow | 5 |
| A | 4 | Green | 5 |
| A | 5 | Blue | 5 |
| B | 6 | Purple | 10 |
| B | 7 | Red | 10 |
| B | 8 | Orange | 10 |
| B | 9 | Yellow | 10 |
| B | 10 | Green | 10 |
In DAX, I can add a column and use:
MaxGroupValue =
CALCULATE (
MAX ( Table[Value] ),
ALLEXCEPT ( Table, Table[Group] )
)If there a way to do it in Power Query in one add-a-column step? I do not want to do a Group By (lose the Color granularity) and then have to merge back to the original table due to performance concerns.
Thank you!
