PQ: Max of absolute values in across columns

[DRSteele]

Can someone please help me in Power Query?

I would like a custom column to report the maximum magnitude of values per record, in other words I would like the max absolute values. The column names are on row 30. It seems easy in Excel, but I can't figure out the syntax in PQ.

	A	B	C	D	E	F	G	H	I
Forecast Day	Max Magnitude

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]30[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]8/23/2017[/TD]
[TD="align: right"]-2.1[/TD]
[TD="align: right"]-0.1[/TD]
[TD="align: right"]-0.1[/TD]
[TD="align: right"]-1.1[/TD]
[TD="align: right"]-1.1[/TD]
[TD="align: right"]-3.1[/TD]
[TD="align: right"]-6.1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]6.1[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]8/24/2017[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]-3.5[/TD]
[TD="align: right"]-2.5[/TD]
[TD="align: right"]-2.5[/TD]
[TD="align: right"]-4.5[/TD]
[TD="align: right"]-0.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]4.5[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]8/25/2017[/TD]
[TD="align: right"]-0.2[/TD]
[TD="align: right"]-0.2[/TD]
[TD="align: right"]-0.2[/TD]
[TD="align: right"]-1.2[/TD]
[TD="align: right"]-1.2[/TD]
[TD="align: right"]-1.2[/TD]
[TD="align: right"]-1.2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1.2[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]8/26/2017[/TD]
[TD="align: right"]-1.8[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]-0.8[/TD]
[TD="align: right"]-1.8[/TD]
[TD="align: right"]-2.8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]2.8[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]8/27/2017[/TD]
[TD="align: right"]-2.1[/TD]
[TD="align: right"]-1.1[/TD]
[TD="align: right"]-2.1[/TD]
[TD="align: right"]-1.1[/TD]
[TD="align: right"]-1.1[/TD]
[TD="align: right"]-3.1[/TD]
[TD="align: right"]-5.1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]5.1[/TD]

</tbody>

Query2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I31[/TH]
[TD="align: left"]{=MAX(ABS(B31:H31))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[citizenbh]

You can add a column for Max by first selecting the columns 1..7 and then on the Add Column tab > Statistics > Max
and manual add function Number.ABS for all columns [1]..[7] in step #"Inserted Maximum" or copy next code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"no", Int64.Type}, {"Forecast Day", type text}, {"1", type number}, {"2", type number}, {"3", type number}, {"4", type number}, {"5", type number}, {"6", type number}, {"7", type number}}),
    #"Inserted Maximum" = Table.AddColumn(#"Changed Type", "Max", each List.Max({Number.Abs([1]), Number.Abs([2]), Number.Abs([3]), Number.Abs([4]), Number.Abs([5]), Number.Abs([6]), Number.Abs([7])}), type number)
in
    #"Inserted Maximum"

or shorter use List.Min function and Number.Abs around List.Min

 

[DRSteele]

Thank you very much. I got it to work in my actual code, even though Add Column tab > Statistics > Max was greyed out.

But I don't understand why List.Min combined with Number.Abs would provide the maximum magnitude. Can you elaborate a bit for me?

 

[citizenbh]

All numers is negative. Number.Abs(Min)=Max

 

[DRSteele]

Some numbers are positive and some are zero. I wanted the largest absolute values, which I think we did.

 

[citizenbh]

Then the first variant!

 

[MarcelBeug]

If the values are in the last 7 columns before adding the custom column, you can use:

let
    Source = Table1,
    #"Added Custom2" = Table.AddColumn(Source, "Max Magnitude", each List.Max(List.Transform(List.LastN(Record.FieldValues(_),7),Number.Abs)))
in
    #"Added Custom2"

 

[DRSteele]

Terrific, thanks. I tried nesting those functions but I got all confused. It's too bad PQ doesn't have such a simple and obvious way to find the max, min or average absoute value.