Dec 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...
Dec 24 2020 12:09 AM
Dec 24 2020 12:29 AM
here is the sample if hardcode ranges into the formula.
Dec 24 2020 03:31 AM
Thanks for your reply!
How much of that can be done without the advanced editor?
Dec 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
8) 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
Dec 24 2020 10:24 AM
@Sergei Baklan Could you clarify what you mean by "2) Reference source table" please?
Dec 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
Dec 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!
Dec 24 2020 11:55 AM
@bartvana , glad to help
Dec 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
8) 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