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
30	Forecast Day	1	2	3	4	5	6	7	Max Magnitude
31	8/23/2017	-2.1	-0.1	-0.1	-1.1	-1.1	-3.1	-6.1	6.1
32	8/24/2017	-1.5	-1.5	-3.5	-2.5	-2.5	-4.5	-0.5	4.5
33	8/25/2017	-0.2	-0.2	-0.2	-1.2	-1.2	-1.2	-1.2	1.2
34	8/26/2017	-1.8	0.2	0.2	0.2	-0.8	-1.8	-2.8	2.8
35	8/27/2017	-2.1	-1.1	-2.1	-1.1	-1.1	-3.1	-5.1	5.1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Query2​

Array Formulas Cell Formula I31 {=MAX(ABS(B31:H31))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

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