SOLVED

Reference the maximum value of a column in Power Query

%3CLINGO-SUB%20id%3D%22lingo-sub-2013899%22%20slang%3D%22en-US%22%3EReference%20the%20maximum%20value%20of%20a%20column%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2013899%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20a%20table%20in%20a%20Power%20Query%2C%20and%20would%20like%20to%20reference%20the%20maximum%20value%20of%20another%20column.%3C%2FP%3E%3CP%3ESo%20let's%20say%20this%20is%20my%20table%20in%20Power%20Query%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%2243.4477355181628%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EID%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EValue%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E1%3C%2FP%3E%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E%3CP%3E30%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E40%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E50%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E60%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E70%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E80%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Ethen%20I'd%20like%20to%20add%20a%20Custom%20Column%20%22Range%22%2C%20with%20the%20formula%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3E%3D%20if%20%5BValue%5D%26lt%3B0.33*MAX(%5BValue%5D)%20then%20%220-33%25%22%26nbsp%3B%0Aelse%20if%20%5BValue%5D%26lt%3B0.66*MAX(%5BValue%5D)%20then%20%2233-66%25%22%0Aelse%20%2266-100%25%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ebut%20of%20course%20%22MAX%22%20isn't%20a%20formula%20in%20Power%20Query.%3C%2FP%3E%3CP%3EThe%20end%20result%20should%20be%20something%20like%20(some%20ranges%20may%20be%20incorrect%2C%20but%20you%20get%20the%20idea)%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22102.29007702744289%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EID%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3EValue%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3ERange%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E%3CP%3E1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E10%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E0-33%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E20%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E0-33%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E%3CP%3E30%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E%3CP%3E0-33%25%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E40%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E33-66%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E50%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E33-66%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E6%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E60%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E33-66%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E70%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E66-100%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E80%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E66-100%25%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EI'm%20not%20a%20Power%20Query%20Power%20User%20(as%20you%20have%20guessed)%2C%20so%20preferably%20using%20functions%20in%20the%20ribbon...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2013899%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2013916%22%20slang%3D%22en-US%22%3ERe%3A%20Reference%20the%20maximum%20value%20of%20a%20column%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2013916%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F863782%22%20target%3D%22_blank%22%3E%40bartvana%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBefore%20that%20add%20step%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3EmaxValue%20%3D%20List.Max(prevStep%5BValue%5D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20use%20it%20in%20calculations%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2013917%22%20slang%3D%22en-US%22%3ERe%3A%20Reference%20the%20maximum%20value%20of%20a%20column%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2013917%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F863782%22%20target%3D%22_blank%22%3E%40bartvana%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ehere%20is%20the%20sample%20if%20hardcode%20ranges%20into%20the%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2014171%22%20slang%3D%22en-US%22%3ERe%3A%20Reference%20the%20maximum%20value%20of%20a%20column%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2014171%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20your%20reply!%3C%2FP%3E%3CP%3EHow%20much%20of%20that%20can%20be%20done%20without%20the%20advanced%20editor%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2014259%22%20slang%3D%22en-US%22%3ERe%3A%20Reference%20the%20maximum%20value%20of%20a%20column%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2014259%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F863782%22%20target%3D%22_blank%22%3E%40bartvana%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20significantly%20complicates%20the%20task.%20It%20could%20be%20like%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1)%20query%20source%20table%3C%2FP%3E%0A%3CP%3E2)%20Reference%20source%20table%2C%20rename%20as%20maxValue%3C%2FP%3E%0A%3CP%3E3)%20select%20in%20it%20Value%20column%2C%20Transform-%26gt%3BStatistics-%26gt%3BMaximum%3C%2FP%3E%0A%3CP%3E4)%20Reference%20source%20table%2C%20rename%20reference%20as%20Range%3C%2FP%3E%0A%3CP%3E5)%20Add%20Column%20-%26gt%3B%20Custom%20Column%20-%26gt%3B%20name%20as%20%3CEM%3Emax%3C%2FEM%3E%20-%26gt%3B%20%3CEM%3E%3DmaxValue%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E6)%20Select%20Value%20column%20-%26gt%3B%20Add%20Column%20-%26gt%3B%20Standard%20-%26gt%3B%20Percentage%20Of%20-%26gt%3B%20from%20drop-down%20near%20Value%20select%20Use%20Value%20or%20column%20-%26gt%3B%20select%20%3CEM%3Emax%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E7)%20Select%20Percent%20Of%20-%26gt%3B%20Transform%20-%26gt%3B%20Standard%20-%26gt%3B%20Divide%20-%26gt%3B%20100%3C%2FP%3E%0A%3CP%3E%3CLI-EMOJI%20id%3D%22lia_smiling-face-with-sunglasses%22%20title%3D%22%3Asmiling_face_with_sunglasses%3A%22%3E%3C%2FLI-EMOJI%3E%20Transform%20-%26gt%3B%20Data%20Type%20-%26gt%3B%20Percentage%3C%2FP%3E%0A%3CP%3E9)%20Add%20Column%20-%26gt%3B%20Conditional%20Column%20and%20here%20combine%20something%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20899px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242708i948BA359A30DBC1C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E10)%20Select%20columns%20ID%2C%20Value%2C%20Range%3C%2FP%3E%0A%3CP%3E11)%20Home%20-%26gt%3B%20Remove%20Columns%20-%26gt%3B%20Remove%20Other%20Columns%20(from%20drop-down%20menu)%3C%2FP%3E%0A%3CP%3E12)%20Home%20-%26gt%3B%20Close%20%26amp%3B%20Load%20To%20-%26gt%3B%20Table%20-%26gt%3B%20select%20desired%20options%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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:

IDValue

1

10
220
3

30

440
550
660
770
880

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)

IDValueRange

1

100-33%
2200-33%
3

30

0-33%

44033-66%
55033-66%
66033-66%
77066-100%
88066-100%

I'm not a Power Query Power User (as you have guessed), so preferably using functions in the ribbon...

8 Replies

@bartvana 

Before that add step like

maxValue = List.Max(prevStep[Value])

and use it in calculations

@bartvana 

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?

best response confirmed by bartvana (Contributor)
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

image.png

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

 

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

@bartvana 

If you right click on any query you will see Reference in the menu

image.png

If click on it new query will be created with reference on first one

image.png

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