Forum Discussion
Reference the maximum value of a column in Power Query
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
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
SergeiBaklan Could you clarify what you mean by "2) Reference source table" please?
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