Powerquery - How to extract a row value based on conditional logic comparing multiple cells

Question

I have a data extract that will refresh with an unknown number of columns with data similar to below screenshot. The column name "Custom" will always be present but the other columns names are dynamic. I am looking to create the following logic

1. Convert all column names apart from Custom column to type "Decimal"
2. Replace null values with "" from all the columns in step 1 above
3. Convert errors to ""
4. Create a new column where the value for each row will be to take the highest value found in any of the columns apart from the "Custom" column.
   Hence in below example the value, for row 79, the value extracted to the new column will be 1558.48. The "1 EA" appearing in row 80 will appear as "" due to step 4 above
   

Answer 1

You cannot have a "" in a column that is of Decimal.Type without returning an error. "" cannot be numeric. You must leave the nulls as null, and convert the error to null, or leave the type as any.

Leaving nulls as null and converting errors to null:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Decimal Columns" = List.RemoveItems(Table.ColumnNames(Source),{"Custom"}),
    #"Change Types" = Table.TransformColumnTypes(Source, {
        {"Custom", Int64.Type}} 
        & List.Transform(#"Decimal Columns", each {_, Decimal.Type})),

    #"Replace Errors" = Table.ReplaceErrorValues(#"Change Types", List.Transform(#"Decimal Columns", each {_,null})),

    #"Max Data" = Table.AddColumn(#"Replace Errors","Max Data", (r)=>
        List.Max(Record.FieldValues(Record.RemoveFields(r,{"Custom"}))), Decimal.Type)
in
    #"Max Data"

Source

Max Data