Compare columns return maximum power query

Question

I have data from multiple suppliers which I wish to compare. The data shown in the image below has been previously transformed via a series of steps using power query. The final step was to pivot the Supplier column (in this example consisting of X,Y,Z) so that these new columns can be compared and the maximum value is returned.

How can I compare the values in columns X, Y and Z to do this? Importantly, X Y and Z arent necessarily the only suppliers. If I Add Say A as a new supplier to the original data, a new column A will be generated and I wish to include this in the comparison so that at column at the end outputs the highest value found for each row. So reading from the top down it would read in this example: 3,3,1,1,5,0.04,10 etc.

Thanks

Link to file https://onedrive.live.com/?authkey=%21AE_6NgN3hnS6MpA&id=8BA0D02D4869CBCA%21763&cid=8BA0D02D4869CBCA

M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", type text}, {"Residual Solvents", type text}, {"RMQ", type text}, {"COA", type text}, {"Technical Data Sheet", type text}}),
    
    //Replace Time and null with blank
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","00:00:00","",Replacer.ReplaceText,{"Material", "RMQ", "Residual Solvents", "Technical Data Sheet", "COA"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"",Replacer.ReplaceValue,{"Material", "RMQ", "Residual Solvents", "Technical Data Sheet", "COA"}),
    
    //Trims all whitespace from user
    #"Power Trim" = Table.TransformColumns(#"Replaced Value1",{{"Material", #"PowerTrim", type text}, {"Residual Solvents", #"PowerTrim", type text}, {"RMQ", #"PowerTrim", type text}, {"COA", #"PowerTrim", type text}, {"Technical Data Sheet",#"PowerTrim", type text}}),
  
    //Unpivot to develop a single column of solvent/metals/date data
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Power Trim", {"Material", "Supplier"}, "Attribute", "Value"),

    //split into rows by line feed
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Unpivoted Other Columns", 
        {{"Value", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Value", Text.Trim, type text}}),

    //filter out the blank rows
    #"Filtered Rows" = Table.SelectRows(#"Trimmed Text", each ([Value] <> "" and [Value] <> "Not Provided")),

    //Add custom column for separating the tables
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each try Date.FromText([Value]) otherwise 
        if [Value] = "Heavy Metals" or [Value] = "Residual Solvents" or [Value] = "Other" then [Value] else null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type1",{"Custom"}),

    //Filter the value and custom columns to remove contaminant type from Value column and remove dates from Custom column
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([Custom] = "Heavy Metals" or [Custom] = "Residual Solvents") and ([Value] <> "Heavy Metals" and [Value] <> "Residual Solvents")),

    //split substance from amount
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Filtered Rows1", "Value", 
        Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Substance", "Amount"}),
    //Filter for Solvents Table
    #"Filtered Rows2" = Table.SelectRows(#"Split Column by Delimiter1", each ([Custom] = "Heavy Metals")),
    #"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows2",{{"Amount", type number}}),
    
    //Group by Material and Substance, then extract the Max contaminant and Source
    #"Grouped Rows" = Table.Group(#"Changed Type2", {"Substance","Material", "Supplier"}, {
        {"Amount", each List.Max([Amount]), type number},
        {"Source", (t) => t[Attribute]{List.PositionOf(t[Amount],List.Max(t[Amount]))}, type text}
        }),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Substance", Order.Ascending}}),


    //PIVOT to compare suppliers
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Supplier]), "Supplier", "Amount", List.Sum)
in
    #"Pivoted Column"

Answer 1

• Add an Index Column starting with zero (0).
• Add a Custom Column:

=List.Max(
Record.ToList(
Table.SelectColumns(#"Added Index",
List.RemoveFirstN(
    Table.ColumnNames(#"Pivoted Column"),3)){[Index]}))

• Then remove the Index column

Algorithm

• Generate a list of the relevant column names to sum.
  • We exclude the first three column names from this list
  • Note that we refer to the step PRIOR to adding the Index column for the list of column names. If we referred to the actual previous step where we added the Index column, we'd also have to remove the Last column name
  • Select the relevant columns
  • {[Index]} will return a record corresponding to the Index number.
  • Convert the record to a list and use List.Max