Extract max value for every column in a table

Hello AlB 

you can use Table.Profile, then taking the column Column and Max, Transpose it. For the sum take Record.FieldValues and then sum it. Here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlRQ0lEyBRHGIMIEzjUDEQoKSrE60UpGcHmwqDmcZYmkCJt+sEoLJEVgeSNUlRZwMZCiWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column1 " = _t, #"Column2 " = _t, #"Column3 " = _t, #"Column4 " = _t, #"Column5 " = _t, #"Column6 " = _t, #" ....        " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1 ", Int64.Type}, {"Column2 ", Int64.Type}, {"Column3 ", Int64.Type}, {"Column4 ", Int64.Type}, {"Column5 ", Int64.Type}, {"Column6 ", Int64.Type}, {" ....        ", type text}}),
    Table = Table.Profile(#"Changed Type"),
    Select = Table.Transpose(Table.SelectColumns(Table, {"Column", "Max"})),
    #"Promoted Headers" = Table.PromoteHeaders(Select, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{" ....        ", type text}, {"Column1 ", Int64.Type}, {"Column2 ", Int64.Type}, {"Column3 ", Int64.Type}, {"Column4 ", Int64.Type}, {"Column5 ", Int64.Type}, {"Column6 ", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Sum Of Max", (add)=> List.Sum(List.Transform(Record.FieldValues(add), each if Value.Is(_,type number) then _ else Number.FromText(_))))
in
    #"Added Custom"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Hi AlB 

Another solution, but I like Jimmy801 approach with Table.Profile, neat.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIFYmMgNoGyzYDY0MRUKVYnWskIKgcSM4fSliB5I7A0ujaQEguQFiNDsDxIzghJjQWUb2ysFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, ColumnN = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"ColumnN", Int64.Type}}),
    FindMax = List.Transform(Table.ToColumns(#"Changed Type"), each List.Accumulate( _ ,0,(state,current)=>if state>current then state else current)),
    #"Converted to Table" = Table.FromList(FindMax, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    Final = Table.RenameColumns(#"Transposed Table" , List.Zip({Table.ColumnNames(#"Transposed Table"), Table.ColumnNames(#"Changed Type")}))

in
    Final

Phil

Hey AlB - I know you already have two solutions, but I gave it some thought and came up with this. I needed this pattern for another potential project so thought I would work through it as well.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zY65EcAwDMN2Ue0ipPPO4vP+a4TwFC6gCtBxjFK1cujhDFe4wxPe8AUdHEyhClfIwha68EUgClN4/aYwhSlMYQpTmMIU/ajZxi5TcrT27LIlx2vQNmNyes35Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t, Column20 = _t, Column21 = _t, Column22 = _t, Column23 = _t, Column24 = _t, Column25 = _t, Column26 = _t, Column27 = _t, Column28 = _t, Column29 = _t, Column30 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", Int64.Type}, {"Column14", Int64.Type}, {"Column15", Int64.Type}, {"Column16", Int64.Type}, {"Column17", Int64.Type}, {"Column18", Int64.Type}, {"Column19", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column2", "Test"}}),
    varColumnNames = List.Buffer(Table.ColumnNames(#"Renamed Columns")),
    varColumnCount = List.Count(varColumnNames),
    MaxValues = 
        List.Generate(
            ()=> [varMaxValue = List.Max(Table.Column(#"Renamed Columns", Table.ColumnNames(#"Renamed Columns"){0})), varCounter = 0],
            each [varCounter] < varColumnCount,
            each [varMaxValue = List.Max(Table.Column(#"Renamed Columns", Table.ColumnNames(#"Renamed Columns"){varCounter})), varCounter = [varCounter] + 1],
            each [varMaxValue]

        ),
    MaxTable = Table.FromColumns({MaxValues}),
    #"Transposed Table" = Table.Transpose(MaxTable, Table.ColumnNames(#"Renamed Columns"))
in
    #"Transposed Table"

Turns this:

to this:

Surprisingly it worked on the text values in your columns 23-33. I was expecting List.Max to fail there, but it it didn't.

Hi, AlB , you might want to try this single-line solution in spite that this thread is already marked "solved",

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zY65EcAwDMN2Ue0ipPPO4vP+a4TwFC6gCtBxjFK1cujhDFe4wxPe8AUdHEyhClfIwha68EUgClN4/aYwhSlMYQpTmMIU/ajZxi5TcrT27LIlx2vQNmNyes35Aw==", BinaryEncoding.Base64), Compression.Deflate))),
    #"Changed Type" = Function.Invoke(Table.TransformColumnTypes, {Source, List.Accumulate(Table.ColumnNames(Source), {}, (s,c)=>s&{{c,Int64.Type}})}),

    Solution = Table.FromColumns(List.Transform(Table.ToColumns(#"Changed Type"), each {List.Max(_)}), Table.ColumnNames(Source))
in
    Solution