12-23-2020 11:56 PM
12-23-2020 11:56 PM
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:
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)
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
Before that add step like
maxValue = List.Max(prevStep[Value])
and use it in calculations
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 AMSolution
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
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!