SOLVED

Contributor

# Reference the maximum value of a column in Power Query

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

8 Replies

# Re: Reference the maximum value of a column in Power Query

``maxValue = List.Max(prevStep[Value])``

and use it in calculations

# Re: Reference the maximum value of a column in Power Query

here is the sample if hardcode ranges into the formula.

# Re: Reference the maximum value of a column in Power Query

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

best response confirmed by bartvana (Contributor)
Solution

# Re: 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

# Re: Reference the maximum value of a column in Power Query

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

# Re: Reference the maximum value of a column in Power Query

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

# Re: Reference the maximum value of a column in Power Query

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