Max Value between multiple column for each row in a Table

Hi shahzadmkd,

You can try to use power query to achieve your requirement.

Prerequisite: table contains index column.

Logic: Remove other columns which not used to compared, convert current row to list and use list.max function to get max value.

Sample:

 Add custom column 'max value'.

 #"Added Custom" = Table.AddColumn(#"Changed Type", "Max Value", each List.Max(Record.ToList(Table.RemoveColumns(#"Changed Type","Index"){[Index]})))

Result:

Full query:

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Desktop\sample tab.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Index", Int64.Type}, {"C1", Int64.Type}, {"C2", Int64.Type}, {"C3", Int64.Type}, {"C4", Int64.Type}, {"C5", Int64.Type}, {"C6", Int64.Type}, {"C7", Int64.Type}, {"C8", Int64.Type}, {"C9", Int64.Type}, {"C10", Int64.Type}, {"C11", Int64.Type}, {"C12", Int64.Type}, {"C13", Int64.Type}, {"C14", Int64.Type}, {"C15", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Max Value", each List.Max(Record.ToList(Table.RemoveColumns(#"Changed Type","Index"){[Index]})))
in
    #"Added Custom"

Notice:
1. if table contains multiple not needed columns, use {"Column Name1","Column Name2",...} to replace above "Index" part.
2. You only need to modify "Index" part to keep only number columns calculated in this formula.

Regards,

Xiaoxin Sheng