Reference the maximum value of a column in Power Query

[bartvana]

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...

[#2]

@bartvana 

Before that add step like

maxValue = List.Max(prevStep[Value])

and use it in calculations

[#3]

@bartvana 

here is the sample if hardcode ranges into the formula.

[#4]

Thanks for your reply!

How much of that can be done without the advanced editor?

[#5 · Accepted solution]

@bartvana 

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

[#6]

@Sergei Baklan Could you clarify what you mean by "2) Reference source table" please?

[#7]

@bartvana 

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

[#8]

@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!

[#9]

@bartvana , glad to help