12-23-2020 11:56 PM
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...
12-24-2020 12:09 AM
12-24-2020 12:29 AM
here is the sample if hardcode ranges into the formula.
12-24-2020 03:31 AM
Thanks for your reply!
How much of that can be done without the advanced editor?
12-24-2020 05:49 AM
SolutionThat 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
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
12-24-2020 10:24 AM
@Sergei Baklan Could you clarify what you mean by "2) Reference source table" please?
12-24-2020 10:31 AM
If you right click on any query you will see Reference in the menu
If click on it new query will be created with reference on first one
12-24-2020 10:47 AM
@Sergei Baklan Fabulous! It was the reference part that I didn't know about, your explanation makes it all very clear now. I reproduced your steps and understand now, thank you!
12-24-2020 11:55 AM
@bartvana , glad to help