Forum Discussion
Reference the maximum value of a column in Power Query
Hello,
I have a table in a Power Query, and would like to reference the maximum value of another column.
So let's say this is my table in Power Query:
| ID | Value |
1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
| 5 | 50 |
| 6 | 60 |
| 7 | 70 |
| 8 | 80 |
then I'd like to add a Custom Column "Range", with the formula
= if [Value]<0.33*MAX([Value]) then "0-33%"
else if [Value]<0.66*MAX([Value]) then "33-66%"
else "66-100%"but of course "MAX" isn't a formula in Power Query.
The end result should be something like (some ranges may be incorrect, but you get the idea)
| ID | Value | Range |
1 | 10 | 0-33% |
| 2 | 20 | 0-33% |
| 3 | 30 | 0-33% |
| 4 | 40 | 33-66% |
| 5 | 50 | 33-66% |
| 6 | 60 | 33-66% |
| 7 | 70 | 66-100% |
| 8 | 80 | 66-100% |
I'm not a Power Query Power User (as you have guessed), so preferably using functions in the ribbon...
That significantly complicates the task. It could be like:
1) query source table
2) Reference source table, rename as maxValue
3) select in it Value column, Transform->Statistics->Maximum
4) Reference source table, rename reference as Range
5) Add Column -> Custom Column -> name as max -> =maxValue
6) Select Value column -> Add Column -> Standard -> Percentage Of -> from drop-down near Value select Use Value or column -> select max
7) Select Percent Of -> Transform -> Standard -> Divide -> 100
😎 Transform -> Data Type -> Percentage
9) Add Column -> Conditional Column and here combine something like
10) Select columns ID, Value, Range
11) Home -> Remove Columns -> Remove Other Columns (from drop-down menu)
12) Home -> Close & Load To -> Table -> select desired options
8 Replies
here is the sample if hardcode ranges into the formula.
Thanks for your reply!
How much of that can be done without the advanced editor?
That significantly complicates the task. It could be like:
1) query source table
2) Reference source table, rename as maxValue
3) select in it Value column, Transform->Statistics->Maximum
4) Reference source table, rename reference as Range
5) Add Column -> Custom Column -> name as max -> =maxValue
6) Select Value column -> Add Column -> Standard -> Percentage Of -> from drop-down near Value select Use Value or column -> select max
7) Select Percent Of -> Transform -> Standard -> Divide -> 100
😎 Transform -> Data Type -> Percentage
9) Add Column -> Conditional Column and here combine something like
10) Select columns ID, Value, Range
11) Home -> Remove Columns -> Remove Other Columns (from drop-down menu)
12) Home -> Close & Load To -> Table -> select desired options
