Preferred column value based on another column + conditional blank

Hi PatrickWong ,

According to your description, here's my solution, add a custom column.

if Record.FieldOrDefault(_,Text.From([Preferred route]))<>null then [Preferred route] else Table.ColumnNames(Source){List.PositionOf({[1],[2],[3],[4]},List.Max({[1],[2],[3],[4]}))+1}

Get the correct result:

Here's the whole M syntax, you can copy-paste it in a blank query to see the details:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUtJRMjcAEqZgwgxIAJGxUqxOtFJIUWlyNogHkjI2BRImBhB5E7C8b35JflFyZXJOKlDIDCRlBtJvBFZuDGIpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Model = _t, #"1" = _t, #"2" = _t, #"3" = _t, #"4" = _t, #"Preferred route" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}, {"4", Int64.Type}, {"Preferred route", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "recommend route", each if Record.FieldOrDefault(_,Text.From([Preferred route]))<>null then [Preferred route] else if[1]=List.Max({[1],[2],[3],[4]})then 1 else if [2]=List.Max({[1],[2],[3],[4]}) then 2 else if [3]=List.Max({[1],[2],[3],[4]})then 3 else 4),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if Record.FieldOrDefault(_,Text.From([Preferred route]))<>null then [Preferred route] else Table.ColumnNames(Source){List.PositionOf({[1],[2],[3],[4]},List.Max({[1],[2],[3],[4]}))+1}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Int64.Type}})
in
    #"Changed Type1"

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.