DAX Code to Power Query

[BekahLoSurdo]

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!

[Zubair_Muhammad · Accepted solution]

As a custom column try this

=let mygroup=[Group] in
List. Max(
Table. SelectRows(source, each [Group] =mygroup) [Value])

[BekahLoSurdo]

Perfect, thank you!! This helps so much!