Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Perfect, thank you!! This helps so much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |